Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup field (97)

    I received some code not long ago that I thought worked but at closer look - it doesn't. When someone enters a group number (which is a text field), if there is another record with the same group number then it pulls in certain information from specified fields like Group name and Client number. This is the code that is attached to the After Update event of the Group Number field:

    Private Sub GroupNumber_AfterUpdate()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_Handler

    'Only do this if user is on new record
    If Me.NewRecord Then
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblReissue", dbOpenDynaset)
    rst.FindFirst "GroupNumber=" & "GroupNumber"
    'Did we get a match?
    If rst.NoMatch = False Then
    'Copy some fields
    ClientNumber = rst!ClientNumber
    GroupName = rst!GroupName
    End If
    End If

    Exit_Handler:
    'Clean Up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

    When I put in a Group Number that I know is in the database, it doesn't bring in the correct information - even if there isn't a like group number, it brings in information.

    Your help is greatly appreciated.

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

    Re: Lookup field (97)

    Try changing the following command:
    rst.FindFirst "GroupNumber=" & "GroupNumber"
    to:
    rst.FindFirst "GroupNumber=" & Chr(34) & Me!GroupNumber & Chr(34)

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup field (97)

    Group Number is a text field. I tried what you said but it didn't work. I assume you mean put single quotes around & 'Me!GroupNumber'.

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

    Re: Lookup field (97)

    I'll try again, I don't think you can use the Find method with a dynaset type recordset, it must be a table type recordset.

    What I would do is replace:
    Set rst = dbs.OpenRecordset("tblReissue", dbOpenDynaset)
    rst.FindFirst "GroupNumber=" & "GroupNumber"

    with:
    Set rst = dbs.OpenRecordset("SELECT ClientNumber, GroupName FROM tblReissue WHERE GroupNumber ='" & Me!GroupNumber & "'")

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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