Results 1 to 7 of 7

VBA: Find Record from in table from form

  1. #1
    shimmy84 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    4

    VBA: Find Record from in table from form

    Hi all

    I have a database that holds information about prospective students (name, address, phone number, email etc etc).

    There is a form set up for entry of new records. What I want to be able to do is after someone enters the first name and last name, access searches the table to see if there are any matching records and displays the details (address, phone, email) in a message box. This is a way of checking for duplicate records before entering data. (For example, you might be entering data for a John Smith in Sydney and there might already be a John Smith in the database but he might live in Melbourne. Or Sally Jones from Sydney might have emailed to say she wanted to be put on the database and then phoned as well. So when you go to enter her details a second time, you can see that she is already in the database.)

    I was thinking to put code behind the AfterUpdate event of the last name field and I know it would have something to do with opening and querying recordsets but i'm just not sure how to do it.

    Hope that makes sense.
    Thanks.

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    927
    I have a table called Table 1 with the Following Fields
    1) ID
    2) FirstName
    3) LastName
    4) Address
    5) Phonenumber

    I make a from with this table and attach the following code to the AfterUpdate Event of the LastName Text Box.

    The code basically loops through the table whan it encounters the the same name it pops up a msgbox


    Private Sub LastName_AfterUpdate()
    Dim rs As Object
    Set rs = CurrentDb.OpenRecordset("Table1")
    If rs.EOF And rs.BOF Then
    MsgBox "No Record"
    End If
    Do While Not rs.EOF
    If rs!FirstName = Me.FirstName And rs!LastName = Me.LastName Then
    MsgBox "Address:" & rs!Address & vbCrLf & "Phone Number: " & rs!PhoneNumber
    Exit Do
    End If
    Loop
    Set rs = Nothing

    End Sub

    note:Replace the name of the table with the table in your database and also the field names.

  3. #3
    shimmy84 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    4
    Thanks Maximus. I tried this code but it causes access to freeze (whether it's because of the number of records in the database, i'm not sure).

    Any ideas on how to fix this?

    Thanks again

  4. #4
    shimmy84 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    4
    Just an afterthought, could the problem be that the table is a linked table from another database?

  5. #5
    shimmy84 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    4
    Problem solved. It was because of the linked tables.
    Thanks for your help.

  6. #6
    tmills7 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    1
    I tryed this coding but it never moves of the 1st recorderd. what cause this code to move to the next record or how do i get to move to the next record?

  7. #7
    maximus's Avatar
    maximus is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Location
    India
    Posts
    927
    Private Sub LastName_AfterUpdate()

    Adding rs.Movenext will move u to the next record

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Table1")
    If rs.EOF And rs.BOF Then
    MsgBox "No Record"
    rs.close
    set rs=nothing
    End If
    Do While Not rs.EOF
    If rs!FirstName = Me.FirstName And rs!LastName = Me.LastName Then
    MsgBox "Address:" & rs!Address & vbCrLf & "Phone Number: " & rs!PhoneNumber
    Exit Do
    End If
    rs.Movenext
    Loop
    Set rs = Nothing

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

Similar Threads

  1. Replies: 4
    Last Post: 07-22-2009, 01:33 PM
  2. Replies: 1
    Last Post: 05-16-2009, 07:47 AM
  3. Find Record button error
    By Zedlexx in forum Forms
    Replies: 0
    Last Post: 03-11-2009, 08:26 AM
  4. How to Find a Record
    By lschlitt in forum Programming
    Replies: 2
    Last Post: 09-09-2006, 05:24 PM
  5. Replies: 0
    Last Post: 06-30-2006, 08:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums