Combo Drop Down filled with specific information only

[DAOpsMatt]

Hello,

I have a form that has a combo box drop down list for Suppliers that pulls the list of supplies from the tblSupplier. I have a second combo box drop down for Name of Service that pulls the list of serviced from the tblServiceType. I can't figure out how after I select a Supplier to only display those Services related to that supplier and not all services in the table.

Thank you for your help,



Matt

[ranman256]

The SQL for the second combo looks at the 1st combo....
select item from tInventory where [supplier]=forms!myForm!cboSupplier

so the only code you need is to refresh combo 2 when user changes combo1

private cboSupplier_afterupdate()
cboItems.requery
end sub

[CJ_London]

or just reference the control directly in the rowsource

select item from tInventory where [supplier]=[cboSupplier]

will still need to code to requery

[DAOpsMatt]

Hello,

Thank you very much for your help with this, but I think I am still missing something as it is not working.

So here is what I have done:
1) cboSupplier - added Event on AfterUpdate

Private Sub cboSupplier_AfterUpdate()cboServiceType.Requery
End Sub

2) cboServiceType - Added to Row Source

SELECT tblServiceType.Name of Service=[cboSupplier}

When I click the drop down at end of Name of Service it is blank with no options.

Where did I go wrong.

[CJ_London]

just take a closer look at your rowsource

as I am sure you are aware, having spaces in field names is a bad idea - you need to surround with square brackets - you havent

and you have a typo - the last character should be a ] not a }

I'm also curious as to why 'Name of Service' is a supplier? descriptions don't seem relevent

[DAOpsMatt]

Thank you Ajax.

I have fixed the typo. Still isn't doing what I think it should. Right now, nothing is happening.

What I am trying to do is create a form, that has 4 fields. 1)Supplier 2) Name of Service 3) Trip 4) Number of guests (those are the label names) After I select the Supplier, I would like the Name of Service field to only show the services at apply to that Supplier.

I will admit I am probably making this a lot more difficult than it needs to be.

Overall idea: I am contracting services from suppliers for trips we operate. I need to send a list of dates we will be at the supplier and what type of service it applies to with number of guests for each date.

I have a tblItinerary that has the trip laid out. I have a tblDepartures to show each departure and info related to it. I have a tblSupplier that has my suppliers in it. I have a tblRate for the rates and a tblServiceType for type of service. I have a qryServiceDate2019 to pull the exact dates of service based on the tblItinerary. Example, I need list of dates for Supplier A. Supplier A is on day 4. In departure table Trips starts on Date Y, Service date is Date Y + 4, etc. Then I have rptDatePage to display information to send to Supplier. Now come the Form to simplify data enter needed.

Hope that makes more sense?

Thank you again for all your help.

[CJ_London]

Sounds like you need what is called ‘cascading combo’. Am away at the moment but google the term and you will find plenty of links

[DAOpsMatt]

THAT WORKED!!!!! Thank you so much!!! Now brought out other problems, but that is ok, one step at a time.

Thank you so much.