Looping through records in 2002

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

TIA:

Saw post about looping through records. I tried exact
same code in Access 2002 (2000 format) and i got an error
msg. Does DAO work in 2002 or is there different code to
do the same thing?


This is were I get confused...

Thanks for help.
Joel
 
Access 2000 and 2002 use ADO by default, but you can still use DAO.

With any code module open, select Tools | References from the menu, and
scroll through the list of available references until you find the Microsoft
DAO 3.6 Object Library. Check the box beside it and close the dialog.

Actually, if you're not going to be using ADO, uncheck the reference to
Microsoft ActiveX Data Objects 2.1 Library when you add the reference to
DAO. That's because there are objects with the same name in the two models,
and you'll need to disambiguate to ensure you're using the DAO version
(declare it as DAO.Recordset, for instance, to guarantee a DAO recordset.
The equivalent to guarantee an ADO recordset would be ADODB.Recordset). The
list of objects with the same names in the 2 models is Connection, Error,
Errors, Field, Fields, Parameter, Parameters, Property, Properties and
Recordset
 
Thanks, that got me unstuck to some degree..

One other question, I was opening an existing query that
has parameters of fields coming from an open form. When
i run the code, error about not inputing parameters.

Any idea of how to specify parameters in the code?

Thanks again,

Joel
 
One other question, I was opening an existing query that
has parameters of fields coming from an open form. When
i run the code, error about not inputing parameters.
Click to expand...
This is a peculiar problem that comes from Access being too nice to you.
When a query runs in the GUI, Access will go through the sql and work out
any vba functions, calls to other parts of the GUI such as form controls,
etc before handing off to Jet.

Unfortunately when you use VBA to pass a query directly to Jet, none of
that happens, and your SQL must be valid and make sense to the dbengine.
This means no custom functions, no controls etc.

One possibility is to substitute the values into the SQL:
strSQL = "SELECT This, That, TheOther " & _
"FROM Somewhere " & _
"WHERE Base = """ & lstBase.Value & """; "

or, somewhat neater, to fill in the parameters yourself

Set qdf = QueryDefs("qryMyQuery")
With qdf
' This is a "normal" one
.Parameters("TopLevel") = txtTopLevel.Value

' You can leave the parameters the same; after all it's
' only a name
.Parameters("Forms!frmLevels!txtBottomLevel") = txtBottomLevel

' if you know the order of the parameters, you can use
' the index, but it's liable to break if you edit the query
.Parameters(3) = "Eric"

.Execute dbFailOnError

End With


Hope that helps


Tim F
 
Back
Top