Form & Table Design help

  • Thread starter Thread starter dindigul
  • Start date Start date
D

dindigul

I need to create form for invoice. The customer details appear nicely.
However I am not able to understand how to design tables & forms so that I
can get it as:
ItemCode ItemName ItemRate Quantity Total

As is obvious the first three form a table. I know combobox can display one
and put another value, but here all three are from a single table. I need to
display ItemName so that ItemCode & ItemRate come on their own. I was
advised by MVPs not to use lookup in tables so that is also a point I want
to adhere to. Please help
 
You need a separate ItemInventory table to hold data about your items
(ItemCode, ItemName, ItemRate). Then when you enter an item on your detail
subform, you can choose the ItemCode from a dropdown, whose recordsouce is
ItemInventory.

When you are entering a new Item,you'd choose the ItemCode form a dropdown
on the detail form, and the After_Update event of the dropdown would
automatically populate ItemName and ItemRate in your detail table. YOu would
then enter the Quantity manually. The ItemName and ItemRate would be
read-only fields in your details subform. It's important to store these
values in your detail table since at some point you might change the ItemName
or ItemRate for an item, and you would want your detail table to store the
ItemName and ItemRate at the time the invoice was created.

I would not advise using a Total field in the detail table. This is
unnecessary since it can always be calculated, and you have to remember to
update it every time the ItemID or Quantity updates. A better idea is to have
a calculated field Total = Qty * ItemRate in your query as a read-only field.
 
I need to create form for invoice. The customer details appear nicely.
However I am not able to understand how to design tables & forms so that I
can get it as:
ItemCode ItemName ItemRate Quantity Total

As is obvious the first three form a table. I know combobox can display one
and put another value, but here all three are from a single table. I need to
display ItemName so that ItemCode & ItemRate come on their own. I was
advised by MVPs not to use lookup in tables so that is also a point I want
to adhere to. Please help
Click to expand...

Change ItemCode to Combo. Edit Row Source for combo and create a query
with following fields, use your tables there (I assume there are 2
tables):

ID, ItemCode, ItemName, ItemRate

Set
Column Count: 4
Column Widths: 0; 0; 1; 0

Leave ItemName and ItemRate as text boxes and set Control Source to

=ComboName.Column(1)

and

=ComboName.Column(3)

Set Enable = False or Locked = True to prevent user to change values.

Regards,
Branislav Mihaljev
 
Back
Top