display data on subform based on comboboxes in main form?

[Brad_512]

Hello, I'm trying to use information based on items selected in a combobox in the main form. Once the user selects the desired info in the comboboxes on the main form, I want the subform to display the data that matches the search criteria in the main form. For instance, if I select a job number, I want everything that matches that job number to be displayed the subform (along with the corresponding job name, version, etc.). I also want to get more specific in a search where I could select a job number, job name, and job version from the comboboxes in the main form to where all the data that matches the search criteria in the main form will be displayed in the subform.

Attached is what I have so far. The form I'm trying to perform this in is the SearchByResults form and the subform is the qryk95 form. I'm trying to populate this using the K95-Template table. I performed a query (qryk95) to select only the fields I want to display in my subform. Any help would be much appreciated.

 

[Brad_512]

can send file with help

I can send a file of what I'm doing currently, but will need help on how to attach the file. I can cut my database down to under 100 KB. However, when I zip the file, the filesize goes up to 5 MB (which is too big to upload). Does anyone have any suggestions as to how to get around this?

 

[clerics]

I suggest you to make a Combobox with ControlWizard It's the easiest way !
Search this Forum or Access 's Help is very useful

Or this way --->
in main form :
Add event Combo_afterUpdate -->
and type this code :

Dim strSQL as string
strSQL =" SELECT * FROM [K95-Template ]
WHERE ((([K95-Template ].ID)=[Forms]![Your main form 's name]![Combo]));"

Me.Refresh
Me.Sub_Form.RecordSource=strSQL
Me.Sub_Form.Requery

 

[Brad_512]

control wizard

The control wizard isn't doing everything I want it to. Supposedly, it's able to create both the main form and subform at the same time. I've followed step-by-step instructions from a couple of places and can't get the menu to popup that will allow me to create both forms at the same time. If there's something you think I might doing something wrong, please let me know. As for the code you gave me, it's saying the RecordSource is not found.

 

[Brad_512]

found that problem

Ok, I found the problem with that. After the subform name, I needed to type .Form as well.

Example:

Me.qryK95subform.Form.RecordSource = strSQL

Now, my problem is a 3078 error which states that the input table or query does not exist. It says for me to make sure I didn't mispell it or anything. I don't see a mistake, but here's what I have in case someone else can spot it:

Private Sub cboJobNumber_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT * FROM qryk95 WHERE"
strSQL = "[Forms]![qryk95subform]![JobName] = [Forms]![SearchByQuery]![cboJobNumber];"

Me.Refresh
Me.qryK95subform.Form.RecordSource = strSQL
Me.qryK95subform.Form.Requery
End Sub

Should I be matching up the names in my properties or should I be matching the control sources? The JobName is actually the JobNumber (fields are flipflopped in the table on purpose for accounting purposes), so that shouldn't be the problem.

 

[clerics]

strSQL = "SELECT * FROM qryk95 WHERE"
strSQL = "[Forms]![qryk95subform]![JobName] = [Forms]![SearchByQuery]![cboJobNumber];"

This is your Problem !

Let write like this --->

strSQL = "SELECT * FROM qryk95 WHERE qryk95.JobID = [Form]![SearchByQuery]![cboJobNumber];"

Or

strSQL = "SELECT * FROM qryk95 WHERE qryk95.JobID = " & Me.cboJobNumber & ";"

The control wizard isn't doing everything I want it to. Supposedly, it's able to create both the main form and subform at the same time.

I didn't mean you have to create a form by ControlWizard ! I mean you should create a Combobox by that one !
Let look at this link . There s the same example for you !
http://www.access-programmers.co.uk/forums/showthread.php?t=107347

 

[mintyFresh]

Hey Clerics and Brad512!

Just a lurker, sort of, but this whole post made my own program work, you guys are amazing!

However, my problem is that everytime you select something from the Combo box a small box pops out asking for an JobID Number. Why is that?

I used this code:

Private Sub cboOrdrNbr_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT * FROM qryTest WHERE qryTest.OrderNbr = " & Me.cboOrdrNbr & ";"
Me.Refresh
Me.sbfrmOrder.Form.RecordSource = strSQL
Me.sbfrmOrder.Form.Requery
End Sub

Please help. Thanks. =D