Creating a Recordset from a Parameter Query

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

I need to create a recordset from a parameter query and
then display the records in an unbound single form. I
would like to display a popup form (frmCustomerCriteria),
have the user enter the criteria on the form and then use
the parameter query (qryCustomerData)to create the
recordset. Is there an example of how to do this. Thanks.

I tried to do the above with the follow code, but it
gives me an error: "Invalid SQL statement;
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT'
or 'UPDATE'"

Option Compare Database
Option Explicit
Dim intRecType As Integer
Dim objConn As ADODB.Connection
Dim rsCustomer As ADODB.Recordset

Private Sub cmdFind_Click()
Set objConn = CurrentProject.Connection
Set rsCustomer = New ADODB.Recordset
strFunction = "Maintenance"
DoCmd.OpenForm "frmCustomerCriteria"
rsCustomer.Open "qryCustomerData", objConn,
adOpenDynamic, adLockOptimistic
MsgBox "The number of records are " &
rsCustomer.RecordCount, vbInformation

End Sub
 
Hi Gary:

Let's do a rewrite-

Option Compare Database
Dim intRecType As Integer
Dim objConn As ADODB.Connection
Dim rsCustomer As ADODB.Recordset
Dim SQLState As String
Option Explicit

'----------------------------------------------------
Private Sub cmdFind_Click()
Set objConn = CurrentProject.Connection
Set rsCustomer = New ADODB.Recordset
rsCustomer.ActiveConnection = CurrentProject.Connection
strFunction = "SELECT Maintenance.* FROM Maintenance"
rsCustomer.Open strFunction
MsgBox "The number of records are " & rsCustomer.RecordCount,
vbInformation
rsCustomer.Close
End Sub
'----------------------------------------------------

That should theoretically work... let us know if it does.

Regards,
Al
 
Back
Top