VBA grogramatically change query SQL based on user input

Question

I'm attempting to create a macro that based on a user input (on an excel sheet) will pull data from a query I made in Access. In order for it to pull only the applicable lines (rows) of data it needs to edit the WHERE statement accordingly. I have adapted the following code from a previous question but I am running into issues when I try to replace the SQL.

Private Sub CommandButton4_Click()
Const DbLoc As String = "MYfilepath"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim wb1 As Workbook, ws1 As Worksheet, ws2 As Worksheet, SQL As String, recCount As Long
Set wb1 = Workbooks("mytool.xlsm")
Set ws1 = wb1.Sheets("Inputs")
Set ws2 = wb1.Sheets("raw")
Set db = OpenDatabase(DbLoc)
Set userinput = ws1.Range("D6")


SQL = "SELECT Dock_Rec_Problems.Merch_Name, Dock_Rec_Problems.Vendor_Error_Code, Dock_Rec_Problems.DC, Dock_Rec_Problems.Vendor_ID_IP, Dock_Rec_Problems.Vendor_Name, Dock_Rec_Problems.PO_Number, Dock_Rec_Problems.SKU_No, Dock_Rec_Problems.Item_Description, Dock_Rec_Problems.Casepack, Dock_Rec_Problems.Retail, Dock_Rec_Problems.Num_Of_Cases, Dock_Rec_Problems.Dock_Rec_Problems_DGID"
SQL = SQL & "FROM Dock_Rec_Problems;"
SQL = SQL & "WHERE [Dock_Rec_Problems_DGID] =" & userinput

Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)
If rs.RecordCount = 0 Then
    MsgBox "Not found in database", vbInformation + vbOKOnly, "No Data"
    GoTo SubExit
End If

ws2.Range("A1").CopyFromRecordset rs

SubExit:
On Error Resume Next
    Application.Cursor = xlDefault
    rs.Close
    Set rs = Nothing
Exit Sub

End Sub

Let me know if there is anything I can clear up...thanks!

Original Query SQL

SELECT Dock_Rec_Problems.Merch_Name, Dock_Rec_Problems.Vendor_Error_Code, 
Dock_Rec_Problems.DC, Dock_Rec_Problems.Vendor_ID_IP, 
Dock_Rec_Problems.Vendor_Name, Dock_Rec_Problems.PO_Number, 
Dock_Rec_Problems.SKU_No, Dock_Rec_Problems.Item_Description, 
Dock_Rec_Problems.Casepack, Dock_Rec_Problems.Retail, 
Dock_Rec_Problems.Num_Of_Cases, Dock_Rec_Problems.Dock_Rec_Problems_DGID
FROM Dock_Rec_Problems;

Single input SQL

SELECT Dock_Rec_Problems.Merch_Name, Dock_Rec_Problems.Vendor_Error_Code, Dock_Rec_Problems.DC, Dock_Rec_Problems.Vendor_ID_IP, Dock_Rec_Problems.Vendor_Name, Dock_Rec_Problems.PO_Number, Dock_Rec_Problems.SKU_No, Dock_Rec_Problems.Item_Description, Dock_Rec_Problems.Casepack, Dock_Rec_Problems.Retail, Dock_Rec_Problems.Num_Of_Cases, Dock_Rec_Problems.Dock_Rec_Problems_DGID
FROM Dock_Rec_Problems
WHERE (((Dock_Rec_Problems.Dock_Rec_Problems_DGID)="D040323000"));

Double input SQL

SELECT Dock_Rec_Problems.Merch_Name, Dock_Rec_Problems.Vendor_Error_Code, Dock_Rec_Problems.DC, Dock_Rec_Problems.Vendor_ID_IP, Dock_Rec_Problems.Vendor_Name, Dock_Rec_Problems.PO_Number, Dock_Rec_Problems.SKU_No, Dock_Rec_Problems.Item_Description, Dock_Rec_Problems.Casepack, Dock_Rec_Problems.Retail, Dock_Rec_Problems.Num_Of_Cases, Dock_Rec_Problems.Dock_Rec_Problems_DGID
FROM Dock_Rec_Problems
WHERE (((Dock_Rec_Problems.Dock_Rec_Problems_DGID)="D040323000")) OR (((Dock_Rec_Problems.Dock_Rec_Problems_DGID)="D040323012"));

Can you more clearly explain what is the problem? What you are expecting to happen, what is actually happening? — pgSystemTester
– pgSystemTester, Commented May 15, 2018 at 21:38
Yes, so the error I am getting is on the line below my ' Replace the SQL comment. If a user puts D040323000 in a cell and runs my macro it should pull only a single row of data back to the worksheet. If a user inputs D040323000, D040323012 it should return two rows. — The Dude MAN
– The Dude MAN, Commented May 15, 2018 at 21:49
Shouldn't that be WHERE [column] IN ([comma-separated-values])? Also, Bobby Tables. — Mathieu Guindon
– Mathieu Guindon, Commented May 15, 2018 at 21:50
So I added what the SQL looks like when I manually edit it in access but is there a way I can have VBA do this for me based on the actual input from a user? Thanks' — The Dude MAN
– The Dude MAN, Commented May 15, 2018 at 22:00
What's the maximum number of selections you would expect to have? 10? — pgSystemTester
– pgSystemTester, Commented May 15, 2018 at 22:05

Parfait · Accepted Answer · 2018-05-18 21:08:13Z

1

Because the size of your user input is open-ended, consider using a temp table saved in MS Access with exact structure as your query (can be built with: SELECT * INTO temp_table FROM myquery). Then, with each call of the Excel macro:

Clean the temp table out with DELETE.
Iterate through the user input Excel range of cells to append needed rows to table with INSERT INTO...SELECT.
Create recordset from temp table.

And once again, here is a prime use case for SQL parameterization especially since the query receives user input. A clever, malicious user can potentially clean out your database! But at the very least, code is arguably more maintainable. Because you are using DAO, consider QueryDefs to bind parameter value to a prepared, saved query and then bind into a recordset.

SQL (save as an MS Access stored action query)

PARAMETERS [userparam] TEXT(255);
INSERT INTO Excel_Table (Merch_Name, Vendor_Error_Code, DC, Vendor_ID_IP,
                         Vendor_Name, PO_Number, SKU_No, Item_Description,
                         Casepack, Retail, Num_Of_Cases, Dock_Rec_Problems_DGID)
SELECT d.Merch_Name, d.Vendor_Error_Code, d.DC, d.Vendor_ID_IP, 
       d.Vendor_Name, d.PO_Number, d.SKU_No, d.Item_Description, 
       d.Casepack, d.Retail, d.Num_Of_Cases, d.Dock_Rec_Problems_DGID
FROM Dock_Rec_Problems d
WHERE d.[Dock_Rec_Problems_DGID] = [userparam];

VBA

...
Dim qdef As DAO.QueryDef
Dim cel As Range

Set qdef = db.QueryDefs("mySavedQuery")

' CLEAN OUT TEMP EXCEL TABLE
db.Execute "DELETE FROM Excel_Table"

' ITERATIVELY APPEND TO EXCEL TABLES
For Each cel In userinput.Cells
    qdef!userparam = cel.Value            ' BIND PARAM
    qdef.Execute dbFailOnError            ' EXECUTE ACTION
Next cel

' OPEN RECORDSET TO TABLE
Set rs = db.OpenRecordset("Excel_Table", dbOpenSnapshot)

If rs.RecordCount = 0 Then
    MsgBox "Recieving problem not found in database", vbInformation+vbOKOnly, "No Data"
    GoTo SubExit
End If

ws2.Range("A1").CopyFromRecordset rs
.......

edited May 18, 2018 at 21:08

answered May 16, 2018 at 20:24

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

The Dude MAN Over a year ago

thanks for your answer. I'm having a little trouble saving the action query correctly (super bad with Access). Do you mind walking me through it? When I try to running your SQL a msg box says 'characters found after end of SQL statement.

Parfait Over a year ago

Whoops! I accidentally had a premature semicolon. I removed it. Now save the SQL in the query design of MS Access (SQL mode). Be sure to have created Excel_Table beforehand.

The Dude MAN Over a year ago

Getting application defined error on set qdef = db.querydefs("myquery") line. Other saved queries work but the action query we saved for some reason gets this error... any ideas?

Parfait Over a year ago

MS Access does not allow you to save a query with a syntax error. Are you sure the database has a query object named myquery fully saved? And I assume db is a DAO.Database.

The Dude MAN Over a year ago

it's actually named "RAG_query" and is correctly saved in the database. Something to note... When I try to run the action query (RAG_query) and enter a userparam manually, it says "the INSERT INTO statement contains the following unknown field name 'Vendor_Error_Code'

|

Parfait · Accepted Answer · 2018-05-16 20:29:02Z

0

There are a few problems with the code you've displayed. For instance, the strNewFields variable is attempted to be used, before you've set it to anything, here:

strNewSQL = strNewSQL & Replace(WHERE_FIELDS, "<INSERT FIELDS>", strNewFields)

At this point strNewFields is totally blank, but you're trying to do a replace.

I would suggest:

Change you WHERE_FIELDS Const from

Const WHERE_FIELDS As String = "WHERE " _
     & "(((Dock_Rec_Problems.Dock_Rec_Problems_DGID) = <INSERT FIELDS>)); "

to

Const WHERE_FIELDS As String = "WHERE " _ 
     & " [Dock_Rec_Problems].[Dock_Rec_Problems_DGID] IN (<INSERT FIELDS>); "

I find this easier to read then all the nested brackets, it removes the equals sign in preference of the IN() statement.

Now you want to populate the strNewFields variable with whatever inputs they gave you. Probably using a Do While Loop to iterate through the INPUTS. Each input is added to the strNewFields variable something like this.

Dim rs as Recordset
Set RS = currentdb.mydataset  ' You need to modify this line
rs.Open
strNewFields = strNewFields & "'" & rs("InputFieldName") & "'"
rs.MoveNext

Do While rs.EOF = False
    strNewFields = strNewFields & ",'" & rs("InputFieldName") & "'"
Loop

strNewFields = StrNewFields & ")"

Now that you have strNewFields populated you can simply run your replace()
```
Replace(WHERE_FIELDS, "<INSERT FIELDS>", strNewFields)
```

You need to look at the order in which you are setting variables though, as pointed out above, you've got some order of event issues.

Michael

edited May 16, 2018 at 20:29

Parfait

108k19 gold badges103 silver badges138 bronze badges

answered May 15, 2018 at 23:15

The Special

417 bronze badges

5 Comments

dbmitch Over a year ago

OP is also going to have to remove strNewFields and the associated replace from BASIC_FIELDS

The Dude MAN Over a year ago

Working on the second part... currentdb.mydataset. Is this a reference to the Access DB as a whole or a specific table name?

The Special Over a year ago

@Mnathan: Okay so the currendb.mydataset part is referencing the INPUT table where you users get to say what indices they are looking for, and is just a sample as I don't know what your using. It probably should look more like : set rs = currentdb.OpenRecordset( italic_yourdatatablename_italic, dbOpenDynaset). Sorry, my formatting isn't working well on this question for some reason.

The Dude MAN Over a year ago

Ok thanks! so the user inputs are located on a worksheet. so something like set rs = ws1.Range("D6")? I'll also probably need to work in a split OP somewhere for when a delimiter is present but for now I'm just trying to get it to work with a single input.

The Dude MAN Over a year ago

@thespecial I changed my code to something I think would be easier to to solve. It also might be easier to tell what I am trying to accomplish. I still have the same end goal but what do you think about that? The error I am getting now is with my line Set rs = db.openrecordset(SQL, dbOpenSnapshot) Thank you for your help!

Collectives™ on Stack Overflow

VBA grogramatically change query SQL based on user input

2 Answers 2

11 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related