Trouble with Syntax for DSum in Form

[MuskokaMad]

Thanks in advance your your assistance.

I am working on a Order Management Database.

In this Database I have a form frm_NewOrders that is bound to a table tbl_Orders with the Primary Key Order_ID. I have within that form a subform called frm_NewOrderDetails Subform that is Bound to a table tbl_OrderDetails With a Primary Key of OrderDetails_ID. The two are related through the Order_ID Field with the same name on both sides of the relationship.

In the subform I call Product Records from a Table tbl_Products with a Primary Key of Product_ID. I use 3 fields from tbl_Products in the subform. A list box which is keyed to the Product_ID, RetailPrice which is a field in the Product Table (the names are probably self explanatory.) and TaxPercentage (a number not a percentage)

I also have controls for Quantity, Discount (a number not a percentage)

In the subform I have 2 Calculated fields called Subtotal and SubtotalWithTaxes that use the following :

Subtotal: =([RetailPrice]*[Quantity])*((100-[Discount])/100)
SubtotalWithTaxes: =([RetailPrice]*[Quantity])*((100-[Discount])/100)*(([ Taxpercentage]/100)+1)

These return the correct values in their fields.

I would like a text box on the main form that displays a running total for the with tax case.

I have tried 5 or 6 different ways with a DSum but keep getting an error value.

Please Help!

Jason

 

[vbaInet]

Have you got an example of the DSUM you've written that isn't working?

 

[MuskokaMad]

In the form Properties I have set the following code on the AfterInsert, AfterUpdate and AfterDelConfirm Events.

Private Sub Form_AfterInsert()
Forms("frm_NewOrderEntry").Recalc
End Sub

Private Sub Form_AfterUpdate()
Forms("frm_NewOrderEntry").Recalc
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Forms("frm_NewOrderEntry").Recalc
End Sub

I have tried creating a text box with the Control Source Set to =DSum("SubtotalWithTax","tbl_OrderDetails","Order_ID=" & "Order_ID)

 

[vbaInet]

If Order_ID is definitely a number then use this:

=DSum("[SubtotalWithTax]","tbl_OrderDetails","[Order_ID]=" & Me.Order_ID)

 

[MuskokaMad]

Sorry Order_ID is a replication ID.... does this make any difference?

 

[vbaInet]

What do you mean by a replication ID? Autonumber field or string?

 

[MuskokaMad]

It is the ReplicationID option in the AutoNumber field. It generates a random string. I need this to facilitate offline replication of 4 db instances that will run in physically isolated locations.

 

[vbaInet]

Try both. Here's the code if it's a string:

=DSum("[SubtotalWithTax]","tbl_OrderDetails","[Order_ID]='" & Me.Order_ID & "'")Notice the bits I added in red. That's how you handle strings.

 

[MuskokaMad]

Thanks for your continuing help.

I entered the DSum you provided and I get a #Name? in the field.

Sorry to keep pestering

 

[vbaInet]

That's fine.

Both of them gave you the error?

Are you sure SubTotalWithMax and Order_ID are BOTH existing fields in tbl_OrderDetails?

 

[MuskokaMad]

Order_ID is the Child Key that links the tbl_OrderDetails and tbl_Orders. They are the same string type.

SubtotalWithTax refers to a text box with the Name SubtotalWithTax it is a calculated field with the Control Source

=([RetailPrice]*[Quantity])*((100-[Discount])/100)*(([ Taxpercentage]/100)+1)

Is this calculated field causing the problem?

 

[vbaInet]

Correct.

=DLookup("Argument 1", "Argument 2", "[Argument 3]")

The first argument in the DLookup is the value you want to return from your table.

 

[dfenton]

Replication IDs require special handling in Access. Details here:

http://trigeminal.com/usenet/usenet011.asp?1033

Basically, you have to use the conversion functions, GUIDFromString() and StringFromGUID() to get results. The "nice" thing about that is that it's hardly ever predictable where this issue kicks in. Try this:

DSum("[SubtotalWithTax]","tbl_OrderDetails","[Order_ID]=" & Chr(34) & StringFromGUID(Me.Order_ID) & Chr(34))

...or some variation thereof.