Subform Setup Issues (Possibly Code Related Options)

[Scyclone]

Hello Everyone,

I am having a bit of an issue trying to get an order form to function properly with a subform.

I have several tables designed around various things, that will be utilized in an order.

ProdList (Contains ID, ProductName, ProductPartNo, ProdDesc, BrandID, CategoryID)
ProdBrand (Contains ID, BrandName)
ProdCat (Contains ID, CatName)
OrderHeader (Contains ID, CustID, DateMade, StatusID)
OrderDetail (Contains ID, OrderID, ProdId, Qty, UMID)
UnitsMeasure (Contains ID, UnitMeasure)

What I am trying to create is an order form for multiple rows

The user loads the customer information above and then enters in the items they wish to order in the paired subform

[Qty of Item] [UnitMeasure *Dropdown*] [BrandName *Dropdown*] [CatName *Dropdown based on BrandName Selected] [ItemName *Dropdown based on CatName Selected*] [ProdPartNumber *Based on ItemName*] [ProdDesc *Based on ItemName*]


=====
Extra information on the Fields

Qty is a text field, which will save on the Order Detail table.

Units of Measurement is a dropdown field (populated from the UM table) now this field will need to be saved on the Order Detail table but as the ID not the displayed name. Example - UM table has Case, Each, and lets say Gallon. If a user enters in a new item for the order and selects Case, I need to take and store the value as 1 on the Order Detail table (2 for each, 3 for Gallon).

BrandName is a visual dropdown listing off the Brand Names from the ProdBrand table. It has no value to be stored on the Order Detail table but will need to be used for populating dropdowns.

CatName is a visual dropdown listing off the Category Names from the ProdCat table that match the BrandName previously selected. For example, Brand A contains Cat 1A, 2A, 3A. While Brand B contains Cat 1B, 2B, 3B. If I select Brand A in the previous dropdown, I want to only see Cat 1A, 2A, 3A for this dropdown (Outside of a subform I have already gotten this to work via VB-script)

ItemName is a visual and stored dropdown listing off the various items in the Category. The displayed value is the name of the item, however, it will need to store the ID of the item on the Order Detail entry. For example, Item A has an ID of 6 and belongs to Category 1A. So if the user previously selected Brand A and Category 1A, they should be able to select Item A off a dropdown list. When saving the order, it needs to enter the ID of 6 into the Order Detail table.

Product Description is a non-editable text field that displays the Description of the product as provided by the selected Item A. For example: Once the user selects Item A, I want to display Item A's description (held on the same row as Item A in ProdList) on the following field. This value doesn't need to be saved anywhere.

=====

Basically what I am trying to do is create an area to enter in line items for an order.

(Example, Order #1 has 5 different items purchased, I want to save on OrderHeader, the order itself, then on OrderDetail save all 5 lines of items associated to Order #1, the order number.)

Any help/programming would be greatly appreciated, this has been a long standing issue for me, and I need to get it resolved. I have tried numerous things to no avail whatsoever.

Thanks again,
Steven

[alansidman]

This may help you

http://www.datapigtechnologies.com/f...subforms1.html

http://www.datapigtechnologies.com/f.../subform2.html

Alan

[Scyclone]

I have seen them before. I just wish I could see a video of Access 2007 and also one that involves data entry utilizing data/ids of multiple tables. The videos do no good for me unfortunately

[Scyclone]

No updates at all?

[alansidman]

Suggest you go to You Tube and search on MS Access Sub Forms and AC 2007. There is a lot of information and tutorials out there.

BTW: AC2007 and earlier versions work virtually the same. It is only the Menus and Ribbons that make it appear different. There are other differences but the building of forms and subforms require the same processes.

Alan

[hertfordkc]

has examples which will help you.

[Scyclone]

At the moment, I seem to be having three issues going on.

The first is bizarre. On the subform (by itself), I can go to datasheet/open view and select Brand from the dropdown. When selected, it instantly populates the Category dropdown.

However, on the Create Order form (which includes the subform), doing the same thing throws the pop up window as you see below.



My second issue stems around the 'multiple' row issue. If for example, I enter 1 into the QTY of the first row of the subform (happens both by itself or inside the Create Order form), it will populate 1 across all the rows. Honestly, I am confused how these rows even exist considering in OrderDetail table, I only have 1 entry to begin with.

My third issue is related to having the right data and doing the right thing with it. I am not sure how to make sure that what I enter into this order form will successfully save as entries into OrderDetail. Please refer to the posts above to see whats put into what, but at the moment it doesn't save or go anywhere (Probably a good thing with the data being odd)

Any thoughts?

-Steven

[Scyclone]

Hmm...

Still working on trying to get this resolved, have been for a month or so. I can send an access file now (Ive gotten most of the confidential stuff removed). I need help to get this working so that I do not have to delay this project any further, it is really beginning to impact things here.