Share via

VBA stLinkCriteria that will open a specific form record based on two sets of criteria

Anonymous
2013-06-11T12:42:20+00:00

Hello,

I am having trouble trying to use a button that will link back to the original entry form for a specific record. Here are some specifics of the database:

Form1[FamiliyEntry]; field1[LastName]; populates table[AllLastNames]

Form1Subform[Info]; field1[FirstName, field2[Age], etc...; populates table [AllInfo]

Form2[DisplayBoard]; a query is used to display a small portion of the fields from Form1 and Form2.

Form2 is mostly used as a tracking tool. The layout is:

[LastName][FirstName][FinishTime][Button]

Some of the fields that are displayed can be edited. The changes made are reflected in the Form1Subform. I have added a button (displays a separate button for each record) that will open Form1 (including the subform) so that the fields that are not visible can be edited, but the problem is trying to open the specific record that corresponds to the button that is clicked at the end of the specific record.

This is how the stLinkCriteria looks:

stLinkCriteria = "[LastName]=" & "'" & Me![LastName] & "'"

This opens the last record entered that matches the [LastName] field that the button correspond with; however it does not match the first name of that record. I would like to have the record open that matched the [LastName] and [FirstName]. I found some suggestions in other forums and one of the solutions was:

stLinkCriteria = "([LastName] = '" & Me![LastName] & "') And ([FirstName] = " & Me![FirstName] & ")"

This yields a "Enter Parameter Value" pop up box and instead of having a field listed above the text entry box, the first name for that record is listed. If I type the first name into the text entry box, the correct record does open. I would like for the button to open the correct record without having to enter a value. Can someone please let me know if this is possible and how to make it possible?

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2013-06-13T17:16:30+00:00

    We need to see the Recordsource for the form. But you can try:

    stLinkCriteria = "[Classroom]=" & Me![Classroom] & " AND[CourseNumber]=" & Me![CourseNumber]

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-06-13T13:35:46+00:00

    I completly agree with you on the fact that last name should not be used. The actual names of my fields are different than what I listed, i probably should have mentioned that. The example fields I gave were to make it simple, so I will try to explain it in a different way.

    Form1[Classroom(primary, no duplicates allowed]; field1[RoomNumber]; populates table[Classrooms]

    Form1Subform[ClassInfo]; field1[CourseNumber, field2[DaysAvailable], field3[TimesAvailable]; populates table [ClassInfo]

    Form2[DisplayBoard]; a query of table[Classrooms] and table[ClassInfo] is used to display:

                 [Classroom]   [CourseNumber]   [ViewCourseInfo(button)]

    LN1       RM 210          MATH 100             CLICK HERE FOR INFO

    LN2       RM 210          ENG 215               CLICK HERE FOR INFO

    LN3       RM 150          SCI 125                 CLICK HERE FOR INFO

    When the "CLICK HERE FOR INFO" button is clicked I would like it to open the record for that line. For example if you clicked the button for line 2 it would open the record for [Classroom]RM 210, [CourseNumber] ENG 215. Currently, if that button is clicked it will open a record for [Classroom] RM 210, however not for the correct [CourseNumber]. It always opens the last record entered that matches the [Classroom].

    stLinkCriteria = "[Classroom]=" & Me![Classroom] opens the correct record for [Classroom] but I need it to also match the correct [CourseNumber]. So something like,

    stLinkCriteria = "[Classroom]=" & Me![Classroom] AND "[CourseNumber]=" & Me![CourseNumber]

    Thats the only part I cannot figure out, any ideas? Again these are only examples.

    Thank you for the prompt response.

    Was this answer helpful?

    0 comments
  3. ScottGem 68,810 Reputation points Volunteer Moderator
    2013-06-11T15:15:42+00:00

    You shouldn't be using last Name, it may not be unique. Table AllLastNames should have a primary key field, preferably an autonumber. Table AllInfo should have a foreign key to store that value. The subform should be linked to the main from on those 2 fields. 

    From there, stLinkCriteria would be assigned as:

    stLinkCriteria = "[AllLastNamesID]=" & Me![allLastnamesID]

    Was this answer helpful?

    0 comments