“Syntax error in JOIN operation” (1 Viewer)

coypu

Registered User.
Local time
Today, 11:48
Joined
Mar 12, 2019
Messages
26
Misleading “Syntax error in JOIN operation”

Hi

I’m getting a “Syntax error in JOIN operation” when switching from datasheet back to form view. The form has a tab control containing several subforms. The error only appears after a particular tab and subform were active before switching to datasheet view. The error occurs after clicking form view from toolbar and before the parent form’s Current event i.e. seemingly before it can be handled. The subform’s recordset is dynamically set by the parent form’s Current event. The SQL syntax looks fine and works consistently with no issue in form view. Switching between form and datasheet view is necessary to the application.

I’ve applied the advice I’ve seen to enclose the “ON” statement in “(…)” but that does not fix this.

I’ve been thinking I need to null the subform’s recordset when the switch to datasheet view occurs, but by then the subform no longer exists (or does it?). The error occurs, even whilst remaining on the same parent record, for which the subform’s recordset was ok in form view, before the switch to datasheet but not after the switch back to form view.

Hope that makes sense.
 
Last edited:

coypu

Registered User.
Local time
Today, 11:48
Joined
Mar 12, 2019
Messages
26
Update: setting the suspect subform's recordsource in form design rather than dynamically, using identical SQL syntax, eliminates the above error message, but drops the required functionality, which as I say works just fine as long as one stays in form view.
There is evidently something happening in the switch of view, that is tolerant of many things but not this?
 

coypu

Registered User.
Local time
Today, 11:48
Joined
Mar 12, 2019
Messages
26
Solved by creating a button on the parent form, called say “Datasheet” whose click event does this:
Code:
Me!subform.Form.RecordSource = ""
DoCmd.RunCommand (acCmdDatasheetView)
I.e. it empties the subform's recordsource before then switching the parent form to datasheet view. When I then use the form view button, the misleading “JOIN” error no longer occurs, and the parent form’s current event sets the subform’s recordsource to what it needs to be, and all works as needed.

That seems ok, except I still have to prevent the toolbar's datasheet button being used, unless someone knows of a way detecting the form's View Change event, rather than the form’s Current event, which occurs too late. (There is a form View Change event, but its nothing to do with the change between datasheet and form view, more's the pity ;-))

Any experience of others in this area will be appreciated, whilst I hope this helps some who encounter the same.
 
Last edited:

Users who are viewing this thread

Top Bottom