EXCEL VBA QUERY - HOW DO I USE VARIABLES 1

[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,

Is there any reason why you are ADDing this querytable via code?

I can understand EXECUTING the query with new parameters one it has been added to the sheet. I do that almost every day.

Here's the drill.

BTW, each time your run a QueryTables.ADD, it adds a QueryTable object to you sheet. YOU ONLY NEED ONE!

Insert > Name > Define Names and look for every named range in your sheet that begins with Query_from... and DELETE ALL of them.

1. Add the querytable via Data > Import External Data > New Database Query.

2. Use this code to capture the SQL code.

sub GetQuery()
  dim sSQL as string

  with YourSheetObject.Querytables(1)
     Debug.Print .CommandText
  end with
end sub

View > ImmediateWindow

and observe your SQL Code. Copy it and paste it into the GetQuery code like this...

sub GetQuery()
  dim sSQL as string

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
FROM `C:\MYDATABASE`.tbl_Client tbl_Client, `C:\MYDATABASE`.tbl_ClSite tbl_ClSite
WHERE tbl_Client.ClientNum = tbl_ClSite.ClientNum And ((tbl_Client.CSR = Me.cb_whoList.Value)) And ((tbl_ClSite.Primary = 1))
ORDER BY tbl_Client.cName
 

 with YourSheetObject.Querytables(1)
     Debug.Print .CommandText
  end with
end sub

Hit the Comment Block button in your Edit toolbar THREE TIMES.

Edit > Replace
Find '''
Replace ssql = ssql & "

Now you should have...

Sub GetQuery()
  Dim sSQL As String

sSQL = sSQL & "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"
sSQL = sSQL & "FROM `C:\MYDATABASE`.tbl_Client tbl_Client, `C:\MYDATABASE`.tbl_ClSite tbl_ClSite"
sSQL = sSQL & "WHERE tbl_Client.ClientNum = tbl_ClSite.ClientNum And ((tbl_Client.CSR = Me.cb_whoList.Value)) And ((tbl_ClSite.Primary = 1))"
sSQL = sSQL & "ORDER BY tbl_Client.cName"
 

 With YourSheetObject.QueryTables(1)
     Debug.Print .CommandText
  End With
End Sub

Finally, change you code to...

Sub GetQuery()
  Dim sSQL As String

sSQL = sSQL & "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"
sSQL = sSQL & vblf
sSQL = sSQL & "FROM `C:\MYDATABASE`.tbl_Client tbl_Client, `C:\MYDATABASE`.tbl_ClSite tbl_ClSite"
sSQL = sSQL & vblf
sSQL = sSQL & "WHERE tbl_Client.ClientNum = tbl_ClSite.ClientNum"
sSQL = sSQL & "  And ((tbl_Client.CSR = " & Me.cb_whoList.Value & "))"
sSQL = sSQL & "  And ((tbl_ClSite.Primary = 1))"
sSQL = sSQL & vblf
sSQL = sSQL & "ORDER BY tbl_Client.cName"
 
 With YourSheetObject.QueryTables(1)
     .CommandText = sSQL
     .Refresh false
  End With
End Sub

assuming that tbl_Client.CSR is a NUMERIC value. If not then the Me. value must be in single quotes.

sSQL = sSQL & "  And ((tbl_Client.CSR = '" & Me.cb_whoList.Value & "'))"

Give that a try.


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

 

[Evil8]

>>Is there any reason why you are ADDing this querytable via code?

I want to grab a different record set each time, hence the variables. Once the task is completed the spreadsheet won't be saved and filed, but rather discarded. I have very little experiance with this and I am learning as I go. So your help and guidence is very much apprieciated!

 

[SkipVought]

You can make a parameter query. I do this from time to time. Or I just substitute the value via a variable like you are doing.

Either way, leave the query on the sheet. Just supply a different value to your variable and refresh the query, rather than adding a workbook and adding a qt etc.

In THIS workbook, that you will be saving with your code, you going to be doing this stuff often, right.

You could do it either way. I like my queries all set up and accessible.

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

 

[Evil8]

That makes sense. Thanks!