Pass through query with userinput in access form

Question

I have Access front end which is powered by SQL Server in backend.

I made a form which requires user input, based on the input it should run a pass through query and should display result in the subform. I have read on internet this cannot be done through sql command and it requires sql coding to be wrapped in VBA coding.

I have made a code this like this:

Private Sub id_AfterUpdate()
Dim MyDb As Database, MyQry As QueryDef
Set MyDb = CurrentDb()
Set MyQry = MyDb.CreateQueryDef("")

MyQry.Connect = "ODBC;DSN=mikecollections;UID=***;PWD=****;DATABASE=mikecollections;"
MyQry.SQL = "select currency.tb_coins.id,documenttype,documentsubtype,documentname" & _
            "from currency.tb_coins" & _
            "inner join collectibles.tb_documents on tb_coins.id=collectibles.tb_documents.prodid" & _
            "where currency.tb_coins.id=[forms]![test_form]![id]"

End Sub

This code should fire after I enter value in the id field in the form, but nothing happens. I do not know how to make this code work. Im new to SQL and VBA, Pls help!

I added the line and hit F5, it opens with Macro box, i dont see any MsgBox. — Mike
– Mike, Commented May 2, 2016 at 13:06
You are right, i changed the name from "id" to "user input", now when i update the field, i get a popup box with the "select currency.tb_coins.id,documenttype,documentsubtype,documentname & _ from currency.tb_coins & _ inner join collectibles.tb_documents on tb_coins.id=collectibles.tb_documents.prodid & _ where currency.tb_coins.id=[forms]![test_form]![id], this is basically the query without quotes — Mike
– Mike, Commented May 2, 2016 at 13:20
Thank you, access/VBA is not converting "[forms]![test_form]![userinput]" to a value, instead it passes the whole code to sql server. What should be a better code in VBA which can take input from form? — Mike
– Mike, Commented May 2, 2016 at 14:19
Well i added the code but no change in result. Maybe im not doing it correctly. — Mike
– Mike, Commented May 2, 2016 at 14:30

johnDanger · Accepted Answer · 2019-11-15 21:53:06Z

1

I created a stored procedure in sql server and created a parameter in access to pass into sql server and got it working. Thank you Hansup and iDevlop for all the help.

Private Sub userinput_AfterUpdate()
Dim qrypass As DAO.QueryDef
Dim rst As DAO.Recordset

Set qrypass = CurrentDb.QueryDefs("qu_test")
qrypass.SQL = _
    "exec collectibles.sp_coinsvsdocuments " _
    & "@userinput=" _
    & Forms!test_form!userinput

End Sub

edited Nov 15, 2019 at 21:53

johnDanger

2,37920 silver badges25 bronze badges

answered May 3, 2016 at 4:14

Mike

691 silver badge11 bronze badges

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

1 Comment

Patrick Honorez Over a year ago

That's excellent. You can also use a SQL Server table valued Function for that.

June7 · Accepted Answer · 2022-07-19 02:45:18Z

1

You logically can't refer to an Access object in a passthru query.
So your sql should pass the value of the control, instead of referencing the control itself.
If id is a long:

MyQry.SQL = "select currency.tb_coins.id,documenttype,documentsubtype,documentname" & _
            "from currency.tb_coins" & _
            "inner join collectibles.tb_documents on tb_coins.id=collectibles.tb_documents.prodid" & _
            "where currency.tb_coins.id= " & [forms]![test_form]![id]

Then you need to add the code to assign that querydef to a recordset, in order to read it. Something like:

set rs = myQry.OpenRecordset

If you prefer, you can also name your queryDef. it will then be available like any other query:

Set MyQry = MyDb.CreateQueryDef("someName")
'''specify all properties here...
doCmd.OpenQuery "someName"

edited Jul 19, 2022 at 2:45

June7

21.7k8 gold badges28 silver badges36 bronze badges

answered May 2, 2016 at 13:05

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

2 Comments

Mike Over a year ago

Thank you, how do i make access to pass input to SQL server? If i run the query alone by giving some value to the id it works fine.

Mike Over a year ago

Thank you, i have set rs, now how to map the form id field to recordset?

John Henriksen · Accepted Answer · 2021-09-23 08:50:19Z

Here is a solution for all Microsoft Access

just copy the below code into a Microsoft Access Module and change the parameter

Sub test()
' The "Kim_ZZ_Orderlines" has a string 'arg1' in the sql so we just replace this 
' with the new value

RunPassThrough "KIM_ZZ_Orderlines", "Indput value", "10"
End Sub


Sub RunPassThrough(QueryToRun, ArgPrompt, ArgDefault)
' John Henriksen
' 2021-09-23
' Sub procedure to run a Pass-through query with argument
' in the query there must be the string 'Arg1" you want to replace
' e.g. AND APPS.OE_ORDER_LINES_ALL.ATTRIBUTE1     = 'arg1'

arg1 = VBA.InputBox(ArgPrompt, ArgPrompt, ArgDefault)

Dim db As DAO.Database
Dim qry, qryNy As String
Dim qrydefOrginal As DAO.QueryDef


Set db = CurrentDb
Set qdef = db.QueryDefs(QueryToRun)

' Save the original sql
qry = qdef.sql


' Make a new sql, here we search for the string "arg1" and replace with our arg1 value
qryNy = VBA.Replace(qry, "arg1", arg1)

' Set the new sql
qdef.sql = qryNy


DoCmd.OpenQuery QueryToRun

' Set the original sql
qdef.sql = qry


Set qdef = Nothing
Set db = Nothing


End Sub

Collectives™ on Stack Overflow

Pass through query with userinput in access form

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related