Need to fill a listbox with optiongroup

[rb9999]

I am trying to populate a listbox based on option boxes that are chosen in an optiongroup. The optiongroup is called frameTable. I have three tables (Table1 - Table3) that contain the values I want to list in the listbox.

If you choose the first option button, then the stuff in Table1 will populate the listbox. Case 1 below.

If you choose the second option button then...you know the rest.

The option value for each of the buttons are 1,2 and 3 respectively. I am attempting to push the Rowsource property of the listbox based on which button is selected.

When I run the UpdateListBox sub I get a "Runtime Error 424, object required. It doesn't like the action statement of each case statement below.

I am guessing my syntax is wrong or something. Any ideas?

Here is the code.

Sub UpdateListBox()
    Select Case [Forms]![frmMain]![frameTable]
    Case 1
        [Forms]![frmMain]![lstElements].[RowSource] = "SELECT tblTable1.FieldName, tblTable1.Element FROM tblTable1;"
    Case 2
        [Forms]![frmMain]![lstElements].[RowSource] = "SELECT tblTable2.FieldName, tblTable2.Element FROM tblTable2;"
    Case 3
        [Forms]![frmMain]![lstElements].[RowSource] = "SELECT tblTable3.FieldName, tblTable3.Element FROM tblTable3;"
    End Select
    
End Sub

 

[rb9999]

I just figured this out by the way. Thought I'd post it here in case someone else has this problem.

All I had to do was remove the brackets from around RowSource above. So each line would read:

[Forms]![frmMain]![lstElements].RowSource = .....

 

[TheAceMan1]

How are ya rb9999 . . . . .

Hmmmmmm . . . . thats odd, [purple]espcially since your syntax appears correct![/purple].

Go over the code and insure [blue]Table Names[/blue] as well as the [blue]ListBox Name[/blue] are correct (you probably already have). Mispelling or wrong name could raise this error. It appears you know what your doing, so I don't really think this is the problem, but its doesn't hurt to check.

If name & spelling are correct, then, since it saids [blue]Object Required![/blue], lets give it one. Test your modified code below, which includes [purple]Full Ojectivity![/purple].

[blue]Sub UpdateListBox()
    Dim [purple]frm[/purple] As Form, [purple]ctlFrame[/purple] As OptionGroup, [purple]prpRS[/purple] As Property
    
    Set [purple]frm[/purple] = Forms!frmMain
    Set [purple]ctlFrame[/purple] = frm!frameTable
    Set [purple]prpRS[/purple] = frm!lstElements.Properties("RowSource")
    
    Select Case [purple]ctlFrame[/purple]
    Case 1
        [purple]prpRS[/purple] = "SELECT tblTable1.FieldName, tblTable1.Element FROM tblTable1;"
    Case 2
        [purple]prpRS[/purple] = "SELECT tblTable2.FieldName, tblTable2.Element FROM tblTable2;"
    Case 3
        [purple]prpRS[/purple] = "SELECT tblTable3.FieldName, tblTable3.Element FROM tblTable3;"
    End Select
    
End Sub[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

Hay rb9999 . . .

Glad to see ya fixed it, but now I gotta research why brackets made a difference. Their use is for names that have spaces in them. Normally, it doesn't matter otherwise.

See Ya! . . . . . .