Populating master table with list from list table

sburgess · August 29th, 2006, 08:45 AM

Another question. I have a form that I am working on.

When you open the form, it asks for a tool # (which will be a drop-down box). There will be a command button that will bring up a sub-form. In the subform, there are 2 combo boxes. One picks the group that the tool falls under, and the other has the section of the group for the tool.

When the user picks the section, I need it to filter the questions that pertain to that section (which is listed in a list table), and pull those questions, and populate the master table with them. (i.e., Pulls "where is tool?" from tblQuestions, and puts it in the tblQuestionMaster when section is selected from combobox).

kindler · August 29th, 2006, 04:32 PM

Assuming you have the info in one table with fields along the lines of Section | Question, set the After Update event on the ComboBox for Section to [Event Procedure], click the "..." button to the right, and use code similar to the code below:

Me.comboQuestion.RecordSouce = "SELECT DISTINCT Question FROM tbl WHERE Section = '" & Me.comboSection.Value & "';"

Edit: Then just use set the action for a command button to initiate an Append query for the master table using the values selected, you should be able to google that easily enough.

sburgess · August 30th, 2006, 07:31 AM

Thanks for the info.

I'm having another problem with this as well.

The 2 combo boxes that I have are unbound. I'm trying to complete the above using a subform, with the child/parent link from the subform to the 2nd combo box (2nd combo box filters based on the 1st one). But when I try to create a subform, it won't let me make a connection between the two.

This is getting very frustrating, and my deadline is at the end of the week!!

EEK!

kindler · August 30th, 2006, 11:25 AM

You can't use child/parent links to connect to a subform in that manner. You'd need to change the AfterUpdate event on the second combo box to change the RecordSource for the subform to a SQL string that returns the info you're after similar to the string above.

Child/Parent relationships only work between related tables, whereas your effort is more directed at developing a query upon a table.

sburgess · August 30th, 2006, 11:31 AM

Again thanks for the info!! I'm sorry to keep asking questions, but my boss seems to think that even though I can set up a database, that I should know how to do all this back-end stuff as well, and I'm still learning a lot!

Can you enlighten me on SQL statements a bit?

If I understand this correctly, I can create a subform, but I can not create a child/parent link to the 2nd combo box. I would need to put some coding in on the After Update under the 2nd combo box. Do I have this right so far?? Would it be along the same lines of the SQL statement from about?

Also, is there a reason in VB when I try to put coding in, (i.e., section.requery), it's giving me errors, and not recognizing the requery?

kindler · August 30th, 2006, 03:11 PM

You seem to have it pretty well under control. The code for the AfterUpdate on the 2nd event would be similar to:

strSection = Me.cboSection.Value
strQuestion = Me.cboQuestion.Value
Me.subfrmName.Form.RecordSource = "SELECT * FROM tbl WHERE Section = '" & strSection & "' AND Question = "' & strQuestion & "';"
Me.subfrmName.Requery

sburgess · August 31st, 2006, 06:30 AM

Not too well under control. Here is the coding that I have so far. I'm getting error's on the last code that I'm putting in. It's telling me that the Variable is not defined. sorry for all the questions. (I have a programming class at the end of September).

Code:

Option Compare Database
Option Explicit


Private Sub Combo0_AfterUpdate()
Me!Combo2 = Null
Me!Combo2.Requery

End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


    DoCmd.Close

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click

End Sub
Private Sub CmdNewQuestion_Click()
On Error GoTo Err_CmdNewQuestion_Click


    DoCmd.GoToRecord , , acNewRec

Exit_CmdNewQuestion_Click:
    Exit Sub

Err_CmdNewQuestion_Click:
    MsgBox Err.Description
    Resume Exit_CmdNewQuestion_Click

End Sub

Private Sub Combo2_AfterUpdate()
strSection = Me.Combo2.Value
StrQuestion = Me.FTQQuestions.Value
Me.sbfrmQuestions3.Form.RecordSource = "SELECT * FROM tblFTQChecklistMaster WHERE Section ='" & strSection & "' AND FTQQuestions = '" & StrQuestion & "';"
Me.sbfrmQuestions3.Requery
End Sub

Any help would be great!

sburgess · September 1st, 2006, 06:03 AM

I almost have this form done.

I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.

What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.

I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.