Copy query parameter

[andyrew30]

Hi All

So the question I have is that I am trying to copy the parameter value that is entered to a variable so that it can be used to run other queries without typing it in over and over. Once my form is finished it will run 4 queries, and the current setup is that I would have to retype the parameter each time.

Thanks!

 

[pbaldy]

I would have the user enter the parameter on the form, and let the queries all get it from there.

 

[andyrew30]

ok. I will try that. I think that may work.

Thanks

 

[pbaldy]

No problem, post back if you get stuck.

 

[andyrew30]

So I set it up to pull from the form as you had suggested. But I can't figure out the code to get the reports to open based on a saved variable from the form. I am currently trying this: Where ScannerTxt is my input box.



Private Sub Command5_Click()

Dim stPartNum As String
Dim stDocName As String
Dim stCond As String

Set stPartNum = Me.ScannerTxt
stCond = "[SAP Product PN] = '" & stPartNum & "'"
stDocName = "RptPackingList"
'open & print report
x = acApp.DoCmd.OpenReport(stDocName, acPreview, , WhereCondition:=stCond)




End Sub

Thanks

 

[pbaldy]

Try:

DoCmd.OpenReport "RptPackingList", acPreview, , "[SAP Product PN] = '" & Me.ScannerTxt & "'"

 

[andyrew30]

Life saver man!!! Thanks

 

[pbaldy]

Happy to help! I don't bother setting variables if I'm only going to use the value once.

 

[andyrew30]

I ran into what I believe will be the last major error in this program and it's still along the lines of the scannertxt. Essentially i am attaching a dlookup function to pull the value of one of the columns in a table when a parameter is entered.


This is currently what my dlookup code looks like, where control plan # is the name of the column I am trying to pull the data from, controlplanlist is the name of the table, and SAP Product PN is the number that is passed through the query.


F01U = DLookup("[Control Plan #]", "ControlPlanList", "SAP Product PN = Me.ScannerTxt")

 

[pbaldy]

Due to the inadvisable spaces, the field name needs to be bracketed.