Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    15

    Unanswered: Finding a record

    I am having trouble finding the record I want on a form.

    My form contains 2 combo boxes, a listbox, and 4 other text boxes. When the user selects a value from each of the combo boxes, a query is run to determine the listboxes contents. The listbox has 2 columns - one is hidden as it is the key field, the other is the boxnumber. The query is working fine and displays the correct result/s (ie the boxnumber), however I want to find the first record based on the code (hidden key field) as opposed to the boxnumber which it is currently doing. Can anybody please help me with this?

    I have tried changing the bound coulmn to 2, and changing my code to:
    rs.FindFirst "[code] = '" & Me![List14] & "'"
    It gave the following error data type mismatch in criteria expression.

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Finding a record

    Originally posted by JCooke
    I am having trouble finding the record I want on a form.

    My form contains 2 combo boxes, a listbox, and 4 other text boxes. When the user selects a value from each of the combo boxes, a query is run to determine the listboxes contents. The listbox has 2 columns - one is hidden as it is the key field, the other is the boxnumber. The query is working fine and displays the correct result/s (ie the boxnumber), however I want to find the first record based on the code (hidden key field) as opposed to the boxnumber which it is currently doing. Can anybody please help me with this?

    I have tried changing the bound coulmn to 2, and changing my code to:
    rs.FindFirst "[code] = '" & Me![List14] & "'"
    It gave the following error data type mismatch in criteria expression.
    You can use the columns property to refer to a particular column.

    rs.FindFirst "Code = '" & List14.Columns(0, List14.ListIndex) & "'"

    By the way, the bound column in the property sheet that is set to 1, is referred to as Column(0). It's an Access quirk.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  3. #3
    Join Date
    Feb 2004
    Posts
    15

    Re: Finding a record

    Thanks I tried that, but the same error appears:
    data type mismatch in criteria expression.
    Any idea what this means?
    I've set rs as object.

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Finding a record

    Originally posted by JCooke
    Thanks I tried that, but the same error appears:
    data type mismatch in criteria expression.
    Any idea what this means?
    I've set rs as object.
    What type of data is the Code column? Remove the single quotes if it is not a string.


    rs.FindFirst "Code = " & List14.Columns(0, List14.ListIndex)
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  5. #5
    Join Date
    Feb 2004
    Posts
    15
    Thanks again, but I'm afraid it doesn't work.
    Code is an autonumber.
    I tried your replacing with your line of code,
    but the following error appears:
    Syntax error (missing operator) in expression

    This is the code:

    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone

    rs.FindFirst "Code = " & List1.Column(1, List1.ListIndex)
    Me.Bookmark = rs.Bookmark

    I've tried changing the column number and the rs as object, etc.
    Any idea what it is?

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by JCooke
    Thanks again, but I'm afraid it doesn't work.
    Code is an autonumber.
    I tried your replacing with your line of code,
    but the following error appears:
    Syntax error (missing operator) in expression

    This is the code:

    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone

    rs.FindFirst "Code = " & List1.Column(1, List1.ListIndex)
    Me.Bookmark = rs.Bookmark

    I've tried changing the column number and the rs as object, etc.
    Any idea what it is?
    I see you started a new thread to check on the FindFirst method.
    Here's code I set up to check what you are doing. Column 0 is an autonumber field (long integer).

    Private Sub List1_AfterUpdate()
    Dim rs As DAO.Recordset, strSQL As String
    Set rs = Me.RecordsetClone
    strSQL = "EventID = " & List1.Column(0, List1.ListIndex)
    rs.FindFirst strSQL
    If rs.NoMatch Then Exit Sub
    Me.Bookmark = rs.Bookmark
    End Sub


    Works great.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

Posting Permissions

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