Combobox

[jeffcravener]

How do I, if its possible, in a form, populate...well....here..let me tell you what i have

I have 5 tables.......one of which i will create a form for to enter data, the other 4 as support for the main table.

I want to put on the form a combo box that will hold the values of table 2....and then what i want to do is have a second combo boxes that, depending on what is chosen in the first, will determine which table the second combobox will be attached to.....for each of the other tables (3-5)....

But, i don't want those visible until a selection is made from that frist combo box...and that will hold the values of the 2 table....and depending on what is selected from the first combo box will determine which of the other three combos will be shown.....

is this possible in Access? (we use access 97)

basically:

is it possible for me to have just the two combos, and when a certail list item is selected in the first item, it will decide which of the tables that second combo box is pointed to?

 

[joebater]

Hi Jeff

Yes, it's actually quite easy. Consider the following code:

Option Compare Database
Option Explicit

'initially start with the second combo box invisible
Private Sub Form_Load()
Combo2.Visible = False
End Sub

'the first combo box is probably designed using the combo box
'wizard and will have the rowsource set - let me know if I'm
'assuming incorrectly
Private Sub Combo1_AfterUpdate()
'ignore error handling for this example

'I've done a simple select case looking for the index value on the
'table which is matched against the bound column in the
'properties of the combo box
Select Case Combo1.Value
Case 1
'manually set the row source of the 2nd combo box
'you may want to use more sophisticated SQL than this
Combo2.RowSource = "SELECT * FROM Table2;"
Case 2
'and again....
Combo2.RowSource = "SELECT * FROM Table3;"
End Select

'sanity check
Debug.Print Combo2.RowSource

'now make the combo box visible
Combo2.Visible = True

End Sub

You would probably wish to ensure that none of the above occurs unless the values that you wish for are selected also. An if condition then statement would be sufficient.

blue skies
j.

 

[jeffcravener]

COOL!

It's just like VB, which I know....but how do you get code into an Access form?

 

[indesisiv]

Code

In the design view of your form.
If you get to this then look in the view menu there is an item that says code.
This brings up your background code. Just like vis basic..

Hope this helps
Steve

 

[jeffcravener]

haha....

woops......

ok....again, new at Access here....

how do i make the option i select in the second combo box go into the fielf for that record?

And, when i hit next, to enter the next record, it didn't clear that box, and when i did change it, it changed the first records choice...

 

[bradcccs]

You will need to "bind" the combo box.

Look at the properties of the combo box, and set the "Bound Column" property to the relevant column from your rowsource.

Brad

 

[jeffcravener]

yeah....

i just found that...so thats working fine....but in VB when you go to combbox, you can use the arrwo keys to traverse the list....this one isn't doing it....can it?