Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    finding a record (XP, 2000)

    I am trying to search through a table looking for a record that contains a value in one of two fields. My code isn't working and wondered if anyone can see what is wrong with it. I am using text28 (unbound) and want to look in either a barcode field or a item code field and when the record is found, display it on the form in some other controls.

    Private Sub Text28_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Barcode] = '" & Me![Text28] & "'" Or "[item code] = '" & Me![Text28] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: finding a record (XP, 2000)

    I have not tried it, but change:
    rs.FindFirst "[Barcode] = '" & Me![Text28] & "'" Or "[item code] = '" & Me![Text28] & "'"
    to:
    rs.FindFirst "[Barcode] = '" & Me![Text28] & "' Or [item code] = '" & Me![Text28] & "'"

    In other words I think you need to put the OR inside the string.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding a record (XP, 2000)

    That was a good try but I get a syntax error.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: finding a record (XP, 2000)

    What code does not appear to be working?

    Do you realise that the EOF does not work with FindFirst but you have to test with NoMatch instead.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding a record (XP, 2000)

    No, I was not aware of that. In fact, I hardly know what I am doing except that this is a modified version of code I got from Hans some time ago for a different application. I am learning slowly and sometimes I am amazed that anything I do actually works.

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding a record (XP, 2000)

    Pat,

    My Mistake, the syntax error was my clumsy pasting. It works fine but your comment about EOF got me to thinking that it would be good to have a msg box appear if there is nomatch. I put this code in but it doesn't do anything. I'm sure I have it wrong.

    Private Sub Text28_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Barcode] = '" & Me![Text28] & "' Or [item code] = '" & Me![Text28] & "'"
    If nomatch Then MsgBox "Sorry, couldn't find anything", vbOKOnly, " Search Results"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: finding a record (XP, 2000)


Posting Permissions

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