Using Combo box selection to drive subform. 1

[Steven547]

I have an entry form.
On that form, I have a combo box (cboMigUnitSearch).
There is a subform on there as well. This subform lists directory paths and other fields, based on the (cboMigUnitSearch) selection.
That subform is based off a query, which in turn, looks to see what the user selected in the cbo box, and populates the subform based on that.
Long story short, the user makes the selection from the combo box, BUT, after they do, the first record in that database is displayed on screen instead of what they just selected. What they selected, IS stored in the table, but not shown. I have a "requery" running on the AFTER UPDATE event of the cbo box. This is to refresh the subform based on the user selection.
How can I get it to display what the the user selects instead of it jumping to the first record in the table?

 

[MajP]

You can actually do this a lot simpler. You can actually link a subform directly to a control. Change the value in the control and it changes the linked records.

in the subform
link master field: [cboMigUnitSearch]
link child field: [some field linking to the combo]

The wizard may pop up and say you can not link an unbound form to a subform, but you can in fact link a subform to a control

 

[Steven547]

Sorry, I'm not understanding this:

So in my subform, under the RECORD SOURCE of the form...link that to the combo box of my MAIN form?

 

[MajP]

In the subform control you have the "Link Child Fields", "Link Master Fields" properties. Normally if you have a main form with a subform these are the names of the fields that link the master table to a child table. But you can use a control name instead for the Master.

So lets say cboMigUnitSearch, is based on a query returning a SearchID and some path. If you make the SearchID the bound field (the value of the combo), and link it to the subform by SearchID this will filter the subform.

It may require a form refresh on the after update event, but I am not certain.

 

[Steven547]

I apologize again, but where are you seeing this "link child field" and "Link master fields"? I don't see those anywhere.

 

[Steven547]

ok..finally found those.
Here is what I am trying to do and what is happening:

My "Migration Unit Selection" combo is based off a small table.(MigrationUnit). That's it. There are only about 5 fields in there. Thy are just Migration Unit names.
2 forms: frmRebuild (main)and frmsubformUserTrustees(sub)


The subform is based off a query (qryMigUnitSearch).
That query contains the table that supplies the info for the subform.
That query (Migration Units field) is run based off the combo box (forms)(frmRebuild)(cboMigUnitSearch).

The main form is based off the table (tblMigration). This table is where all the new input is entered.

So what happens, is when the user selects the Migration Unit from the combo box on the main form, it populates the subform, but the "record navigation" resets back to the first record. You have to then scroll to what you just entered. So if you were on record 5 (or selected Add New Record), made your selection, the form would then display record 1 instead of record 5.

Maybe it's Thursday, but I don't think I've ever been so confused or frustrated by access in my 10 yrs of doing this! haha

 

[MajP]

In the properties of the subform control. A sub form has a subform control and then a form inside the subform control (source object). If you click on the far outside that is the subform control, if you click on the form inside you get the properties of the form.

 

[Steven547]

Holy frustration, Batman. I think I have it working finally. Think i did what you said. The MASTER link is the cboMigUnit (main form). The child is the subforms Mig Unit. Seems to be working!

Thank you very much. I've looked at this thing for a few days now and was at my "wits" end!

 

[MajP]

Is the record you are looking for in the subform the record with the Migration Unit Name = to the Migration Unit Name in the combo box? Or are there multiple records in the subform with the same migration unit name?

If that is the case you could do something like this in the after update event of the combo

dim rs as dao.recordset
set rs = me.yourSubfrmControl.form
rs.findfirst "yourFieldName = '" & cboMigUnitSearch & "'"

 

[PHV]

MajP, I'd replace this:
set rs = me.yourSubfrmControl.form
with this:
Set rs = Me!yourSubfrmControl.Form[!].Recordset[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Yes good catch. That is what I meant.