Trouble opening a query in VBA 1

[bozic12]

Hi all,
I have a query which I want to pull all records from a table, where one field is equal to the value inputted by the user on a form.
To do this, I have a simple select query, "ToolCharacteristics", from the table, and I want to match the assembly number with that from the "Assembly Number" field on the form "Bill of Materials". To accomplish this, in the criteria field of Assembly Number in the query, i have: [Forms]![Bill of Materials]![Assembly Number]. There are no other criteria
Now when the next form loads, I want to run the query and count the rows, but when it tries to open the query, I get a message saying, "Too few Parameters. Expected 1."
Here is the code I have to open the query.

Private Sub Form_Load()
Dim rst as Recordset, QCount as Integer

Set rst = Application.CurrentDb.OpenRecordset("ToolCharacteristics&quot - This is where the error occurs
QCount = rst.RecordCount
.
.
.

If anyone can offer any help, I would greatly appreciate it.

Thanks,

Jeff

 

[Zathras]

Try this:

Put this in a code module declarations:

Public SelectedAssembly

In the first form capture the datum:

:
:
SelectedAssembly = [Assembly_Number]
:
:

Put this in the second form to simulate processing the recordset:

Private Sub Form_Load()
Dim i As Integer
Dim qry As QueryDef
Dim rst As Recordset
  If SelectedAssembly <> &quot;&quot; Then
    Set qry = CurrentDb.QueryDefs(&quot;TestQ&quot;)
    qry.Parameters(0) = SelectedAssembly
    Set rst = qry.OpenRecordset
    For i = 0 To 3
      MsgBox rst.Fields(0)
      rst.MoveNext
    Next i
    Set rst = Nothing
    Set qry = Nothing
  End If
End Sub

 

[Zathras]

Sorry, that should have been "ToolCharacteristics" instead of "TestQ."

Alternatively, if the first form is still open while you are showing the second form, then you could just use this in the second form's code (no separate code module with public variable needed in that case):

Option Compare Database
Option Explicit

Private Sub Form_Load()
Dim i As Integer
Dim qry As QueryDef
Dim rst As Recordset
  If Forms!UserFormQ![Assembly Number] <> &quot;&quot; Then
    Set qry = CurrentDb.QueryDefs(&quot;ToolCharacteristics&quot;)
    qry.Parameters(0) = [Forms]![Bill of Materials]![Assembly Number]
    Set rst = qry.OpenRecordset
    For i = 0 To 3
      MsgBox rst.Fields(0)
      rst.MoveNext
    Next i
    Set rst = Nothing
    Set qry = Nothing
  End If
End Sub

The point is, to supply parameters, you use a QueryDef object.

 

[bozic12]

Zathras,
Thank you for your response. The first user form will remain open, so I am using your second suggestion. It all makes sense, and I think it will preform exactly what I wanted, however, I'm recieving a type mismatch error on the line: Set rst = qry.OpenRecordset
I'm pretty new to VBA for access, and I can't figure out why. One thing I noticed, and I'm sure this is just my ignorance, but when I declare the rst and qry variables, the Recordset and QueryDef words do not turn blue, like how an integer or string type would. I assume this is just because it is an access specific object and not a typical VBA declaration. In the help, it says that all arguments after the openrecordset are optional for QueryDef objects, so I have no idea why I am recieving this error. Perhaps there is a reference that I must set? hopefully you can help me out here. Thank you much.

-Jeff

 

[Zathras]

Hmmmm. The minimum set of references I need are:

Visual Basic For Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.6 Object Library

This is for Access 97. Access 2K should be similar.

 

[bozic12]

Zathras,
sorry it took me a while to respond to your suggestion, but I had a coupld other bugs I had to work out. I figured out that I had to declare my Access object variables: rst as DAO.recordset and qry as DAO.querydef, instead of just rst as recordset and qry as querydef. Other than that, you suggestion worked out perfectly for me. Thank you.

-Jeff