Subform Help staying with current record

[TxSteve]

Good morning all,

I'm trying to use a subform and I cannot get the Position combo box on my Post survey form to sync with my current record. (Works great on the pre survey)

Simply put, the combo box is pulling all positions from table and I need it only pull the data from my current record as displayed.

I've attached the db. Please help.

 

[TxSteve]

Okay...no responses after about 4 hours...guess I explained it wrong. Surely this is painfully easy.

My post-survey subform does know what client record I have open. How do I get the subform to show data for that specific record? I've been reading about folks using SQL with a "WHERE" phrase, I've seen something about using VB and ".ME", I've seen others toggle something in properties.

Please help. Original post has the db so you can see what I am talking about.

--More--
Open frmMAIN.
With the default customer displayed, select a role from the properties cbo.
enter data into the survey fields if desired. (Not really needed for this part)
Select the POST-SURVEY tab.
in the cbo, it should only display what was selected on the pre-survey form, instead it displays data for all client records.

Hope this helps better explain what I am looking for.

 

[boblarson]

Why do you have the combo on the subforms at all. If the selection is supposed to be the same across both then it should be on the main form.

 

[TxSteve]

Good question, I set it up this way because multiple folks from the same school will be completing the survey.

The cboPosition on the first subform (pre-survey) pulls from a position table. The first person completing it may be a nurse, followed by a vp and so on. In short, one school may have 4 or more entries.

Later on, we will go back in and post the later results (post survey) and we need to match the nurse entry to the nurse entry, vp to vp and so on so that we can show progress. This cboPosition on this form is supposed to go to the tblBaseline and pull the record from the position column. It does, but it does not see which record is open. So it returns all entries for all schools.

 

[TxSteve]

Anyone have any ideas that I should focus on?

 

[HiTechCoach]

Anyone have any ideas that I should focus on?

IMHO, what you need to focus on is to properly normalize your data. You have repeating fields.

Your form is working correctly based on your table design since the two child tables (sub forms) are not related to each other.

After reading your post many times, I am really still not sure exactkly what you want. My guess is that what you want is to select a record in the Pre-survey sub form. In the Post-survey sub form, only show the record for the position in the Pre-survey sub form. If this is true, then you will first need to fix your tables to reflect this relationship because current they are not related to each other.

Note: Generally the GUI standard is to use radio buttons in a option group. Check boxes normally mean that you can pick not that one item. You form was very confusing since it does not follow the GUI standards.

 

[TxSteve]

Thank you for your response. I appreciate the kindness in the words selected.

What this is about. We have a trainer go into a school and hold a training session. They hand out a pencil and paper to the students and asks them a series of questions to see what they know about a given subject. The papers are returned and a staff member enters the results into the database. They select pre-survey, and select from the list a position (Nurse, other, administration etc) and enter the data. Lets say a nurse and administrator attend the training. (Just these two)

Meanwhile the training occurs and time goes by. At the conclusion of training, a second survey is handed out. Same questions, to the same students.

We collect the surveys and go back to the database to key in the results. This time the data entry person selects post-survey for this specific school. The data entry clerk, looking at the paper sees the nurse completed this sheet so when they select the combo box, it would only have nurse and administration because these are the only two students from this school that participated.

I need for the the post-survey postion combobox to look at the pre-survey table and display the matching two entries for that specific school. In my words (may not be the correct term as I am a rookie), this would be accomplished by passing a variable (Dist_ID) from frmMain to frmPost. The more I google this, the more it looks like I need to use some sort of ".ME..." statement to pass this variable.

Note: All the other fields (check boxes) do not need to be prepopulated as the clerk is about to enter a second set of results (post survey) Once the pre-survey and post-survey data has been entered, my goal is to produce a report showing the difference School ABC had between the pre and post results.

 

[TxSteve]

Making a little progress. I added this to the cboPosition on the post-survey subform.

"SELECT tblBaseline.ID_Base, tblBaseline.Position FROM tblBaseline WHERE (((tblBaseline.Dist_ID_F)=Forms!frmMain!Dist_ID)) ORDER BY [Position]; "

It works great for the first record but does not detect when the record changes to another school.

(~Edit~)
I think I have it...

I added the following,

Private Sub Combo200_Enter()
Me.Combo200.Requery
End Sub

to the "On Enter" event.