Run time error 3011 (1 Viewer)

[kirkm]

Attempting to set subform data from on Current event on Main Form.

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]

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]

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]

Me.Child0.SourceObject = "Query.QueryK"

 

[kirkm]

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]

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]

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]

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]

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]

Thanks Colin, fair bit to digest. Will try out what you've suggested and let you know how I get on.

 

[The_Doc_Man]

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]

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]

@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

 

[June7]

Review https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b