Finding/creating record in one form to match previous form

I'm constructing a set of data entry forms. The user will fill these in
sequentially with data on different assessments of a single individual. Each
form has 'Participant ID' as the primary key. When the user tabs out of the
last field in e.g. Form A, I have the next form in sequence opening up e.g.
Form B. What I'd appreciate some advice on is how to have Form B search for a
record with ParticipantID matching the ParticipantID of the record which was
just open in Form A, and either display that record, or if it does not exist
then to create it (i.e. fill the ParticipantID field).

Note that the last open record will not necessarily have the largest
ParticipantID. I think I should be using DLookup - but I'm a bit of a novice
and can't get beyond that.

Thanks for any help!

On Thu, 7 Jan 2010 05:10:05 -0800, JCA wrote:

I'm constructing a set of data entry forms. The user will fill these in
sequentially with data on different assessments of a single individual. Each
form has 'Participant ID' as the primary key. When the user tabs out of the
last field in e.g. Form A, I have the next form in sequence opening up e.g.
Form B. What I'd appreciate some advice on is how to have Form B search for a
record with ParticipantID matching the ParticipantID of the record which was
just open in Form A, and either display that record, or if it does not exist
then to create it (i.e. fill the ParticipantID field).

Note that the last open record will not necessarily have the largest
ParticipantID. I think I should be using DLookup - but I'm a bit of a novice
and can't get beyond that.

Thanks for any help!

STOP.

You're going about this the wrong way!

You don't need a different table for each assessment, unless the assessments
are collecting totally different structures of information.

You *certainly* don't need a different Form for each assessment!

You don't need any VBA code at all!

Start with the Table structure. Tables are fundamental; forms are *just
tools*, windows to manage data in tables. Starting with Forms and then fitting
tables to them is like pouring the foundations for your house... after framing
in the walls and roof.

I'd see a table of Participants related one to many to a table of Assessments;
this Assessments table may be related one to many to a table of Answers,
itself related many to one to a table of Questions, if each assessment
consists of multiple questions. you would use a Form based on participants,
with a Subform based on Assessments (and perhaps a sub-subform based on
Answers).

You might want to take a look at some of these resources to see how to
properly normalize a database:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]