[X]My Assistant

[Snoopy]

Hello again.

I have a form where I want to have the user add parts to a parts list for an invoice.

I have the parts list as a subform. The Control Source for the form that is used as the subform is an SQL query.

I am able to Insert the new record into the table, but I can't get the subform to display the new parts.

CODE

'Now REQUERY the SubForm

Forms!frmMTa01aNewInvoice!subfrmSubParts.Form.Requery
Forms.frmMTa01aNewInvoice.subfrmSubParts.Form.Requery
Forms!frmMTa01aNewInvoice!subfrmSubParts.Requery
Forms.frmMTa01aNewInvoice.subfrmSubParts.Requery

' Requery 1
'Forms!frmSubParts.Form.Requery
' Requery 2
'Forms.frmSubParts.Form.Requery


Set ctrlParts = Forms!frmMTa01aNewInvoice!subfrmSubParts
ctrlParts.Form.Requery
ctrlParts.Requery
Set ctrlParts = Nothing

Requery 1 gives me a runtime error of 2450 - Microsoft Access cannot find the referenced form 'frmSubParts'. But that is the name of the form (that I am using as a subform.

Requery 2 gives me a runtime error of 438 - Object doesn't support this property or method.

My searching/googling lead me to referencing the control, so I tried the control references, but again no visible result

The four earlier lines compile (and run?), but do not cause anything to visually update.

I am at a loss.

As you can see, I am not sure if I should be using the bang or dot operator, so I tried both...

Also unsure if I should reference the control or the (sub) form directly.

Would really appreciate any guidance or direction.

Heading to bed now, and hopefully will have a clearer head when I get ups, so I can try some suggestions.

Thanks in advance.

[Doug Steele]

Check the name of the control on the parent form. Depending on how you added the subform, the name of the subform control may not be the same as the name of the form being displayed as a subform. You must use the name of the control.

[Snoopy]

Thanks Doug. I think I already have the proper reference...

Here is my structure:

Table

[Snoopy]

Thanks Doug. I think I already have the proper reference...

Here is my structure:

Table - tblParts
Query - qryPartsforInvoice
Form (used as sub) - frmSubParts (uses qry as Record Source)


VBA code Adds record to tblParts
I am trying to "refresh" or "requery" the to reflect the new record in the table.

Form - frmMTa01aNewInvoice
Subform Control - subfrmSubParts
Subform - frmSubParts

So I stumped as to what to try next.
This post has been edited by Snoopy: Nov 11 2019, 07:06 PM

[tina t]

Form - frmMTa01aNewInvoice
Subform Control - subfrmSubParts
Subform - frmSubParts

based on the above, the following expression from your first post looks correct.

Forms!frmMTa01aNewInvoice!subfrmSubParts.Form.Requery

of course, it partly depends on where you're calling the requery from: if the code is running in the mainform, then

Me!subfrmSubParts.Form.Requery

should work, though using the full reference shouldn't make a difference. if the code is running in the subform, then

Me.Requery

should be sufficient.

i had a similar issue several months ago, with a subform in A2016. it just flatly would not requery, though i have not run into this problem with every other subform in the same db. i posted here at UA as well, and finally got it to work with the following advice:

reset the subform control's SourceObject property, as

Me!subfrmSubParts.SourceObject = "frmSubParts"

or

Me!subfrmSubParts.SourceObject = Me!subfrmSubParts.SourceObject

hth
tina

[Snoopy]

Thank You Tina T.

That did it - the reset of the subform control source object.

What a great site full of helpful people.

[tina t]

you're welcome, hon, we're all happy to share the wealth. :) tina