by Thaddeus Freeman (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Tue 24th October 2000
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Run Access Parameter queries from a VB interface when name or number of arguments in unknown.
API Declarations
a combo box called 'cboquery'
Also bind a MSFLEX grid to a data control called 'data1'
grind name should be 'gridresults'
paste this code and it should work.
VBcoders
dim db as database
dim qry as querydef
Set db = OpenDatabase("A Database")
'scroll through the querydefs and add the names of each query to the combo box
For Each qry In db.QueryDefs
cboquery.AddItem qry.Name
Next qry
db.Close
Exit Sub
app_err:
Set db = Nothing
Call app_err(Err.Number, Err.Description, Me.Caption)
End Sub
Private Sub btnexec_Click()
Dim db As Database
Dim rs As Recordset
Dim qry As QueryDef
Dim param As Parameter
Dim resp As String
On Error GoTo app_err:
If cboquery = "" Then
MsgBox "You must Select a Query to run", vbExclamation, "No Query Selected"
exit sub
End If
Set db = OpenDatabase("A Database")
'Get the query name you want to run from the cbobox name 'cboquery'
Set qry = db.QueryDefs(cboquery)
'go through each parameter and get a value
'this code assumes string because that's what I am using, but you
'may need to do some type conversion for other types like integer
For Each param In qry.Parameters
resp = InputBox(param.Name, "Enter Parameter Value")
If resp = "" Then 'person hit cancel exit sub
db.Close
Exit Sub
End If
'set the value of the parameter to user input
qry.Parameters(param.Name).Value = resp
Next param
Screen.MousePointer = vbHourglass
'open the recordset and set it to a recordset object
Set rs = qry.OpenRecordset
'set the rs of the data control to rs
Set Data1.Recordset = rs
'refresh the grid to show the results
grdresults.Refresh
Screen.MousePointer = vbDefault
Exit Sub
app_err:
Set db = Nothing
Call app_err(Err.Number, Err.Description, Me.Caption)
No comments have been posted about Run Access Parameter queries from a VB interface when name or number of arguments in unknown.. Why not be the first to post a comment about Run Access Parameter queries from a VB interface when name or number of arguments in unknown..
