Syntax Error in Query Expression

[camidon]

Here is my SQL Expression:

StrCriteria = "SELECT * "
StrCriteria = StrCriteria & "FROM [tbl_CustomerAddress] "
StrCriteria = StrCriteria & "WHERE [tbl_CustomerAddress].[RCustomerID] = "
StrCriteria = StrCriteria & RCustomerID

For some reason I get a Synax Error When I attempt to Execute the DoCmd.Openform Action on a form.

If I debug.print the string and copy it into a querys SQL line, it gives me all of the fields that I need and seems to work fine.

Any ideas?

Thanks,

Chris

 

[JeremyNYC]

Can you paste both the DoCmd.Openform line and the sql from the debug window?

My guess is the problem is in the docmd line. If you're using this sql in the where clause, you've got too much in there.

In any case, if you post those two things we should be able to get you up and running.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[Akart]

Hi,

Try this

StrCriteria = "SELECT * "
StrCriteria = StrCriteria & "FROM [tbl_CustomerAddress] "
StrCriteria = StrCriteria & "WHERE [tbl_CustomerAddress].[RCustomerID] = "
StrCriteria = StrCriteria & "'" & RCustomerID & "'"

Regards,

Akart

 

[camidon]

The DoCmd line is as follows:

DoCmd.OpenForm "frm_CustomerAddress", , StrCriteria

The SQL from Debug comes out as:

SELECT * FROM [tbl_CustomerAddress] WHERE [tbl_CustomerAddress].[RCustomerID] = 2215

 

[camidon]

Akart,

I tried the code that you supplied and recieved the same error message. I was thinking that I might get a data type mismatch, but supprisingly, it gives me the same syntax error.

Thanks for your responce guys!

Chris

 

[JeremyNYC]

Can you post the rest of the code before and after the openForm line?

Are you sure that's the line giving you the error?

Seems pretty solid to me.

But if you post all of the code, maybe we can see something else.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[camidon]

My entire Sub looks like this:

Private Sub cmdAttachAddress_Click()
On Error GoTo Err_cmdAttachAddress_Click

Dim stDocName As String
Dim StrCriteria As String
Dim MyDb As Database
Dim MySet As Recordset
Dim frm_CustomerInformation As Form
Dim RCustomerID As String

Set MySet = Forms.frm_CustomerInformation.Recordset

RCustomerID = MySet.Fields![RCustomerID]
Debug.Print RCustomerID

StrCriteria = "SELECT * "
StrCriteria = StrCriteria & "FROM tbl_CustomerAddress "
StrCriteria = StrCriteria & "WHERE qry_CustomerAddress.RCustomerID = "
StrCriteria = StrCriteria & RCustomerID

DoCmd.OpenForm "frm_CustomerAddress", acNormal, , StrCriteria

Exit_cmdAttachAddress_Click:
Exit Sub

Err_cmdAttachAddress_Click:
MsgBox Err.Description
Resume Exit_cmdAttachAddress_Click

End Sub

Any ideas??

 

[JeremyNYC]

Set MySet = Forms.frm_CustomerInformation.Recordset

shouldn't this say RecordsetClone at the end?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[camidon]

If I change it to recordsetclone it gives me the wrong RCustomerID. With it set to recordset, it gives me the correct ID.

You asked earlier if I was sure that it was giving the error on the DoCmd.Openform line and the answer is yes.

 

[JeremyNYC]

Hmm. Don't know what to tell you. =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.