S
scadav
I have a problem I was hoping someone could give me some suggestions on
a solution. I am trying to create a database which budgets items.
When you create a line item in the budget, you can assign it to a
single category or split it among several categories.
Here are two examples:
Example #1:
$500 worth of supplies at Wal-Mart
All $500 is assigned to the finance group
Example #2
$500 worth of supplies at Wal-Mart:
$200 is assigned to the finance group
$200 is assigned to the marketing group
$100 is assigned to the operations group
The database is setup where you have two main tables, one table holds
the description of the transaction and the total cost [Transactions]
and another table holds the break out of how the cost is categorized
[Categories]. So for example #1 you would have a 1 to 1 relationship
for rows in the Transactions table to the Categories table, but for
example #2 you would have 1 row in the Transactions table and 3 rows in
the Categories table.
On the form I have a description of the line item and the total value.
Then I have a drop down where you can choose the category. I
originally had the category drop down box as a bound combo box, but I
ran into a problem where you needed to assign multiple categories, so I
had to create and unbound drop down box where you list all valid
categories and then one item that said MULTI. When the combo box said
MULTI a separate form would open up where you could assign multiple
categories. If you choose a single item, I would just use INSERT INTO
to update the table.
So my problem is when I go into the form, I have 3 different situations
that need happen to that unbound Category combo box.
-If it is a new entry (no previous value), the combo box should have a
pick list of items
-If the item has an entry already (previous value - single item), the
combo box should have the same pick list of items, but the value should
be populated with what the user previously chose
-If the record has an entry already with multiple value, the combo box
should have the word 'MULTI' with the same pick list of items (in case
the user wanted to change from a MULTI to a single item entry).
Have I confused you yet?
Anyhow, here is how I tried to accomplish the above, but it did not
work
Dim intCount As Integer
intCount = DCount("[PrimaryKey]", "Category", "[EntryID] =
Forms!TransactionLog.EntryID")
If intCount = 0 Then
Me.CategoryCombo.RowSourceType = "Table/Query"
Me.CategoryCombo.RowSource = "sp_GetAllCategories" 'Query that
returned all values
ElseIf intCount = 1 Then
Me.CategoryCombo.RowSourceType = "Table/Query"
Me.CategoryCombo.RowSource = "sp_GetSingleCategory" 'Query
that returned a single value
Else
Me.CategoryCombo.RowSourceType = "Value List" 'Hard coded
single word
Me.CategoryCombo.RowSource = "MULTI"
End If
The above works, with one MAJOR problem, when intCount is not equal 0
it gives you the correct value but does not give the user the ability
to choose other values. Can anyone help?
a solution. I am trying to create a database which budgets items.
When you create a line item in the budget, you can assign it to a
single category or split it among several categories.
Here are two examples:
Example #1:
$500 worth of supplies at Wal-Mart
All $500 is assigned to the finance group
Example #2
$500 worth of supplies at Wal-Mart:
$200 is assigned to the finance group
$200 is assigned to the marketing group
$100 is assigned to the operations group
The database is setup where you have two main tables, one table holds
the description of the transaction and the total cost [Transactions]
and another table holds the break out of how the cost is categorized
[Categories]. So for example #1 you would have a 1 to 1 relationship
for rows in the Transactions table to the Categories table, but for
example #2 you would have 1 row in the Transactions table and 3 rows in
the Categories table.
On the form I have a description of the line item and the total value.
Then I have a drop down where you can choose the category. I
originally had the category drop down box as a bound combo box, but I
ran into a problem where you needed to assign multiple categories, so I
had to create and unbound drop down box where you list all valid
categories and then one item that said MULTI. When the combo box said
MULTI a separate form would open up where you could assign multiple
categories. If you choose a single item, I would just use INSERT INTO
to update the table.
So my problem is when I go into the form, I have 3 different situations
that need happen to that unbound Category combo box.
-If it is a new entry (no previous value), the combo box should have a
pick list of items
-If the item has an entry already (previous value - single item), the
combo box should have the same pick list of items, but the value should
be populated with what the user previously chose
-If the record has an entry already with multiple value, the combo box
should have the word 'MULTI' with the same pick list of items (in case
the user wanted to change from a MULTI to a single item entry).
Have I confused you yet?
Anyhow, here is how I tried to accomplish the above, but it did not
work
Dim intCount As Integer
intCount = DCount("[PrimaryKey]", "Category", "[EntryID] =
Forms!TransactionLog.EntryID")
If intCount = 0 Then
Me.CategoryCombo.RowSourceType = "Table/Query"
Me.CategoryCombo.RowSource = "sp_GetAllCategories" 'Query that
returned all values
ElseIf intCount = 1 Then
Me.CategoryCombo.RowSourceType = "Table/Query"
Me.CategoryCombo.RowSource = "sp_GetSingleCategory" 'Query
that returned a single value
Else
Me.CategoryCombo.RowSourceType = "Value List" 'Hard coded
single word
Me.CategoryCombo.RowSource = "MULTI"
End If
The above works, with one MAJOR problem, when intCount is not equal 0
it gives you the correct value but does not give the user the ability
to choose other values. Can anyone help?