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.”

Bruces,

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
question:

Private Sub txtJNumber_AfterUpdate()

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

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
    Else
        ' Make sure a first name has been entered
        If txtFirstName.Value = "" Or IsNull(txtFirstName.Value) Then
            MsgBox "You must enter a First Name"
            txtFirstName.SetFocus
        Else
            ' Build the criteria
            strWhere = "FirstName = '" & txtFirstName.Value & "' AND
LastName = '" & txtLastName.Value & "'"
            ' Check how many records match the First and Last Name
entered
            intMatchCount = DCount("JNumber", "tblReferrals",
strWhere)
            ' 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",
strWhere)
            Else
                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
database.

Additional Resources:
Look in Access Help at DLookup and DCount

MVPS Access FAQ
http://www.mvps.org/access/

FAQ on Concatenating Values into Criteria
http://www.mvps.org/access/forms/frm0001.htm

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

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.