RE: How is the ordes details retrieving the Unit Prices?

Ken:

I will try not to be as long winded, but I noticed a distinct difference
between my query in which my "Order Details" Subform is based and the
Northwind's query for which its subform is based.

The query for the Northwinds uses "UnitPrice" of the "Order Detials" table
and not from the "Products" Table in their query;
where as, my query uses the "Product Price" from my Product Services Table
and not my Order Details table.

I hope that makes sense. If this is true, then that would maybe explain the
error because it was replaing information it was already programmed to find.

I think I had set the query up like that before for my subform, but could
not get the "Product Price" to show in that query and get the subtotal in the
footer to work.

What are your thoughts?


"Ken Sheridan" wrote:

I'm not sure why you are getting that error. Ideally you need to debug the
code using the built in debugging tools, but lets first review how the
Product ID and product price controls are set up on the subform:

1. The Product ID field's control is your combo box from which you select a
Product Name. The way this works is that you see the names from the table of
products, but the value of the control is the Product ID for that name. This
achieved by hiding the first column of the list by setting properties of the
combo box as follows, so review these to see that its set up correctly:

ControlSource: [Product ID] (the underlying field from the Product
Details table)

RowSource: Either an SQL statement along the following lines or the
name of a saved query whose SQL is similar:

SELECT [Product ID], [Product Name]
FROM [Product Services]
ORDER By [Product Name];

You'll see that this query returns two columns the Product ID and Product
Name, and is ordered by the latter:

BoundColumn: 1 (this makes the first column, Product ID, the value of
the control)

ColumnCount: 2 (the number of columns returned by the RowSource query)

ColumnWidths: 0cm;8cm (or rough equivalent in inches. The important
thing is that the first dimension is zero; this hides the Product ID column
so you see just the names.


2. The Product Price control is a text box with [Product Price], the field
in the underlying Product Details table, as its ControlSource property.

The code in the Products combo box's AfterUpdate event procedure doesn't
'paste' the product price value from the Product Details table into the field
in the Order Details table, it 'assigns' the value to it with this line:

Me![Product Price] = DLookup("[Product Price]", "[ Product Services]",
strFilter)

You should see the value appear in the Product Price text box when a
selection is made from the combo box's list. At this stage the subform is
'Dirty', i.e. the record isn't yet saved to the Product Details table. That
is done when you leave the subform, move to another record in it or expressly
save the record using the menu, toolbar or keyboard short cut.

Check that the two controls are set up as above. If they are there should
be no problem, so some debugging will be needed to pin down the reason for
the error. If necessary I'll try and walk you through how to do that but its
really something you'd have to tackle yourself as there is a limit to how
much I can do remotely without having access to your file.

Ken Sheridan
Stafford, England

"EZ KEY" wrote:

Ken:

First, thanks for the book info. I printed it out and will certainly look
some stuff up.

Well, I did what you suggested exactly as posted and I got an error similar
to this:
"You canceled the previous operation",
everytime I select something in my subforms drop down selection under
Product Name.

I think I did it correctly, I think it is just interfering with something
else already happening.

Now, this may be a stupid question/comment. However, in studying the VBA
commands I put in. I don't see how those commands would know to copy my
Product Price from my Subform "Order Details", and paste that value in the
Order Details table under the "Product Price" field there?

This is the last detail I need to get to function and my database is done.
So, I'm sure you may understand why I am so eager to understand and get this
done.

Thanks again. I look forward to your reply. Thanks for the book info too.




.

Relevant Pages

  • RE: How is the ordes details retrieving the Unit Prices?
    ... UnitPrice in that row to the subform "UnitPrice" text box. ... UnitPrice field in the Order Details table that the parent form containing ... issue is How do I get the product price in my subform to show up in my Order ... The code then assigns this to the Product Price control ...
    (microsoft.public.access.gettingstarted)
  • RE: How is the ordes details retrieving the Unit Prices?
    ... By 'Product Details' I actually meant Order Details, ... If the RecordSource of the subform is a query then ... The code then assigns this to the Product Price control ...
    (microsoft.public.access.gettingstarted)
  • RE: How is the ordes details retrieving the Unit Prices?
    ... but I have a hunch that the query might in itself be ... select a Product Name in the Order Details subform, ... Control Source: Product ID ... Control Source: Product Price ...
    (microsoft.public.access.gettingstarted)
  • RE: Summing Fields
    ... query for the order details that match the order shown on the main form. ... shown on the subform. ... "Bob Love" wrote: ... I use a query to find all details that match the order # in the main form. ...
    (microsoft.public.access.gettingstarted)
  • RE: How is the ordes details retrieving the Unit Prices?
    ... so I could try out my theory on the Query differences I ... Product ID and product price controls are set up on the subform: ... but the value of the control is the Product ID for that name. ... The Product Price control is a text box with, ...
    (microsoft.public.access.gettingstarted)
Loading