I need my Subform to go to specific record identified in an unbound form

[dbasegirl]

Hello, I tried to figure this out. I really need this database to work! I have the main form called FRM_All Visitors, the subform in the main form is called frm_VRINfoSub. I created a separate form named FRM_Active Personnel based on a query that identifies all the active personnel (Name & Authorization Info) that are currently active for access in different areas and some personnel may have at least 4 authorizations under their name. So I created a button on the separate form (FRM_Active Personnel) that when pressed will open the main form (FRM_All Visitors) and have the subform (Frm_VRInfoSub) either find or goto the identified record which is named the VR_Visitor_ID. I have the VR_Visitor_ID in the subform (frm_VRINfoSub) and the separate form (FRM_Active Personnel). I am unable to link both forms with the VR_Visitor_ID. At this time, the form will open the main form and the first record in the subform based on the Primary_Visitor ID. My goal in creating the separate form (FRM_Active Personnel) was to enable to user to go directly to the active identified record in the subform to check all the other information before badging the visitors instead of them scrolling through the records. I know it is my coding because I am not identifying the separate form. I came across the coding the for ProcError because I kept getting the 2105 Error. Thank you for your assistance.

Private Sub cmdGO_TO_RECORD_Click()
On Error GoTo ProcError


Dim sWhere As String

sWhere = "Primary_Visitor_ID=" & Me!Primary_Visitor_ID
DoCmd.OpenForm "FRM_All Visitors", , , sWhere

Forms![FRM_All Visitors]![frm_VRInfoSub].SetFocus
sWhere = "VR_Record_ID=" & Me!VR_Record_ID
DoCmd.GoToRecord , , acNext
ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2105
MsgBox "Only 1 record in this list.", _
vbInformation, "End of the list..."
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in NextPP_Click procedure..."
End Select
Resume ExitProc

End Sub

[June7]

I wonder if a form/subform is really necessary. I would like to analyze and debug the db. Can provide it by following instructions at bottom of my post.

[dbasegirl]

Hi June7,
Thank you for your help, but I believe a subform is needed the one person will have many records active and inactive. We have to keep them on file for at least 2 years. I await your response. Thanks! dbasegirl

[June7]

Why are you collecting SSN? Non-US visitors would not even have an SSN. Why is this identification required from a visitor? I presume these names are fake.

The db opens to frmVisitorMainScreen1. What should I do from here?

[dbasegirl]

June7
All the information is fake and we do not deal with Non-US Citizens. If you just select ACTIVE or INACTIVE button, it will open the form that I am having issues with the GO TO RECORD button. This database will be encrypted on a secure network. I am only having issues with this section of the database. The Visitor Main Screen is a way to search out records.

[dbasegirl]

June7, the fake dbase that I provided is not the right one. I need to send you another one. Please delete the one you have. thanks. Dbasegirl

[June7]

Error handler procedures always get in the way of debugging. I prefer not to activate error handler until procedure works as I expect it to.

Why don't you open frm_VRInfoSub as a standalone filtered to VR_Record_ID?

Why two visitor tables? You have them joined on Primary_Visitor_ID and Primary_Visitor_LastName in Relationships builder which won't work because one is autonumber and other is text. In spite of the Relationship link, a query definitely fails with this link.

However, the forms are linked on a compound join of LastName and FirstName fields. Names make very poor keys. You should have a number field in tbl_VR_New for the Primary_Visitor_ID and link the tables and forms on the numeric keys. Then should not duplicate primary visitor name into tbl_VR_New.

I see a Kim Lott in TBL_VR_New but there is only a Maria Lott in TBL_ALL_Visitors - why?

[dbasegirl]

June 7, please do not re-post this database back on the forum. Both forms are linked by the Primary_Visitor_ID. The VR_Record_ID is not identified in the main so I could not say open where VR_REcord_ID = me!VR_REcord_ID? I should only have them linked by Primary_Visitor_ID. I realize that the name is not a good way to join. Looks like I need to go back the drawing board.

[dbasegirl]

I used the error handler because I did not understand the error code 2105.

[dbasegirl]

June7, I am totally scrapping this database. I noticed that when I updated the New VR table and changed the name, it did not update the subform. This is a mess. That is why you see Kim Lott in TBL_VR_New but there is only a Maria Lott in TBL_ALL_Visitors. Do not waste anymore of your time. I will set it up the way advised to above. Working all weekend again.