field filled out based on option selection

[SylvieB]

I have this situation
In a form, I have a combo box named Product line with 4 options to select
from: instrumentation, targetry, engineering, services. I also have a text
box called “Participation%â€. When the user makes a selection from the Product
line, the percentage gets entered in the "Participation%" field automatically.
Those have been defined in he table tblProductLine that contains the product
name associated to the percentage.
I don’t know how to do that. I don’t know much VBA. Any help will be greatly
appreciated.

In advance, thank you.

 

[Al Campagna]

SylvieB,
Add the ParticipationPct (I avoid any symbols in names) column to
your combobox. I'll call them ProdLine and PartPct for ease...
First Col Second Col
ProdLine PartPct

Are the Participation% values always the same, or might they change
for each ProductLine over time? I'll assume that a ProdLine Targetry
today,
with 10% PartPct... could be 15% at some other time.

Use the AfterUpdate event of cboProdLine to set the value of your
PartPct control
Private Sub cboProdLine_AfterUpdate()
PartPct = cboProdLine.Column(1)
End Sub

When referring to combo column values, the columns are numbered
0, 1, 2, 3, etc... so the second column is Column(1)
That could be done with a Macro also...
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Sylvie]

SylvieB,
    Add the ParticipationPct (I avoid any symbols in names) column to
your combobox.  I'll call them ProdLine and PartPct for ease...
        First Col     Second Col
        ProdLine       PartPct

    Are the Participation% values always the same, or might they change
for each ProductLine over time?   I'll assume that a ProdLine Targetry
today,
with 10% PartPct... could be 15% at some other time.

    Use the AfterUpdate event of cboProdLine to set the value of your
PartPct control
Private Sub cboProdLine_AfterUpdate()
    PartPct = cboProdLine.Column(1)
End Sub

    When referring to combo column values, the columns are numbered
0, 1, 2, 3, etc... so the second column is Column(1)
    That could be done with a Macro also...
--
    hth
    Al Campagna
    Microsoft Access MVP 2006-2009
   http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."







- Show quoted text -

Thank you Al. I like your idea but I'm confused. I made the change on
the combo box like you suggested and created the event but it does not
pass the value of column1 (PartPct) to the text box Participation. I'm
getting a run time error "2113" the value you entered is not valid for
this field.

 

[Al Campagna]

Sylvie,
Please top post if the person who's trying to assist you does so.
It makes the sequence of the threads much easier to follow.
No big deal... just a heads up...

Also, whenever you have code problems, always cut & paste the
code you used into your reply. Describe where and how you placed
that code. We need to see that code... exactly as you have it.
Sorry, but I have to ask... have you done VB module code before?

Also, please describe your combo columns, as you have them now,
in detail... just to make sure we're on the same page...

When referring to combo column values, they are numbered
0, 1, 2, 3, etc... from left to right. The leftmost would be Column(0),
the next to the right Column(1).. etc...
To be sure that the value you are trying to place into a control is
the correct one... place an unbound text control on the form, and set
the Control Source to...
= cboProdLine.Column(1)
Is that the value you want for Participation?

If not... try
= cboProdLine.Column(0)
etc.. til you get the proper value.

Note: When you added ProdPct in the combo, you should have
changed...
Column Count = 1 to Column Count = 2
Column Widths = 1" to Column Widths = 1" ; 1"
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

SylvieB,
Add the ParticipationPct (I avoid any symbols in names) column to
your combobox. I'll call them ProdLine and PartPct for ease...
First Col Second Col
ProdLine PartPct

Are the Participation% values always the same, or might they change
for each ProductLine over time? I'll assume that a ProdLine Targetry
today,
with 10% PartPct... could be 15% at some other time.

Use the AfterUpdate event of cboProdLine to set the value of your
PartPct control
Private Sub cboProdLine_AfterUpdate()
PartPct = cboProdLine.Column(1)
End Sub

When referring to combo column values, the columns are numbered
0, 1, 2, 3, etc... so the second column is Column(1)
That could be done with a Macro also...
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."







- Show quoted text -

Thank you Al. I like your idea but I'm confused. I made the change on
the combo box like you suggested and created the event but it does not
pass the value of column1 (PartPct) to the text box Participation. I'm
getting a run time error "2113" the value you entered is not valid for
this field.

 

[Sylvie]

Sylvie,
    Please top post if the person who's trying to assist you does so.
    It makes the sequence of the threads much easier to follow.
    No big deal... just a heads up...

    Also, whenever you have code problems, always cut & paste the
code you used into your reply.  Describe where and how you placed
that code.  We need to see that code... exactly as you have it.
    Sorry, but I have to ask... have you done VB module code before?

    Also, please describe your combo columns, as you have them now,
in detail... just to make sure we're on the same page...

    When referring to combo column values, they are numbered
0, 1, 2, 3, etc... from left to right. The leftmost would be Column(0),
the next to the right Column(1).. etc...
    To be sure that the value you are trying to place into a control is
the correct one... place an unbound text control on the form, and set
the Control Source to...
        = cboProdLine.Column(1)
Is that the value you want for Participation?

If not... try
        = cboProdLine.Column(0)
etc.. til you get the proper value.

    Note: When you added ProdPct in the combo, you should have
changed...
    Column Count = 1   to  Column Count = 2
    Column Widths = 1"  to  Column Widths = 1" ; 1"
--
    hth
    Al Campagna
    Microsoft Access MVP 2007-2009
   http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."










Thank you Al. I like your idea but I'm confused. I made the change on
the combo box like you suggested and created the event but it does not
pass the value of column1 (PartPct) to the text box Participation. I'm
getting a run time error "2113" the value you entered is not valid for
thisfield.- Hide quoted text -

- Show quoted text -

Al, I don’t know much about VBA. You probably figured that out. I
appreciate your guidelines.
This is the code I have for the combo box Product Type
Private Sub cboProductType_AfterUpdate()
STSPart = cboProductType.Column(2)
End Sub

STSPart is the control name for Participation.
When I select an option from the combo box Product type, I have to
click on the text box Participation to pass the value. For example: I
select Training under Product type, I have to click to the
Participation box for the value 10% to show. How can the value pass
directly to the Participation box? Does it make sense?
Thank you

 

[Sylvie]

Al, I don’t know much about VBA. You probably figured thatout. I
appreciate your guidelines.
This is the code I have for the combo box Product Type
Private Sub cboProductType_AfterUpdate()
     STSPart = cboProductType.Column(2)
End Sub

STSPart is the control name for Participation.
When I select an option from the combo box Product type, I have to
click on the text box Participation to pass the value. For example: I
select Training under Product type, I have to click to the
Participation box for the value 10% to show. How can the value pass
directly to the Participation box? Does it make sense?
Thank you- Hide quoted text -

- Show quoted text -

Ok. I think I figured it out. I added a requery to refresh the data in
the Participation box. STS is the name of the text box. It works fine.
Again, Thank you for all your help.
Private Sub cboProductType_AfterUpdate()
STSPart = cboProductType.Column(2)
Me!STS.Requery
End Sub