Dropdown Question

  • Thread starter Thread starter PaulHilgeman
  • Start date Start date
P

PaulHilgeman

I would like to create a dropdown in a form that will also affect
another textbox.

My form calls up the "Description" Field from my "Jobs" Table. I
already have it set up where this description is selectable from
another table that contains all job descriptions (take-up, haul away,
carpet install etc). In the "Jobs" table, I have another field for
"Rate" per yard for each of the items.

Now, in my form, I want to be able to type in the number of yards, then
select from the drop-down list and have it automaitcaly fill in the
rate (from the table referenced for descriptions), and also calculate
the "Extended Rate" (Rate x Yards). Extended rate is a field in the
"Jobs" Table.

I would think that this is a fairly common operation to perform, and I
am probably overlooking somthing simple.

Any help would be apreciated.

Thanks,
Paul
 
Yes, it's a fairly common need, Paul.

Firstly, you will *not* have a field for the Extended Rate in your table. If
you do, you run the risk of storing a value that is not the same as the Rate
x Yards, which means your data is invalid. Instead, create a query, and type
this expression into a fresh column in the Field row:
ExtendedRate: [Rate] * [Yards]
Now use that query as the source for your form or report. It can never be
wrong.

The Rate is a different story. If you don't store that Rate with each job,
then one day when you have a price increase, all the calculations for the
old jobs would be wrong. In this case you must store the Rate with each job,
so what you are asking to do (to lookup and copy the rate) is perfectly
correct.

For an example of how to lookup the rate, open the Northwind sample
database, and the Orders Subform. The After Update event procedure of the
ProductID combo performs the lookup, and assigns the current default price
for the product to the UnitPrice field.

This form also illustrates how to use the query (named Order Details
Extended) to get the calculated extended rate.

For a summary explanation of when and how to use the 2 techniques, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html


BTW, if you want to avoid rounding errors, and insist that Access treats the
result of the calculation as currency, and handle nulls, the calculated
field would be:
ExtendedRate: CCur(Nz(Round([Rate] * [Yards],2),0))
 
Here is a little update.

I have been doing some more work on this.

In the dropdown box, for the afterupdate, I have coded this:
***********************************************************************
Private Sub ITEM_1_DESCRIPTION_AfterUpdate()
Table1.ITEM_1_RATE.Value = Table1.ITEM_1_DESCRIPTION.Column(3)
End Sub
***********************************************************************
Where:
Table1 is the form that I am working in
ITEM_1_RATE is the text box / table value that I want to update
ITEM_1_DESCRIPTION is the drop-down.

The drop down is coded as such:

SELECT [LINE ITEMS].ID, [LINE ITEMS].DESCRIPTION FROM [LINE ITEMS];

Where:
LINE ITEMS is the table that holds the line item descriptions and
prices.
DESCRIPTION is the 2nd column, containing the descriptions of the line
items.

There is a 3rd column called "PRICE" that holds the price of each line
item.

Any help would be greatly apreciated.

-Paul
 
Right now my code looks like this:

Private Sub ITEM_1_DESCRIPTION_AfterUpdate()

ITEM_1_RATE = DLookup("RATE", "LINE ITEMS", "ITEM_1_DESCRIPTION = "
& [ITEM_1_DESCRIPTION])

End Sub

Where:

ITEM_1_RATE is the name of the text box that I want to fill in on the
form.
RATE is the field name in the table "LINE ITEMS" that I want to fill
the above text box with.
ITEM_1_DESCRIPTION is the name of the drop-down box that is driving the
event.

I need to do this so that it updates the text box value, which is in
turn saved in a separate table.

Because of this I dont think that I can do it in the ControlSource
field of a text box, as that simply provides a visual display.

-Paul
 
Paul, I'm quite confused here. Your orignal post talked about a Jobs table,
with fields:
Description
Rate
Now the table seems to be named [LINE ITEMS].
Presumably it has other fields, including a primary key.

I take it ITEM_1_DESCRIPTION is a combo box.
You say it is coded as:
SELECT [LINE ITEMS].ID, [LINE ITEMS].DESCRIPTION FROM [LINE ITEMS];
Presumably you mean that this is what is in the combo's RowSource property.
Then you are referring to Column(3) of the combo.
But the combo's RowSource only has 2 fields, and the Column() property is
zero-based, so value choices would be Column(0) or Column(1).

This form (which seems to be named Table1???) must be bound to a table other
than the Jobs/[LINE ITEMS] lookup table. This table should have a foreign
key field where you store a value matching the primary key of the lookup
table. The combo's Bound Column should match the field from its RowSource
that is the foreign key field.

If the foreign key is a Number field, then the kind of statement you have
below is on the right track. If the foreign key is a Text type field (not a
Number type field), you need extra quotes. Without trying to sort out your
table and field names, the idea would be:
Me.[ITEM_1_RATE] = DLookup("RATE", "LINE ITEMS", _
"[ITEM_1_DESCRIPTION] = """ & Me.[ITEM_1_DESCRIPTION] & """")
 
Thanks a lot for the help!!!

Let me try and clarify.

Two tables - Jobs and Line Items, Named "Table1" and "Line Items"

Jobs Table has many many fields including "ITEM 1 DESCRIPTION" AND
"ITEM 1 RATE"

"LINE ITEMS" Table contains "ID", "DESCRIPTION", and "RATE"

For the Combo Box I did:

"ITEM 1 DESCRIPTION" RowSource = SELECT [LINE ITEMS].ID, [LINE
ITEMS].DESCRIPTION FROM [LINE ITEMS];

I tried your code for the AfterUpdate Procedure of the ComboBox, and it
seems to always call the first rate in the "LINE ITEMS" "RATE" field.

I also tried a few variations.

They Key of the "LINE ITEMS" Table is an AutoNumber field.

Thanks a ton. You have really helped a new guy like me out. I have
been using access for about 8 hours now, luckily I have lots of coding
experience to make it much easier.
 
Not sure I can follow, Paul.

I don't see a reason to be programmatically setting the RowSource.

If table [LINE ITEMS] has an AutoNumber key field, then your related table
should have a Number field to relate to that.

Where you have repeating field names such as [ITEM 1 RATE], you probably
need a related design.

I suggest that you open the Northwind sample database, and study the
relationships diagram (Relationships on Tools menu) to see how the tables
fit together, particularly Products, Orders and OrderDetails. Once you
understand that, see how the Orders form and its subform work.

That's probably as far as I can take you with this one.
 
Back
Top