appending new records

[Linda in Iowa]

I want to add new records from an imported spreadsheet. I have a query using
a new records table and current records table. FirstName and LastName are
two of the fields. I have the tables linked by LastName. I have an
autonumber field as a primary key and the criteria is set to Is Null. The
problem is if there is a current record with the same last name as my new
record it doesn't think there is a new record. How can I check for first
name also or is there an easier way. Another problem is that sometimes the
first name might be Steve and when the person renews membership they list
their first name as Stephen so the query would think it is a new record
instead of just an update. There is also a field for new member and
renewing member and this would help but I have seen where it is entered
incorrectly.

I am also working on an update query that isn't returning any info yet.

Thanks for any help on this.

 

[Wayne-I-M]

Hi Linda

1st of all (very important) don't link the records on the last name - you
may have many people called Smith and if you have more than one person with a
1st name of John then you have a problem as the application will not be able
to see these as different people.

Always use a unique number as the primary field and link all other tales to
this.

If you have a form (based on your table) it is simple to see if some "may"
be already in the table.

Say your table is called MyTable and in this table you have a primary field
called MyID, your form is called MrForm and in MyForm you have 2 fields
called txt1stName and txt2ndName.

You enter data into txt1stName - such as John Mary Bill etc
Then you enter data into txt2ndName - such as Smith Jones etc

After you enter the 2nd name you could check to see if the same combination
(of 1st name and 2nd name) is already in the table.

But I would still allow the same combination - as you may have more than one
Jogn Smith - but I would also tell the user this is the case and then they
can check to see if they wish to take any action.

Something like this

If ((DLookup("[MyID]", "[MyTable]", "[txt1stName] ='" & Form!txt1stName
& "' AND [txt2ndName] = '" & Form!txt2ndName & "'"))) Then
MsgBox "This person may already have a record", vbOKOnly, "New
record checker"
End If

Of course you can increase this may includeing other criteria - instad of
just checking 1stname 2ndname you could also check for date of birth

something like this

Dim strID As String

strID = Nz(DLookup("[MyID]", "[MyTable]", "[txtsName] =""" & _
Form!txt1stName & """ AND [txt2ndName] = """ & Form!txt2ndName & _
""" AND [txtDateofBirth] = " & _
Format(txtDateofBirth, "\#yyyy\-mm\-dd\#")), "0")


Of course you would need to warn the user if the record (person) seemed to
be already in the database - you could add a code to go to the record if the
user wants to, or to cancel the input, etc etc.


Many database use this type of checker

Hope this helps