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

    Data lookup (Access 97 or 2000)

    I hope I can phrase this right. I was asked if the database that I am creating can do the following: When a person (in a blank record) types in a field (say a group number field), if there is a matching record out there, certain fields(not all) will be populated. The only thing is they want the records unique by making a key that includes the group number and a date field. They want this database to do so many things, I am losing track. I love this site - it is my only salvation. Thanks for all your help.....

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

    Re: Data lookup (Access 97 or 2000)

    You could put code in the AfterUpdate of the text box for the group number that searches for a match and if found, copies some fields from the matching record to the current one.

    Something like the following. Warning: this is air code. The table, field and control names are made up, of course. You'll have to modify this for your situation.

    Private Sub txtGroupNum_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("tblSomething", dbOpenDynaset)
    rst.FindFirst "GroupNum = " & txtGroupNum
    ' Did we get a match?
    If rst.NoMatch = False Then
    ' Copy some fields
    txtSomeField = rst!SomeField
    txtAnotherField = rst!AnotherField
    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

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

    Re: Data lookup (Access 97 or 2000)

    Thank you - I tested it in another table and it works perfectly.

Posting Permissions

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