Results 1 to 8 of 8
  1. #1
    slaterino is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    25

    How to bring up a record using values from 2 combo boxes

    Hi,
    Can someone help me put together this small VB sub. I need a way that once I click a button, a form will find a specific record using two values from two different combo boxes.

    The source of the form is tblSrvRspns. This table includes the fields RspnsID, SrvID and URN. My two combo boxes are cboSrvID and cboURN. They are used to choose the SrvID and URN values desired.

    What can I do to make the form go to the particular record using the unique RspnsID once the command button has been pressed. I know I will be using the OnClick event but don't know what function to use. I have used FindRecord before but only when searching for one value. Is it possible to use it to search for two or is there another way of doing this?



    Thanks
    Russ

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Where ([Field1] = Me.cbo1) AND ([Field2] = Me.cbo2)
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    slaterino is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    25
    Thanks for your help! I'm almost there but having a few problems just getting the final code. I don't know why I find Visual Basic so hard to code.

    Anyway, this is my current code, but I am getting an error message on the DoCmd line. I'm presuming this must be because my strFind line is incorrect but I have tried a few different combinations and had no luck yet. Is this how I would use the where statement or do I need to use it differently?

    Else
    Dim strFind As String
    strFind = "[RspnsID] Where ([SrvID] = Me!cboSrvID) AND ([URN] = Me!cboURN)"
    DoCmd.FindRecord strFind, acEntire, , , , , False
    End If

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Try:
    strFind = "[RspnsID] Where ([SrvID] = " & Me!cboSrvID & ") AND ([URN] = " & Me!cboURN & ")"
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    slaterino is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    25
    Hey,
    Have just tried your suggestion but still the same response; Run-time Error 2162, with the error pointing at the FindRecord line.

    Is there any other way of doing this that you know? Would I have any more luck with DoCmd.GoToRecord?

    Thanks
    Russ

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    After posting I realized you were using FindRecord which I have never used. If it is in the RecordSource of the current form then I would use FindFirst.
    Code:
    ' Find the record that matches the control.
    Dim strFind As String
    strFind = "[RspnsID] Where ([SrvID] = " & Me!cboSrvID & ") AND ([URN] = " & Me!cboURN & ")" 
    Me.RecordsetClone.FindFirst strFind
    If Not Me.RecordsetClone.NoMatch Then
        Me.Bookmark = Me.RecordsetClone.Bookmark
    Else
        MsgBox "Could not find!"
    End If
    Actually I'm not sure that works either.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Maybe something like:
    Code:
    ' Find the record that matches the controls.
    Dim strFind As String
    strFind = "[SrvID] = " & Me!cboSrvID & " AND [URN] = " & Me!cboURN  
    Me.RecordsetClone.FindFirst strFind
    If Not Me.RecordsetClone.NoMatch Then
        Me.Bookmark = Me.RecordsetClone.Bookmark
    Else
        MsgBox "Could not find!"
    End If
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I feel confident the following code will do what you want.
    Code:
    ' Find the record that matches the controls.
       Dim strFind As String
       '-- Use DLookup() to locate the unique [RspnsID] we need
       strFind = DLookup("[RspnsID]", "tblSrvRspns", "([SrvID] = " & Me!cboSrvID & ") AND ([URN] = " & Me!cboURN & ")")
       '-- Now locate the record
       Me.RecordsetClone.FindFirst "[RspnsID] = " & strFind
       If Not Me.RecordsetClone.NoMatch Then
          Me.Bookmark = Me.RecordsetClone.Bookmark
       Else
          MsgBox "Could not find!"
       End If
    It may not be the fastest way, but it will work until we think of a better way.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. Replies: 3
    Last Post: 01-30-2012, 09:43 AM
  2. Replies: 3
    Last Post: 02-26-2009, 10:17 AM
  3. Problem with Cascading Combo Boxes
    By CushingMT in forum Forms
    Replies: 0
    Last Post: 11-13-2008, 09:44 AM
  4. combo boxes
    By labrams in forum Forms
    Replies: 0
    Last Post: 04-20-2006, 09:28 AM
  5. Combo Boxes
    By Mxcsquared in forum Forms
    Replies: 0
    Last Post: 01-19-2006, 04:59 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