Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    23

    Unanswered: Search for specific record using VBA in a subform

    Hi all,

    I have a company database (Access 2003) with two sub forms, one an entry form to add members of staff against the company the second is a summary that shows all the staff for the comany in a datasheet view.

    The summary view isn't editable but the entry form is so Im trying to write some VBA so that I can doubdle click the Staff ID number in the summary form and it will find the correct record in the entry subform.

    So far this is what I have
    _________________________________________

    Private Sub Staff_ID_DblClick(Cancel As Integer)
    Dim Staff_ID As Long

    ''Set focus on summary subform staff ID
    Form_StaffList.Staff_ID.SetFocus


    ''Variable to hold summary subform staff ID number
    StaffNo = Staff_ID

    ''Setting focus to relevant filed in editable subform
    Form_Main.Staff.SetFocus
    Form_Staff.Staff_ID.SetFocus

    ''Find Staff record
    DoCmd.FindRecord StaffNo, , , , , acCurrent

    End Sub

    ______________________________________________

    The problem I have is I have no idea if the variable has the correct data if any and Im not sure if the search is working.

    The code certainly selects the required field but no the required record.

    Any help appreciated.

  2. #2
    Join Date
    Apr 2011
    Posts
    23
    Its the variable, just changed "StaffNo = Staff_ID" to "StaffNo = 501" and it works fine. Any help with saving the ID number as the variable would be appreciated.

  3. #3
    Join Date
    Apr 2011
    Posts
    23
    Scratch that working fine now.

    Code -

    _______________________________________

    Private Sub Staff_ID_DblClick(Cancel As Integer)
    Form_StaffList.Staff_ID.SetFocus

    StaffNo = Staff_ID
    Form_Main.Staff.SetFocus
    Form_Staff.Staff_ID.SetFocus
    DoCmd.FindRecord StaffNo, , , , , acCurrent

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •