If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DLookUp for multiple forms
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 |
#2
|
|||
|
|||
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. -- Good luck Jeff Boyce Access MVP wrote in message oups.com... 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 |
#3
|
|||
|
|||
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 wrote: 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. -- Good luck Jeff Boyce Access MVP wrote in message oups.com... 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 |
#4
|
|||
|
|||
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? -- Good luck Jeff Boyce Access MVP wrote in message oups.com... 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 wrote: 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. -- Good luck Jeff Boyce Access MVP wrote in message oups.com... 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 |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Menubar listing open forms? | Harmannus | Using Forms | 0 | December 23rd, 2004 12:33 AM |
Can I close all open forms? | DBS | General Discussion | 3 | October 7th, 2004 04:35 PM |
forms in Outlook 2003 | Marty Leaf | General Discussion | 1 | August 18th, 2004 06:13 PM |
datasheet forms open in form mode from the Switchboard | Paul James | Using Forms | 5 | July 13th, 2004 06:51 AM |