Limit values in combo boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for taking the time to read my question.

I have 5 combo boxes, all with a Query/Table as a rowsource.

There is only one field(Category). This is a list of all the expense
categories.

I would like to limit each one based on the values of the others.

If combobox1 = Food
then
combobox? can have every other category except for Food.

I can do this if none of the other combo boxes are Null, but if they are, it
doesn't work.

How do I get around this?

I have a table with the Categories in it (3 fields: AutoID, Category,
CategoryDescription) The Rowsource is:

SELECT tblCategory.Category FROM tblCategory ORDER BY tblCategory.Category;

What do I add to this to limit it if other comboboxes have values in them,
but not limit it if the other comboboxes are null.

Thanks so much for your help,

Brad
 
Thanks for taking the time to read my question.

I have 5 combo boxes, all with a Query/Table as a rowsource.

There is only one field(Category). This is a list of all the expense
categories.

I would like to limit each one based on the values of the others.

If combobox1 = Food
then
combobox? can have every other category except for Food.

I can do this if none of the other combo boxes are Null, but if they are, it
doesn't work.

How do I get around this?

I have a table with the Categories in it (3 fields: AutoID, Category,
CategoryDescription) The Rowsource is:

SELECT tblCategory.Category FROM tblCategory ORDER BY tblCategory.Category;

What do I add to this to limit it if other comboboxes have values in them,
but not limit it if the other comboboxes are null.
Click to expand...

I'd like to make another, quite different suggestion. It appears that
you are trying to store five categories into five fields in a single
record in a table. This suggests that your table is not properly
normalized!

Consider instead a Many to Many relationship: if each expense can
belong to five (or three, or eleven) categories, and each category can
pertain to multiple expenses, a better design would be to have an
ExpenseCategory table related one-to-many to your Expenses table.
You'ld use a Subform to enter a new *row* into this table for each
category. The Primary Key of ExpenseCategories would consist of two
fields - the ExpenseID and the Category; this will prevent selecting
the same category twice for a given expense.

If you have a VERY GOOD reason to use the non-normalized design, or if
I've misunderstood your table structure, please post back. What you
ask can be done - it's just that it's probably not a good idea!

John W. Vinson[MVP]
 
Thanks for the reply John,

My form is being used to split one record into a max of 5 records, changing
the Category and Dollar Amt. The record that is split, is still in the table,
but not visible on the form, but the new records are.

For example:

I go to the store and purchase some food and motor oil. These are 2
different expense categories, but are paid for on one transaction. When I
import the data from my bank, the transaction is on one line. Using this
form I can split it into 2 lines, with 2 differen categories, and the dollar
amt divided accordingly.

I just want to make sure that the user does not apply the split dollar amt
to the same category by mistake.


e.g.

From Bank
Field: Location Date Dollar Amt Category
Data: StoreX 15-01-05 $25.00 (assigned on form)

Split Record:
Field: Location Date Dollar Amt Category
Data: StoreX 15-01-05 $10.00 Food
Data: StoreX 15-01-05 $15.00 Car Maint

Thanks again,

Brad
 
My form is being used to split one record into a max of 5 records, changing
the Category and Dollar Amt. The record that is split, is still in the table,
but not visible on the form, but the new records are.
Click to expand...

Visible... where? In a second "many" side table? How are you doing the
splitting - in code?

If you have five (unbound, I presume) combo boxes being used to
generate the five categories, and they're named cboCat1, cboCat2, ...
cboCat5 then you could have code like this (adapted to each combo of
course) in each one's BeforeUpdate event:

Private Sub cboCat2_AfterUpdate(Cancel as Integer)
If Me!cboCat1 = Me!cboCat2 _
OR Me!cboCat3 = Me!cboCat2 _
OR Me!cboCat4 = Me!cboCat2 _
OR Me!cboCat5 = Me!cboCat2 Then
Cancel = True
Beep (or MsgBox "Please pick an unused category")
Me!cboCat2.Undo
End If
End Sub


John W. Vinson[MVP]
 
I hadn't thought of doing that.

Thanks so much for the help.

Yes the comboboxes are unbound.

The form that splits the records is different than the form that displays
all the records.

The record that gets split also holds the ID of the records that it was
split into. The form only displays records that have null values in the
fields that hold the ID of the records it was split into. Hope that makes
sense :)

Thanks again,

Brad
 
Back
Top