Open Form by Name not reading apostrophe's

[qwerty99]

I have a button in one form that opens a different form and want it to show specific data to display. I choose this option in the command button wizard and set it up to have the name from my main form be the same name as my new form to be opened. Everything works perfectly until I choose a name that has an apostrophe in it. So if I choose the name "Ender's Game" it gives me the alert:

"Syntax error (missing operator) in query expression '[bookName]='Ender's Game".

I think I've found where the problem is, I just can't figure out how to fix it. When I looked at the onClick embedded macro it has the Where Condition as:

=="[bookName]= & "'" &[BookName] & "'"

Is there a way to get Access to accept that an apostrophe in the name is meant to be part of the string itself?

 

[pbaldy]

Try

"[bookName]= & Chr(34) & [BookName] & Chr(34)

 

[qwerty99]

I tried that but now the opened form doesn't reflect the name at all (even if there isn't an apostrophe) and instead is showing all of the records.

Thanks for you help btw!

 

[pbaldy]

Sorry, typo from copy/pasting yours. Try

"[bookName]= " & Chr(34) & [BookName] & Chr(34)

 

[qwerty99]

Tried it and it's still showing all of the records.

 

[pbaldy]

Can you post the db here? I don't use macros, only VBA, so it may help to play with it.

 

[nanscombe]

If the Field name and the Control name are both the same, apart from capitalisation, ie bookName / BookName perhaps Access does not know which one to use?

Try to qualify your code a bit more, you could try.

"[bookName] = " & Chr(34) & Forms("yourFomName").[BookName] & Chr(34)

You might even be able to get away with ..

"[bookName] = " & Chr(34) & Me.[BookName] & Chr(34)

 

[qwerty99]

nanscombe I got it to work be specifying the form. Thanks!