openrecordset question

[grizzlyjdw2]

ok, basically what i am trying to do is open a recordset based off of a query, here is what i have

Dim db As DAO.Database
Dim rs1 As DAO.Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Select * from main where offshore_analyst = Forms![main_form]![offshore_analyst].Value;")


the query i have above is already defined as a seperate query, is there a way to just refference the query name?

like openrecordset ("qry_name")
when i try this it says too few parameters

if not why is the above example giving me an error? im sure im not refferencing the form correctly.

 

[Access_guy49]

I think your

where offshore_analyst

Is giving you the problem.

Should be:

WHERE main.offshore_analyst

You need Tablename.Field

Not just field name.

 

[grizzlyjdw2]

still getting the too few parameters error message.

if i take out the form reference and just use

Set rs1 = db.OpenRecordset("SELECT main.*, main.offshore_analyst FROM main WHERE (((main.offshore_analyst)='userID'));")

it works, but i want the querry to run with either prompting or reading the txt box off the form.

 

[SOS]

It works better (and more consistently) if you pass the explicit value instead of including the control in the quotes:

Set rs1 = db.OpenRecordset("Select * from main where offshore_analyst =[B][COLOR=red]" & Chr(34) &[/COLOR][/B]  Forms![main_form]![offshore_analyst] [COLOR=red][B]& Chr(34)[/B][/COLOR])

Is this code ON the main form? If so just use ME:

Set rs1 = db.OpenRecordset("Select * from main where offshore_analyst =[B][COLOR=red]" & Chr(34) &[/COLOR][/B]  Me.main_form.offshore_analyst [COLOR=red][B]& Chr(34)[/B][/COLOR])