• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Run Access Parameter queries from a VB interface when name or number of arguments in unknown.

Run Access Parameter queries from a VB interface when name or number of arguments in unknown.

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.




Rate Run Access Parameter queries from a VB interface when name or number of arguments in unknown.

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

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)