If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Finding/creating record in one form to match previous form
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] |
Thread Tools | |
Display Modes | |
|
|