Help with adding records to tables

[Qwert0000]

I have a db with 2 main tables. All members are in my PersonnelInfo table while only "students" are in my TrainingRecords table. This two tables are linked by a common SSN field. I have turned off Add record to all my forms to insure that all my required fields are populated when a new record is added.

I have an AddRecord Form that has all the required fields as text and comboboxes and have my command button checking for approiate information before adding the record.

I need to first check to insure the record does not currently exist and respond with a msg box if it does.
If the record does not exist I want to add the record where PersonnelInfo.SSN = txtAddSSN, TrainingRecords.SSN = TxtAddSSN, PersonnelInfo.LastName = TxtAddLastName TrainingRecords.TraingStartDate = TxtAddStartDate. ect

I can handle the if then statement to write the fields depending on weather the record is a stff or student. I just cant get a handle on checking for an eof when i check to see if it exists or how to write the record after all the checking is done.

Thanks for taking time to assist a Novice user.

 

[aradha]

Hi there,
Depending on the field you are searching on create a recordset and use the seek method to locate the record. Use if statement and nomatch property to update the record in the table if not found.

with the help of the input box get the information from the user and store it in a string . Use the string to locate the record.

set db = currentdb()
set mytable = db.openrecordset ("PersonalInfo&quot
mytable.index = [the field you are doing a search on]
mytable.movefirst
do until mytable.eof
mytable.seek = , answer
loop
if mytable.nomatch then
docmd.openform (and add records)
else
record already exists
end if

I hope this helps.

 

[Qwert0000]

I can't seem to get the nomatch command to work. I am using Access 2k2. No sure which library to add.

I have managed to get the records wrote to the table but im tryng to avoid a record already exists error.

Thanks for any insite you can give me

 

[PeteJohnston]

I think you could use the DCount function

If DCount("SSN", "PersonalInfo", "SSN = '" & txtAddSSN & "'") > 0 Then
   MsgBox "SSN " & txtAddSSN & " already exists"
Else
   ...
   Your add record code
   ...
End If

If the SSN field is numeric you don't need the single quotes in the the DCount expression, so it should be just

DCount("SSN", "PersonalInfo", "SSN = " & txtAddSSN)

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[Qwert0000]

Dcount did the trick. Thanks for you help.

- Eric

 

[Qwert0000]

Dcount did the trick. Thanks for your help.

- Eric