What's wrong with my syntax ?

Can someone tell me what's wrong with my syntax? Please.



Set rst=dbs.OpenRecordSet("Select [MainPhone],[MainPhoneExt] from [tblProvider] where [ProviderID]= "& Me.txtField11)

I agree...what is wrong with it...looks fine to me. What error are you getting? As long as [ProviderID] is a number and Me.txtField11 is a number it will work. I am also assuming that tblProvider is not a Linked Table. If it is you will need to declare the type of recordset you are opening. The default setting is dbOpenTable if not declared and cannot be used with Linked Tables.

lwells

It is a linked table. My db was created in the 97 version of Access and worked fine as written. I have recently converted it to 2002 and now get this:

Run-time error '3075':
Syntax error (missing operator) in query expression '[ProviderID]='.

Would the correct syntax using a linked table be:

Set rst=dbs.LinkedRecordSet("Select [MainPhone],[MainPhoneExt] from [tblProvider] where [ProviderID]= "& Me.txtField11)

or can the default be altered, overwritten, or otherwise bi-passed?

Thanks for your help!

First, I may have missed this originally but it looks like you may also be missing a space between the "& in the last part of the syntax:

[ProviderID]= "& Me.txtField11)

Should look like this:
[ProviderID]= " & Me.txtField11)

Secondly, using a linked table you will need to declare the type of recordset,

Set rst=dbs.OpenRecordSet("Select [MainPhone],[MainPhoneExt] from [tblProvider] where [ProviderID]= " & Me.txtField11, dbOpenDynaset)

Or

Set rst=dbs.OpenRecordSet("Select [MainPhone],[MainPhoneExt] from [tblProvider] where [ProviderID]= " & Me.txtField11, dbOpenSnapshot)


See if that will solve the problem.

lwells