Determing if Record Exists

[Grant Nosbush]

Hopefully someone can be of some help with the following question. I am
putting together a database that tracks patients at different centers
for a research project.

I have created a form to enter background information for each patient.
Currently, when this form loads, it opens another sub-form asking for
the center id and patient id. These two variables are passed back to
the background form.

What I want to happen is when the user clicks okay on the sub-form, I
want Access to check the background database and see if there already is
a record with this center id and patient id. If so, the background form
would load it. If the record does not exist, I want to have Access
create a new record by pulling in the center id and patient id and then
load the background form with this newly created record.

Hopefully this is real simple and I'm just missing something here. I've
been working all day trying to put this together so I'm kind of tired.

My assumption is this code would all go in the sub for the okay button
on click. Can someone suggest how to go about this? Thanks

 

[Todd Shillam]

I would create a 'bound' query most likely; however, its difficult to tell without actually seeing the situation you have.

For example, I might create the query (using whichever tables you want) and bound those two fields, each to a form control, like a combobox.

Then you could use the DLookup function to 'test' and determine if such a record exists via a little VBA script.

Let's say you have a combobox for 'centers' and your query is bound to this combobox on the form; meanwhile, there's another combobox for 'patients' also bound to the query.

Using the button's OnClick event, you could 'test' if such a record exist in the query:


Dim vPatient

vPatient = DLookup("patientID", "QueryNameHere")
If Nz(vPatient, "") = "" Then
'NO RECORD EXISTS - INSERT YOUR SCRIPT HERE
Else
Dim vCenter

vCenter = DLook("centerID","QueryNameHere")
If Nz(vCenter, "") = "" Then
'NO RECORD EXISTS FOR BOTH FIELDS
Else
'THERE IS AN EXISTING RECORD - INSERT SCRIPT HERE
End If

End If

Best regards,

Todd
Hopefully someone can be of some help with the following question. I am
putting together a database that tracks patients at different centers
for a research project.

I have created a form to enter background information for each patient.
Currently, when this form loads, it opens another sub-form asking for
the center id and patient id. These two variables are passed back to
the background form.

What I want to happen is when the user clicks okay on the sub-form, I
want Access to check the background database and see if there already is
a record with this center id and patient id. If so, the background form
would load it. If the record does not exist, I want to have Access
create a new record by pulling in the center id and patient id and then
load the background form with this newly created record.

Hopefully this is real simple and I'm just missing something here. I've
been working all day trying to put this together so I'm kind of tired.

My assumption is this code would all go in the sub for the okay button
on click. Can someone suggest how to go about this? Thanks