Checking a table for overdue invoices

[FireViking]

hi all,
on startup i have a form that is not linked to a table. In the form_load sub I want to check 3 invoice tables for overdue invoices and show a message to alert the user.
I have tried to access these tables using

Dim db as database
Dim rs as recordset
Set db = Currentdb
Set rs = db.openrecordset("Invoice1&quot
'etc

But for some reason everytime I try to access a table this way it gives a 'type mismatch'error.
I feel i should be using the 'docmd' but am not sure of the syntax.

Is there an easyier way to access tables as I usually code in visual basic and I find that the same rules dont apply in access.
Any help would be appreciated.

thanks alot
Eric

 

[Maquis]

Well, I'm certainly not an expert with VBA yet, and there may be a more efficient way to do this, but when I want to open up a table's records, I just construct a SQL str to bring back the fields I need.

Dim strSQL as string
Dim rs as recordset

strSQL = "select [InvoiceDate] from Invoice1"
set rs = db.openrecordset(strSQL)


Hope this helps ya. B-)

 

[MichaelRed]

The Ms. Access systax is generally correct. There is an additional optional parameter to the openrecordset command "type" which is generally used, however this should NOT give a type mis-match error. A likely suspect is the literal "Invoice1", however this would be a real stretch - even for the Ms. Access error handler. If it is due to the source, You probably have more than one object named "Invoice1". Another likely suspect is that you are using DAO syntax - but referencing ADO recordsets. This often occurs when using ver 2K, as the DEFULT here is ADO, while in other recent versions DAO was the default.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[DougP]

In Access 2000 you have to turn on DAO reference
Open VBA editor and click "Tools" menu the "References" and find "Microsoft DAO 3.6 library" then put a check in it.
and sometimes you have to add DAO to the code of these two.

Dim db as [red]DAO.[/color]database
Dim rs as [red]DAO.[/color]recordset
Set db = Currentdb
Set rs = db.openrecordset("Invoice1&quot


DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[FireViking]

hi,
sorry i didnt get back to you all straight away.
DougP's solution fixed my little problem.
Thank you all, especially DougP.