Combo Box values based on preceding selection

[speckytwat]

Hi, I'm trying to set up a couple of Combo Boxes on a form as follows:

The first Combo Box has a defined list of values, so the user has to select one of these from the dropdown. e.g Customer, Supplier, Contractor.

What I want is for the second Combo Box to fill with all the possible values of the "Name" field for the appropriate table, based on the selection made in the first box.

So if the user selects Customer, then in the second Combo Box the available values that can be selected are each of the "Name" values from the Customers table.

(I was thinking of just having it check to see if a typed Name exists, but then figured that having each available name as an option was far better- we want to force users to pick a name that already exists in the relevant table)



I tried playing with the AfterUpdate event of the first Combo Box but all this does is create an Event Procedure which then waits for me to insert some appropriate code. But I can't figure what code should actually be added in there- and if any should be added into the second box as well. Any ideas?

[jzwp11]

...Customer, Supplier, Contractor.

Are customers, supplier and contractors in separate tables?

[speckytwat]

Yes, these are all separate tables. I wanted to keep these data separate so that in future users could run quick queries just on that part of the application.

[jzwp11]

I would recommend putting all in the same table. I am guessing that the 3 tables have similar structures (i.e. fields). You can add an additional field to indicate what type of role (customer, supplier etc) the person/company plays.

[speckytwat]

Unfortunately it's a bit late for that really as we have already set up the data and application- going back and redoing it all now is a non-starter as it needs to be completed very soon. Surely there must be a way of doing it with multiple tables? i.e if option1 is selected, check and show names from table1, if option 2 then check and show names from table2 etc. ???

[jzwp11]

You will have to use code in order to populate the second combo box.

In the After Update event of the first combo box, you will have to change the row source of the second combo box. The code would go something like this (air code, not tested)

dim mySQL as string
 
SELECT CASE me.combobox1name
case is = "Supplier"
   mySQL="SELECT field1,... FROM suppliertablename"
case is = "Customer"
   mySQL = SELECT field1... FROM customertablename"
case is = "Contractor"
   mySQL = "SELECT field1... FROM Contractortablename"
END SELECT
 
me.combobox2name.rowsource=mySQL

[speckytwat]

Guess I must be doing something wrong still, I added this as the code for the second combo box (name):

Private Sub name_AfterUpdate()
Dim mySQL As String
 
Select Case Me.category
Case Is = "Supplier"
   mySQL = "SELECT EmployeeName FROM Employees"
Case Is = "Client"
   mySQL = "SELECT ClientName FROM Clients"
Case Is = "Sub Contractor"
   mySQL = "SELECT SubContractorName FROM Subcontractors"
End Select
 
Me.name.RowSource = mySQL
End Sub

Checked that all the field names etc. in each table were correct. However in the form, if I select any of the three dropdown values above in the first combo box, the second box doesn't populate with any of the values from the respective tables...

You said I need to be put something in the code for the first combo box (change the row source of the second combo box), how would I do that?

[jzwp11]

The word "name" is a reserved word in Access, so I would definitely change that to something else. The code should go in the after update event of the first combo box, not the second

What is the bound field of the first combo box or does it only have 1 field?

[speckytwat]

Thanks, I will try that. The field for the first combo box is actually based on Category (Supplier, Client etc.) values in the transactions table, I created a manual list of acceptable values, e.g: "Sub Contractor";"Supplier";"Client" etc. in the Row Source.

Update: I tried the following code in the AfterUpdate event of category (the first combo box), but no joy:

Private Sub name_AfterUpdate()Dim mySQL As String Select Case Me.categoryCase Is = "Supplier"   mySQL = "SELECT EmployeeName FROM Employees"Case Is = "Client"   mySQL = "SELECT ClientName FROM Clients"Case Is = "Sub Contractor"   mySQL = "SELECT SubContractorName FROM Subcontractors"End Select Me.name.RowSource = mySQLEnd Sub

The second box still doesn't show any names...

[jzwp11]

Any chance you can post your database with any sensitive data removed?

Category (Supplier, Client etc.) values in the transactions table

Actually, you could have used a query

SELECT DISTINCT Category
FROM transactionstablename

[speckytwat]

It would probably take days to firstly make sure everything was removed and then put it all back again unfortunately, there are a number of tables that would need to have all their data removed and I still wouldn't be certain that all information had been flushed from the db.

What I did try though was the following in the AfterUpdate event (not that it worked however)

Private Sub category_AfterUpdate()

Dim strRowSource As String

Select Case Me!category
  Case "Sub Contractor"
      strRowSource = "Select SubContractorName From Subcontractors;"
      Me!NameOnTransaction.RowSource = strRowSource
  Case "Customer"
      strRowSource = "Select ClientName From Clients;"
      Me!NameOnTransaction.RowSource = strRowSource
  Case "Employee"
      strRowSource = "Select EmployeeName From Employees;"
      Me!NameOnTransaction.RowSource = strRowSource
End Select

End Sub

Note I've changed "name" in the field on the form and Transactions table to something a bit better. Unfortunately it's all getting pretty frustrating now, I can't see where it's going wrong and the code seems ok...

[jzwp11]

How about this, create a new database and then import the table definitions only (no data) from the original database. There is an option to do this in the import table window. Also import the forms, queries etc. I can then populate some dummy data and see if I can get it two work.