Dependant Combo Boxes 2

[Herriot]

Hi Guys. Can anyone solve this conundrum?

The problem I am having is this:

I have a form with a subform

The subform has two Combo boxes that depend on each other. Box1 has the main information (in this case its called the provider), Box2 displays info based on the option chosen in Box 1(box 2 in this case is the Venue belonging to the provider)

When the subform is opened on its own it work beautifully but when its opened as part of the main form Box 2 keeps asking for a parameter value.

Help!

Here is some code I am using:

Box1
**Combo Box Name: Provider
**ROWSOURCE
SELECT tbl_eProvider.eProviderID, tbl_eProvider.eProvider_name FROM tbl_eProvider;

**After update event procedures:
Private Sub provider_AfterUpdate()
Me.venue.Requery
End Sub

Box2
**Combo Box Name: venue
**ROWSOURCE
SELECT tbl_Venues.Venue_Title FROM tbl_Venues WHERE (((tbl_Venues.eProviderID)=Forms!frm_Event_subform!Provider));

Hopefully someone can help.

Peter

 

[dhookom]

I generally update the Row Source SQL like:

Private Sub provider_AfterUpdate()
    Me.Venue.RowSource = "SELECT Venue_Title " & _
        "FROM tbl_Venues WHERE eProviderID = " & _
        Me.Provider 
End Sub

This assumes eProviderID is numeric.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Herriot . . .

When you insert a form in a form making it a subform ... depending on where you call controls from you change how you reference the subform. Since your [blue]recordsource[/blue] for [blue]venue[/blue] mimics the SQL of a query, you need to fully reference the subform ... that is ... you left out the mainform name!

[tt][blue]
Current  :WHERE (((tbl_Venues.eProviderID)=Forms!frm_Event_subform!Provider));
Should Be:WHERE (((tbl_Venues.eProviderID)=Forms![red][b]YourMainFormName[/b][/red]!frm_Event_subform[red][b].Form[/b][/red]!Provider));[/blue][/tt]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Herriot]

Thanks to both Duane's and TheAceMan1. I got it working with both your help

Thanks a million guys once again you solved, in a matter of minutes (relatively speaking), what I had been trying to do for hours

Herriot - a happy Scotsman (hard to imagine I know )