Combo Box Update

T

TL

What is the code and where should it be place to update a main form combo box
after entering data into a subform combox?
 
T

TL

I have tried the code you detailed but keep coming up with a "Compile
Error:Syntax Error"

Not sure whether the "Parent" section of the "Me.Parent!LOTOGroups" code
referenced the mainform I tried leaving it as parent and also entering the
mainforms name and keep receiving above error.

The subform combo box "LOTOGroups" enters data into a LOTOtable.
The LOTOtable has a one-to-many relationship with a PREVENTTable.
The mainforms combo box "LOTOGroup" has a row source of "SELECT [LOTO
Table].[LOTOGroups] FROM [LOTO Table] ORDER BY [LOTOGroups];"

I'm note sure where I'm going wrong - could it be the query behind the
mainform?
 
A

Armen Stein

I have tried the code you detailed but keep coming up with a "Compile
Error:Syntax Error"

Not sure whether the "Parent" section of the "Me.Parent!LOTOGroups" code
referenced the mainform I tried leaving it as parent and also entering the
mainforms name and keep receiving above error.

The subform combo box "LOTOGroups" enters data into a LOTOtable.
The LOTOtable has a one-to-many relationship with a PREVENTTable.
The mainforms combo box "LOTOGroup" has a row source of "SELECT [LOTO
Table].[LOTOGroups] FROM [LOTO Table] ORDER BY [LOTOGroups];"

I'm note sure where I'm going wrong - could it be the query behind the
mainform?

The rowsource of the combobox shouldn't be an issue, nor should the
query behind they main form. You're having trouble just referencing
the combobox, which result in a compile error. The fact that it is a
syntax error means that you likely haven't written the line of code
correctly. Post your exact code for us to look at.

Also, use the immediate window (Ctrl-G) while your form is open to try
a direct reference.

?Forms!MyMainFormName!LOTOGroups

Does that return a value? If not, maybe the form isn't truly the top
level form, maybe it is another subform?

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
T

TL

See code below:

Private Sub Form_AfterUpdate()
Me.Parent!LOTOGroup
End Sub

I'm not sure what the cntrl-G reference is.

Armen Stein said:
I have tried the code you detailed but keep coming up with a "Compile
Error:Syntax Error"

Not sure whether the "Parent" section of the "Me.Parent!LOTOGroups" code
referenced the mainform I tried leaving it as parent and also entering the
mainforms name and keep receiving above error.

The subform combo box "LOTOGroups" enters data into a LOTOtable.
The LOTOtable has a one-to-many relationship with a PREVENTTable.
The mainforms combo box "LOTOGroup" has a row source of "SELECT [LOTO
Table].[LOTOGroups] FROM [LOTO Table] ORDER BY [LOTOGroups];"

I'm note sure where I'm going wrong - could it be the query behind the
mainform?

The rowsource of the combobox shouldn't be an issue, nor should the
query behind they main form. You're having trouble just referencing
the combobox, which result in a compile error. The fact that it is a
syntax error means that you likely haven't written the line of code
correctly. Post your exact code for us to look at.

Also, use the immediate window (Ctrl-G) while your form is open to try
a direct reference.

?Forms!MyMainFormName!LOTOGroups

Does that return a value? If not, maybe the form isn't truly the top
level form, maybe it is another subform?

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

.
 
J

John W. Vinson

See code below:

Private Sub Form_AfterUpdate()
Me.Parent!LOTOGroup
End Sub

This doesn't *DO* anything. It's like sending a postcard saying "Aunt Mary"
and nothing else!
 
A

Armen Stein

This doesn't *DO* anything. It's like sending a postcard saying "Aunt Mary"
and nothing else!

Right. That's why you have a syntax error. As John says, you need to
DO something with that control (although I'm not sure what his Aunt
Mary has to do with it. :)

What do you want to do with it? In your original post you said you
wanted to "update" it.

Maybe:
Me.Parent!LOTOGroup = Me.MyControlOnThisSubform

We can't tell for sure, because you haven't told us.

Regarding Ctrl-G: Try pressing it when your form is open. It's the
Immediate Window, which allows you to display the values of various
controls and functions "immediately", instead of in code. If you're
going to be programming using VBA, you'll want to learn about this.
Check Access Help.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
T

TL

What I am trying to do is update (requery?) a main form combo box after
entering data into a subform combo box?

What I previously sent was the code that was placed into the subform's
update section.

Will the "Me.Parent!LOTOGroup = Me.MyControlOnThisSubform" do this?

Is the "Parent" section of this code supposed to the name of the mainform,
or remain as parent?

I hope this helps
 
A

Armen Stein

What I am trying to do is update (requery?) a main form combo box after
entering data into a subform combo box?

Okay. Try:

Me.Parent!LOTOGroup.Requery

This will requery the entries in the dropdown list for that combobox.
It will NOT change the current value of the item selected in the
combobox, if any.
What I previously sent was the code that was placed into the subform's
update section.

That seems to be the right place, but again, we can't see everything
you have.
Will the "Me.Parent!LOTOGroup = Me.MyControlOnThisSubform" do this?

No. It would change the VALUE of the combobox on the main form, which
is what I thought you were trying to do.
Is the "Parent" section of this code supposed to the name of the mainform,
or remain as parent?

My (and many others') convention is to prefix anything that needs to
be changed to *your* naming with "My". However, in this case Parent
is a property of an Access form that refers to the form that contains
it. It can even go further - Me.Parent.Parent refers to a subform's
Parent's Parent form. (Grandparent :)

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
T

TL

Receiving "Run-Time Error 2452 - Expression you have entered has an invalid
reference to the Parent property"

Review of my design

EquipmentDataTable (LOTOGroup field) -> EquipmentType (Query with LOTOGroup
field) -> EquipmentFrm (mainform) - which has the LOTOGroup field (Combobox
with a Row Source of "SELECT [LOTO Table].[LOTOGroups] FROM [LOTO Table]
ORDER BY [LOTOGroups];"

LOTOTable (LOTOGroups field) -> LOTOFrmADD (subform) - which has the
LOTOGroups field with the On Update code of the form set to
"Me.Parent!LOTOGroup.Requery"

I open the LOTOAddFrm (subform) from the EquipmentFrm (mainform) via a
command button with the following code:

Private Sub Command24_Click()
On Error GoTo Err_Command24_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "LOTOFrmAdd"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command24_Click:
Exit Sub
Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click
End Sub

What am I Missing?
 
J

John W. Vinson

LOTOTable (LOTOGroups field) -> LOTOFrmADD (subform) - which has the
LOTOGroups field with the On Update code of the form set to
"Me.Parent!LOTOGroup.Requery"

Are you just typing that in the On Update property? If so, change it to [Event
Procedure] and edit it into the VBA code for the event.
 
T

TL

I am going into the LOTTOFrmADD form's Properties, Event Tab, After Update
code and typing the code in their?

John W. Vinson said:
LOTOTable (LOTOGroups field) -> LOTOFrmADD (subform) - which has the
LOTOGroups field with the On Update code of the form set to
"Me.Parent!LOTOGroup.Requery"

Are you just typing that in the On Update property? If so, change it to [Event
Procedure] and edit it into the VBA code for the event.
 
J

John W. Vinson

I am going into the LOTTOFrmADD form's Properties, Event Tab, After Update
code and typing the code in their?

No.

Click on the ... icon next to the box, and choose Code Builder. This will open
the VBA editor. Write the code there.
 
T

TL

That is exactly what I am doing.

John W. Vinson said:
No.

Click on the ... icon next to the box, and choose Code Builder. This will open
the VBA editor. Write the code there.
 
T

TL

That is exactly what I am doing.

I open the form in design view, right click at the bottom of the form and
open properties, click on "After Update" event, clicking to the far right of
that line and opening code builder, then entering code there.
 
T

TL

That is exactly what I am doping.

Opening the form in design view, right-clicking at bottom of form and
opening properties, going to the event tab, clicking to the right of the
"After Update" to open up the code and typing in my code.
 
J

John W. Vinson

LOTOTable (LOTOGroups field) -> LOTOFrmADD (subform) - which has the
LOTOGroups field with the On Update code of the form set to
"Me.Parent!LOTOGroup.Requery"

Odd. Try

Forms!EquipmentFrm!LOTOGroup.Requery
 
T

TL

Receive Run-Time Error 438 - Object doen't support this property or method.

Am I wrong to assume that 2nd form (LOTOFrmADD) that I open from the 1st
Form (EquipmentFrm) is not truely a subform but rather just another form -
could this be the isse?

I'm about ready to place a "Refresh" button on the EquipmentFrm form and be
done, which I have tried and does work to update the combo field.
 
A

Armen Stein

Am I wrong to assume that 2nd form (LOTOFrmADD) that I open from the 1st
Form (EquipmentFrm) is not truely a subform but rather just another form -
could this be the isse?

Whoa there! If you "open" a Form2 from Form1, then Form2 is *just a
form*, not a subform at all. That may be the cause of all your
confusion.

A subform is a form that resides in a *subform control*, on another
form often called the "main form".

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top