Need to Hard Code in Combo Boxes Instead of using a lookup table

[desireemm via AccessMonster.com]

I created a table called the Family_Hours (look up table for a combo box)
this is based on the type of family and how many chidren they have and if
dad is at home then that determines how many hours they are responsible for
per week and month.
The table has three fields one is called TypeOfPariticpant, Month and Weekly

2 parent family = Weekly_hrs 40 Hours = Month 173 Hours
single parent child over 6 years = Weekly_hrs 30 hours = Month 130 Hours
sigle parent child under 6 years = Weekly_hrs 20 hours = Month 87 Hours
child under 1 year = Weekly_hrs 0 hours = Month 0 Hours


the users wants to choose what kind of family it is then have the hours
automatically pop up in the drop down. I hope that makes sense??
The part with the family is called TypeofParticipant the lower numbers are
WeeklyHours and the Higher numbers are Montly totals


and in the Event View under AfterUpdate here is the code I used

Combo1.RowSource = "SELECT FamilyHrID, TypeOfParticipant, WeeklyHours,
MonthlyHours FROM FamilyHours_tbl"
Combo1.ColumnCount = 4
Combo1.ColumnWidths = 0cm;2,503cm;0cm;0cm (sorry: in centimetres here!)
Combo1.BoundColumn = 1
Combo1.LimitToList = True

Combo2.RowSource = "SELECT FamilyHrID, WeeklyHours FROM FamilyHours_tbl"
Combo2.ColumnCount = 2
Combo2.ColumnWidths = 0cm;2,503cm
Combo2.BoundColumn = 2
Combo2.LimitToList = True

Combo3.RowSource = "SELECT FamilyHrID, MonthlyHours FROM FamilyHours_tbl"
Combo3.ColumnCount = 2
Combo3.ColumnWidths = 0cm;2,503cm
Combo3.BoundColumn = 2
Combo3.LimitToList = True

Private Sub TypeOfParticipant_AfterUpdate()
Me.Weekly = Me.TypeOfParticipant.Column(2)
Me.Weekly.SetFocus
Me.Weekly.Dropdown
Me.Month = Me.TypeOfParticipant.Column(3)
Me.Month.SetFocus
Me.Month.Dropdown
Me.TypeOfParticipant.SetFocus

End Sub

I need to hard code this into the afterupdate of the combo box instead of
using a lookup table and then coding it. and I was wondering how would I do
this. The Fields will never change, they will always remain the same there
wont be any change to TypeofParticipants, will always be the same thing. Can
someone show me how I can hard code this in and just use the Combo box
instead, please? Thank you

 

[Jeff Boyce]

Why? If you already have a functional lookup table and procedure, why do
you feel you need to "hard code" the same functionality into a combobox?

Could you just continue using the combobox to refer to the lookup table?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I created a table called the Family_Hours (look up table for a combo box)
this is based on the type of family and how many chidren they have and if
dad is at home then that determines how many hours they are responsible
for
per week and month.
The table has three fields one is called TypeOfPariticpant, Month and
Weekly

2 parent family = Weekly_hrs 40 Hours = Month 173 Hours
single parent child over 6 years = Weekly_hrs 30 hours = Month 130 Hours
sigle parent child under 6 years = Weekly_hrs 20 hours = Month 87 Hours
child under 1 year = Weekly_hrs 0 hours = Month 0 Hours


the users wants to choose what kind of family it is then have the hours
automatically pop up in the drop down. I hope that makes sense??
The part with the family is called TypeofParticipant the lower numbers are
WeeklyHours and the Higher numbers are Montly totals


and in the Event View under AfterUpdate here is the code I used

Combo1.RowSource = "SELECT FamilyHrID, TypeOfParticipant, WeeklyHours,
MonthlyHours FROM FamilyHours_tbl"
Combo1.ColumnCount = 4
Combo1.ColumnWidths = 0cm;2,503cm;0cm;0cm (sorry: in centimetres here!)
Combo1.BoundColumn = 1
Combo1.LimitToList = True

Combo2.RowSource = "SELECT FamilyHrID, WeeklyHours FROM FamilyHours_tbl"
Combo2.ColumnCount = 2
Combo2.ColumnWidths = 0cm;2,503cm
Combo2.BoundColumn = 2
Combo2.LimitToList = True

Combo3.RowSource = "SELECT FamilyHrID, MonthlyHours FROM FamilyHours_tbl"
Combo3.ColumnCount = 2
Combo3.ColumnWidths = 0cm;2,503cm
Combo3.BoundColumn = 2
Combo3.LimitToList = True

Private Sub TypeOfParticipant_AfterUpdate()
Me.Weekly = Me.TypeOfParticipant.Column(2)
Me.Weekly.SetFocus
Me.Weekly.Dropdown
Me.Month = Me.TypeOfParticipant.Column(3)
Me.Month.SetFocus
Me.Month.Dropdown
Me.TypeOfParticipant.SetFocus

End Sub

I need to hard code this into the afterupdate of the combo box instead of
using a lookup table and then coding it. and I was wondering how would I
do
this. The Fields will never change, they will always remain the same
there
wont be any change to TypeofParticipants, will always be the same thing.
Can
someone show me how I can hard code this in and just use the Combo box
instead, please? Thank you

 

[Daryl S]

What do you need the other two drop-downs for if you plan to populate them
with the weekly and monthly hours from the first drop-down? You can show all
three fields in the one drop-down that they choose from. If you need the
weekly and monthly hours in separate fields, put them there from the first
drop-down's record source on the BeforeUpdate event.

 

[desireemm via AccessMonster.com]

Hi everyone I have a look up table(FamilyHours_tbl) that i created for my
combo boxes but its not showing in the datasheet view and I dont kwow why. It
has a one to many relationship with the main table. I inherited this database
and where there is a value list I am trying to make it into a lookup table.
there's a total of 675 records in there already. What I did was run a make
table query using the People_tbl's primary key and I am wondering was that
the wrong approach or something???
see the thing is I inhertied the database and originaly the combo box was a
value list not a table/query. I am trying to create a cascading combo box but
its nto working

What do you need the other two drop-downs for if you plan to populate them
with the weekly and monthly hours from the first drop-down? You can show all
three fields in the one drop-down that they choose from. If you need the
weekly and monthly hours in separate fields, put them there from the first
drop-down's record source on the BeforeUpdate event.

I created a table called the Family_Hours (look up table for a combo box)
this is based on the type of family and how many chidren they have and if

[quoted text clipped - 52 lines]

someone show me how I can hard code this in and just use the Combo box
instead, please? Thank you

 

[Daryl S]

I am not clear on your table structure. What I think I understand is that
you have a main form with a combo box you converted from a value list to a
table/query. This table is the FamilyHours_tbl. You say the datasheet is
not showing - do you mean the data in the combo box? If your question is how
to get this table to show up in the combo box, then bring up the properties
for the combo box and make sure the following is there:

In the Data tab:
Row Source = "Table/Query"
Row Source = <name of your table or query>
Bound Column = 1 (assuming this is the primary key column in your
table/query)

In the Format tab:
column Count = <number of column you want to see in the combo box - 4?>
Column widths = 0";2";2";2" (These are the widths of the columns -
0" would hide the first column, often done for primary keys that the users
don't need to see. Use widths appropriate to your data)

When you talk about the make table query with People_tbl primary key - what
is this make table for? Were you trying to get the data for the table behind
the combo box? I thought you had already created the Family Hours table for
that.

Can you give us a better understanding of your tables? Do you just have
one main People table and then a few lookup tables to help populate the
People table?
--
Daryl S

Hi everyone I have a look up table(FamilyHours_tbl) that i created for my
combo boxes but its not showing in the datasheet view and I dont kwow why. It
has a one to many relationship with the main table. I inherited this database
and where there is a value list I am trying to make it into a lookup table.
there's a total of 675 records in there already. What I did was run a make
table query using the People_tbl's primary key and I am wondering was that
the wrong approach or something???
see the thing is I inhertied the database and originaly the combo box was a
value list not a table/query. I am trying to create a cascading combo box but
its nto working

What do you need the other two drop-downs for if you plan to populate them
with the weekly and monthly hours from the first drop-down? You can show all
three fields in the one drop-down that they choose from. If you need the
weekly and monthly hours in separate fields, put them there from the first
drop-down's record source on the BeforeUpdate event.

I created a table called the Family_Hours (look up table for a combo box)
this is based on the type of family and how many chidren they have and if

[quoted text clipped - 52 lines]

someone show me how I can hard code this in and just use the Combo box
instead, please? Thank you

--



.