Linked Comboboxes

[India_Tech_Geek]

HI All,

I want to create two combo-boxes in my MS Access 2000 form.

The first combo-box displays all column headings of a data-base table(Main_Table) of my Access data-base.

When I select a value from the first combo-box, i need the second combo-box to be populated with the all the values of the selected column from the table(Main_Table).

Please help me to solve this issue.

Thanks

 

[mshirwan]

make a search on the forum

I saw something similar on this form, an example, make a search u will find it, make search for something like linked combo boxes

 

[Len Boorman]

The first combo-box displays all column headings of a data-base table(Main_Table) of my Access data-base.

Do not think you can actually do this because a combo box displays attribute values not attribute names.

Edit. This is incorrect . See Mile's post. You learn something new every day


Len B

 

[Mile-O]

You can set the combo's RowSourceType to Field List and the set the RowSource to the table you want to display the fields for.

 

[India_Tech_Geek]

Second Part of my Query

Thanks a lot Mile.

Actually I cud populate the first combo-box. What I need to know is how to populate the second combo-box as I cant specify the RawSource directly in the form "Select column-name from table"
as the column-name is infact the selected value of the first combo-box.

So please throw some light as to how to populate the second combo-box with values which are the values of the column selected by the first combo-box.

Thanks

 

[Mile-O]

I suppose you'd need to build a querydef here:

i.e.

Private Sub SecondCombo_AfterUpdate()

    Dim db as DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb

    strSQL = "SELECT " & Me.SecondCombo & " FROM MyTable;"

    On Error Resume Next
    db.QueryDefs.Delete "NewQuery"
    On Error Goto Err_ErrorHandler
    Set qdf = db.CreateQueryDef("NewQuery", strSQL)

Exit_ErrorHandler:
    strSQL = vbNullString
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

Err_ErrorHandler:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler
End Sub

 

[India_Tech_Geek]

Thanks!!!!

Thanks Mile.
U R WONDERFUL!!!!!!!!!!!!!!1

 

[India_Tech_Geek]

Please Review

Hi M-o-P,

I was doing some R&D on the snipplet that you had given.
I tried something like the one shown below...

This looks to be simpler...so I wonder if it has any adverse effects...


Private Sub Combo1_AfterUpdate()
Dim strSQL As String
strSQL1 = "SELECT DISTINCT " & Me.Combo1 & " FROM MAIN_TABLE;"
On Error GoTo Err_ErrorHandler
Combo2.RowSource = strSQL
Combo2.Requery

Exit_ErrorHandler:
strSQL = vbNullString
Exit Sub

Err_ErrorHandler:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler

End Sub

Do you find any problems in the above method.?

Thanks,
AG