Run time error 3011 (1 Viewer)

kirkm

Registered User.
Local time
Tomorrow, 08:05
Joined
Oct 30, 2008
Messages
1,257
Attempting to set subform data from on Current event on Main Form.

Code:
Private Sub Form_Current()
Me.Caption = Me!TestField
Dim sql As String, MyQuery As QueryDef
sql = "Select * from Query8 where (((Query8.TestField)= '" & Me!TestField & "'));"
Set MyQuery = CurrentDb.QueryDefs("QueryK")
MyQuery.sql = sql$
Set MyQuery = Nothing

Me.Child0.SourceObject = "QueryK"
End Sub
The last line is bringing up
Run-time error '3011':
The Microsoft Jet database engine could not find the object
'~sq_cForm1~sq_cChild0'. Make sure the object exists and that you
spell its name and the path name correctly.


I believe QueryK is being built ok (I can run it on it's own).
I don't have an object called '~sq_cForm1~sq_cChild0' .. there's just Form1 and Child0.
I obviously have something wrong. How does one assign a query (to the subform) as I'm attempting? Maybe it is not 'SourceOnbject' ?

Thanks for any info.
 

June7

AWF VIP
Local time
Today, 11:05
Joined
Mar 9, 2014
Messages
5,423
Why would you need to use code to set subform RecordSource? Why not use the Master/Child Links properties of subform container control to synchronize main and subform records?
 

isladogs

MVP / VIP
Local time
Today, 19:05
Joined
Jan 14, 2017
Messages
18,186
The source object property gives the name of the object. In this case the name of the subform. It should be automatically shown in the property sheet.

If you want to specify the data source for your subform, you would use the 'recordsource' property
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:05
Joined
May 7, 2009
Messages
19,169
Me.Child0.SourceObject = "Query.QueryK"
 

kirkm

Registered User.
Local time
Tomorrow, 08:05
Joined
Oct 30, 2008
Messages
1,257
Many thanks arne, that stopped the error and everything's working. But I'm not using RecordSouce property (I couldn't find it). Should I be ? I didn't follow everything Ridders said.
June that is a good question, maybe it is the better way but I'm not familiar with it. If you want to guide me through it, it may be a good time to learn ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:05
Joined
May 7, 2009
Messages
19,169
I thin it would be:

Me.Child0.Form.Recordsource=" your string sql here"

But this one will only work if the su form has same columns as in the field in the query string.
 

isladogs

MVP / VIP
Local time
Today, 19:05
Joined
Jan 14, 2017
Messages
18,186
Ok sorry about that
Record source property is on the data tab of the property sheet

The source object property can be done using sql but is normally created automatically when you drag an object onto the form such as queryK

Anything else you want me to explain?


Sent from my iPhone using Tapatalk
 

kirkm

Registered User.
Local time
Tomorrow, 08:05
Joined
Oct 30, 2008
Messages
1,257
Arne, the subform doesn't have any columns, but it gets (inherits?) whatever is in QueryK. Or so it seems here. I'll try Me.Child0.Form.Recordsource=" your string sql here" as that would be a lot simpler.I did look at the property sheet Colin, but couldn't see RecordSource. Should it be under the 'data' tag ? TBH I'm struggling to distinguish between Source Object and Record Source.
 

isladogs

MVP / VIP
Local time
Today, 19:05
Joined
Jan 14, 2017
Messages
18,186
First of all, form current is not the best event as it will run each time you change records in the the main form. Form load would be better but using the form property sheet best of all.

As you only want one field in your subform, you definitely need to set the record source.

If you click on the outline of the subform, you are selecting it as a control in the man form. If so, the data tab lists source object, master field, child field etc.

However, click the grey square in the top left corner of the subform and you are selecting the subform itself. The data tab then has record source as the first item. If you click the ellipsis (... Button) you can then select the single field you want from your queryK.

You can then remove the source object line from your form code

Finally if you want your subform to be filtered to the corresponding record in the main form, set the master and child field properties as mentioned earlier.

Hope that's clear and that I haven't confused you further.
 

kirkm

Registered User.
Local time
Tomorrow, 08:05
Joined
Oct 30, 2008
Messages
1,257
Thanks Colin, fair bit to digest. Will try out what you've suggested and let you know how I get on.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:05
Joined
Feb 28, 2001
Messages
26,999
For what it is worth to know this, whenever you see any query reference that begins with a tilde (~), it was created by using a query wizard for a combo box or list box or some other similar action. Which apparently you did on form Form1, control cChild0.

As to how you do a parent/child link, if you have the wizards turned on, if you have a relationship already defined between the parent and child tables and then build a sub-form, and then finally create the sub-form control on the parent, the control wizard will do that automatically. If there is no relationship, still build the sub-form first, then build the sub-form control on the main. The dialog will ASK if there is a link between the two and you get the chance to supply its name.

Note that it would really help if the sub-form's underlying table has a key. In fact, if you build the relationship between parent and child tables, the parent MUST have a prime key that the child can use as a foreign key.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:05
Joined
Jul 9, 2003
Messages
16,244
For what it is worth to know this, whenever you see any query reference that begins with a tilde (~), it was created by using a query wizard for a combo box or list box or some other similar action.

I didn't know that! Thank you!


I have been following this thread for quite a while now and I was wondering whether to link to some of my videos on subforms but thought they might muddy the water somewhat.

However I think this one might be of use:-

http://www.niftyaccess.com/control-records-displayed-subform/

Sent from my SM-G925F using Tapatalk
 

isladogs

MVP / VIP
Local time
Today, 19:05
Joined
Jan 14, 2017
Messages
18,186
@Doc Man
Sorry, but this isn't completely accurate

For what it is worth to know this, whenever you see any query reference that begins with a tilde (~), it was created by using a query wizard for a combo box or list box or some other similar action. Which apparently you did on form Form1, control cChild0.

In fact ALL items on a form/subform/report/subreport that have a record source or row source will have an associated object in MSysObjects that begins ~sq_

That applies to the form/report itself as well as combo boxes / listboxes
It doesn't matter whether you use the wizard or not, the object is still created

These objects are identified as TEMP queries (type = 5 ; flag = 3) and the object name will look similar to this:

~sq_c#frmMessageLog~sq_ccboPhone
~sq_cfrmMessageLog~sq_ccboPhone
~sq_cfrmSendEmail~sq_cLstContactEmail
~sq_ffrmPStats3
~sq_rrptPRecordTeacherCrosstab

NOTE:
~sq_f = form ; ~sq_c = form control ; ~sq_r = report ; ~sq_d = report control

Access ignores these 'TEMP queries' in the query designer window list

I gave more details about this in a code repository thread about the MSysObjects table that I wrote last summer: https://www.access-programmers.co.uk/forums/showthread.php?t=293579
 

Users who are viewing this thread

Top Bottom