The tables that I use for this issue are: [Inventory], [PODetails] & [Units].
The [Inventory] table is set up with the following field names: InvID,
Product & UnitsID (which pulls its info into a combobox from the [Units]
Table.
The [PODetails] table has a combo box that allows you to select an inventory
item and assign it to that order and uses the UOM field from the [Inventory]
table. So what happens is that if they need to change this UOM value it
changes it at the [Inventory] table level and doesn't assign it to the
specific order. I know that I need to have a UOM field in my [PODetails]
table but I want it to pick up the default value assigned to that particular
item from the UOM field.
Mr. B said:
Hi, JK.
There is no problem. I just did not get all of the idea straight in my mind.
I am concerned about the fact that when you are entering data in the
PODetails form and you select a different UOM for that detail record, that
you seem to say that making that change, changes the UOM for all of that item
that have ever been ordered. The PODetails table should have one record for
each item ordered and you should be able to then change the UOM for any one
record and not affect any other records.
I think we need to examine just how your database is structured.
Can you provide the schema for you database? Just the tables and the
relationships between the tables.
Right now I am beinning to think that this may not be a problem with a
control on a form but may be a design issue with your databse.
If I am still off base with my understanding of the issue, please forgive
me, but if the database is designed correctly there should be no way that
changiing the UOM for one ordered item would ever change the value for all
items that have ever been ordered.
Please post back and let's see if we can lick this thing.
Mr. B
askdoctoraccess dot com
:
I have read your reply and then when I re-read my question I realized that I
didn't really ask it correctly (I was missing some information) so I will try
again.
I have a PODetail Table that allows me to enter the details of our orders.
On the form I currently have a box called UOM which is the UOM for that
inventory item (These UOM's are attached to each inventory item inventory).
So when we order an item such as brackets (which have a standard UOM of
"Pairs") and they order single brackets instead, they will change the UOM to
"each" which will then change the UOM for all the brackets that have ever
been ordered.
So what I am trying to do now is have a seperate combobox, that still pulls
in the UOM from the Units Table (rather then the inventory table), but I want
this combo box to use the value in the UOM field from Inventory as the
default value. This way if the order brackets it will come up as "pairs" by
default but the user can change it to "each" and it only effects the item
ordered at that time.
I hope this is clearer and may get me an answer that works great.
Thank-you in advance and I apologize with my first question.
:
JK,
The default value will not be applied to existing records. Only new records
will acquire the default value. You have a couple of choices. You can use
an update query to update the appropriate records with the default value or,
if this is not a valid approach, then you can place code in the OnCurrent
event of your form that will check the appropariate fileds for existing
values and then use a Case statement to populate the combo box with the
correct value based on the results of checking the existing values.
-----
HTH
Mr. B
askdoctoraccess dot com
:
I have a combo box for UOM which pulls in units measurements from the units
table and applies them to inventory items. The problem I am having is that
sometimes an item's UOM will change Ie: sundry items can be in lengths,
pairs, or each.
I have created a second UOMID combo box which I have told to use UOM as the
default value but the box will not pull in the value is there something I am
doing wrong? Is there a better way to work this issue out?
