replace values simultaneously

[Emir via AccessMonster.com]

Table 1: Invoice Main File
Field 1: Item Code
Field 2: Item Description

Table 2: Invoice Quantity File
Field 1: Item Code
Field 2: Item Description

In Table 1, Item Code Lookup was set to Combo Box where the Row Source is
coming from Table 2, Item Code

When using Table 1 and clicking the Item Code, the combo box is working
properly and replacing the value of Table 1 Item Code with the value from
Table 2 Item Code.

Now, what will I do so that as soon as the Table 1 Item Code lost its focus,
the Item Description with will be replaced by the Item Description from Table
2 Item Description?

Please help.

 

[Beetle]

Several issues here.

First, from your post it would appear that you are working directly in
the tables. Tables are for the purpose of storing raw data only. Any
data entry should be done through the use of forms.

Second, you are using lookup fields in your tables, which is a practice
that is generally recommended against. For more on that discussion
see this link;

http://www.mvps.org/access/lookupfields.htm

Third, you appear to be redundantly storing the same data in multiple
tables. In a relational database like Access, attributes are stored in
one place - and one place only. There are a few exceptions. For example,
you may need to store an item's price in an invoice table, so that you know
what the price of the item was at the time of the sale, but in most cases
you don't redundantly store data like this. The item description should
only be stored in the Items table (whatever that table is called). The only
value that would be stored in any related tables would be the Primary
Key of the item (which in your case appears to be ItemCode) as a
Foreign Key.

 

[Emir via AccessMonster.com]

I am actually not working directly with the table. I am using a form to enter
data. Of course, you wouldn't like to type the item descriptions everytime
you prepare an invoice, for example. And for purpose of simplicity of
illustration, I did mention only those fields that are related to each other.
Thank you for the link, but do you have any codes that would run this thing?

Several issues here.

First, from your post it would appear that you are working directly in
the tables. Tables are for the purpose of storing raw data only. Any
data entry should be done through the use of forms.

Second, you are using lookup fields in your tables, which is a practice
that is generally recommended against. For more on that discussion
see this link;

http://www.mvps.org/access/lookupfields.htm

Third, you appear to be redundantly storing the same data in multiple
tables. In a relational database like Access, attributes are stored in
one place - and one place only. There are a few exceptions. For example,
you may need to store an item's price in an invoice table, so that you know
what the price of the item was at the time of the sale, but in most cases
you don't redundantly store data like this. The item description should
only be stored in the Items table (whatever that table is called). The only
value that would be stored in any related tables would be the Primary
Key of the item (which in your case appears to be ItemCode) as a
Foreign Key.

Table 1: Invoice Main File
Field 1: Item Code

[quoted text clipped - 16 lines]

Please help.

 

[Beetle]

You're right, I would never trouble myself (or a user) with having
to manually enter the description for every line item of an invoice.
However, i would not attempt to solve that issue by using code
(or any other method) to store the item description in multiple tables.

I would have a table of items like;

tblItems
******
ItemCode (Primary Key)
ItemDescription
any other attributes of each item

Then, on my Invoice form I would use a combo box to allow the users
to select the proper item for each line on the invoice. This combo box
could be set up one of two ways, depending on how the users prefer
to enter data;

1) If the users prefer to go by the ItemCode, then the combo box
would display the ItemCode but would also contain a column
for the ItemDescription. I would then have an unbound text box
next to the combo box with a Control Source of;

=[cboItemCombo].Column(1)

which would *display* (not store) the correct ItemDescription
based on which ItemCode was selected in the combo box.

2) If the users prefer to go by the ItemDescription, then the combo
box would contain the same two columns as above (ItemCode
and ItemDescription), but in this case the first column would
be hidden and the combo box would display the ItemDescription.

In both cases, the only value that would be stored in the Invoice table
would be the ItemCode. The ItemDescription is merely displayed based
on the ItemCode.

--
_________

Sean Bailey

I am actually not working directly with the table. I am using a form to enter
data. Of course, you wouldn't like to type the item descriptions everytime
you prepare an invoice, for example. And for purpose of simplicity of
illustration, I did mention only those fields that are related to each other.
Thank you for the link, but do you have any codes that would run this thing?

Several issues here.

First, from your post it would appear that you are working directly in
the tables. Tables are for the purpose of storing raw data only. Any
data entry should be done through the use of forms.

Second, you are using lookup fields in your tables, which is a practice
that is generally recommended against. For more on that discussion
see this link;

http://www.mvps.org/access/lookupfields.htm

Third, you appear to be redundantly storing the same data in multiple
tables. In a relational database like Access, attributes are stored in
one place - and one place only. There are a few exceptions. For example,
you may need to store an item's price in an invoice table, so that you know
what the price of the item was at the time of the sale, but in most cases
you don't redundantly store data like this. The item description should
only be stored in the Items table (whatever that table is called). The only
value that would be stored in any related tables would be the Primary
Key of the item (which in your case appears to be ItemCode) as a
Foreign Key.

Table 1: Invoice Main File
Field 1: Item Code

[quoted text clipped - 16 lines]

Please help.

 

[Emir via AccessMonster.com]

I tried to use the first one but all the item descriptions were filled with
only one description.

You're right, I would never trouble myself (or a user) with having
to manually enter the description for every line item of an invoice.
However, i would not attempt to solve that issue by using code
(or any other method) to store the item description in multiple tables.

I would have a table of items like;

tblItems
******
ItemCode (Primary Key)
ItemDescription
any other attributes of each item

Then, on my Invoice form I would use a combo box to allow the users
to select the proper item for each line on the invoice. This combo box
could be set up one of two ways, depending on how the users prefer
to enter data;

1) If the users prefer to go by the ItemCode, then the combo box
would display the ItemCode but would also contain a column
for the ItemDescription. I would then have an unbound text box
next to the combo box with a Control Source of;

=[cboItemCombo].Column(1)

which would *display* (not store) the correct ItemDescription
based on which ItemCode was selected in the combo box.

2) If the users prefer to go by the ItemDescription, then the combo
box would contain the same two columns as above (ItemCode
and ItemDescription), but in this case the first column would
be hidden and the combo box would display the ItemDescription.

In both cases, the only value that would be stored in the Invoice table
would be the ItemCode. The ItemDescription is merely displayed based
on the ItemCode.

I am actually not working directly with the table. I am using a form to enter
data. Of course, you wouldn't like to type the item descriptions everytime

[quoted text clipped - 29 lines]