Recordset problems

[RobbieD]

Desperately seeking help for a recordset problem. I have built a query that gets information from a table depending what date is selected on a form (used the build tool to query building). I then have tried to recordset this query but everytime i do i get an error message 'Too few parameters : at least one expected ' Probably quiet basic but i have been staring at this for hours. . . Thanks in advance to all help

 

[Steve101]

If you publish the code in question, it will be easier to help you solve the problem.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[RobbieD]

The Query is just a standard select query on a table (auto number, date, value and employee) with the criteria for filtering by date made from a selection on a combobox field on a form ([forms]![frmValues]![Date]). Once the date has been selected i wanted to recordset the query to populate certain fields, using the code


Dim RsDate as dao.recordset
Dim X as integer

Set RsDate = currentdb.openrecordset("Query1", dbopensnapshot)


With RsDate
.MoveFirst
Do Until .EOF
x = .Fields("autonumber&quot

Select Case x
Case 1
Txtbox1.Value = .Fields("Value&quot
Case 2
Txtbox2.Value = .Fields("Value&quot
end select

set rsdate = nothing
rsdate.close



Hope this helps and thanks

 

[Steve101]

Robbie,

Check online help on the QueryDef object, and the parameters collection. Some examples exist here on how to do what you want.

An alternative that I often use is a little simpler, and I'll illustrate below. It involves incorporating the SQL straight into the recordset definition (and thus does not a query object to be set up).

code snipped example:

dim DB as database: set DB = currentdb()
dim rsDate as dao.Recordset
dim F as form: set F = Forms!FrmValues
dim sq as String

sq = "SELECT * " & _
"FROM tblYourTable " & _
"WHERE [Date] = CVDate('" & F![Date] & "')"

set rsDate = DB.openrecordset(sq)

etc.

This technique effectively 'hardcodes' the date value from the form into the recordset, making it unnecessary to have to define and then populate the query parameter objects. Whilst simpler, this is not the most optimal way of doing things, but in most cases, its ease and flexibility justifies the approach.

Another tips if I might: Try to avoid using field names or Form Control names like "Date"; since these are part of the Access language itself (reserved words), they can cause confusion and problems.

I probably havnt answered the whole of your post, but hope that this helps meantime,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[RobbieD]

Tops. . thanks for this