stLinkcriteria

[ruchijigs]

Hi,

I am using the following syntax to link one form to another.

Private Sub btnOpenPractice_Click()
On Error GoTo Err_btnOpenPractice_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PRACTICE"

stLinkCriteria = "[PracticeName]=" & "'" & Me![GroupName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

GroupName field in the current form links to PracticeName field in the Practice form. The syntax works fine and it does open the Practice form. But when the Practice Name has a quote in it such as "Frank's Practice", it gives me an error "Syntax error (missing operator) in query expression '[PracticeName]='Frank's Practice",

I don't what that means or how to fix it. Can anyone provide me with some guidance on this. Thanks.

 

[ruchijigs]

Help me please! This is a big project for me...

The syntax above is fine...I just want to know what to do if you are comparing two strings and you have a quote in the string. How do I change my code?

Thanks!

 

[MsfStl]

From what I see I wouldn't use the single quotation marks in the criteria statement. I had something similar in a DB that I am working on where I was calling the chosen ID found in an option list. I had a global check variable called RID and what I did was make sure RID was populated with the correct value and then used the RID value as my criteria. I.e.,

RID = Me![List2]
stLinkCriteria = "[ID]=" & RID

the above works perfectly for me. However, I by no means am an expert, so if anyone else jumps in and says something otherwise, I would definitely listen to what they have to say. I hope this helps though. Peace.

 

[ruchijigs]

Thanks for replying..How do you declare a global check variable?

 

[MsfStl]

I created a module (right click module) on the left side of the vba code page and typed underneath the "Option Compare Database":

Option Explicit
Public RID As Variant

I use the Option Explicit to insure I am coding my names correctly and the Public statement creates the variable RID so it may be used across the entire db.

The "as Variant" is kind of a default when I don't know exactly what I may use the variable for.

 

[MsfStl]

You know what? you can also create a module in design mode and then follow the same procedure as above. sorry, I forgot about that, I usually create them as i need them when I code.

 

[RuralGuy]

You can also avoid the single quote with:
stLinkCriteria = "[PracticeName]=" & Chr(34) & Me![GroupName] & Chr(34)

 

[MsfStl]

Hey Ruralguy!

what is the Chr(34)?

 

[Mile-O]

what is the Chr(34)?

The Chr() function returns the character for a given ASCII value. In this instance, the character represented by the ASCII code of 34 is returned. That character is "

As for putting a " within a string, you would use two of them together. i.e. ""

stLinkCriteria = "[PracticeName]= """ &  Me.[GroupName] & """"

 

[RuralGuy]

Go to the immediate window and type ?Chr(34) and hit enter.

 

[ruchijigs]

It doesn't work. Still gives me the same error when I come across a string like "Women's"

 

[ghudson]

The syntax works fine and it does open the Practice form. But when the Practice Name has a quote in it such as "Frank's Practice", it gives me an error "Syntax error (missing operator) in query expression '[PracticeName]='Frank's Practice",

You are breaking one of the many rules by using special characters in the naming of your database objects. You will eliminate your problem is you rename your db objects and following a standard Naming Convention.

 

[RuralGuy]

Which solution did you utilize? Add the following diagnostic code:

stLinkCriteria = "[PracticeName]=" & Chr(34) & Me![GroupName] & Chr(34)
[B][COLOR="Red"]Debug.Print "[" & stLinkCriteria & "]"[/COLOR][/B]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Then go to the immediate window when you get the error and see what was in the stLinkCriteria. If you are still getting the "Syntax error (missing operator) in query expression" error then there is a problem somewhere else.

 

[Mile-O]

You are breaking one of the many rules by using special characters in the naming of your database objects. You will eliminate your problem is you rename your db objects and following a standard Naming Convention.

ghudson, the apostrophe is in a record and not an object name.

 

[Mile-O]

It doesn't work. Still gives me the same error when I come across a string like "Women's"

What doesn't work?

I've given you a solution and RuralGuy has given you something to try in the Immediate window. Which are you referring to?

Also, you can delete the Variant variable; it doesn't seem to be serving any purpose, especially when you state you don't know what you need it for.