Autofill field within subform, using another field from another subform

[Shadows1989]

Hi All,



New joiner and first time poster, hoping somebody can help me here with what I deem to be a complex issue, but you experts may deem to be simple!

I've created a database that has a main form (frm_Main). Within that main form, there is a tab control with subforms (frm_subform1, frm_subform2, etc) on each tab.

What I would like to happen, is that when Combobox_2 is changed on frm_subform2 then Combobox_1 on frm_subform1 is automatically changed to reflect that.

So far, I have changed the control for Combobox_1 to be:

=[Forms]![frm_Main]![frm_Subform2].[Form]![Combobox_2].[Column](1)

I have also set this to disabled, so that it cannot be changed.

This correctly changes upon update of Combobox_2 in frm_Subform2 when tested.

I then went onto frm_Subform2 and clicked on Combobox_2 in frm_Subform2. In the Property Sheet of the Combobox_2 control, I changed the AfterUpdate to be [Event Procedure]. I then clicked on the 3 dots to open up VBA.

I then have the following code within the VBA script:

Private Sub Combobox_2_AfterUpdate()
With Me.frm_Main.frm_Subform1.Form
    !Combobox_1 = frm_Subform2.Combobox2
    .Dirty = False
End With
End Sub

When I go back to frm_Subform2, to test the code out and make a change in Combobox_2, the code runs, however I get the following error:

Compile error:

Method or data member not found

The following VBA code is automatically highlighted:

.frm_Main

As far as I'm aware, I have done the coding correctly, but I must be missing something as it's coming up in error.

Any help? I can provide a copy of the database/screenshots if required. The names of the forms/controls are different within the actual database, but for the purpose of this explanation, I have simplified them. I hope this is okay?

Thanks in advance and apologies for the long post.

[CJ_London]

instead of

With Me.frm_Main.frm_Subform1.Form

try


With Me.Parent.frm_Subform1.Form

[Shadows1989]

instead of

With Me.frm_Main.frm_Subform1.Form

try


With Me.Parent.frm_Subform1.Form

Hi,

Thanks for your reply. I managed to get this working following this guide here: https://bytes.com/topic/access/answe...nother-subform, my code is like so:

Private Sub Combobox_2_AfterUpdate()
Me.Parent![frm_Subform_1].Form.Requery
End Sub

Very similar to what you have posted @Ajax.

Thank you for your help.

[Shadows1989]

instead of

With Me.frm_Main.frm_Subform1.Form

try


With Me.Parent.frm_Subform1.Form

Hi,

Thanks for this. I did post a reply around 11am GMT but for some reason, it isn't showing.

I ended up doing something similar to what you said:

Private Sub combobox_2_AfterUpdate()
Me.Parent![frm_Subform1].Form.Requery
End Sub

This can be marked as solved and I've added to your reputation too.

Thank you!

[CJ_London]

thanks

with regards this

Me.Parent![frm_Subform1].Form.Requery


you only need to requery the subform control


Me.Parent![frm_Subform1].Requery

also using a ! late binds [frm_Subform1]. Late binding means that any errors will not be generated until the code is executing. Using a dot - Me.Parent.[frm_Subform1] - early binds so any errors will be generated when you compile the code. Early binding also means you have the benefit of the VBA intellisense if available (i.e. try Me.somecontrolname instead of Me!somecontrolname). Spell somecontrolname incorrectly and Me. will generate an error on compile, Me! won't - but will when you execute the code. Accepted wisdom is to early bind controls.

[Shadows1989]

thanks

with regards this

Me.Parent![frm_Subform1].Form.Requery


you only need to requery the subform control


Me.Parent![frm_Subform1].Requery

also using a ! late binds [frm_Subform1]. Late binding means that any errors will not be generated until the code is executing. Using a dot - Me.Parent.[frm_Subform1] - early binds so any errors will be generated when you compile the code. Early binding also means you have the benefit of the VBA intellisense if available (i.e. try Me.somecontrolname instead of Me!somecontrolname). Spell somecontrolname incorrectly and Me. will generate an error on compile, Me! won't - but will when you execute the code. Accepted wisdom is to early bind controls.

Thank you again. I will take heed of this and change my coding.

I have also added to your reputation again.

[pbaldy]

Thanks for this. I did post a reply around 11am GMT but for some reason, it isn't showing.

It was moderated because of the link, now approved.

[Shadows1989]

It was moderated because of the link, now approved.

Thank you bud. I found that link helpful, so thought it would help others .

[pbaldy]

No worries and it probably will help others. It's an anti-spam setting on the forum; you'll be able to post links after 10-15 posts I think.