Douglas,
Thanks...that helps a lot.....
But still having some problems....
When I read data from the input table, that seems to work, as evidenced by
displaying the results in a MsgBox.
However, writing that data to a new table doesn't seem to work. I have a
table named OutputTable that has a number of empty rows. Actually, I would
prefer that the output table be empty prior to running the code, but if I do
that I get a "No Current Record" error message.
Here's the code I have to write data to the output table:
OutputTable.Edit
LLCampNo = CampNo(I)
OutputTable![RECORD_ID] = LLRecord_ID
OutputTable![CampNo] = LLCampNo
That doesn't seem to work. When I use the Step Into feature under Debug, I
can step through the whole module. But when I look at the output table, it's
still empty.
When I try to run the module from a macro, I get an "Action Failed" error
message after the module has looped through the code and correctly displayed
some of the results to me via a MsgBox.
Is there a problem with how I'm opening or closing the tables? I have the
following at the end of the module:
OutputTable.Close
InputTable.Close
Thanks for your help! If needed, I could email you the code so you can see
all of it, just let me know.
Douglas J. Steele said:
First of all, your dimension statements probably aren't doing what you want.
Dim Dog, Cat, Mouse, MySet As Recordset
declares Dog, Cat and Mouse as Variants, and only MySet as a Recordset.
Presumably you want all 4 to be recordsets, in which case you need to use
Dim Dog As Recordset, Cat As Recordset, Mouse As Recordset, MySet As
Recordset
Another problem that Access 97 used DAO, and, by default, Access 2000
doesn't include a reference to DAO.
With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library
If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset) This means that the statement above would
actually need to be written as
Dim Dog As DAO.Recordset, Cat As DAO.Recordset, Mouse As DAO.Recordset,
MySet As DAO.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
All of your code below should work after that change (with the exception of
Set mydb = Application.CurrentProject)
Of course, you haven't declared to be a recordset anywhere...
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
The dimension statements are like this:
Dim Dog, Cat, Mouse, MySet As Recordset
Dim dbcurrent, mydb As Database
Dim myworkspace As Workspace
:
I have Access 2000, and have used an old Access 97 module as my source
for
the syntax of statements for a new module I'm writing. Basically, I want
to
have a macro execute the code, which will read a table from the open
database
one record at a time, perform some logic, and write the results to a new
table. I think I'm ok with all the logic and the reading and writing of
the
records.
However, I'm getting hung up at the beginning of the module with the dim
statements and others.
Here are the statements for which I need the correct syntax..
Set mydb = Application.CurrentProject
Set dbcurrent = CurrentDb
Set myworkspace = DBEngine.Workspaces(0)
Set mydb = myworkspace.Databases(0)
DoCmd.SetWarnings False
DoCmd.Echo False, "opening tblRecallQuery..."
Set Fred = mydb.OpenRecordset("tblRecallQuery")
Thanks for any help...
