Problem flling a control on a subform from the main form

[Guest]

I want to be able to enter data in a control on a subform when a selection is
made in a combo box on the subform. The code is in the After Update event of
this combo box on the subform and it refers to a combo box on the main form
in which a selection has already been made. The data I want is in the 4th
literal column in the SQL, but since it's zero based, I've put Column 3 in
the code. I have a similar process working on the subform for another control
that contains similar information. When I make a selection in the combo box
on the subform, I receive runtime error 450: Wrong number of arguments or
invalid property assignment. Can anyone help?

Here is the code for both. Is there something wrong with my syntax? (I've
included my comments in the code)

This is the code that generates an error
Private Sub cboExAction_AfterUpdate()
'Enters FIPS code for the primary state selected in cboState on the main
form into
'txtFIPS_1
txtFIPS_1 = Forms!frm_SR_Data_Entry!cboState.[Column](3)
End Sub

This code works fine. But of course the reference is to a control on the
same form.
Private Sub cboExPartnerState_AfterUpdate()
'Enters FIPS code for the partner state selected in cboPartnerState into
txtFIPS_2
txtFIPS_2 = Me!cboExPartnerState.[Column](1)
End Sub

 

[Guest]

Hi, Susan.

If the cboState combo box is on the main form, then in the OnAfterUpdate( )
event of the subform's control, change:

txtFIPS_1 = Forms!frm_SR_Data_Entry!cboState.[Column](3)

To:

Me!txtFIPS_1.Value = Me.Parent.Controls!cboState.Column(3)

You don't need to place brackets around the Column Property of a combo box.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

thankyou thankyou and more...syntax is everything.

should I change the second statement to
txtFIPS_2.Vaue = etc.

And one more question -- how can I tell when to use ! instead of a dot. All
a mystery to me!
--
susan

Hi, Susan.

If the cboState combo box is on the main form, then in the OnAfterUpdate( )
event of the subform's control, change:

txtFIPS_1 = Forms!frm_SR_Data_Entry!cboState.[Column](3)

To:

Me!txtFIPS_1.Value = Me.Parent.Controls!cboState.Column(3)

You don't need to place brackets around the Column Property of a combo box.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I want to be able to enter data in a control on a subform when a selection is
made in a combo box on the subform. The code is in the After Update event of
this combo box on the subform and it refers to a combo box on the main form
in which a selection has already been made. The data I want is in the 4th
literal column in the SQL, but since it's zero based, I've put Column 3 in
the code. I have a similar process working on the subform for another control
that contains similar information. When I make a selection in the combo box
on the subform, I receive runtime error 450: Wrong number of arguments or
invalid property assignment. Can anyone help?

Here is the code for both. Is there something wrong with my syntax? (I've
included my comments in the code)

This is the code that generates an error
Private Sub cboExAction_AfterUpdate()
'Enters FIPS code for the primary state selected in cboState on the main
form into
'txtFIPS_1
txtFIPS_1 = Forms!frm_SR_Data_Entry!cboState.[Column](3)
End Sub

This code works fine. But of course the reference is to a control on the
same form.
Private Sub cboExPartnerState_AfterUpdate()
'Enters FIPS code for the partner state selected in cboPartnerState into
txtFIPS_2
txtFIPS_2 = Me!cboExPartnerState.[Column](1)
End Sub

 

['69 Camaro]

Hi, Susan.

should I change the second statement to
txtFIPS_2.Vaue = etc.

Try:

Me!txtFIPS_2.Value = Me!cboExPartnerState.Column(1)

And one more question -- how can I tell when to use ! instead of a dot.
All
a mystery to me!

Use the dot operator after Me when referring to a form or report property.
Use the bang (!) operator when referring to a user-defined object, such as
the text box that displays the value of a bound field. When creating new
forms or new controls on an existing form, don't accept the default name
given by Access if these controls are going to be referenced in VBA code or
in the Properties dialog window. For example, if the Form Wizard creates a
text box bound to the FIPS_2 field, then renaming this FIPS_2 text box to
txtFIPS_2 was a good choice for avoiding future bugs. Remember that each
bound field is a property of the form or report, so:

Me.FIPS_2 refers to the form property or the control of the same name that
displays this bound field; and
Me!FIPS_2 refers to the control.

When Access resolves the ambiguity of the first item above, you may not
agree with Access's choice. So renaming the FIPS_2 text box control to
txtFIPS_2 ensures that there's never any ambiguity-causing bugs that grab
the property instead of the text box control when executing a VBA command or
assigning/retrieving a value to/from a property. (One usually gets circular
reference errors when this happens, but there are other bugs one could
encounter, too, so it's best to avoid the ambiguity in the first place.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

thankyou thankyou and more...syntax is everything.

should I change the second statement to
txtFIPS_2.Vaue = etc.

And one more question -- how can I tell when to use ! instead of a dot.
All
a mystery to me!
--
susan

Hi, Susan.

If the cboState combo box is on the main form, then in the
OnAfterUpdate( )
event of the subform's control, change:

txtFIPS_1 = Forms!frm_SR_Data_Entry!cboState.[Column](3)

To:

Me!txtFIPS_1.Value = Me.Parent.Controls!cboState.Column(3)

You don't need to place brackets around the Column Property of a combo
box.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.

I want to be able to enter data in a control on a subform when a
selection is
made in a combo box on the subform. The code is in the After Update
event of
this combo box on the subform and it refers to a combo box on the main
form
in which a selection has already been made. The data I want is in the
4th
literal column in the SQL, but since it's zero based, I've put Column 3
in
the code. I have a similar process working on the subform for another
control
that contains similar information. When I make a selection in the combo
box
on the subform, I receive runtime error 450: Wrong number of arguments
or
invalid property assignment. Can anyone help?

Here is the code for both. Is there something wrong with my syntax?
(I've
included my comments in the code)

This is the code that generates an error
Private Sub cboExAction_AfterUpdate()
'Enters FIPS code for the primary state selected in cboState on the
main
form into
'txtFIPS_1
txtFIPS_1 = Forms!frm_SR_Data_Entry!cboState.[Column](3)
End Sub

This code works fine. But of course the reference is to a control on
the
same form.
Private Sub cboExPartnerState_AfterUpdate()
'Enters FIPS code for the partner state selected in cboPartnerState
into
txtFIPS_2
txtFIPS_2 = Me!cboExPartnerState.[Column](1)
End Sub

 

[Guest]

Gunny: Wow! Am I glad I asked the question. And thank you for the detailed
answer. I'm going to go back through my code in this database to make sure my
references are "clean." I've been wondering about the dot and the bang ever
since I started trying to teach myself Access/VBA. Now I understand! Thanks.
And thanks too for the reference to the Q-Built Access site, where i plan to
hang out!

Many, many thaks.
--
susan

Hi, Susan.

should I change the second statement to
txtFIPS_2.Vaue = etc.

Try:

Me!txtFIPS_2.Value = Me!cboExPartnerState.Column(1)

And one more question -- how can I tell when to use ! instead of a dot.
All
a mystery to me!

Use the dot operator after Me when referring to a form or report property.
Use the bang (!) operator when referring to a user-defined object, such as
the text box that displays the value of a bound field. When creating new
forms or new controls on an existing form, don't accept the default name
given by Access if these controls are going to be referenced in VBA code or
in the Properties dialog window. For example, if the Form Wizard creates a
text box bound to the FIPS_2 field, then renaming this FIPS_2 text box to
txtFIPS_2 was a good choice for avoiding future bugs. Remember that each
bound field is a property of the form or report, so:

Me.FIPS_2 refers to the form property or the control of the same name that
displays this bound field; and
Me!FIPS_2 refers to the control.

When Access resolves the ambiguity of the first item above, you may not
agree with Access's choice. So renaming the FIPS_2 text box control to
txtFIPS_2 ensures that there's never any ambiguity-causing bugs that grab
the property instead of the text box control when executing a VBA command or
assigning/retrieving a value to/from a property. (One usually gets circular
reference errors when this happens, but there are other bugs one could
encounter, too, so it's best to avoid the ambiguity in the first place.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

thankyou thankyou and more...syntax is everything.

should I change the second statement to
txtFIPS_2.Vaue = etc.

And one more question -- how can I tell when to use ! instead of a dot.
All
a mystery to me!
--
susan

Hi, Susan.

If the cboState combo box is on the main form, then in the
OnAfterUpdate( )
event of the subform's control, change:

txtFIPS_1 = Forms!frm_SR_Data_Entry!cboState.[Column](3)

To:

Me!txtFIPS_1.Value = Me.Parent.Controls!cboState.Column(3)

You don't need to place brackets around the Column Property of a combo
box.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

I want to be able to enter data in a control on a subform when a
selection is
made in a combo box on the subform. The code is in the After Update
event of
this combo box on the subform and it refers to a combo box on the main
form
in which a selection has already been made. The data I want is in the
4th
literal column in the SQL, but since it's zero based, I've put Column 3
in
the code. I have a similar process working on the subform for another
control
that contains similar information. When I make a selection in the combo
box
on the subform, I receive runtime error 450: Wrong number of arguments
or
invalid property assignment. Can anyone help?

Here is the code for both. Is there something wrong with my syntax?
(I've
included my comments in the code)

This is the code that generates an error
Private Sub cboExAction_AfterUpdate()
'Enters FIPS code for the primary state selected in cboState on the
main
form into
'txtFIPS_1
txtFIPS_1 = Forms!frm_SR_Data_Entry!cboState.[Column](3)
End Sub

This code works fine. But of course the reference is to a control on
the
same form.
Private Sub cboExPartnerState_AfterUpdate()
'Enters FIPS code for the partner state selected in cboPartnerState
into
txtFIPS_2
txtFIPS_2 = Me!cboExPartnerState.[Column](1)
End Sub

 

['69 Camaro]

You're very welcome!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

Gunny: Wow! Am I glad I asked the question. And thank you for the detailed
answer. I'm going to go back through my code in this database to make sure
my
references are "clean." I've been wondering about the dot and the bang
ever
since I started trying to teach myself Access/VBA. Now I understand!
Thanks.
And thanks too for the reference to the Q-Built Access site, where i plan
to
hang out!

Many, many thaks.
--
susan

Hi, Susan.

should I change the second statement to
txtFIPS_2.Vaue = etc.

Try:

Me!txtFIPS_2.Value = Me!cboExPartnerState.Column(1)

And one more question -- how can I tell when to use ! instead of a dot.
All
a mystery to me!

Use the dot operator after Me when referring to a form or report
property.
Use the bang (!) operator when referring to a user-defined object, such
as
the text box that displays the value of a bound field. When creating new
forms or new controls on an existing form, don't accept the default name
given by Access if these controls are going to be referenced in VBA code
or
in the Properties dialog window. For example, if the Form Wizard creates
a
text box bound to the FIPS_2 field, then renaming this FIPS_2 text box to
txtFIPS_2 was a good choice for avoiding future bugs. Remember that each
bound field is a property of the form or report, so:

Me.FIPS_2 refers to the form property or the control of the same name
that
displays this bound field; and
Me!FIPS_2 refers to the control.

When Access resolves the ambiguity of the first item above, you may not
agree with Access's choice. So renaming the FIPS_2 text box control to
txtFIPS_2 ensures that there's never any ambiguity-causing bugs that grab
the property instead of the text box control when executing a VBA command
or
assigning/retrieving a value to/from a property. (One usually gets
circular
reference errors when this happens, but there are other bugs one could
encounter, too, so it's best to avoid the ambiguity in the first place.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

thankyou thankyou and more...syntax is everything.

should I change the second statement to
txtFIPS_2.Vaue = etc.

And one more question -- how can I tell when to use ! instead of a dot.
All
a mystery to me!
--
susan


:

Hi, Susan.

If the cboState combo box is on the main form, then in the
OnAfterUpdate( )
event of the subform's control, change:

txtFIPS_1 = Forms!frm_SR_Data_Entry!cboState.[Column](3)

To:

Me!txtFIPS_1.Value = Me.Parent.Controls!cboState.Column(3)

You don't need to place brackets around the Column Property of a combo
box.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a
message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember
that
questions answered the quickest are often from those who have a
history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

I want to be able to enter data in a control on a subform when a
selection is
made in a combo box on the subform. The code is in the After Update
event of
this combo box on the subform and it refers to a combo box on the
main
form
in which a selection has already been made. The data I want is in
the
4th
literal column in the SQL, but since it's zero based, I've put
Column 3
in
the code. I have a similar process working on the subform for
another
control
that contains similar information. When I make a selection in the
combo
box
on the subform, I receive runtime error 450: Wrong number of
arguments
or
invalid property assignment. Can anyone help?

Here is the code for both. Is there something wrong with my syntax?
(I've
included my comments in the code)

This is the code that generates an error
Private Sub cboExAction_AfterUpdate()
'Enters FIPS code for the primary state selected in cboState on the
main
form into
'txtFIPS_1
txtFIPS_1 = Forms!frm_SR_Data_Entry!cboState.[Column](3)
End Sub

This code works fine. But of course the reference is to a control on
the
same form.
Private Sub cboExPartnerState_AfterUpdate()
'Enters FIPS code for the partner state selected in cboPartnerState
into
txtFIPS_2
txtFIPS_2 = Me!cboExPartnerState.[Column](1)
End Sub