423,850 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

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

P: 10
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]

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Dim db As Database
  3. Dim rstUpdates As Recordset
  4.     Set db = CurrentDb
  5.     Set rstUpdates = db.OpenRecordset("Daily Fault Update", dbOpenDynaset)
  6. If Not rstUpdates.EOF Then rstUpdates.MoveFirst
  7. Do While Not rstUpdates.EOF
  8.     vntTempData = vntTempData & rstUpdates!Description
  9.     rstUpdates.MoveNext
  10. Loop
  11. Me.FaultComms = vntTempData
  12. 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!
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Dim db As Database
  3. Dim vntTempData as Variant
  4. Dim rstUpdates As Recordset
  5. Dim rstUpdates1 As Recordset
  6. Dim strOpen As String
  7. strOpen= "[TT_Number] =[Forms]![TXFaults].[TT_Number]"
  8.     Set db = CurrentDb
  9.     Set rstUpdates = db.OpenRecordset("Daily Fault Update", dbOpenDynaset)
  10.     rstUpdates.Filter=strOpen
  11.     Set rstUpdates1= rstUpdates.OpenRecordset
  12. If Not rstUpdates1.EOF Then rstUpdates1.MoveFirst
  13. Do While Not rstUpdates1.EOF
  14.     vntTempData = vntTempData & rstUpdates1!Description
  15.     rstUpdates1.MoveNext
  16. Loop
  17. Me.FaultComms = vntTempData
  18. End Sub
I am also getting the rutime error too few parameters. Expected 3 on this line
Expand|Select|Wrap|Line Numbers
  1. Set rstUpdates1= rstUpdates.OpenRecordset
Jun 3 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Dim rstUpdates as DAO.RecordSet
  2. 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
Jun 3 '08 #2

P: 10
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?
Jun 3 '08 #3

P: 10
I have got it sorted out! I changed the code to:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.    Dim vntTempData as Variant
  3.    Dim rstUpdates As DAO.Recordset
  4.    Set rstUpdates = CurrentDb.OpenRecordset("SELECT * FROM [Daily Fault Update] WHERE [TT_Number] = " & [Forms]![TXFaults].[TT_Number])
  5.    While Not rstUpdate.EOF
  6.       vntTempData = vntTempData & rstUpdates!Description
  7.       rstUpdates.MoveNext
  8.    Wend
  9.    Me.FaultComms = vntTempData
  10. End Sub
And it worked!
Jun 3 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.