The object doesn't contain the Automation object '|'

Hi Experts,
I have a bookmark issue that I can't solve on a MS Access 2013/Windows 7/MySQL back end set up.
When I start the application it loads a form with client data and there is a subform showing details of quotations for those clients.
I'm trying to access the first quotation for the selected client by setting a bookmark on the subform but I'm getting a Run-time error '2766': The object doesn't contain the Automation object '|' - the bar character may be a clue perhaps?
It's failing on line 170 in the code below, so the quotation has been found by the ".findfirst" action
110   With Form_frmGrpMbrs.RecordsetClone
120       If .RecordCount <> 0 Then
130           .FindFirst "[quote_id]=" & Me.quote_id
140           If .NoMatch Then
150               MsgBox "Booking Member Quote not Found: " & Form_frmGrpClSel.quote_id
160           Else
170               Form_frmGrpMbrs.Bookmark = .Bookmark
180               Form_frmGrpMbrs_Main.txtGMHdng.Requery
190           End If
200       End If
210   End With

Open in new window


Can anyone help please?
redpoppyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You object naming is a bit confusing, but have in mind that the opening sequence of a form with a subform is:
   - subform open
   - subform close
   - main form open
   - subform open

The quick-n-dirty solution may be to state:

   On Error Resume Next

before your code.

/gustav
0
Dale FyeCommented:
1.  Why are you requerying the textbox on the main form in line 180?

2.  What event are you using to call this code?

3. I strongly urge you not to use the Form_frmName syntaxy you are using above.  Over the years, I've found that can cause unexpected errors.  Occassionally I will use that syntax while writing code, because it allows use of intellisense, but when I'm done, I'll run a find & replace to replace that syntax with Forms("formName").
I generally use:

110   With Forms("frmGrpMbrs").RecordsetClone
120       If .RecordCount <> 0 Then
130           .FindFirst "[quote_id]=" & Me.quote_id
140           If .NoMatch Then
150               MsgBox "Booking Member Quote not Found: " & Forms("frmGrpClSel").quote_id
160           Else
170               Forms("frmGrpMbrs").Bookmark = .Bookmark
180               Forms("frmGrpMbrs_Main").txtGMHdng.Requery
190           End If
200       End If
210   End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
redpoppyAuthor Commented:
Hi Dale,
Thanks for replying. In answer to your questions:
1) The requery on line 180 is just setting up a "Group Description"  from the main form on another form, after the Client record has been accessed.
2) The routine that I posted is called from the "On Current" event in the Main form, so I'm assuming the subform should be loaded OK at that point
3) Thanks for the tip - I've used the "Form_frm..." format as a way of validating the syntax of field names - it displays a drop down list of all fields associated with the form and it's easy just to select the required field without having to type it all out. I'll try your format and see if it makes any difference.

Have you any idea why the error message is displaying the "bar" character at the end instead of a field name? (The object doesn't contain the Automation object '|') - does that suggest maybe the subform isn't loaded at that point?

Thanks again for your help, much appreciated
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
Is it the main forms "form_Current" event?

As Gustav mentioned, you might try :On Error Resume next

but I don't particularly care for that because it allows any error to get through.  

Is your subform linked to the the main form in a master/child relationship?

You might want to change line 120 to:

If .RecordCount > 0 Then
0
redpoppyAuthor Commented:
Hi Dale,
Yes it's the main form "on current" event.
The subform isn't linked to the main form - I don't always want to load that particular subform, so I did it this way instead - I also understood it was more efficient to do a look up as I'm doing?
I've just changed the coding to reference the forms in the way that you suggest and I'm now getting a run-time error 2450 on line110: cannot find the referenced form 'frmGrpMbrs'. I'm fairly sure the syntax is correct and the form definitely exists. Here's the code that I changed it to:
110   With Forms("frmGrpMbrs").RecordsetClone
120       If .RecordCount > 0 Then
130           .FindFirst "[quote_id]=" & Me.quote_id
140           If .NoMatch Then
150               MsgBox "Booking Member Quote not Found: " & Forms("frmGrpClSel").quote_id
160           Else
170               Forms("frmGrpMbrs").Bookmark = .Bookmark
180               Forms("frmGrpMbrs_Main").txtGMHdng.Requery
190           End If
200       End If
210   End With

Open in new window

Thanks again for your help
0
Dale FyeCommented:
OK, so "frmGrpMbrs" is actually the source object for the subform?  In that case, you need to refer to the subforms control name with a syntax like:

With forms("frmGRPMbrs_Main").Controls(UseTheControlNameHere).Form.Recordsetclone

Open frmGRPMbrs_Main in design view and get the name associated with the subform control, since this subform is not in a master child relationship with the main form, I'm guessing it has a name like Child0 (unless you have changed it).

You can also use syntax:

With forms("frmGRPMbrs_Main").UseTheControlNameHere.Form.Recordsetclone
0
redpoppyAuthor Commented:
Hi Dale, I searched around on that error 2450 and found a suggestion to try an "isloaded" condition and it's worked". Here's the revised code:
105   If CurrentProject.AllForms("frmGrpMbrs").IsLoaded Then
110     With Forms("frmGrpMbrs").RecordsetClone
120         If .RecordCount > 0 Then
130             .FindFirst "[quote_id]=" & Me.quote_id
140             If .NoMatch Then
150                 MsgBox "Booking Member Quote not Found: " & Forms("frmGrpClSel").quote_id
160             Else
170                 Forms("frmGrpMbrs").Bookmark = .Bookmark
180                 Forms("frmGrpMbrs_Main").txtGMHdng.Requery
190             End If
200         End If
210     End With
215   End If

Open in new window

It seems like the " on current" event gets triggered several times while the database is loading - maybe through filters & sorts, which it makes use of? - so I'm guessing that one of the times the subform isn't loaded, but this code seems to sort it. Thanks again for your help.
So, I'll accept your answer seeing as it pointed me in the right direction
0
Dale FyeCommented:
Glad you were able to resolve it.

Based on that syntax, it doesn't look like that is actually a subform, but is a popup form.  Is that accurate?  Subforms are embedded in other forms while a popup form would open either in another tab (Access 2007 or greater) or as a floating form (possible with any version of Access).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.