How To pass a table name as an argument to a procedure call 1

[maupiti]

Access 2003
Name of the actual table: Table_Bus

Compile error: ByRef argument type mismatch
The program highlight yellow the calling statement below,
and specifically the argument Table_Bus

///////////////////////////////////////////////////////

Call Create_SQL_String(Me!List_Bus, Table_Bus, Public_Bus_String, Public_Bus_SQL)

//////////////////////////////////////////////////////

Private Sub Create_SQL_String(ByRef Ctl_List_Box As Control, ByRef The_Table, ByRef Text_String As String, _
ByRef SQL_String As String)

Dim varItem As Variant
Dim strSQL As String
Dim strSQL_No_Comma_At_End As String

For Each varItem In Ctl_List_Box.ItemsSelected
strSQL = strSQL & Ctl_List_Box.ItemData(varItem) & ", "
Next varItem

strSQL_No_Comma_At_End = Left$(strSQL, Len(strSQL) - 2)
Text_String = strSQL_No_Comma_At_End

SQL_String = "SELECT * FROM The_Table " & _
"WHERE The_Table.From_Section IN (" &
strSQL_No_Comma_At_End & ")" & _
"ORDER BY The_Table.From_Section"

End Sub

 

[PHV]

Call Create_SQL_String(Me!List_Bus, "Table_Bus", Public_Bus_String, Public_Bus_SQL)
...
SQL_String = "SELECT * FROM [" & The_Table & "] " & _
"WHERE From_Section IN (" &
strSQL_No_Comma_At_End & ")" & _
"ORDER BY From_Section"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

maupiti,

Try enclosing the name of the table in quotes, like this:

Call Create_SQL_String(Me!List_Bus, [COLOR=red]"Table_Bus"[/color], Public_Bus_String, Public_Bus_SQL)

HTH,

Ken S.

 

[MajP]

Minor point, but also declare as as string

"ByRef The_Table as string"

without a type declaration is becomes a variant. Bad habit to get into.