Results 1 to 6 of 6
  1. #1
    Lxmanager is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    24

    Using a Combo Box to find a record on another form

    Hi Guys, please could someone help? I have a form on which I have put a combo box and button on to find records, it has the following code and works well..

    Private Sub cmdGotoRecord_Click()
    On Error GoTo Err_cmdGotoRecord_Click

    Dim strReport As Integer
    Dim tmp As Integer

    strReport = Me.cboSelectRptNo

    'abort if number not selected
    If Len(Trim(strReport)) = 0 Then
    MsgBox "Report number Not Selected"
    End If

    'get current ReportNo
    tmp = Me.ReportNo
    Me.ReportNo.Enabled = True
    Me.ReportNo.SetFocus
    DoCmd.FindRecord strReport
    ' move to some other control to
    ' be able to be able to set


    ' ReportNo Enabled to false
    Me.cmdGotoRecord.SetFocus
    Me.ReportNo.Enabled = False
    'check if the report no. was found and
    If (tmp = Me.ReportNo) And (Me.ReportNo <> strReport) Then
    MsgBox "Report number " & strReport & " Not found"
    End If

    Exit_cmdGotoRecord_Click:
    Exit Sub
    Err_cmdGotoRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdGotoRecord_Click
    End Sub

    The problem is, I have a switchboard on which I have put a similar combo and button to find a record and to open the form and display the record, for this I altered the original code to..

    Private Sub cmdGotoActRecord_Click()
    On Error GoTo Err_cmdGotoActRecord_Click

    Dim strReport As Integer
    Dim tmp As Integer

    strReport = Me.cboSelectAccAct

    'abort if number not selected
    If Len(Trim(strReport)) = 0 Then
    MsgBox "Report number Not Selected"
    End If

    'get current ReportNo
    tmp = Forms!frmInspSht!ReportNo
    Forms!frmInspSht!ReportNo.Enabled = True
    Forms!frmInspSht!ReportNo.SetFocus
    DoCmd.FindRecord strReport
    ' move to some other control to
    ' be able to be able to set
    ' ReportNo Enabled to false
    Forms!frmInspSht!cmdGotoActRecord.SetFocus
    Forms!frmInspSht!ReportNo.Enabled = False
    'check if the report no. was found and
    If (tmp = Forms!frmInspSht!ReportNo) And (Forms!frmInspSht!ReportNo <> strReport) Then
    MsgBox "Report number " & strReport & " Not found"
    End If

    Exit_cmdGotoActRecord_Click:
    Exit Sub
    Err_cmdGotoActRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdGotoActRecord_Click

    End Sub

    When I select the record in the combo and click the button I get the following messege

    Microsoft cannot find the referenced file 'frmInspSht'

    What have i done wrong??

    Thanks Andy

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Let's learn how to debug. Comment out this line:

    On Error GoTo Err_cmdGotoActRecord_Click

    and run the code. Now when it errors you'll be offered the option to debug, which will take you to the offending line. If that doesn't help you figure out the error, post back with what line it is.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Lxmanager is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    24
    Thanks for the reply, Im still new to this VBA.

    It errors out on line

    tmp = Forms!frmInspSht!ReportNo

    I know its the way Im calling up the form frmInspSht. It could be that the original code will only work with a combo button on the same form. Im not shure if it needs to be a different code when the search combo is on a different form.

    Can't figure it out. Could someone suggest the correct code?

    Thanks

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You do know that the frmInspSht form *must* be open for this to work, correct?

  5. #5
    Lxmanager is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    24
    Your dead right RuralGuy, Ive added a line to open the "shtInspSht" form at the start and it now works ok.

    I knew it had to be something simple,, just like me Doh....

    I will catch up eventually guys.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I think you are ready to follow the link in my sig and mark this thread as Solved; what do you think?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA: Find Record from in table from form
    By shimmy84 in forum Programming
    Replies: 6
    Last Post: 03-19-2012, 10:51 PM
  2. Find as you type Combo boxes in MS Access
    By HAPPYWITHU in forum Programming
    Replies: 13
    Last Post: 06-03-2010, 08:41 AM
  3. Replies: 4
    Last Post: 07-22-2009, 02:33 PM
  4. Replies: 1
    Last Post: 05-16-2009, 08:47 AM
  5. How to Find a Record
    By lschlitt in forum Programming
    Replies: 2
    Last Post: 09-09-2006, 06:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums