Results in a form with DMax function

A

AndyEduardo

Thanks for your attention.

the problem is as follow.

I have one form to make the Invoice with a subform inside to mak
Details of the Invoice (ProductID, NameProduct, UnitOfMeasure ... (lik
kg or ton or thousands of units), Price, Bruto, Tare, etc)... in th
form Invoice I have a txtbox with Bruto, Tare and Bruto-Tare

when I select the product and the other information is has been added
then txtBruto in the form Invoice shows the higher Bruto, through on
querie and DMax function, the same with txtTare in the form but wit
DMin function...I wrote code for this in Form_AfterUpdate (Details o
Invoice), and Access did it.

I need help about this..... when the UnitOfMeasure is different o
tons, like kg or thousands of units, etc ...in at least one product o
Details of Invoice, this code won't be executed and txtBruto an
txtTare turn empt
 
A

Allen Browne

Presumably you have tables something like this:

Unit table (one record for each unit of measure, e.g. ton, kg, ...)
UnitID primary key (pk)
...

Product table (one record for each product):
ProductID primary key
ProductName
DefaultUnitID the most common Unit this product is sold in.
...

ProductPrice table:
ProductID relates to Product.ProductID
UnitID relates to Unit.UnitID
PriceEach Currency current price for this unit of this product.

Invoice table:
InvoiceID p.k.
...

InvoiceDetail table:
InvoiceID relates to Invoice.InvoiceID
Quantity Number
ProductID relates to Product.ProductID
UnitID unit of measure
PriceEach Currency
...

Then in the subform bound to InvoiceDetail table, you could use the
AfterUpdate event procedure of ProductID to look up the default unit for the
product, and the AfterUpdate of UnitID to look up the current price for this
product sold in this unit:

Private Sub ProductID_AfterUpdate
Dim strWhere As String
If Not IsNull(Me.ProductID) Then
strWhere = "ProductID = " & Me.ProductID
Me.UnitID = DLookup("DefaultUnitID", "Product", strWhere)
Call UnitID_AfterUpdate
End If
End Sub

Private Sub UnitID_AfterUpdate
Dim strWhere As String
If Not (IsNull(Me.UnitID) Or IsNull(Me.ProductID)) Then
strWhere = "(ProductID = " & Me.ProductID & _
") AND (UnitID = " & Me.UnitID & ")"
Me.PriceEach = DLookup("PriceEach", "ProductPrice", strWhere)
End If
End Sub

Note: The code assumes UnitID and ProductID are Number type fields. You need
extra quotes if they are Text type fields, e.g.:
strWhere = "ProductID = """ & Me.ProductID & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"AndyEduardo" <[email protected]>
wrote in message
 
A

AndyEduardo

Allen said:
Presumably you have tables something like this:

Unit table (one record for each unit of measure, e.g. ton, kg, ...)
UnitID primary key (pk)
...

Product table (one record for each product):
ProductID primary key
ProductName
DefaultUnitID the most common Unit this product is sold in.
...

ProductPrice table:
ProductID relates to Product.ProductID
UnitID relates to Unit.UnitID
PriceEach Currency current price for this unit of this product.

Invoice table:
InvoiceID p.k.
...

InvoiceDetail table:
InvoiceID relates to Invoice.InvoiceID
Quantity Number
ProductID relates to Product.ProductID
UnitID unit of measure
PriceEach Currency
...

Then in the subform bound to InvoiceDetail table, you could use the
AfterUpdate event procedure of ProductID to look up the default uni
for the
product, and the AfterUpdate of UnitID to look up the current price fo
this
product sold in this unit:

Private Sub ProductID_AfterUpdate
Dim strWhere As String
If Not IsNull(Me.ProductID) Then
strWhere = "ProductID = " & Me.ProductID
Me.UnitID = DLookup("DefaultUnitID", "Product", strWhere)
Call UnitID_AfterUpdate
End If
End Sub

Private Sub UnitID_AfterUpdate
Dim strWhere As String
If Not (IsNull(Me.UnitID) Or IsNull(Me.ProductID)) Then
strWhere = "(ProductID = " & Me.ProductID & _
") AND (UnitID = " & Me.UnitID & ")"
Me.PriceEach = DLookup("PriceEach", "ProductPrice", strWhere)
End If
End Sub

Note: The code assumes UnitID and ProductID are Number type fields. Yo
need
extra quotes if they are Text type fields, e.g.:
strWhere = "ProductID = """ & Me.ProductID & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"AndyEduardo" <[email protected]>
wrote in message
Click to expand...


I did all that you said, but the problem isn't as you understood.
I have a Product list with many products, those products can b
measured in Tons when I sell rice or flour, but when I sell milk, th
measure isn't Tons, the measure is Units because it's contained.
When I sell in tons, I need three values, Bruto, Tare and Bruto-Tare
what I want? If I sell 2 products measured in tons(or three,or four
txtBruto look for the high Bruto, but if at least one product in th
Invoice isn't measured in tons, txtBruto remain empt
 
A

Allen Browne

Andy, I guess I don't understand the concept of Bruto, Tare and Bruto-Tare.

Do you sell products all 3 ways?
Should the ProductPrice table contain pricings for all 3 ways?
 
A

AndyEduardo

Allen said:
Andy, I guess I don't understand the concept of Bruto, Tare an
Bruto-Tare.

Do you sell products all 3 ways?
Should the ProductPrice table contain pricings for all 3 ways?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"AndyEduardo" <[email protected]> wrot
in
message news:[email protected]...
Click to expand...

I don't sell the same product in three ways, the product sells in tons
can't be sold in units
I have many differents products in my list, and I need when I sell one
product in tons (or two, or three) txtBruto and txtTare resume that
information......but when only one product in the invoice is not sold
in tons the sum has not sense, for this reason I want what I said
 
A

AndyEduardo

Allen said:
Andy, I guess I don't understand the concept of Bruto, Tare an
Bruto-Tare.

Do you sell products all 3 ways?
Should the ProductPrice table contain pricings for all 3 ways?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"AndyEduardo" <[email protected]> wrot
in
message news:[email protected]...
Click to expand...

Tare: when you take the weight of a car without the product above
Bruto: when you take the weight of a car with the product above

-* This is only useful when the product can be sold in tons-

I don't sell the same product in three ways, the product sells in tons
can't be sold in units
I have many differents products in my list, and I need when I sell one
product in tons (or two, or three) txtBruto and txtTare resume that
information......but when only one product in the invoice is not sold
in tons the sum has not sense, for this reason I want what I said
 
A

Allen Browne

Ah, I think I understand: the total count in the invoice footer section
makes no sense if there are different units of measurement.

In report design view, open the Sorting And Grouping dialog (View menu).
You already have the InvoiceID there, with Yes for the Group Header and
Footer.
On the next line of the dialog, add the field that contains Bruto, Tare, or
whatever.
In the lower pane, set Group Footer to Yes.
Access adds a new section to the report.

Drag the text box that gives the total quantity up from the InvoiceID group
footer into this new group footer. This gives you a meaningful subtotal for
each unit of measurement on the invoice, instead of a meaningless total in
the group footer. If there is only one type of measurement, there will only
be one subtotal, so that still gives what you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
 
Top