Google Answers Logo
View Question
Q: Automatically looking up/filling in Microsoft Access field values ( Answered 5 out of 5 stars,   0 Comments )
Subject: Automatically looking up/filling in Microsoft Access field values
Category: Computers > Software
Asked by: bruces-ga
List Price: $60.00
Posted: 17 Mar 2003 14:54 PST
Expires: 16 Apr 2003 15:54 PDT
Question ID: 177507
My database is in Access 2000 format. I’m familiar with VBA code, but
as shown by my question below, I am far from being an expert.

I have designed a table named tblReferrals. It contains fields named
JNumber, FirstName, and LastName. There will be about 500 records in
this table. It is possible that after a record is entered into the
table, it will be necessary later to correct or change the JNumber,
FirstName, and/or LastName entries. JNumber is the Primary Key in
tblReferrals, so each value is unique. However, it is conceivable that
two records will have an identical combination of FirstName and
LastName entries.

I have designed another table. It’s named tblAif. It is linked to
tblReferrals in a one (tblReferrals) to many (tblAif) relationship
using JNumber as the linking field. The user enters data into tblAif
using a form named frmAif. The Primary Key in tblAif is a field that
is formed by a concatenation of two fields, JNumber and Milestone.

I’ve set up an error trapping routine such that if a user enters a
JNumber on frmAif that does not have an existing JNumber match in
tblReferrals, the user is instructed to close frmAif and arrange for
the proper entry to be made in tblReferrals.

What are the best ways for me to accomplish these further objectives?

1. Allow the user to enter the JNumber directly on frmAif by typing it
into the JNumber text box. Upon completion of the entry of the
JNumber, the FirstName and LastName boxes would be automatically
filled in on frmAif, with the data items retrieved from tblReferrals, to help
the user check that this is, in fact, the right JNumber.

2. If it is possible, I would also like to allow the user to enter the
JNumber into tblAif indirectly by typing in FirstName and LastName
entries on frmAif. This would automatically fill in the corresponding
JNumber, retrieved from tblReferrals, and when the user exited the
record in frmAif, the JNumber entry appearing on frmAif would be
stored in the JNumber field in tblAif. If the user typed in a
combination of FirstName and LastName that was not unique, the user
would get a message with the theme, “You’ll need to enter the JNumber
yourself on frmAif.”
Subject: Re: Automatically looking up/filling in Microsoft Access field values
Answered By: hammer-ga on 17 Mar 2003 16:01 PST
Rated:5 out of 5 stars

Without seeing your form, it is difficult to know which events to use,
but I've written you some examples. Please ask for clarification if
you need help getting your actual case to work as you want it to.

The functions you want are the Domain Aggregate family. In this case,
DCount and DLookup. These functions will retrieve information from
other tables. They both take the same three arguments.
1. The name of the field in the external table
2. The name of the table
3. Criteria telling it which record to get the information from 

Note: In the code examples below, no lines of code should wrap. The
answer box will often wrap lines, so you may need to unwrap them if
you paste them into your own modules.

Note: If you use the below code directly, make sure you adjust it to
match the names of your fields, tables and controls.

Here is an example of the functionality in the first part of your

Private Sub txtJNumber_AfterUpdate()

    txtFirstName = DLookup("FirstName", "tblReferrals", "JNumber = " &
    txtLastName = DLookup("LastName", "tblReferrals", "JNumber = " &

End Sub

Here is an example of the functionality requested in the second part
of your question:

Private Sub txtLastName_Exit(Cancel As Integer)
' Declare some variables
Dim intMatchCount As Integer
Dim strWhere As String

    ' Make sure a last name has been entered
    If txtLastName.Value = "" Or IsNull(txtLastName.Value) Then
        MsgBox "You must enter a Last Name"
        Cancel = True
        ' Make sure a first name has been entered
        If txtFirstName.Value = "" Or IsNull(txtFirstName.Value) Then
            MsgBox "You must enter a First Name"
            ' Build the criteria
            strWhere = "FirstName = '" & txtFirstName.Value & "' AND
LastName = '" & txtLastName.Value & "'"
            ' Check how many records match the First and Last Name
            intMatchCount = DCount("JNumber", "tblReferrals",
            ' Do things based on the number of matches
            If intMatchCount = 0 Then
                MsgBox "No matching JNumber found."
            ElseIf intMatchCount = 1 Then
                ' Fill in JNumber
                txtJNumber = DLookup("JNumber", "tblReferrals",
                MsgBox "You must enter the JNumber manually."
            End If
        End If
    End If

End Sub

Again, I took a guess at the events you would need to use. Please ask
for clarification if you need help using the examples in your actual

Additional Resources:
Look in Access Help at DLookup and DCount


FAQ on Concatenating Values into Criteria

Good luck with your Access project!

- Hammer

Request for Answer Clarification by bruces-ga on 05 Apr 2003 19:21 PST
The solution has worked wonderfully…until today. Someone has a name
with an apostrophe in it. Let’s assume it’s Sean O’Callaghan (there is
no name in my file currently with that name). When I type in the last
name O’Callaghan and move out of the control, I get a runtime error,
apparently because the string criteria that has been created has an
extra apostrophe in it. How do I get around this problem?

Serves me right for overlooking all the O’Callaghan’s and their
countrymen on the day I posted my question—St. Patrick’s Day.

Clarification of Answer by hammer-ga on 06 Apr 2003 08:55 PDT
Try replacing the line of code that generattes the WHERE clause with
this one. As before, the line should not wrap. Chr(34) produces a
double quote mark (").

strWhere = "FirstName = " & Chr(34) & txtFirstName.Value & Chr(34) & "
AND LastName = " & Chr(34) & txtLastName.Value & Chr(34)

- Hammer

Request for Answer Clarification by bruces-ga on 06 Apr 2003 09:51 PDT
My thanks to Hammer for this fine-tuning that does the job perfectly.

Clarification of Answer by hammer-ga on 06 Apr 2003 10:09 PDT
Glad to be of service, especially to such a gracious customer.

- Hammer
bruces-ga rated this answer:5 out of 5 stars
The very prompt response and the cautions about how lines of code can
wrap in the Answer window were great. I was also impressed with the
learning aids Hammer provided: comments in the code, an explanation of
the functions used, and URLs for further inquiry.

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy