Access XP, problems in my recordset

[Guest_imported]

Hi,

I have these in one of my events:

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT PRIN_EMP, " & _
"PRIN_OT FROM PRINC " & _
"WHERE PRIN_ID = '" & List0 & "'", dbOpenSnapshot)

and it says error 3001 "Invalid argument".
What is wrong with it?

Thanks,

Johns

 

[Bigpapou]

Here's how I deal with this!!!
'first separate the query from the recordset

Dim sql as string
sql = "SELECT PRIN_EMP, PRIN_OT FROM PRINC WHERE (PRIN_ID = '" & List0 & "')"
Set rst = CurrentDb.OpenRecordset(sql, dbOpenSnapshot)

'********************************
That way it's easier to see if the sql is what you want. Use the debug window (if you don't know how... just ask)
'********************************

If you want to be sure about your query... here's how!
Create a new query that'll do the same as your "select...".
When it's working, go to the SQL section of that query
and copy paste the SQL code right here: sql = "paste"

Here's what could be wrong:
1- you're not using the tableName to refer to a column in your table.
EX: tableName.PRIN_EMP FROM tableName WHERE(tableName.PRIN_ID...)

2- maybe the '" & List0 & "' is not what you need it could be ' & List0 & ' or """ & List0 & """. (you'll see with the debug window)

3-Maybe, and this is a longshot, you could try to use a dbOpenDynaset instead of a snapshot

If you need anything else: explanation, precision... just ask!!!
Biggie

 

[KyleS]

It's probably the reference to DAO. Go to Tools --> References and make sure the reference to "Microsoft DAO 4.0 Object Library" is checked. Now change the first line to thi:

Dim rst As DAO.Recordset

That should do it. Kyle

 

[Guest_imported]

Hi Bigpapou,

thanks for your answer.
I try the tree options and it does not work!
It seems the problem is "currentDb".
I try msgbox(Currentdb) before anything else and it reported the same error, pointing at this line. I try with msgbox("opening record...&quot and the message was displayed correctly, but in the recordset part the error appears again.

john2002

 

[KyleS]

John, it's your DAO reference. Access stopped using DAO as it's default with Access 2000 so you have to go in and manually select the reference (just once). Kyle

 

[Guest_imported]

Hi,

Sorry I could not answer before but I was pretty busy these days.
KyleS, you were right, I have done what you recommend and it works perfectly well.
Many thanks,


johnn2002