Filtering Recordset in MS Access-Run Time Error, Too Few Parameters!

[Kassimu]

Hi there,

I have a table [DailyUpdates] with thousands of record entries, usually the user searches this table through SearchForm resulting into some recordset. What I need to do on this recordset is to concatenate the contents of all the records for one field-[Description], and populate the resulting into textbox [FaultComms] of a form [UpdateSummary]

Private Sub Form_Load()
Dim db As Database
Dim rstUpdates As Recordset
    Set db = CurrentDb
    Set rstUpdates = db.OpenRecordset("Daily Fault Update", dbOpenDynaset)
If Not rstUpdates.EOF Then rstUpdates.MoveFirst
Do While Not rstUpdates.EOF
    vntTempData = vntTempData & rstUpdates!Description
    rstUpdates.MoveNext
Loop
Me.FaultComms = vntTempData
End Sub

The problem I am having here is, this code gives data from all the records, I only need a filtered output. I tried to open the recordset based on query "Daily Fault Update Query" I end up with run time error '3061' -too few parameters expected 1.

I tried to use filter property of recordset to limit the records and then open another recordset on it. The second sample of code!

Private Sub Form_Load()
Dim db As Database
Dim vntTempData as Variant
Dim rstUpdates As Recordset
Dim rstUpdates1 As Recordset
Dim strOpen As String
strOpen= "[TT_Number] =[Forms]![TXFaults].[TT_Number]"
    Set db = CurrentDb
    Set rstUpdates = db.OpenRecordset("Daily Fault Update", dbOpenDynaset)
    rstUpdates.Filter=strOpen
    Set rstUpdates1= rstUpdates.OpenRecordset
If Not rstUpdates1.EOF Then rstUpdates1.MoveFirst
Do While Not rstUpdates1.EOF
    vntTempData = vntTempData & rstUpdates1!Description
    rstUpdates1.MoveNext
Loop
Me.FaultComms = vntTempData
End Sub

I am also getting the rutime error too few parameters. Expected 3 on this line

Set rstUpdates1= rstUpdates.OpenRecordset

[Stewart Ross]

Hi. An irritating change introduced in Access 2003 was that the default type of recordset (previously DAO) became the ADO version. As the call parameters of the OpenRecordSet and other methods differs slightly between versions run-time errors result when using the DAO syntax with what Access may presume is an ADO recordset. You should be able to resolve this by explicitly declaring the recordset variables as DAO recordsets:

Dim rstUpdates as DAO.RecordSet
Dim rstUpdates1 as DAO.RecordSet

You will need to check that you have a reference to the DAO object library set - which you do from the VB editor window. Tools, References, make sure that Microsoft DAO 3.6 object library is ticked (or the nearest to it you have).

-Stewart

[Kassimu]

I am still getting the same error!
I remember to have read somewhere that One has to define Query parameter If I s/he has to use query as the base for the recordset.
How to do that?

[Kassimu]

I have got it sorted out! I changed the code to:

Private Sub Form_Load()
   Dim vntTempData as Variant
   Dim rstUpdates As DAO.Recordset
   Set rstUpdates = CurrentDb.OpenRecordset("SELECT * FROM [Daily Fault Update] WHERE [TT_Number] = " & [Forms]![TXFaults].[TT_Number])
   While Not rstUpdate.EOF
      vntTempData = vntTempData & rstUpdates!Description
      rstUpdates.MoveNext
   Wend
   Me.FaultComms = vntTempData
End Sub

And it worked!