How would i link fields within a Access table?

[AccessBeginner]

I have a table with four columns. When i added these fields to may form i
would like the one column as a drop down box. When i select from that drop
down box all the other columns related to the one field will automatically
show . Sample i select a part number the other columns relate to the number
would be description, cost and list price whick live in the other columns.
Is there a way to do within Access 2003?

 

[Jeff Boyce]

It all starts with the data. I may not be clearly understanding your data
structure, but it sounds like you have repeating information from one record
to the next.

Please post a description of your data structure. It's hard to offer
specific "how to" suggestions without an idea of the "what"...\

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have a table with four columns. When i added these fields to may form i
would like the one column as a drop down box. When i select from that drop
down box all the other columns related to the one field will automatically
show . Sample i select a part number the other columns relate to the number
would be description, cost and list price whick live in the other columns.
Is there a way to do within Access 2003?

The description - at least - should exist in the source table, and ONLY in the
source table. You do not want or need to copy the description and store it
redundantly in another table! Instead, you can simply put a textbox on the
form with a control source like

=comboboxname.Column(n)

where n is the zero based index of the field you want to show - i.e. if the
description is the third column in the combo's row source, use (2).

There is, however, good reason to copy a price, since you want to store the
price *as of the date of the order* in the other table. To do so you would use
a Form (never use table datasheets for data entry!) with a combo box, and use
the AfterUpdate event of the combo to "push" the current price into a bound
textbox:

Private Sub comboboxname_AfterUpdate()
Me!txtPrice = Me!comboboxname.Column(3) ' again zero based, fourth field
<etc>
End Sub

 

[AccessBeginner]

The basic outline is with in a table i want to put part number, description,
cost and list price. When i attach these fields to a form, Which is going
to be a invoice form, The user will select from a list box the part number.
When they do this i want to also display the description, cost and list price
of that part number. This way when they print a invoice the information is
automatically list. I hope this make sense. I do not know the access
language very well.

 

[Jeff Boyce]

Be aware that "forms" are for screen display and "reports" are for printed
display. While you can print a form, what works well on-screen isn't
necessarily what works well on paper.

You can use a listbox to list all possible parts, but a combobox might be a
more efficient use of your screen (i.e., form).

You can add unbound controls to the form (i.e., not connected to underlying
fields) and add something like the following in an event procedure for the
combobox's AfterUpdate event (NOTE: the combobox's source needs to have all
the fields you plan to display):

Me!txtDescription = Me!cboPart.Column(1)
Me!txtCost = Me!cboPart.Column(2)
...

Note that the .Column() property is zero-based ... it starts counting 0, 1,
2, ... so .Column(1) is actually the second column.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP