CurrentDB.OpenRecordSet Problem (1 Viewer)

Fazered

Registered User.
Local time
Today, 07:54
Joined
Mar 25, 2008
Messages
29
Right, so those of you who have seen my question in the other thread in the forums (Link here) will have seen the form in which this code is for, all be it a completely different problem.

The problem here is the line coloured Red in the following code. When running it, it stops on the red line and displays the following error:

Runtime error: 3061

Too Few parameters. Expected 1.

Code:
Function ScanBooked()
    
    Dim strSQL As String
    Dim BookedRecs As Recordset
    Dim AmountVar, MixVar, ExtraVar, PriveOverrideVar As Double
    Dim ChargedWaitVar, SundayVar As Boolean
    Dim StatusVar As String
    
    'SQL to retreive all bookings from a given customer
    strSQL = "SELECT Bookings.Status, Bookings.Mix, Bookings.[Order Amount], Bookings.[Price Override], Bookings.Extras, Bookings.[Charged Wait], Bookings.[Customer Number], Bookings.[Sunday Delivery] " & _
             "FROM Customer INNER JOIN (Bookings LEFT JOIN [Completed Orders] ON Bookings.[Order Number] = [Completed Orders].[Order Number]) ON Customer.CardinalisCustomerNumber = Bookings.[Customer Number] " & _
             "WHERE (((Bookings.Status)='Booked' Or (Bookings.Status)='Pre-Paid') AND ((Bookings.[Customer Number])=[Forms]![ViewAccount]![CustNum]));"

    'Open BookedRecs in Read-Only
    [COLOR="Red"]Set BookedRecs = CurrentDb.OpenRecordset(strSQL) ', dbOpenSnapshot)[/COLOR]
    If BookedRecs.RecordCount > 0 Then
        BookedRecs.MoveFirst
        Do While Not BookedRecs.EOF

            'Main Code to extract data
            AmountVar = DLookup("[Order Amount]", "BookedRecs")
            MixVar = DLookup("[Mix]", "BookedRecs")
            ExtraVar = DLookup("[Extras]", "BookedRecs")
            ChargedWaitVar = DLookup("[Charged Wait]", "BookedRecs")
            SundayVar = DLookup("[Sunday Delivery]", "BookedRecs")
            StatusVar = DLookup("[Status]", "BookedRecs")
            PriveOverrideVar = DLookup("[Price Override]", "BookedRecs")
        
            'Only continue if Status is not 'Cancelled'
            If StatusVar <> "Cancelled" Then
                   
                'Function to calculate the totals
                Call AccountFinanceSubs.Calculate_Total(Amount, Mix, Extra, ChargedWait, PriceOverrideVar, SundayVar)
        
                'Code to add up totals for breakdowns of total unpaid and paid
                'If customer has booked and not paid
                If StatusVar = "Booked" Then
                    Form_ViewAccount.UnPaidOpenVar = Form_ViewAccount.UnPaidOpenVar + OrderValueSubs.OrderValueVar
                End If
            
                'If customer has booked and paid
                If StatusVar = "Pre-Paid" Then
                    Form_ViewAccount.PaidOpenVar = Form_ViewAccount.PaidOpenVar + OrderValueSubs.OrderValueVar
                End If
            End If
           
            'Move to the Next Record
            BookedRecs.MoveNext
        Loop
    End If
    
End Function

Im sure there are many other problems later on in the code, but i'll tend to those some other time.
So does anyone have any ideas to what the problem may be?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:54
Joined
Aug 30, 2003
Messages
36,118
Try

"WHERE (((Bookings.Status)='Booked' Or (Bookings.Status)='Pre-Paid') AND ((Bookings.[Customer Number])= " & [Forms]![ViewAccount]![CustNum] & "));"
 

boblarson

Smeghead
Local time
Today, 00:54
Joined
Jan 12, 2001
Messages
32,059
Also your syntax is off:

Set BookedRecs = CurrentDb.OpenRecordset(strSQL) ', dbOpenSnapshot)

Should be

Set BookedRecs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 

Fazered

Registered User.
Local time
Today, 07:54
Joined
Mar 25, 2008
Messages
29
Cheers, that has solved the problem.

I dont suppose you'd be able to help me out with my next problem.

The 'DLookUp' part highlighted in the code below is not the part giving me some problems.

Code:
Function ScanBooked()
    
    Dim strSQL As String
    Dim BookedRecs As Recordset
    Dim AmountVar, MixVar, ExtraVar, PriveOverrideVar As Double
    Dim ChargedWaitVar, SundayVar As Boolean
    Dim StatusVar As String
    
    'SQL to retreive all bookings from a given customer
    strSQL = "SELECT Bookings.Status, Bookings.Mix, Bookings.[Order Amount], Bookings.[Price Override], Bookings.Extras, Bookings.[Charged Wait], Bookings.[Customer Number], Bookings.[Sunday Delivery] " & _
             "FROM Customer INNER JOIN (Bookings LEFT JOIN [Completed Orders] ON Bookings.[Order Number] = [Completed Orders].[Order Number]) ON Customer.CardinalisCustomerNumber = Bookings.[Customer Number] " & _
             "WHERE (((Bookings.Status)='Booked' Or (Bookings.Status)='Pre-Paid') AND ((Bookings.[Customer Number])= " & [Forms]![ViewAccount]![CustNum] & "));"
             '"WHERE (((Bookings.Status)='Booked' Or (Bookings.Status)='Pre-Paid') AND ((Bookings.[Customer Number])=[Forms]![ViewAccount]![CustNum]));"

    'Open BookedRecs in Read-Only
    Set BookedRecs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If BookedRecs.RecordCount > 0 Then
        BookedRecs.MoveFirst
        Do While Not BookedRecs.EOF

            'Main Code to extract data
[COLOR="Red"]            AmountVar = DLookup("[Order Amount]", "BookedRecs")
            MixVar = DLookup("[Mix]", "BookedRecs")
            ExtraVar = DLookup("[Extras]", "BookedRecs")
            ChargedWaitVar = DLookup("[Charged Wait]", "BookedRecs")
            SundayVar = DLookup("[Sunday Delivery]", "BookedRecs")
            StatusVar = DLookup("[Status]", "BookedRecs")
            PriveOverrideVar = DLookup("[Price Override]", "BookedRecs")[/COLOR]
        
            'Only continue if Status is not 'Cancelled'
            If StatusVar <> "Cancelled" Then
                   
                'Function to calculate the totals
                Call AccountFinanceSubs.Calculate_Total(Amount, Mix, Extra, ChargedWait, PriceOverrideVar, SundayVar)
        
                'Code to add up totals for breakdowns of total unpaid and paid
                'If customer has booked and not paid
                If StatusVar = "Booked" Then
                    Form_ViewAccount.UnPaidOpenVar = Form_ViewAccount.UnPaidOpenVar + OrderValueSubs.OrderValueVar
                End If
            
                'If customer has booked and paid
                If StatusVar = "Pre-Paid" Then
                    Form_ViewAccount.PaidOpenVar = Form_ViewAccount.PaidOpenVar + OrderValueSubs.OrderValueVar
                End If
            End If
           
            'Move to the Next Record
            BookedRecs.MoveNext
        Loop
    End If
    
End Function

I need to read each column of data, row by row in the recordset and place them into variables.

I beleive the code to run throught the rows of the recordset are correct, but the DLookup can't find the recordset and this cannot extract the data.
My view on this is that the DLookUp is not the function to be using to get the data.

What can i use to pull out the data for each row in the record set and apply them to the variables?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:54
Joined
Aug 30, 2003
Messages
36,118
Also your syntax is off:

Set BookedRecs = CurrentDb.OpenRecordset(strSQL) ', dbOpenSnapshot)

Should be

Set BookedRecs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Bob, that's actually not an error, as the Type argument is optional. Not saying I would do it, but it is valid syntax as is.

Fazered, you don't need DLookups there:

AmountVar = BookedRecs![Order Amount]
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:54
Joined
Aug 30, 2003
Messages
36,118
By the way, in Access this:

Dim AmountVar, MixVar, ExtraVar, PriveOverrideVar As Double

creates 3 Variants and 1 Double, not 4 Doubles. To get 4 Doubles:

Dim AmountVar As Double, MixVar As Double, ExtraVar As Double, PriveOverrideVar As Double
 

boblarson

Smeghead
Local time
Today, 00:54
Joined
Jan 12, 2001
Messages
32,059
Bob, that's actually not an error, as the Type argument is optional. Not saying I would do it, but it is valid syntax as is.

]
Paul - that is INVALID syntax as you do not put a right paren after strSQL it will squawk at you.

The syntax from the help file is:

Set recordset = object.OpenRecordset (source, type, options, lockedits)

He had an extra right paren and a stray quote mark which wasn't fitting in. The syntax I posted DOES have the source and Type (leaving off the options and lockedits which the OP didn't use anyway.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:54
Joined
Aug 30, 2003
Messages
36,118
Not to be persnickety, but if you put it in VBA you'll see the "stray" quote mark comments out the last part, and the parentheses are properly closed off, if you disregard what's commented out. Presumably the OP commented it out while trying to debug the problem.

Set BookedRecs = CurrentDb.OpenRecordset(strSQL) ', dbOpenSnapshot)

My point being that it would have run without error as originally posted.
 

boblarson

Smeghead
Local time
Today, 00:54
Joined
Jan 12, 2001
Messages
32,059
I guess I need to just not post for today... the brain seems to be working as well as a rusted gear. Think I should stay away from any coding for a day or so and see if that helps.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:54
Joined
Aug 30, 2003
Messages
36,118
Go out and have a frosty cold beer (isn't Portland known for its beers or something?). That will loosen up the rust. I'll buy you one next month.
 

boblarson

Smeghead
Local time
Today, 00:54
Joined
Jan 12, 2001
Messages
32,059
Go out and have a frosty cold beer (isn't Portland known for its beers or something?). That will loosen up the rust. I'll buy you one next month.

Unfortunately, due to medication I have to take, I can't ingest alcohol. So, I'll just have to do something else.
 

ajetrumpet

Banned
Local time
Today, 02:54
Joined
Jun 22, 2007
Messages
5,638
Your problem is that you're trying to identify your declared variables like they are actual strings representing table names:
Code:
            'Main Code to extract data
[COLOR="Red"]            AmountVar = DLookup("[Order Amount]", "BookedRecs")
            MixVar = DLookup("[Mix]", "BookedRecs")
            ExtraVar = DLookup("[Extras]", "BookedRecs")
            ChargedWaitVar = DLookup("[Charged Wait]", "BookedRecs")
            SundayVar = DLookup("[Sunday Delivery]", "BookedRecs")
            StatusVar = DLookup("[Status]", "BookedRecs")
            PriveOverrideVar = DLookup("[Price Override]", "BookedRecs")[/COLOR]
You cannot perform a DLookup on a declared variable (I don't think). Change "BookedRecs" to strSQL.

By the way, why are you using this dlookup method here? You're not specifying any criteria. It's just going to return the same value to you over and over again through the loops. What's the point of this? And also, if you're trying to write the table values (using dlookup) to the variables themselves, then why did you try to open the recordset as read only?
Code:
    'Open BookedRecs in Read-Only
    Set BookedRecs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:54
Joined
Aug 30, 2003
Messages
36,118
Bob: soda, juice, tea etc works for me too.

Adam: I guess there's a lot of rust around today.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:54
Joined
Aug 30, 2003
Messages
36,118
Well, you can’t use a variable for the SQL any more than you can use a recordset for the domain argument. The domain argument must be "a table name or a query name". Using strSQL as you recommended will result in the error "The Microsoft Jet database engine cannot find the input table or query “SELECT…"

While you’re correct that a DLookup with no criteria will return the same value, you failed to notice that the OP is trying to get the current value from the recordset, so as I noted in post 5, all that’s needed is to refer to the recordset directly instead of using DLookup.

As to opening the recordset as read only, you missed that the OP is getting values from the recordset, not writing values to it, so opening read only is fine:

"What can i use to pull out the data for each row in the record set and apply them to the variables"
 

ajetrumpet

Banned
Local time
Today, 02:54
Joined
Jun 22, 2007
Messages
5,638
thanks Paul. I think I'll let you handle this one. I believe I have typed too much during the past few days. Thanks for the correction, and let us hope that I didn't confuse anyone here... :rolleyes: :)
 

israg

New member
Local time
Today, 00:54
Joined
Mar 30, 2008
Messages
2
Missing Param Error

Hi,
The problem is bexause of the end of the sql : "=[Forms]![ViewAccount]![CustNum]));"

You should parse it so it will be like: "select ........... = " & "=[Forms]![ViewAccount]![CustNum] & "));"

isra
 

Fazered

Registered User.
Local time
Today, 07:54
Joined
Mar 25, 2008
Messages
29
Bob, that's actually not an error, as the Type argument is optional. Not saying I would do it, but it is valid syntax as is.

Fazered, you don't need DLookups there:

AmountVar = BookedRecs![Order Amount]


Of course, i should have known that. Never mind, you learn from your mistakes.

Not to be persnickety, but if you put it in VBA you'll see the "stray" quote mark comments out the last part, and the parentheses are properly closed off, if you disregard what's commented out. Presumably the OP commented it out while trying to debug the problem.

Set BookedRecs = CurrentDb.OpenRecordset(strSQL) ', dbOpenSnapshot)

My point being that it would have run without error as originally posted.

That is correct, I had commented out the last part of that line to help with debugging. I just forgot to remove it again before i posted to code into this thread.
 

hturgut

Registered User.
Local time
Today, 18:54
Joined
Dec 18, 2010
Messages
24
Bob, that's actually not an error, as the Type argument is optional. Not saying I would do it, but it is valid syntax as is.

Fazered, you don't need DLookups there:

AmountVar = BookedRecs![Order Amount]

Hi Pbaldy,

I read your message regarding the problem Fazer
 

hturgut

Registered User.
Local time
Today, 18:54
Joined
Dec 18, 2010
Messages
24
Bob, that's actually not an error, as the Type argument is optional. Not saying I would do it, but it is valid syntax as is.

Fazered, you don't need DLookups there:

AmountVar = BookedRecs![Order Amount]

Hi Pbaldy,

I read your message regarding the problem Fazerer had.

I have same problem. I have been trying to apply VBA code every single row that I need to retrieve some values into my code. But it only retrieves first row not others. When I write Queries![MyTable]![MyField] , It brings Error message saying Runtime error 424, Object Required. Could you please help me on where I get wrong.

Thanks
 

Users who are viewing this thread

Top Bottom