Filter combo box in subform based on value in main form

[dawnd3]

Hi there, I thought I had this working using screen.activeform.cboJob in the underlying query of the combo box in the subform. It was filtering the combo box correctly but when I moved from record to record in the main form, after activating the combo box in the subform, the screen got all funky. Blotched out parts and fields sized large and double vision type stuff. (best way I know to explain) I even moved all the objects to a new database, didn't work. And it didn't seem to matter when or where I requeried the combo box or refreshed the form, still happened. Can I please send this to someone to help me get this done. I need to have the subform filter values based on the value in the combo box on the main form, and then I need to requery the box on the subform so that the new values are there when I change records. I thought this was easy, but I have been struggling. Please email me if you can help dawn@calliopeconsulting.net I will post results here. Thanks Dawn

http://www.calliopeconsulting.net

 

[tunsarod]

Dawn,

Forgive me if I'm being to simplistic but your posting isn't clear to me; I can't understand why you need a combo-box in the subform.

In order for a subform's data set to track a main form's record there has to be some common field shared by both forms.

Starting with the main form lets assume you have populated a few fields among which is at least one unique field.

Now using the Subform Wizard (make sure you have the Wizards button on the Tool Box turned on) insert a subform onto the main form, selecting a TABLE as the source rather than an existing Form. Select the fields to populate the subform but note that at least one of the fields you populate the subform with MUST CONTAIN the same information as the unique field in the main form. This is the foreign key field in the Table that supplies the records for the subform. I usually make this invisible in the final design as its not often you'll want to see it.

The two fields do not need to share the same name - just the data. I.E. if you have a main table called PARENTS with a field called PARENT_ID then another table called KIDS will have to have a field which contains the childs Parent's ID in order to link the kids with the correct parents. The field in the KIDS table could be called SuckersID so long as it contains a copy of the related Parent's ID value.

As you progress with the Subform Wizard it will ask you which fields it should use to link the two forms. In fact it will probably suggest the fields to use.

Give the subform a meaningful name and click Finish. You should now have a subform that tracks exactly the contents of the main form. It may need a bit of tidying up but it should work.

It occurs to me that you may be using the combo-box on a subform to control which record is displayed on the main form. If that is so then I would suggest that you incorporate a drop-combo or list-box on the main form to select a record for display. Again use the Wizards to get a combo-box onto the main form that finds a record and makes it the current record. It's easier than programming it manually and works every time.

I hope this helps.
Regards
Rod

 

[tunsarod]

Further: OK now I see - I think. When you clcik the combo-box in the sub-form the active Window changes to the subform so the target in the query criteria keeps changing from main form to sub form.

Code the query criteria as: =[Forms]![MyMainForm]![KeyField]

Then try inserting:

Me.Como-box.Requery

into the Combo-Box's On Enter event or possiblly the On Click event - that way the combo box will be refreshed when the user clicks it.

Trying to update the combo-box from the main form might be troublesome if the subform isn't actually active at the time.

Rod

 

[dawnd3]

Hi Rod, yes too simplistic I am using a subform because it has records that are associated with the main form. So yes it is linked appropriatly. The combo box in the sub form is for a particular field that is linked to the table underlying the subform. However, it is also linked to the field in the main form. Basically it is a Purchase order and the combo in the main form is to select the jobID. The subform has all the items associated with that purchase order and the combo box there is to select the planID that is associated with that item. However, the plan is also linked to the Job. In otherwords, a job has many plans and an plan has many items. But all of those items have to be on the same purchase order. Sounds strange but that is what they wanted. They didn't want to be able to reuse items.

Thank you

Dawn

http://www.calliopeconsulting.net

 

[dawnd3]

Yes, now you see I will try that, but first I am trying to solve the funky screen issue. For some reason, even with all of the code take out to filter and requery, when I activate the combo box and then scroll between records, the screen partially blotches out and doesn't let me use the form. My only guess is that it doesn't like my relationships or something. I have sent the database to one of our wonderful Tek-tip users, so hopefully he can help me. If anyone has ever had problems with the screen blotchy thing, let me know what generated it. And thank you Rod for your help. I will repost soon and let you know how it goes.

Dawn

http://www.calliopeconsulting.net

 

[dawnd3]

Hi again all. Here is the code that I used in the on current of the main form and the after update on the combo box on the main form: (Curtesy of Herb)
If Not IsNull(Me.cmbJob) Then
Forms!frmPurchaseOrders!frmItemSub.Form!cboPlan.RowSource = "SELECT tblPlan.lngPlanID, tblPlan.strPlanName " _
& " FROM tblPlan WHERE (((tblPlan.lngJobID)= " & Me.cmbJob & &quot);"
Forms!frmPurchaseOrders!frmItemSub.Form!cboPlan.Requery
End If

The screen issue was solved by taking out the bitmap image in the form properties. Hmmmm? Not sure what that was all about. But now I have a nice grey form that works perfectly. thank guys!!!

Dawn

http://www.calliopeconsulting.net