Getting error 3021 when trying to retrive data from record set

SirDrums

Registered User.
Local time
Today, 05:34
Joined
Jul 15, 2009
Messages
83
Maybe I am going about this wrong but I have a record set that retrieves a field out of one of our mdb's

Code:
Set FS = db2.OpenRecordset("Select SQL FROM SCSSQL Where String_Name = 'selComponentType';")

when I create a watch on this record set I see the data I want in it.

So I am trying to copy that data into a string variable to use elsewhere. Currently I am trying the following:

Code:
FROMSQL = FS![SQL]

But I get the run time error 3021 and it says that no record exists.

SQL is the name of the field that I am pulling data from in the data base and I think that is what I am supposed to place in the [].

Is this the best way to go about what I am trying to do or is there a better way?


Thanks.
 
First thing is to rename your field as SQL is a reserved word and may be causing some of your pain. But you may get away with it if you include the SQL fieldname in the query in brackets as well.


Code:
Set FS = db2.OpenRecordset("Select [B][COLOR=red][[/COLOR][/B]SQL[B][COLOR=red]][/COLOR][/B] FROM SCSSQL Where String_Name = 'selComponentType';")
 
Your recordset has no records, probably because your Where clause is incorrect. It will find only records where the value of the String_Name field is "selComponentType".

I expect you want somethig more like:
Code:
... Where String_Name = '" & Me.selComponentType & "'"

This will use the value in the bound column of what I assume is a combo on the module's form called selComponentType.
 
Thanks guys, both of those corrections helped. I am slowly but surely getting the hang of things. mainly bc of helpful sites like this.

Thanks.
 
Not sure if this is sorted now but if not you haven't specified whether you move into a record before using the field.

When opening it you will be @ FS.BOF, not the first record. FS.MoveFirst will bring you to the first record.
 
Not sure if this is sorted now but if not you haven't specified whether you move into a record before using the field.

When opening it you will be @ FS.BOF, not the first record.

That is incorrect. You WILL be at FS.BOF AND at the first record when opening it. If there are NO records then you will be

If FS.BOF AND FS.EOF Then ' then there are no records.

But if you have records you are at the first one including being at the beginning of file (BOF) and therefore you do not need to use movefirst.
 
I stand corrected then.

Thanks for clearing that up. :)
 

Users who are viewing this thread

Back
Top Bottom