On Click Event Procedure

[MrRichDean]

Ok, form question for you.

I have a form that uses several buttons that when clicked, will send the user to a new form. Fine, lovely stuff. What I would like to do, is set up the on click so that it takes one of the fields on the current record on the one form into account and uses this as a reference to go to the second form.

So for example, I have Form 1 featuring client ID, client name, staff member as fields. On Form 2, I have staff member, address, telephone number.

If I click on the button in Form 1, in this case, staff details, this would take the user to Form 2 but focus on look at the staff member currently present in Form 1.

Currently the syntax is this:

Private Sub Current_Staff_Detail_Click()
On Error GoTo Err_Current_Staff_Detail_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "tblIndividualStaff"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Current_Staff_Detail_Click:
Exit Sub
Err_Current_Staff_Detail_Click:
MsgBox Err.Description
Resume Exit_Current_Staff_Detail_Click

End Sub

I strongly suspect I have to insert an "On Current" focus or something like that but being a bit thick, I 'm npot sure how to proceed.

If anyone can assist me in this, I will be extremely grateful

Rich

 

[dhookom]

You should filter the Form 2 to the appropriate record. The command button wizard to open a form generally allows you to do this. Generically the code might look like:

Private Sub Current_Staff_Detail_Click()
    On Error GoTo Err_Current_Staff_Detail_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
 [Green]   'assuming a field StaffMember in the new form
    'assuming a text box txtStaffMember in the current form
    'assuming StaffMember is a numeric field
 [/Green]    stLinkCriteria = "[StaffMember]=" & Me.txtStaffMember
    stDocName = "tblIndividualStaff"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Current_Staff_Detail_Click:
    Exit Sub
Err_Current_Staff_Detail_Click:
    MsgBox Err.Description
    Resume Exit_Current_Staff_Detail_Click
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[MrRichDean]

Hello dhookom

Yes I was being more than a bit dim and ignoring the option in the command button wizard.

Typically Tuesday thick moment from me!

But thatnk you for the syntax, makes sense.