Tabbed form for survey data entry

[CluelessAccess101]

Please be patient with me. I'm relatively new to access and I have no coding knowledge. Excited to try to help me yet? I want to keep this as SIMPLE as possible.

We have a 17 page, 205 question survey. Some questions are yes/no, some on 1-5 or 1-7 scales, 5 open ended questions, and 1 check all that apply. The leads on this study want the form to mirror the paper version of the survey exactly, meaning I need to have 17 pages (Yes...really).

I have two tables: TblPtMain and TblPtEntry. Main houses the participant info (Participant ID, Name, DOB) and Entry will hold all the survey data entry. I have a main form which includes the fields from TblPtMain. On that form I created tabs for each page of the survey (I know this is excessive, but I don't have much of a choice here). My plan is to have each tab contain a subform and the user will just click each tab to continue with data entry of that record.

Right now, the entry works well on the main and Pg1 of the survey. When I click to Pg2, it keeps the ID field populated but it creates a new record for the for the fields on that page. How do I link these subforms to continue on the same record? Is there anything I can to do clean this up and make it easier to work with?



Thanks!

[RuralGuy]

Have you already looked at this link: http://www.rogersaccesslibrary.com/f...-93f53fb6.html

[always404]

There are a couple reasons why this might happen.

1) I'm thinking your subform might be set to data entry, this could set the subform to make a new record each time you open it.

To fix that you would need to go to your subform properties, go to the data tab, and change data entry to "No"

2) It also sounds like you may not have a relationship between the two tables/forms.

TblPtMain and TblPtEntry should have their own unique key, but TblPtEntry should have a column with the keys from TblPtMain in its own column. That way TblPtEntry knows which entry belongs to which person. Once this is done also make sure the "Relationship" is set so that the primary link in TblPtMain is connected to the foreign key (same thing but in another table) for TblPtEntry. In access 2007 there is a tab called database tools in the top ribbon that lets you edit Relationships.
By doing this you can make it so that whenever a certain client is being displayed in the form, the subforms will know what to display.

Would you mind posting a screenshot though, I need a clearer image of what kind of setup you have.
Remove any personal data of course.

[qa12dx]

why dont you create 17 or how many ever needed forms. think of it as pages.

- keep form size to fit screen.
- split the q's into sequential forms.
- have a "next" button which closes the form and opens the next form.
- on the next form till the last second last form have have "previous" and "next" button which will close the current form and open the form desired
- last form will have "previous" and "finish"
- you will have to store the record ID on some other form so u can use it as a reffernce to stay in the same record

in form properties, set pop up = yes and modal = yes. maximize ur form.
also look up how to disable right click etc. so the users can't do anything else other than how u want them to use ur form.
if ur machine is decent and your form formatting is good, people wont even notice that a form is closing and opening

[CluelessAccess101]

There are a couple reasons why this might happen.

1) I'm thinking your subform might be set to data entry, this could set the subform to make a new record each time you open it.

To fix that you would need to go to your subform properties, go to the data tab, and change data entry to "No"

Data entry is set to "no"

2) It also sounds like you may not have a relationship between the two tables/forms.

TblPtMain and TblPtEntry should have their own unique key, but TblPtEntry should have a column with the keys from TblPtMain in its own column. That way TblPtEntry knows which entry belongs to which person. Once this is done also make sure the "Relationship" is set so that the primary link in TblPtMain is connected to the foreign key (same thing but in another table) for TblPtEntry. In access 2007 there is a tab called database tools in the top ribbon that lets you edit Relationships.
By doing this you can make it so that whenever a certain client is being displayed in the form, the subforms will know what to display.

Relationship is set:





See how there is a new record beginning with "B1BrthFlat"? That is the first question on Tab 2:

[CluelessAccess101]

why dont you create 17 or how many ever needed forms. think of it as pages.

- keep form size to fit screen.
- split the q's into sequential forms.
- have a "next" button which closes the form and opens the next form.
- on the next form till the last second last form have have "previous" and "next" button which will close the current form and open the form desired
- last form will have "previous" and "finish"
- you will have to store the record ID on some other form so u can use it as a reffernce to stay in the same record

in form properties, set pop up = yes and modal = yes. maximize ur form.
also look up how to disable right click etc. so the users can't do anything else other than how u want them to use ur form.
if ur machine is decent and your form formatting is good, people wont even notice that a form is closing and opening

I thought about doing it this way, but quite understand how the other forms would reference that id. Our previous database (that someone else designed) was supposedly set up this way, but did not function properly.

[always404]

I thought about doing it this way, but quite understand how the other forms would reference that id. Our previous database (that someone else designed) was supposedly set up this way, but did not function properly.

You could:
Make an invisible field on FrmPtSurvey that holds the RecID then
Put this code in the button that launches the next page:
DoCmd.OpenForm "PageX", , , "RecID = " & Me.RecID

This will insure that the new form opens on the RecID of the current survey user.

Actually you probably don't even need two separate tables.

[CluelessAccess101]

You could:
Make an invisible field on FrmPtSurvey that holds the RecID then
Put this code in the button that launches the next page:
DoCmd.OpenForm "PageX", , , "RecID = " & Me.RecID

This will insure that the new form opens on the RecID of the current survey user.

Actually you probably don't even need two separate tables.

I will play around with the format just using forms and not subforms.

Assuming I kept it with the tabs/subforms what would I need to do to correct it? My boss seems to really like the tab format.

[always404]

I will play around with the format just using forms and not subforms.

Assuming I kept it with the tabs/subforms what would I need to do to correct it? My boss seems to really like the tab format.

Go to the tab in question (tab 2), click on the subform, open the property sheet.
What does it say for "Link Master Fields" and "Link Child Fields"??
They should both read: PID

if they don't, then this is your problem

[CluelessAccess101]

Go to the tab in question (tab 2), click on the subform, open the property sheet.
What does it say for "Link Master Fields" and "Link Child Fields"??
They should both read: PID

if they don't, then this is your problem

I had both set as PID. It may seem redundant, but if I used the tab/subform setup could this also be done only using one table?

ETA: I tried this out just using one table and linking the PID as the master/child fields and it did work. Hopefully I'm not setting myself up for disaster later.

[always404]

I'm absolutely positive, one table is fine. Don't worry about that part.
In this case, having another table with the sole purpose of holding the keys of another table is needlessly complex.
Improperly linking tables may have somehow caused your problems.

Also having them both set as PID is not redundant. If you want to use subforms, the subform needs to know which record to display. So the PID of the master form is matched to the PID of the subform.

If you want maximum simplicity you don't even really need subforms, you could put all the controls on your main form. But I think its good that you are using subforms; it will give you good practice making forms and relating them.

[CluelessAccess101]

Perfect, thanks so much for your help!