Limit content of combo/list box

[Mikael Lindqvist]

Hi everyone,

In first field I want to enter customber_number and then I'd like to to have
a list of all available invoices that belongs to that customer in the other
(multibox field).

Customer number is in one table (custTbl, CustID) and invoices in a second
table (invTbl, invNo).

Each table is related by the custID key.

Now, I have really tried to use this advice (from:
http://www.mvps.org/access/forms/frm0028.htm ):

Let's say you have two comboboxes, cbxCombo1 and cbxCombo2. The
RowSourceType of cbxCombo1 is set to "Field List" and RowSource to a table
Category. cbxCombo2 doesn't have anything under RowSource.

In this case, you can put code in the AfterUpdate event of cbxCombo1
that assigns the proper RowSource to cbxCombo2.

'**************** Code Start *************
Private Sub cbxCombo1_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!cbxCombo1
strSQL = strSQL & " from Categories"
Me!cbxCombo2.RowSourceType = "Table/Query"
Me!cbxCombo2.RowSource = strSQL
End Sub
'**************** Code End *************

But I can't get it to work.

1. What does "from Categories" in the SQL reference to? What would that
translate to in my case, invTble maybe?

2. Should cbxCombo1 really set to "Field List" (table/query seems better
choice for some reason)?

3. And what does he mean by setting "RowSource to a table Category"? Is that
custTbl in my case?

Kindly,
Mikael
Sweden

 

[Damon Heron]

You almost got it. Are your tables called "custTbl" and "invTbl"? If so,
just substitute the word Category in the example. However, I would caution
about using Customer ID as a selection in the first combobox. In the first
combobox, I would set the column count to two and have the custID and the
customername with the custID column set to 0 width. It is much easier to
remember names rather than numbers.

Damon

 

[Mikael Lindqvist]

Thanks for your input, but I'm missing something here.

Just to keep it simple, assume I have all data in 1 table (table1).

First column is CustID and second column is InvoiceNo.

I want to enter CustID in first combo-box (cbxCombo1) and then get all the
invoices in second combobox (cbxCombo2).

1. In cbxCombo1 properties: row source (empty), row source (table1), type of
row source (field list), bounded column (0) <- guess that means I should pick
the first column (CustID)?!

2. In cbxCombo2 properties: (control source (empty), row source (empty),
bounded column (1).

Q: Can't find a "column count" property... unless you mean "bounded column"

And then in cbxCombo1 after update:

Private Sub cbxCombo1_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!cbxCombo1
strSQL = strSQL & " from table1"
Me!cbxCombo2.RowSourceType = "Table/Query"
Me!cbxCombo2.RowSource = strSQL
End Sub

But it's not working...

(In cbxCombo1 I can select all the field names... but I want to see a list
of values from column 1, ie. CustID and nothing happens after selection...
cbxCombo2 is empty).

Kindly,
Mikael
Sweden