Incomplete query clause/ Invalid bracketing of name

[st3ve]

I have a 'dynamic' form which can be linked to 1 of 3 tables, the active one being chosen from a combo cboSourceChoice. The tables are called NPD,NPDy,NPDz.

On the form I have another combo Called Combo2, which I use to select the record to display in the form.

In the Row Source I am trying to enter a dynamic sql statement which points to the table selected by combo cboSourceChoice. So far I have tried (amongst others) sql as follows:-

SELECT Fullname FROM " & [Forms].[NPD All Parts SubEdit NEW].cboSourceChoice & ";
..... this gives Error: Invalid bracketing of Name;.

So I tried..

SELECT Fullname FROM " & cboSourceChoice & ";
... and I get Error: Incomplete query clause..

Any ideas (or a donation of hair folicles) gratefully recieved.

 

[alcifer_c]

A couple suggestions...

-Tie the form to queries instead of the tables directly
-Use the "on change" event property of the combo box to change the record source property. A select case might be appropriate.

Just a direction to go...if you need more specifics let me know.

Al

 

[Mile-O]

Just build three queries and on the After_Update even of the combo use a SELECT CASE structure to determine the selection made an dopen the relevant query.

 

[st3ve]

Row Source

I see where you're going.

But I still need to give row source somewhere to fetch its combo list from, ie the Fullname list from the selected (one of the three) tables. I was hoping to refer to the table name in the sql code by referencing cboSourceChoice contents? my problem is how to structure the code correctly in the row source expression.

Perhaps you could clarify your appoach, as perhaps i need to try a new one.

 

[Mile-O]

Private Sub MyCombo_AfterUpdate()
    Select Case Me.MyCombo
        Case Is = "NPD"
            Me.MyControl.Rowsource = "Query1"
        Case Is = "NPDy"
            Me.MyControl.Rowsource = "Query2"
        Case Is = "NPDz"
            Me.MyControl.Rowsource = "Query3"
    End Select
End Sub

 

[st3ve]

Job Done

Thanks for your help. Works a treat now.