EXCEL VBA QUERY - HOW DO I USE VARIABLES

[Evil8]

I want to querry data from an access database using an excel vba userform. The userform has a combobox for the user to choose which customer service rep's records to retrieve.

I keep getting Error:

run-time error '1004';
General ODBC Error

And Debug highlights the line
.Refresh BackgroundQuery:=False

The query runs fine until I add the combobox value.

Private Sub CommandButton1_Click()
'

Workbooks.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MYDATABASE;DBQ=C:\MYDATABASE.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT tbl_Client.cName, tbl_Client.Heading, tbl_Client.CSR, tbl_Client.ProducerNum, tbl_Client.ProspectTag, tbl_ClSite.Address, tbl_ClSite.City, tbl_ClSite.State, tbl_ClSite.Zip, tbl_ClSite.Primary" & Chr(13) & "" & Chr(10) & "" _
, _
"FROM `C:\MYDATABASE`.tbl_Client tbl_Client, `C:\MYDATABASE`.tbl_ClSite tbl_ClSite" & Chr(13) & "" & Chr(10) & "WHERE tbl_Client.ClientNum = tbl_ClSite.ClientNum AND ((tbl_Client.CSR = Me.cb_whoList.Value)) AND ((tbl_ClSite.Primary=1))" & Chr(13) & "" & Chr(10) & "ORDER BY tbl_Client.cName" _
)
.Name = "Query from MYDATABASE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub


Private Sub UserForm_Initialize()
'
'add items to combobox
cb_whoList.AddItem "BJ"
cb_whoList.AddItem "KC"
cb_whoList.AddItem "KV"
cb_whoList.AddItem "MJ"
cb_whoList.AddItem "MR"

End Sub

Once I get this to work I plan on adding another combobox to choose the tbl_Client.ProducerNum value too.

Thanks for your time!

 

[SkipVought]

Hi,

Please post VBA questions in Forum707.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Evil8]

Sorry, thanks.