DSum not working :S

[jamesWP]

Hi there, I have this code in my form_dirty event,

Price.Value = DSum("[Price Each] * [Quantity]", "[Products]", "[OrderID] = " & Form_Orders.OrderID.Value)

Basically I want this to look in the table 'Products' for any value that has the same 'OrderID' as the current 'OrderID' on the form and then multiply 'Price Each' and 'Quantity' and sum the values together.

All that happens is the form hangs for around 2 seconds as if trying to work something out and then returns nothing to price.value.

It's probably me being stupid and I've used the wrong syntax or something, but I can't seem to notice what I'm doing wrong.

Any help would be appreciated .
James Prince

 

[workmad3]

ok. Its not DSum(not sure how to use that ) but heres the SQL that should work for what you want

Dim rs as DAO.Recordset
rs = CurrentDb.Openrecordset("SELECT SUM([Price Each] * [Quantity]) As Result FROM [Products] WHERE [Order ID] = " & Form_Orders.OrderID.Value)

Price.Value = rs.Fields("Result")

If anyone else can help you with the DSum problem, im sure they will, hopefully this will work for now.

 

[jamesWP]

Thanks I think this looks promising, if this works I'll forget DSum all together , do I need to set references at all?

 

[workmad3]

Only if DAO isnt already set(usually is though)

 

[jamesWP]

I referenced the Microsoft DAO 3.6 Object Library, now upon running the code I get an Invalid use of property error on 'RS ='

Thanks for the help so far by the way, very grateful

 

[workmad3]

oops, my mistake, try 'Set rs =' instead

 

[jamesWP]

Set RS = CurrentDb.Openrecordset("SELECT SUM([Price Each] * [Quantity]) As Result FROM [Products] WHERE [Order ID] = " & Form_Orders.OrderID.Value)

Too few parameters. Expected 1.
pops up with that error now, I should probably familiarise myself with DAO some time :$.

Edit:
I needed the [Order ID] as [OrderID], that'll teach me to just copy and paste code and expect it to work

Now access tells me:
Error 3048
'Cannot open any more databases'

 

[workmad3]

found some information about the error.

heres a discussion of several ppl who had the problem and the reasons it happened

http://www.dbforums.com/t722511.html

 

[jamesWP]

Mmmm, I have the latest Jet Service Pack, I'm not using linked tables, and I've only got like 6 tables and 6 queries, I'm using a subform but that means I only have 2 forms open at a time with around 7 bound controls at a time :S.

 

[workmad3]

peculiar. Im using a db with about a hundred sql statements running on a form, and have no problems :S

 

[RuralGuy]

PMFJI but do you have a Set rs = Nothing in the same subroutine?

 

[Bat17]

is [Products] a table or another query?

Peter

 

[jamesWP]

[Products] is a table, I'll try adding set rs = nothing, should I place that at the end?

Private Sub Form_Dirty(Cancel As Integer)
If Filtered1 = True Then Me.CompanyID = Cur
Dim RS As DAO.Recordset
Set RS = CurrentDb.Openrecordset("SELECT SUM([Price Each] * [Quantity]) As Result FROM [Products] WHERE [OrderID] = " & Form_Orders.OrderID.Value)
Price.Value = RS.Fields("Result")
Set RS = Nothing
End Sub

EDIT: I believe that it doesn't want to perform on the dirty event, if I place it in a command button click event it doesn't error, nor does it return anything.

Edit: Finally got it working, but now I need an event which waits until the data has been updated, does afterupdate do this?

 

[RuralGuy]

Hi James,

Since you asked, you have placed the Set RS = Nothing in the correct location. I do however have some problems with the rest of the code. I don't believe you have Option Explicit as the second line of your code page. It would catch a lot of problems with code. Is cur a public variable? You really should have some error handling in this subroutine. Are you still getting the error? If so you might try some of these suggestions from MVP Allen Browne : http://allenbrowne.com/ser-47.html

 

[jamesWP]

Ruralguy, can't believe I forgot Option Explicit :$, cur is a public variable, anyway, got it working now, one more question though while we're here

Dim res1 As String
Dim res2 As String
Dim pno As String
pno = Part_No.Value
res1 = DLookup("[Size]", "[Products List]", "[Part No] = '" & pno & "'")
res2 = DLookup("[Price Each]", "products list", "[Part No] = '" & pno & "'")
Size.Value = res1
Price_Each.Value = res2

for some bizarre reason that doesn't lookup properly, it sets res1 to the same value as pno, and res2 as a random number, I've had this code working before so this is very bizarre :S

P.S,
Thanks for all the help

 

[RuralGuy]

I don't know how much experience you have with Access or what version you are running, but after a series of errors Access can get weird. I would still follow some of Allen Browne's suggestions like decompile. Did you look at his suggestions on how to keep from getting corruptions?

 

[jamesWP]

Ah I'll read into them now(Would've read sooner but I assumed you meant they would help with my last problem only ) Thanks

Nothing seems to help , is there a DAO alternative to DLookup that you guys know of?