How to set Subform data from a main form? (1 Viewer)

Dana_

New member
Local time
Today, 23:14
Joined
Nov 23, 2021
Messages
18
I have a form "MAF_Main" with a subform "AbteilungFunktion", which contains to other forms ("Abteilung", "Funktion").
If button in the main form is clicked, I want to set the value of Combobox inside the subform "Abteilung". Hence I wrote
this line of code in my button clicked event, but it doesnt work. Could someone give me a hint, how could I solve it?
Code:
:
Forms![MAF_Main]![AbteilungFunktion].Form![Abteilung].Form![Me.AbteilungCboBox.Column(0)] = Me.MAF_AbteilungsID

[ATTACH type="full" width="293px"]100899[/ATTACH]
 

Attachments

  • 1654072942096.png
    1654072942096.png
    21.3 KB · Views: 130

CJ_London

Super Moderator
Staff member
Local time
Today, 22:14
Joined
Feb 19, 2013
Messages
16,553
what does 'it doesnt work' mean? nothing happens? wrong value? you get an error?
 

Dana_

New member
Local time
Today, 23:14
Joined
Nov 23, 2021
Messages
18
what does 'it doesnt work' mean? nothing happens? wrong value? you get an error?
Yes I get an error (runtime error), which says it cannot find "Abteilung". I think I am missing something in my code
1654074520209.png
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:14
Joined
Feb 19, 2013
Messages
16,553
since you are already on MAF_Main for your code

AbteilungFunktion.Form.Abteilung.form.Me.AbteilungCboBox = Me.MAF_AbteilungsID

if still can't find it, check spelling

or try building with intellisense - type

Me.AbteilungFunktion. then follow the prompts
 

Auntiejack56

Registered User.
Local time
Tomorrow, 09:14
Joined
Aug 7, 2017
Messages
175
Hey CJ, I think you have an extra 'Me' in there ...
Code:
Me!AbteilungFunktion.Form!Abteilung.Form!AbteilungCboBox = Me.MAF_AbteilungsID
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:14
Joined
Feb 19, 2013
Messages
16,553
oops:eek: just copy pasted the OP's code and didn't notice

But recommend using dot rather than bang (. v !) so you get the benefit of intellisense nd it early binds which technically will be faster than ! which is late bound. Not that it is likely to be noticable
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 19, 2002
Messages
42,976
I want to set the value of Combobox inside the subform "Abteilung"
OK, what subform record are you intending to update? The first one, the last one, #438? Do you know which record in the subform Access thinks is Current? Otherwise, this is a little like the children's game of pin the tail on the donkey. You may hit the mark or you may update an unexpected record. If you are trying to propagate a foreign key, the proper method is to set the master/child links of the subform control. That will tell Access what the PK field is for the parent record and what the FK field is for the child record so Access can automagically populate the FK in the subform each time you add a new record without you writing any code.

There is no logical reason to push a value from the main form to the subform so there is some design flaw in your schema if you need to do this.

@CJ_London corrected your syntax. There is one other potential issue aside from a spelling error. Apparently a "feature" that used to exist only on reports has been added to forms. If you want to reference a RecordSource field in VBA, it MUST be bound to a control. The control may be tiny and hidden but it must be on the subform. Have you created a control named Abteilung on the subform?
 

Dana_

New member
Local time
Today, 23:14
Joined
Nov 23, 2021
Messages
18
Hey CJ, I think you have an extra 'Me' in there ...
Code:
Me!AbteilungFunktion.Form!Abteilung.Form!AbteilungCboBox = Me.MAF_AbteilungsID
This code worked. Thank you and @CJ_London. I tried building with intellisense but id did not show Abteilung....
 

Dana_

New member
Local time
Today, 23:14
Joined
Nov 23, 2021
Messages
18
OK, what subform record are you intending to update? The first one, the last one, #438? Do you know which record in the subform Access thinks is Current? Otherwise, this is a little like the children's game of pin the tail on the donkey. You may hit the mark or you may update an unexpected record. If you are trying to propagate a foreign key, the proper method is to set the master/child links of the subform control. That will tell Access what the PK field is for the parent record and what the FK field is for the child record so Access can automagically populate the FK in the subform each time you add a new record without you writing any code.

There is no logical reason to push a value from the main form to the subform so there is some design flaw in your schema if you need to do this.

@CJ_London corrected your syntax. There is one other potential issue aside from a spelling error. Apparently a "feature" that used to exist only on reports has been added to forms. If you want to reference a RecordSource field in VBA, it MUST be bound to a control. The control may be tiny and hidden but it must be on the subform. Have you created a control named Abteilung on the subform?
Yes I did. I created a control named Abteilung on the subform
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 19, 2002
Messages
42,976
As I said, there are many records in a subform's RecordSource, do you always know which one you are updating?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 19, 2002
Messages
42,976
No, Not always
So, it doesn't matter what record you are updating? If you tell us the business purpose for updating the subform record, we can probably provide more relevant help.
 

Dana_

New member
Local time
Today, 23:14
Joined
Nov 23, 2021
Messages
18
So, it doesn't matter what record you are updating? If you tell us the business purpose for updating the subform record, we can probably provide more relevant help.
It works actually like I wanted, thank you for your reply
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 19, 2002
Messages
42,976
Let me try again. There is ONE main form record and MANY subform records. When you reference the subform from the main form, you are referencing the CURRENT record in the subform. If you haven't scrolled the subform, that will default to the first record. If you have scrolled the subform, the CURRENT record could be ANY record. The reference also updates only ONE record of the subform.
 

Users who are viewing this thread

Top Bottom