Autopopulate second field from first combobox

[katjelko]

I have two tables

1 - tblAddNewStudy

StudyID

StudyName

StudyDate

PointValue

2 - tblEmployeePoints

EmployeeID

StudyID

PointValue

I have created a form showing Employee ID, Study ID and PointValue from the
tblEmployeePoints.

The form has a combobox for the StudyID field. I want the PointValue field to
autopopulate once the StudyID field has been selected by the user.

I want the PointValue that is autopopulated stored in the tblEmployeePoints
table.

Using Access 2002.

Any suggestions? Thanks

 

[Al Campagna]

katjelko,
Use tblAddNewStudy as the RowSource for your StudyID combo (let's name
it cboStudyID) on your EmployeePoints form.
That cboStudyID combo should have 2 columns... StudyID and PointValue,
and the combo should be bound to the StudyID field of your EmployeePoints
table.
Example...
StudyID PointValue
142 15
341 22
524 44

Using the After Update event of the cboStudyID use the following code...

Private Sub cboStudyID_AfterUpdate
PointValue = cboStudyID.Column(1)
End Sub

(When referring to the values in a combo box, the colums are 0, 1, 2, 3,
etc... so the PointValue would be in Column(1))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

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

 

[Jeff Boyce]

Why are you recording [PointValue] in two different tables? If you end up
with a StudyID/PointValue combination in one table with one PointValue and
the same combination in the other table with a DIFFERENT PointValue, which
one is correct?

Access is a relational database. You probably don't need to store that
PointValue twice. Instead, if you know which StudyID is involved, you can
use a query to join the Study and EmployeePoints tables (on StudyID), and
"look up" the PointValue from the Study table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[katjelko]

katjelko,
Use tblAddNewStudy as the RowSource for your StudyID combo (let's name
it cboStudyID) on your EmployeePoints form.
That cboStudyID combo should have 2 columns... StudyID and PointValue,
and the combo should be bound to the StudyID field of your EmployeePoints
table.
Example...
StudyID PointValue
142 15
341 22
524 44

Using the After Update event of the cboStudyID use the following code...

Private Sub cboStudyID_AfterUpdate
PointValue = cboStudyID.Column(1)
End Sub

(When referring to the values in a combo box, the colums are 0, 1, 2, 3,
etc... so the PointValue would be in Column(1))

I have two tables

[quoted text clipped - 31 lines]

Any suggestions? Thanks

Hi Al,

I am kind of new to this. I am not sure how to have the cboStudyID combobox
be bound to the StudyID field of the EmployeePoints table. Thanks for your
help.

 

[Al Campagna]

katjelko,
Please don't <snip> the "relative" text from previous posts. We want to
be able to see the whole flow of the problem in the latetst post.
And please top-post to avoid the sequence of our problem getting all out
of whack. As a general rule, follow the top/bottom posting of the person
who's working with you.
You wrote...

2 - tblEmployeePoints
EmployeeID
StudyID
PointValue

When you select a StudyID using your cboStudyID combo, you want that
value to be stored in your tblEmployeePoints, in the StudyID field.
Indicate StudyID in the ControlSource of cboStudyID. That's what's
called "binding" a control to a table field. Whatever is selected in
cboStudyID will be stored in the StudyID field.
And also, your PointValue control on the form would be "bound" to the
PointValue field in tblEmployeePoints, by indicating PointValue in the
control's ControlSource.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

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

Hi Al,

I am kind of new to this. I am not sure how to have the cboStudyID
combobox
be bound to the StudyID field of the EmployeePoints table. Thanks for
your
help.

katjelko,
Use tblAddNewStudy as the RowSource for your StudyID combo (let's name
it cboStudyID) on your EmployeePoints form.
That cboStudyID combo should have 2 columns... StudyID and PointValue,
and the combo should be bound to the StudyID field of your EmployeePoints
table.
Example...
StudyID PointValue
142 15
341 22
524 44

Using the After Update event of the cboStudyID use the following
code...

Private Sub cboStudyID_AfterUpdate
PointValue = cboStudyID.Column(1)
End Sub

(When referring to the values in a combo box, the colums are 0, 1, 2,
3,
etc... so the PointValue would be in Column(1))

I have two tables

[quoted text clipped - 31 lines]

Any suggestions? Thanks

 

[katjelko]

Why are you recording [PointValue] in two different tables? If you end up
with a StudyID/PointValue combination in one table with one PointValue and
the same combination in the other table with a DIFFERENT PointValue, which
one is correct?

Access is a relational database. You probably don't need to store that
PointValue twice. Instead, if you know which StudyID is involved, you can
use a query to join the Study and EmployeePoints tables (on StudyID), and
"look up" the PointValue from the Study table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two tables

[quoted text clipped - 31 lines]

Any suggestions? Thanks

This is how I think it should be done. Not sure if it's the best method.
First the user inputs the StudyID and the Point Value in tblAddNewStudy table.


Then I create a form with the Employee ID, Study ID and Point Value fields.
I created combo boxes in my form so the user can select the EmployeeID and
the Study ID. The EmployeeID and the StudyID selected by the user is stored
in the tblEmployeePoints table. After the user selects the StudyID I want
the Point Value to autopopulate. Not sure how to have the Point Value
autopopulate (based on the StudyID) and have it stored in the
tblEmployeePoints table.

Hope this makes sense. Is there a better way that I am not thinking of?
Thanks.

 

[Al Campagna]

katjelko,
If the PointValue for a particular StudyID will NEVER change, then use
Jeff Boyce's calculated PointValue.
If the PointValue for a particular StudyID might change in the future,
then use my method of auto populating the PointValue, and storing it in your
table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

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

Why are you recording [PointValue] in two different tables? If you end up
with a StudyID/PointValue combination in one table with one PointValue and
the same combination in the other table with a DIFFERENT PointValue, which
one is correct?

Access is a relational database. You probably don't need to store that
PointValue twice. Instead, if you know which StudyID is involved, you can
use a query to join the Study and EmployeePoints tables (on StudyID), and
"look up" the PointValue from the Study table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two tables

[quoted text clipped - 31 lines]

Any suggestions? Thanks

This is how I think it should be done. Not sure if it's the best method.
First the user inputs the StudyID and the Point Value in tblAddNewStudy
table.


Then I create a form with the Employee ID, Study ID and Point Value
fields.
I created combo boxes in my form so the user can select the EmployeeID and
the Study ID. The EmployeeID and the StudyID selected by the user is
stored
in the tblEmployeePoints table. After the user selects the StudyID I want
the Point Value to autopopulate. Not sure how to have the Point Value
autopopulate (based on the StudyID) and have it stored in the
tblEmployeePoints table.

Hope this makes sense. Is there a better way that I am not thinking of?
Thanks.

 

[Jeff Boyce]

Al nailed it! If your Study PointValue never changes, you don't need to
"autopopulate" (i.e., store it a second time).

If you want to SEE the PointValue on your form after selecting the Study,
you could use the AfterUpdate event of the Study-combobox to do something
like:

Me!txtShowPointValue = Me!cboSelectStudy.Column(x)

where you use your control names instead of the ones in this example, and
you use the "n-1"th column for "x" (.Column() is zero-based).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Why are you recording [PointValue] in two different tables? If you end up
with a StudyID/PointValue combination in one table with one PointValue and
the same combination in the other table with a DIFFERENT PointValue, which
one is correct?

Access is a relational database. You probably don't need to store that
PointValue twice. Instead, if you know which StudyID is involved, you can
use a query to join the Study and EmployeePoints tables (on StudyID), and
"look up" the PointValue from the Study table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two tables

[quoted text clipped - 31 lines]

Any suggestions? Thanks

This is how I think it should be done. Not sure if it's the best method.
First the user inputs the StudyID and the Point Value in tblAddNewStudy table.


Then I create a form with the Employee ID, Study ID and Point Value fields.
I created combo boxes in my form so the user can select the EmployeeID and
the Study ID. The EmployeeID and the StudyID selected by the user is stored
in the tblEmployeePoints table. After the user selects the StudyID I want
the Point Value to autopopulate. Not sure how to have the Point Value
autopopulate (based on the StudyID) and have it stored in the
tblEmployeePoints table.

Hope this makes sense. Is there a better way that I am not thinking of?
Thanks.