DLookUp for multiple forms

[ijw]

Hi,

Below I have described five tables in my database. The end result that
I'm trying to achieve is When I type in a Product in the Order Details
table, I would like a form that not only dlookup's the price, but can
also generate multiple lines on the Order Components table.


Components (tbl)
ComponentID ComponentName
(prim) (ind No dupe)
1 Comp1
2 Comp2
3 Comp3
4 Comp4


Product Break Down (tbl)
ProductNameID ProductBreakDownName ComponentID Colour
(prim) (ind dupe OK) (ind dupe OK) (default colour
.. for component)
1 Prod1 Comp2 GS
2 Prod1 Comp3 G
3 Prod2 Comp1 G
4 Prod2 Comp2 GS
5 Prod2 Comp4 G

Products (tbl)
ProductID ProductBreakDownName UnitPrice
(prim) (ind No dupe)
1 Prod1 $15.00
2 Prod2 $20.00


Order Details (tbl)
OrderDetailsID ProductID UnitPrice
(prim) (ind dupe OK) (DLookUp Products tbl)
1 Prod1 $15.00
2 Prod1 $15.00
3 Prod2 $20.00


Order Components (tbl)
OrderCompID OrderDetailsID ProductID ComponentID Color
(prim) (ind dupe OK) (from Order (DLookUp (DLookUp
.. Details Product Break Product Break
.. tbl) Down tbl) Down tbl)
1 1 Prod1 Comp2 GS
2 1 Prod1 Comp3 G
3 2 Prod1 Comp2 GS
4 2 Prod1 Comp3 G
5 3 Prod2 Comp1 G
6 3 Prod2 Comp2 GS
7 3 Prod2 Comp4 G


The DLookup for the Component ID and Color fields on the Order
Components table will display default components and colors for each
for each product, but these may need to be able to be changed.

When a ProductID is entered in the Order Details table, I need to be
able to generate multiple lines in the Order Components table.

I don't know how to start this next step, and would really appreciate
any advice.

Thanks

 

[Jeff Boyce]

Given your description, I'll guess that you are experienced ... in
spreadsheets. If I recall correctly, DLookup is an Excel function.

If you are working with orders, may I suggest that you look at the Northwind
sample database that comes with Access? This may have everything you need.
Note that I did not say "everything you want". From what you posted, it
sounds like you want to "commit spreadsheet" on Access.

This will only frustrate both you and Access, as it is NOT a spreadsheet.
You'll need to think about data and processes differently to make effective
use of this relational database.

 

[ijw]

Hi again,

Thanks Jeff for your advice. I refer to the Northwind database all the
time; it's more helpful than any textbook.

The reason I have asked about DLookup is because the Unit Price is
displayed after a product is posted on the Order Details table. Maybe
it's not a DLookup I need! I've read several messages on the google
groups and some have suggested recordsets???? Maybe I need to use a
union select query????

Any suggestions???

Thanks

 

[Jeff Boyce]

To go back to your original post, then...

When you select (use a combobox, not typing in a product in a textbox) a
Product, you'd like information about that product to auto-fill something
(e.g., the price). If your cboProduct includes a column of Price, then your
cboProduct AfterUpdate event code could include something like:
Me!txtPrice = Me!cboProduct.Column(n)
where "n" is the n-1 column location for Price in the query's row source
(Column() uses zero-based counting).

But why do you want to create multiple lines? On a table? You need to be
working in forms, and a classic way to handle a 1:M relationship is with
subforms. As in Northwind, an Order is the main form, and the OrderItems
are on the subform. None of those OrderItems are pre-created (how would you
or Access know how many to do?). But by having the subform control linked
Parent-Child to the main form, any entry made on the subform "inherits" the
connecting value(s) from the main form.

What is it that you are doing with OrderComponents that is different from
what Northwind is doing with OrderItems?

 

[ijw]

Hi,

I have a huge database, which uses the customer orders information for
supplier purchase orders. I need to be able to change these details on
the Product Break Down subform.

The database is designed to be as user friendly as possible, for my
mother. I have been working on it for months.

I have an Order by Customer form with Order by Customer Subform, for
the Orders table. The Order details can be changed by clicking on an
Orders button, which opens up the Orders form with Order Details
Subform, for the Order Details table. The Product details can then be
changed by clicking on a Products button, which then opens up the
Products form with Product Break Down Subform, for the ProductBreakDown
table. The Product Break Down Subform has the Component Name from
Components table and Colour from the Product Break Down table.

Could I maybe indicate a number on the Products table for the number of
Component lines are required for each Product?
Products (tbl)
ProductID ProductBreakDownName UnitPrice # of Components
1 Prod1 $15.00 2
2 Prod2 $20.00 3
Would access be able to generate 2 lines on the Product Break Down
subform which is stored on the Order Components table when Prod1 is
typed in the Product field on Order Details Subform.

Thanks again