Hi All,
I am getting a sytax error when i am setting the value from one table to another. The exact error is "Syntax error (missing operator) in query expression '[storename]='Kennedy's electrical cairnstest".
Now i know what is causing the error and that is the Apostrophe in the name Kennedy's as the code works well if the storename has no Apostrophe's. now i could modify data in the underlying table but this is a band aid and would not suit the overall function. My Code is as follows:
' if user has selected data from both list boxes open a hidden form and set value to unmatched store
    ' from existing store (StoreName)
    stDocName = "TblTelstraStaging subform"
    stLinkCriteria = "[StoreName]=" & "'" & Me![Text4] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden
    Forms![TblTelstraStaging subform]!StoreName = Me.Text6.Value
    DoCmd.close acForm, stDocName, acSaveYes

The error occurs on the set value line (2nd from the bottom)
Would anyone be able to help me with resolving this problem please?

Cheers
Darren

Since the subform is an object on the main form, you have to refer to Forms!MainFormName!SubFormName.SubFormControl.

I hate bang dot notation since it never seems to work correctly.

What is worse is that you are apparently opening a sub form, without a main form. Is the subform open by some other form? You can't open it if it is already open. Your best bet here would be to copy the sub form, rename it, then open it, and not the sub form. (and clean up your code as below)

So rename TblTelstraStaging subform to frmTelstraStagingHidden

Then

    Dim sText4, sText6 As String

    sText4 = Me.Text4
    sText6 = Me.Text6

    stDocName = "frmTelstraStagingHidden"
    stLinkCriteria = "[StoreName]='" & sText4 & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden
    Forms!frmTelstraStagingHidden.StoreName = sText6
    DoCmd.close acForm, stDocName, acSaveYes

Big problem here is that the hidden form never really gets open, and since there is latency, the value never comes back.

Single quotes and double quotes are never a good idea in a database. You could also substitute the ' with Chr(39) in the string like, "Kennedy" & Chr(39) & "s electrical cairnstest".

Did ANY of this help?



mmcdonal

Hi Rob,

Thanks for your advice here, the form referred to as a subform is actually a main form and was just named as a sub form. I agree that " and ' and # etc should not be used in the datasets but the firm supplying this information won't budge. But i have taken your advice and cleaned up the code, (bear in mind this is a development version), using your advice i put that subform (tblTelstrastaging SubForm)onto the main form, to allow the sub form data to eliminate the hidden form problems, even with the ' existing the data updates with no problems at all.

Thanks for your Help

Cheers
Darren

Hi macdonal,

Sorry don't know where the name Rob came from, i was in a conf call whilst replying to you.

thanks Again