References

[Erik Vandamme]

When porting an Access 2000 app to 2002/3
the following code produces an error during execution
Undefined function 'Cstr$' in expression
All references appear OK

Sub ExecuteSQL(LSQL As String)
Dim db As Database

Set db = CurrentDb()
Me.Repaint
db.Execute LSQL

' MsgBox CStr(db.RecordsAffected) & " records were affected by this SQL
statement."
Me.ShowTask.Value = CStr(db.RecordsAffected) & " records affected"
Me.Repaint
End Sub

 

[Erik Vandamme]

when i replace Cstr with Format$(number,"0") same error, happens when on
db.execute line of code

 

[tina]

specify the database reference, as

Dim db As DAO.Database

and in the VBE window, click Tools | References and make sure that the DAO
library reference is selected. if you're not using an ADO code in your
database, suggest you go ahead and de-select that reference.

hth

 

[Erik Vandamme]

In 2003 it has: Office11.0 Object Library and DAO 3.6 that does not work
In Office 2002 same but it works, unfortunately client has 2003
Is this a matter of no newer DAO libary? (being deprecated perhaps)

 

[Erik Vandamme]

In fact it is worse in the query below it is the Cstr$ that causes the
problem

INSERT INTO JapanImpByMonth ( YearMM, Country, HS, Quantity, Yen, USD,
[Month] )
SELECT [Year]*100+1 AS Expr3, qJapanImportKate.CountryName,
qJapanImportKate.HS, qJapanImportKate.[Quantity2-Jan],
qJapanImportKate.[Value-Jan], [Value-Jan]*0.5 AS Expr1,
'Jan-' & Right$(Cstr$([Year]),2) AS Expr2
FROM qJapanImportKate;

 

[Douglas J. Steele]

DAO 3.6 exists in Access 2000, 2002 and 2003. It's not selected by default
in Access 2000 or 2002, but it's still there, and can be selected.

Regarding your specific problem with CStr, on the machine(s) where it's not
working, open any code module. Select Tools | References from the menu bar.
Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

Just so you know, CStr is actually contained in the VBA library, but it's
actually unlikely that it's the VBA library that's the specific reference
that's failing. References are like apples: one bad one spoils the whole
bunch. Since the VBA library is the last one Access checks, any of the other
references being Missing will cause problems with the VBA reference.

BTW, you're dealing with VBA code, not macros. Your post would have been
more appropriately made to microsoft.public.modulescoding, or
microsoft.public.modulesdaovba.

 

[Erik Vandamme]

Conclusion expression Cstr$() has been deprecated and Cstr() remain
neat that even the Converter does not notice this, so have to examine
all queries by hand...

 

[Douglas J. Steele]

CStr$ has NOT been deprecated. Why would you conclude that from what I
wrote?

 

[Erik Vandamme]

Not from what you wrote Douglas, but from the fact that when I change
the expression in the query to Cstr() it works...

 

[David W. Fenton]

Not from what you wrote Douglas, but from the fact that when I
change the expression in the query to Cstr() it works...

Have you read the Help file that explains the difference between the
two? Perhaps you need a Variant data type returned in the context in
which your using it and that's why CStr$ doesn't work.