User-Defined Variable Not Defined

mtairhead

Registered User.
Local time
Today, 06:25
Joined
Oct 17, 2003
Messages
138
I'm having a problem with an adapted VBA script. I just want to loop through the recordset, but Access keeps giving me grief.


PHP:
Dim dbs As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("(02)Unique Merchants")

rs.MoveFirst
Do While Not rs.EOF
    MsgBox ("Test to see if actually looping. One will appear for each record.")
    rs.MoveNext
Loop

End Sub

The error I get is: "Compile error: User-defined type not defined"

Well, dang. What's that mean? I found a thread or two with similar problems, and the diagnosis seemed to involve placing "DAO." in front of "database" and "recordset" when the variables are dimmed. Of course, this code snippet already does that...

Thanks for any help you can provide.

~Andrew
 
I'm guessing that it has to do with 1 of 2 things:

1. ("(02)Unique Merchants") - using reserved characters (parentheses) in an object name
2. If you are using Access 2000 or 2002, you haven't set a reference to DAO 3.51 (the default for 2000 and 2002 is ADO).
 
Remove The Brackets From

("Test to see if actually looping. One will appear for each record.")
And Check you have a Reference to DAO 3.6?
 
Bob,

I don't think DAO 3.51 Would work with the above I think it may have something to do with the way the recordsets declared I think it was updated to 3.6 for 2000 and stopped with the end of 97
 
Actually, for the code they are showing, it doesn't matter which DAO version they use, but they need to set the reference to whatever version they have available.

DAO has not stopped with 97. Access 2000 and 2002 have ADO as the default (although you can use DAO if you wish). Access 2003 and 2007 use DAO (yes, Microsoft went and changed back).

The point about the parentheses around the message box text is valid as you can't use them unless you are on the right side of an = sign. But the object name can't use parentheses either.
 
Thank you both for responding!

I was able to get past that initial error, by adding a reference to DAO in Access (Future readers: In the VB screen, TOOLS --> REFERENCES).

I also removed the reserved characters from the object name, and renamed it "UniqueMerchants."

I've changed the code a bit. The original dimmed the dbs variable but then set the db variable. I've corrected it below:


PHP:
Dim dbs As DAO.Database
Dim rs As DAO.Recordset


Set dbs = CurrentDb
Set rs = db.OpenRecordset("UniqueMerchants")

rs.MoveFirst
Do While Not rs.EOF
    MsgBox "Test to see if actually looping. One will appear for each record."
    rs.MoveNext
Loop

End Sub
 
Ok...Despite that, I now get a new error: "Object Required." When I go into the debugger, the highlighting is on this line:
PHP:
Set rs = db.OpenRecordset("UniqueMerchants")

I've checked, and the Query that I want to serve as the recordset is, indeed, named UniqueMerchants.
 
I think I see your problem.

You've set this:
Code:
Dim dbs As DAO.Database
but are referring to db instead of dbs
 
Thanks. That was indeed the problem. Posted below is my updated code:

PHP:
Dim dbs As DAO.Database
Dim rs As DAO.Recordset

DoCmd.SetWarnings (False)
DoCmd.OpenQuery "03 Earase Cardholder Profiles", acViewNormal

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("cardholder proflies")

rs.MoveFirst
Do While Not rs.EOF
    'for each record, do something
    rs.MoveNext
Loop

End Sub
 
Last question, promise...

Is there a reason why this code will only work when I loop through a table's records, and not a query's?

I get an "invalid operation" error, whenever I put a query name in this line:

PHP:
Set rs = dbs.OpenRecordset("query name")



~Andrew
 
Can you post the SQL From the Query And is it a Select Query?

Mick
 
Certainly. Here is the "02 Unique Merchants" query's SQL:
PHP:
SELECT DISTINCT [01 Intial Card Results].Merchant
FROM [01 Intial Card Results]
ORDER BY [01 Intial Card Results].Merchant;

The query "01 Intial Card Results" (Yes, misspelled) has the following SQL:
PHP:
SELECT dbo_tbl_CC.Account_Number, dbo_tbl_CC.Merchant, dbo_tbl_CC.Transaction_Date_Time
FROM dbo_tbl_CC
WHERE (((dbo_tbl_CC.Account_Number)=[Forms]![main input]![cardnumber01] Or (dbo_tbl_CC.Account_Number)=[Forms]![main input]![cardnumber02] Or (dbo_tbl_CC.Account_Number)=[Forms]![main input]![cardnumber03] Or (dbo_tbl_CC.Account_Number)=[Forms]![main input]![cardnumber04] Or (dbo_tbl_CC.Account_Number)=[Forms]![main input]![cardnumber05]) AND ((dbo_tbl_CC.Transaction_Date_Time) Between [Forms]![main input]![Beginning Date] And [Forms]![main input]![Ending Date]));

I don't know if this would affect things, but I have the "Unique Values" set to "YES" and the "Unique Records" set to "NO" in the "02 Unique Merchant" query.

The query opens fine, when I fill in the values on the form that the query "01 Intial Card Results" relies on. It gives me exactly what I'm looking for.

I created a table with the results of the query, and have been using that table for testing purposes, since the code seems to work with tables...So I know that Access likes the results, and I know that Access will open the query manually.

Much appreciated,

~Andrew
 

Users who are viewing this thread

Back
Top Bottom