Data entry related records

[illoquentgent]

Hi, all. I have recently put together a database for entering answers for participants in a study. Each participant has a CASEID (also the primary key) which is the number that identifies each unique participant. I have an initial form where you enter the CASEID#, date interviewed, and the initials of the interviewer. Then I created a button that takes you to the first section of the questionaire. Obviously each section has its own table and CASEID as the primary key on each one. What I want to do is have the CASEID auto-populate in each subsequent form when the data entry person is done with each section. I have tried using the button wizard but the CASEID doesn't fill in when going to the next form. Any help would be greatly appreciated.



Note: I have not written any code or written any macros at all for this database. All my tables follow a basic "value" and "label" structure. Very basic here. But I would like to add the aforementioned feature. Thanks.

[June7]

Use form/subform arrangement. Master/Child links properties of the subform container controls will synchronize related records. The CASEID will automatically populate the foreign key field of the related child table as soon as a value is entered in any field. No code needed. Might want a tab control on the subform to organize the questions.

BTW, changed your thread title to something more informative.

[illoquentgent]

Use form/subform arrangement. Master/Child links properties of the subform container controls will synchronize related records. The CASEID will automatically populate the foreign key field of the related child table as soon as a value is entered in any field. No code needed. Might want a tab control on the subform to organize the questions.

BTW, changed your thread title to something more informative.

Thanks for your help. I already have the form/subform design worked into it, but I think I need something to bridge the gap between 3 separate forms. So basically I have 4 forms; a main menu form and 3 forms with the same set of questionairres to be administered at 3 time intervals (baseline, 6 months, 12 months). I created those three forms with tabs and you are correct, within those individual forms the CASEID remains the same and I don't have to do anything. But here's what I need help with. So I have a main screen where the data entry person enters the new participant's CASEID along with the date interviewed. This info is being recorded into an 'EnrollmentTbl' table. On that same form I have a button labeled 'Enter Interview' which takes you to Form1(baseline). What I want is for the CASEID entered in the menu form to auto-populate into the CASEID for Form1. I also want to have a button that will go from Form1(baseline interview) to Form2(6 month interview) with that same CASEID populated into the field CASEID for Form2. That's my dilemma. Hope that helps. I'd even upload my file if anyone needs to take a look at it. Thanks so much!

[June7]

Do you have the 3 subforms on pages of a tab control?

Analyzing db directly could be helpful. Follow instructions at bottom of my post.

[illoquentgent]

File is 3MB's. Here's the dropbox link. Thanks.

https://www.dropbox.com/s/2yfhje29hp...database.accdb

[illoquentgent]

File is 3MB's. Here's the dropbox link. Thanks.

https://www.dropbox.com/s/2yfhje29hp...database.accdb

Any help would be greatly appreciated.

[June7]

You are not using form/subform structure. You have several 1-to-1 related tables joined into one query as the RecordSource for a single form and the joins are not correct. For instance, in form SeekTestMain the SQL statement is joining SeekTestDrugUse_new and SeekTestMistrust to SeekTestAlchUse_new and SeekTestAlchUse_new to SeekTestDemographics_New (all are INNER join). I think all 3 of these should RIGHT join to SeekTestDemographics_New, like:

SELECT SeekTestDemographics_new.*, SeekTestMistrust.*, SeekTestDrugUse_new.*, SeekTestAlchUse_new.*
FROM SeekTestMistrust RIGHT JOIN (SeekTestDrugUse_new RIGHT JOIN (SeekTestAlchUse_new RIGHT JOIN SeekTestDemographics_new ON SeekTestAlchUse_new.CASEID = SeekTestDemographics_new.CASEID) ON SeekTestDrugUse_new.CASEID = SeekTestDemographics_new.CASEID) ON SeekTestMistrust.CASEID = SeekTestDemographics_new.CASEID;

[illoquentgent]

You are not using form/subform structure. You have several 1-to-1 related tables joined into one query as the RecordSource for a single form and the joins are not correct. For instance, in form SeekTestMain the SQL statement is joining SeekTestDrugUse_new and SeekTestMistrust to SeekTestAlchUse_new and SeekTestAlchUse_new to SeekTestDemographics_New (all are INNER join). I think all 3 of these should RIGHT join to SeekTestDemographics_New, like:

SELECT SeekTestDemographics_new.*, SeekTestMistrust.*, SeekTestDrugUse_new.*, SeekTestAlchUse_new.*
FROM SeekTestMistrust RIGHT JOIN (SeekTestDrugUse_new RIGHT JOIN (SeekTestAlchUse_new RIGHT JOIN SeekTestDemographics_new ON SeekTestAlchUse_new.CASEID = SeekTestDemographics_new.CASEID) ON SeekTestDrugUse_new.CASEID = SeekTestDemographics_new.CASEID) ON SeekTestMistrust.CASEID = SeekTestDemographics_new.CASEID;

wow. thank you so much. where should I place this code?

[June7]

That is a suggested restructure of form SeekTestMain RecordSource sql statement.

[illoquentgent]

I hate sounding so naive but I do not know where the RecordSource sql statement is. Also is there any resource you would recommend so i can start familiarizing myself with programming side of of ACCESS? I'm sure it would be helpful for whenever I turn to this forum for assistance. At the very least I would know how to formulate my questions is more helpful manner. Thank so much.

[June7]

RecordSource is a property of forms and reports. You will find it on the Data tab of the Property Sheet dialog. The RecordSource can be a table, query, or SQL statement. I don't remember which it is in your form. If it is a query then open the query in design view and modify it.

Does not involve programming in macro or VBA.

However, review http://office.microsoft.com/en-us/ac...010341717.aspx for starters.