subform fills parent w/dbl clk (2ndTry)

[Guest]

I have a subform (continous)
"OrdersList" based on "productsTTL" table.
Form OrdersTEST is based on a query

SELECT Orders.*, [Order Details].ProductID, [Order Details].Quantity, [Order
Details].UnitPrice, CCur([Order Details].UnitPrice*[Order
Details].Quantity*(1-[Order Details].Discount)/100)*100 AS ExtendedPrice, *
FROM (Orders INNER JOIN Categories ON Orders.CategoryID =
Categories.CategoryID) INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID;

Is it possible to dblclick /or cmd button on the ProductName field in the
subform & have it populate some fields in the parent form ie. ProductName on
the sub would fill parent "OrdersTEST" cboProductID which has the following
Record source:

SELECT DISTINCTROW [ProductID], [ProductName] FROM ProductsTTL ORDER BY
[ProductName];

I tried using a cmd button
stDocName = "OrdersTEST2"

stLinkCriteria = "[ProductID]=" & Me![ProductName] & """"
DoCmd.OpenForm stDocName, , , stLinkCriteria

But I get an error ...syntax error missing operator...


The combobox on the parent OrdersTEST form doesn't do what I need because it
doesn't filter by category and it is better if the person can see what they
need to choose from a LIST. So the subform becomes the list which is bound by
categoryID to the parent so it is only listing those things in that category.

In the dbl click event what would be the VBA to accomplish this?
And does the parent need code as well?

ALSO:
Could I leave it so they could use either option? Click on the subform or
use the drop down.

Thanks!

 

[John Vinson]

I have a subform (continous)
"OrdersList" based on "productsTTL" table.
Form OrdersTEST is based on a query

SELECT Orders.*, [Order Details].ProductID, [Order Details].Quantity, [Order
Details].UnitPrice, CCur([Order Details].UnitPrice*[Order
Details].Quantity*(1-[Order Details].Discount)/100)*100 AS ExtendedPrice, *
FROM (Orders INNER JOIN Categories ON Orders.CategoryID =
Categories.CategoryID) INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID;

Is it possible to dblclick /or cmd button on the ProductName field in the
subform & have it populate some fields in the parent form ie. ProductName on
the sub would fill parent "OrdersTEST" cboProductID which has the following

Ummm... this is confusing in at least a couple of respects. Typically
a Subform will have many records related to a single mainform record
(i.e. an Order can consist of an arbitrary number of Products). Are
you trying to "push" one Product into the parent form? Which one? And
more importantly, WHY?

Secondly, it sounds like you're trying to store a ProductID in a
ProductName field. This will NOT work, if your table structure is
typical - the ID is ordinarily a number, the product name is text.
You'll get a type conversion error.

Finally - basing your mainform on a Query joining the order and
orderdetails table, and selecting all fields from both tables, is VERY
unusual and seems likely to be wrong.

Could you explain what you're trying to ACCOMPLISH here? What (in a
real-life, business sense) do you want this form to do, that a much
more standard one to many subform with Orders in the mainform and
OrderDetails in the subform, won't do?


John W. Vinson[MVP]

 

[Guest]

Thanks for the response...In line answer...

I have a subform (continous)
"OrdersList" based on "productsTTL" table.
Form OrdersTEST is based on a query

SELECT Orders.*, [Order Details].ProductID, [Order Details].Quantity, [Order
Details].UnitPrice, CCur([Order Details].UnitPrice*[Order
Details].Quantity*(1-[Order Details].Discount)/100)*100 AS ExtendedPrice, *
FROM (Orders INNER JOIN Categories ON Orders.CategoryID =
Categories.CategoryID) INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID;

Is it possible to dblclick /or cmd button on the ProductName field in the
subform & have it populate some fields in the parent form ie. ProductName on
the sub would fill parent "OrdersTEST" cboProductID which has the following

=====================================
Ummm... this is confusing in at least a couple of respects. Typically
a Subform will have many records related to a single mainform record
(i.e. an Order can consist of an arbitrary number of Products). Are
you trying to "push" one Product into the parent form? Which one? And
more importantly, WHY?

I had tried posting this with the title "Form Design Flaw update Order" but
the answers I got didn't help me understand or solve the problem...

Please note I am very new to access and am struggling to get this...

I am trying to have a "list" of products by category that could be clicked
on/ checked off OR?? that would then fill either the orderdetail or the
order form...like an add to cart on a website. When you click finish... it
adds to the order. A cbo isn't as convenient though I would like to have
both. A visual list that AND a cbo in/linked to the actual orders form
(cannot get the combo to only pull the category items for that category)...
for ease of use by end user. I have this structure already designed if you
want me to send it to you... I also want to be able to see what has already
been ordered in the same form if possible. Though that isn't imparitive.

Secondly, it sounds like you're trying to store a ProductID in a
ProductName field. This will NOT work, if your table structure is
typical - the ID is ordinarily a number, the product name is text.
You'll get a type conversion error.

Ok this could be a problem I need to fix then...
My productTTL Table has ProductID as key the because productname can be
duplicated because it could be used in different categories (ie Wire could be
used in the sound or electrical dept.) so I was trying to use productID ...
and using the criteria to view what I wanted to see which was the actual name
of the item. Maybe I have to restructure the product table needs to have 2
keys..name and cat??

Finally - basing your mainform on a Query joining the order and
orderdetails table, and selecting all fields from both tables, is VERY
unusual and seems likely to be wrong.

OK... actually I thought it was wrong initially but I couldn't get the
category/product fields to sync when I split it

Could you explain what you're trying to ACCOMPLISH here? What (in a
real-life, business sense) do you want this form to do, that a much
more standard one to many subform with Orders in the mainform and
OrderDetails in the subform, won't do?

Because this is for purchasing not accounting I find the examples in Nwind
hard to follow and apply exactly.
construction company that has categories for every area of
building a house.
25 Main Categories /166 Sub Categories
350 Suppliers
table of 2000 products =ProductsTTL

category-many products/suppliers/orders
product-many suppliers/orders
suppliers-many products/orders
order-category-purchaserID-ProjectID-
orderdetails-many products/suppliers/uom/unitprice/

Categories.CategoryID 1:n ProductsTTL.CategoryID
ProductsTTL.ProductID 1:n Order Details.ProductID
Order.OrderID 1:n Order Details.OrderID
Orders.PurchaserID 1:n tblPurchaser.PurchaserID
Orders.ProjectID 1;n Projects.ProjID

(Table)ProductsTTL
ProductID(primary key,#)
ProductName (Text) some duplicates
SupplierID (Text) Lookup to qrySupplierLookup)
SupplierCode (Text)
CategoryID (Foreign)#
UnitMultiple (Text) (SELECT tblUOM.UnitMultiple FROM tblUOM ORDER BY
tblUOM.UnitMultiple
UnitPrice (currency)
Quantity (#)
QTYNeed (#)
Order (ck box)Linked to QTYNeed
LastEdited

(TableOrders
OrderID(primary key,#)
ProjectID (Text)(SELECT Projects.ProjID, Projects.ProjectName FROM Projects
CategoryID (# Lookup to Categories table)
PurchaserID (Text)
RequestedBy (Text)(SELECT Overseer.[Last Name], Overseer.OverseersID FROM
Overseer ORDER BY Overseer.[Last Name]
SupplierName (Text)(qrySupplierLookup)
SupplierCode (Text)(SELECT ProductsTTL.ProductName, ProductsTTL.SupplierCode
FROM ProductsTTL
OrderDate (Date/time)
RequiredDate (Date/time)
Description (text)
ProductID (Text) SELECT (DISTINCTROW ProductsTTL.ProductID,
ProductsTTL.ProductName,
UOM (SELECT tblUOM.UnitMultiple FROM tblUOM ORDER BY tblUOM.UnitMultiple
Price (currency)
LastEdited (Date/time)

Table:Order Details
OrderID(primary key,#)
ProductID(primary key,#) (composite)
Quantity (#)
UnitPrice (Currency)


My biggest problem is if I need to get the info from the ProductsTTL table
to append to my orders table... when a QTY needed is entered and checked
off...

Thanks!