troubleshooting Question

Assign the value from an db.OpenRecordset("Select ... statement to a variable

Avatar of mdlp
mdlpFlag for United States of America asked on
Visual Basic ClassicMicrosoft Access
15 Comments1 Solution845 ViewsLast Modified:
Experts, I have to get a value that is a sum() of a column in a seperate mdb, I would usaly
open with dao and findfirst, then loop for each and keep a running total.  But as I have been reading that a select should be much faster, but I can not get it to work.

This code exist in a different database and is accessed via a network on a WAN, that is why  I am using DAO, and that is why I am not using the loop, is was to slow. Don't know if you need this information.
This is the error; "Too few Parameters expected 1
This is where it crashes;  Set rs = db.OpenRecordset("select sum(Count) from sale where InvNo = dblInvNo")

What I am doing might not be the best way so in other words What is the fastest way to get the following answer from the attached MDB;
 if dblInvNo = 2401 then dblCount = 325

Thank you very much Experts
mdlp

 
****************   THIS IS THE CODE THAT I HAVE UP TO THIS POINT
***************  It is currently attache to a Command button

Private Sub cbColShpQnty_Click()
On Error GoTo Err_cbColShpQnty_Click

Dim strDBname As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stDocName As String
Dim dblInvNo As Double
Dim dblCount As Double

dblInvNo = [Forms]![CollectShippingQty]![tbInvNo]

strDBname = "W:\ActCost.mdb"
Set db = OpenDatabase(strDBname)
Set rs = db.OpenRecordset("Sale", dbOpenDynaset)
   
    'ERRROR = "Too few Parameters expected 1
    Set rs = db.OpenRecordset("select sum(Count) from sale where InvNo = dblInvNo")
 
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Need to;
'Calc box size and weight
'Print pick label


Exit_cbColShpQnty_Click:
    Exit Sub

Err_cbColShpQnty_Click:
    MsgBox Err.Description
    Resume Exit_cbColShpQnty_Click
   
End Sub

ActCost.mdb
ASKER CERTIFIED SOLUTION
shorvath
shorvath

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros