Make input selection access a table dependent upon another input

[Guest]

I come back to the well again. Thanks in advance for any assistance. Forum
has been great to date.

I've created an expense form and i want to do the following

Me!MerchantType_input is bound to a combo box table where I have the option
of selectiing from a listing of merchant types; for instance HOTEL, FUEL,
AIRLINES, etc.
I also have tables for each of these HOTEL, FUEL, AIRLINES where there is a
listing of all the Hotels or Airlines we are allowed to use.

When I enter AIRLINES into the Me!MerchantType_input I want a second input
box titled Me!Merchant_Input (set up as a combo box) to allow me to select a
single airline from the list within the AIRLINES table. The same would be
true for HOTEL, etc.

The purpose is to force employees to use only a select merchant and ease
that selection process from within a list of vendors dependent upon the type
of merchant (Hotel, Air, etc.)

As a newbie, I'm not sure how to start this type of code. Help. Thanks.

 

[Ken Snell [MVP]]

See The ACCESS Web for how to filter one combo box based on the value in
another combo box:
http://www.mvps.org/access/forms/frm0028.htm

 

[Guest]

I tried to perform the code noted in the link
http://www.mvps.org/access/forms/frm0028.htm. I could not make it work. I
got no results in either the Me!MerchantType_input nor in Me!Merchant_input.
I tried the code noted below as an alternative, but I get a blank in the
Me!Merchant_input. Me!MerchantType_input does allow me to choose from the
list provided by qry_Airlines. Please advise what I am doing wrong. I did not
fully understand what the link wanted me to do. Thanks for all the
assistance.

Private Sub Merchant_input_GotFocus()
If Me!MerchantType_input = "Airfare" Then
Me!Merchant_input.RowSource = qry_airlines
ElseIf Me!MerchantType_input = "Car Rental" Then
Me!Merchant_input.RowSource = qry_RentalCarListing
End If
End Sub

 

[Ken Snell [MVP]]

Please provide details about how you set up the code / process from
http://www.mvps.org/access/forms/frm0028.htm. so that we can see what might
not be correct.

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

I'm not sure I'm fully understanding, but it appears you want to select a
"table" from the first combo box and use that "table" name to generate the
list of items in the second combo box?

First, your code has some SQL syntax errors. Note that you do not have
spaces on either side of each word that you're concatenating to get the SQL
statement. Using your posted code, the syntax should be this:

Private Sub MerchantType_input_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!MerchantType_input
strSQL = strSQL & " from Categores"
Me!Merchant_input.RowSource = "Table/Query"
Me!Merchant_input.RowSource = strSQL
End Sub

Now, assuming that your first combo box has the table name except for the
prefix "tbl_", you could use code like this to show all fields from the
selected table:

Private Sub MerchantType_input_AfterUpdate()
Dim strSQL As String
strSQL = "Select *"
strSQL = strSQL & " from tbl_" & Me!MerchantType_input
Me!Merchant_input.RowSource = "Table/Query"
Me!Merchant_input.RowSource = strSQL
End Sub

Before I go further, is this answer moving in the right direction?
--

Ken Snell
<MS ACCESS MVP>