Excel VBA with SQL - Using an Input Box to filter Criteria

[heatherjean]

Hello - Below is the VBA Macro with the SQL. The input box is prompting me, but not producing a result. Please help update my code if possible:


Sub SO()
'
' SO Macro
'


'

'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
Dim myVar As Long




'Setup the connection string for accessing MS SQL database
ConnectionString = "DSN=SQL M2M 2;Description=M2M7.0;UID=hunrast;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=ADMIN-PC1;DATABASE=M2MDATA01"


'Opens connection to the database
cnn.Open ConnectionString

'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
cnn.CommandTimeout = 900

'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid
StrQuery = "select jomast.fjobno, jomast.fsono, jodbom.fbompart, jodbom.fbomrev, jodbom.fbomsource, jodbom.fpono, jodbom.fpoqty, jodbom.ftotqty, poitem.fordqty, poitem.frcpqty from dbo.jomast jomast, {oj dbo.jodbom jodbom left outer join dbo.poitem poitem on jodbom.fbompart = poitem.fpartno and jodbom.fbomrev = poitem.frev and jodbom.fjobno = poitem.fjokey } where jomast.fjobno = jodbom.fjobno and ((jomast.fsono = '" & myVar & "' and jodbom.fbomsource in ('B','S'))) order by jomast.fjobno, jodbom.fbompart, jodbom.fbomrev"

'Performs the actual query
rst.Open StrQuery, cnn
myVar = InputBox("Enter the SO", "Example: 036978")

'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
Worksheets("Query").Range("A4").CopyFromRecordset rst



End Sub

 

[Craggs82]

Morning,

So you're currently defining the variable that is need in the SQL after you've opened the connection and pulled the recordset.

((jomast.fsono = '" & myVar & "' and jodbom.fbomsource in  ('B','S'))) order by jomast.fjobno, jodbom.fbompart, jodbom.fbomrev"
    
'Performs the actual query
    rst.Open StrQuery, cnn
    myVar = InputBox("Enter the SO", "Example: 036978")

just move the myVar line above the SQL so its passed to the recordset pull.

Have fun!

 

[heatherjean]

Thanks for responding... but I had already tried that with the same thought. Placing it before also produced an empty result. I can't help but think it has something to do with the fact that I'm entering numbers, but the field id within the table is character. The apostrophe's treat it as text, which should pass. Please confirm the use of apostrophe/quotes in the test below vs. original post. Do you agree?

--->To test, I deactivated the input box and placed the number into the SQL and it produced the desired result:
StrQuery = "select ..... where jomast.fjobno = jodbom.fjobno and ((jomast.fsono = '036978' and jodbom.fbomsource in ('B','S'))) order by jomast.fjobno, jodbom.fbompart, jodbom.fbomrev"

'Performs the actual query
'myVar = InputBox("Enter the SO", "Example: 036978")
rst.Open StrQuery, cnn

An additional thought has to do with the SQL. The input box is coded as a filter and not a parameter. Again, I feel it's coded correctly, just can't figure out why it won't work..ugh!!

Morning,

So you're currently defining the variable that is need in the SQL after you've opened the connection and pulled the recordset.

((jomast.fsono = '" & myVar & "' and jodbom.fbomsource in  ('B','S'))) order by jomast.fjobno, jodbom.fbompart, jodbom.fbomrev"
    
'Performs the actual query
    rst.Open StrQuery, cnn
    myVar = InputBox("Enter the SO", "Example: 036978")

just move the myVar line above the SQL so its passed to the recordset pull.

Have fun!

 

[Craggs82]

Dim myVar as String

I didnt even notice that :D
In the example case the myVar return value will remove the leading 0 because its expecting a Long.

 

[heatherjean]

The update produced a result, but not the desired:

Changed to:
Dim myVar as String

Maintained the order suggested:
myVar = InputBox("Enter the SO", "Example: 036978")
rst.Open StrQuery, cnn

Maintained the myVar quotes to indicate text
....where jomast.fjobno = jodbom.fjobno and ((jomast.fsono = ' " & myVar & " ' and jodbom.fbomsource in ('B','S')))

The result produced ALL SO's and disregarded the input box entry.
Frustrating! Any other options besides (as Long) and (as String) for numbers coded as characters? or alternative options for filtering the data that is easy for the front end user?

Thank you for your responses & support. I really appreciate the time and effort