Autofill field within subform, using another field from another subform (1 Viewer)

TPS · Oct 22, 2019

Hi,

Wondering if someone can help me, I created a previous thread which got extremely messy, so I have removed that one and created a new one.

I hold all of my subforms within one main form as depicted in this screenshot here:

I have a field called Book Discount on my Publisher Info Subform. This field is also present, albeit under a different name on the Marketing Template Subform. What should happen is that, whenever Book Discount on the Publisher Info Subform is changed, it should automatically update the Book Discount on the Marketing Template Subform. This field is set to disabled, so it cannot be changed. It's purely there for display purposes only.

I have done the following to try and achieve this:

Code:
On Marketing Template Subform, in Design View, I created an Unbound Combobox called Unbound_BookDiscount.

I set the control source to be:

Code:

Forms![frm_Publisher_Info_Subform]![Book_Discount_Code]

Because when Book Discount Code on the Publisher Info Subform is changed, I want this Unbound control to be changed.

I then went to the Publisher Info Subform and clicked the combobox called Book Discount Code. I clicked on Event and then clicked on After Update and selected Event Procedure. I then clicked on the 3 dots and it opened VBA. I then have this code from a contributor on this forum, modified to work in my application:

Code:

Private Sub Book_Discount_Code_AfterUpdate() Me.frm_Marketing_Subform.Form.Unbound_BookDiscount.Requery End Sub

Which as I understand it says, When Book Discount Code is updated, requery the combobox Unbound_Book Discount on the Marketing Subform.

Save.

Right click main form and click on Form View and test by changing Book Discount Code on Publisher Info Subform.

I then get the following error:

Code:

Compile Error: Method or data member not found

VBA then automatically highlights the following:

Code:

.frm_Marketing_Subform

Which from what I understand, is saying the subform name is wrong or not found but I know the naming is correct as I have in another block of code that references it and it works correctly. I copied and pasted the name into the new code.

I have this code that I have modified from another member on here, that works correctly when linking different fields from the the main form to different fields on various subforms:

Code:

Private Sub product_group_code_afterupdate()
With Me.frm_Marketing_Subform.Form
    !Product_Group = Me.Product_Group_Code
    .Dirty = False
End With
With Me.frm_Publishing_Info_Subform.Form
    !Contract_Type_Product_Group = Me.Product_Group_Code
    .Dirty = False
End With
End Sub

I have tried to adapt this to work for my subform to subform but I get the same error as shown here:

Code:

Compile Error:
Method or data member not found

VBA then highlights the Subform name as error.

So what am I doing wrong as I can't seem to figure this out?

Gasman · Oct 22, 2019

You are not grasping the structure of forms and subforms.
Deleting that thread was not a good idea, you would have been better linking to it, so people can see what has already been tried. Much like cross posting is frowned upon without mentioning it.

In your other thread you were on the main form and amending a subform control on that form, so you used

Code:

Me                     Main Form
SubformControl         Name of the control holding the form
Form                   The form property of that control
Control                The control on that subform

I *think* you could try Me.Parent. and then the rest of the path to the subform control.

FYI here is a link which explains the references to forms/subforms

http://access.mvps.org/access/forms/frm0031.htm

HTH

TPS · Oct 22, 2019

Gasman said:
You are not grasping the structure of forms and subforms.
Deleting that thread was not a good idea, you would have been better linking to it, so people can see what has already been tried. Much like cross posting is frowned upon without mentioning it.

In your other thread you were on the main form and amending a subform control on that form, so you used

Me Main Form
SubformControl Name of the control holding the form
Form The form property of that control
Control The control on that subform

I *think* you could try Me.Parent. and then the rest of the path to the subform control.

FYI here is a link which explains the references to forms/subforms

http://access.mvps.org/access/forms/frm0031.htm

HTH

No, I understand the relationship between Main Form and Subform as I've done a lot of reading and research in between replies from these topics. I am getting frustrated in answering the same questions over and over again which is why I removed the previous thread.

Thank you for the link.

arnelgp · Oct 22, 2019

use the Parent form:

Code:

Private Sub Book_Discount_Code_AfterUpdate()
Me.Parent!frm_Marketing_Subform.Form!Unbound_BookDiscount.Requery
End Sub

TPS · Oct 22, 2019

arnelgp said:
use the Parent form:

Code:

Private Sub Book_Discount_Code_AfterUpdate() Me.Parent!frm_Marketing_Subform.Form!Unbound_BookDiscount.Requery End Sub

I'm getting a blank showing in the Unbound_BookDiscount Combobox.

Am I right in saying that the control source should be:

Code:

=[Forms]![frm_Publisher_Info_Subform]![Book_Discount_Code]

arnelgp · Oct 22, 2019

im not sure about why you setup the combo to be unbound.
you might not get the proper value when your subform is Linked to the main form (Link Master/Child Fields) and you move your main form to another record.

TPS · Oct 22, 2019

arnelgp said:
im not sure about why you setup the combo to be unbound.
you might not get the proper value when your subform is Linked to the main form (Link Master/Child Fields) and you move your main form to another record.

I understand that it's not unbound seeing as I changed the control to:

Code:

=[Forms]![frm_Publisher_Info_Subform]![Book_Discount_Code]

? Is that correct?

arnelgp · Oct 22, 2019

it is still Unbound.
you need to set the Controlsource of the combo to a Field on the table.

what will happen is when you move to another record in main form(frm_publisher_setup), these unbound combos, the values will Stay them same (since unbound). it will not be sync with the subforms' discount.

if you are willing to share the db (put some dummy data if you may), I will be glad to have a look at it. just make sure to attached a text file of the details of what area and what need to be done.

isladogs · Oct 23, 2019

For info....
Following a PM from TPS where he asked to have his membership revoked, I have complied with that 'request'

Autofill field within subform, using another field from another subform (1 Viewer)

TPS

Banned

Gasman

Enthusiastic Amateur

TPS

Banned

arnelgp

..forever waiting... waiting for jellybean!

TPS

Banned

arnelgp

..forever waiting... waiting for jellybean!

TPS

Banned

arnelgp

..forever waiting... waiting for jellybean!

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread