Select records and processing one of the fields

[krester]

I wrote a function that goes to the table tblPayment and pull out all the records where bill _id is equal to the argument I send to the function. Then, in a loop I check each recorde whether the field "paid" = 1. If it is equal to 1, I accumulate the amount.​

Unfortunate, I get a compaile error in - rst.FindFirst
The error message is: argument not optional.​

I have no idea what's wrong .
Does anyone got any suggestions?​

This is the Function:​

[left][font=Tahoma]Public Function end_payment (bill _id)[/font]
[font=Tahoma] [/font][/left][font=Tahoma][/font]
[font=Tahoma]Dim rst As dao.Recordset[/font]
[font=Tahoma]Dim i, n, bd, c_rez, rez As Integer, tb, w, q As String[/font]
[font=Tahoma]rez = 0[/font]
[font=Tahoma]tb = " tblPayment "[/font]
[font=Tahoma] [/font]
[font=Tahoma]bd = bill _id[/font]
[font=Tahoma]w = " where bill _id =" + bd[/font]
[font=Tahoma]q = "select * from " + tb + w[/font]
[font=Tahoma]Set rst = CurrentDb.OpenRecordset(q([/font]
[font=Tahoma]   n = rst.RecordCount[/font]
[font=Tahoma]   If n Then[/font]
[font=Tahoma]        For i = 0 To n - 1[/font]
[font=Tahoma]            If i = 0 Then[/font]
[font=Tahoma]                rst.FindFirst[/font]
[font=Tahoma]            Else[/font]
[font=Tahoma]                rst.FindNext[/font]
[font=Tahoma]            End If[/font]
[font=Tahoma]c_rez = 0[/font]
[font=Tahoma]            If Not rst.BOF And Not rst.EOF Then[/font]
[font=Tahoma]               If rst("paid") = 1 Then[/font]
[font=Tahoma]                    c_rez = rst("paid")[/font]
[font=Tahoma]               End If[/font]
[font=Tahoma]               rez = rez + c_rez[/font]
[font=Tahoma]            Else[/font]
[font=Tahoma]                Exit For[/font]
[font=Tahoma]            End If[/font]
[font=Tahoma] [/font]
[font=Tahoma]        Next i[/font]
[font=Tahoma]   End If[/font]
[font=Tahoma]   End Function
[font=Tahoma][left][/left][/font]

 

[vbaInet]

I think you might be going through a long winded route. Could you explain exactly what you would like to do or what you were trying to achieve with your code?

 

[krester]

OK – I have bills. Each bill can be paid in some payments.
I what that each time I mark that one of the payments was paid – the program will check all the payments whether they were paid.
As a result I what to know: 1- Is all the dill was paid. 2- What is the balance.
Do you have any idea how to do it?​

 

[vbaInet]

Hmm... I still don't understand your query. Let's go back to your code. Could you amend your pasted code removing all the unnecessary blocks like etc and repaste? Or just amend what you had pasted already and post back saying you have done so.

 

[krester]

Dear vbaInet,
I changed the code and now it's working. I don't know if it's the best way to write it. I'll be grateful if you'll have the time to write your opinion.
But I have a problem:
I want to return the value I have in "rez", but if I write return rez I get compile error: Expected: end of statement.
What's wrong? Why can't I return a value?​

The code is:

Public Function end_payment(bill_id) As Integer
Dim rst As DAO.Recordset
Dim i, n, n_halvaa, c_rez, rez As Integer ', tb, w, q As String
rez = 0
s = 100
bd= bill_id
Set rst = CurrentDb.OpenRecordset("select * from tblPayment where bill_id=" & bd)
n = rst.RecordCount

If n > 0 Then
For i = 0 To s
If i = 0 Then
rst.FindFirst ("bill_id=" & bd)
Else
rst.FindNext ("bill_id=" & bd)
End If

c_rez = 0
If rst.NoMatch Then
Exit For

Else
If rst![paid] = 1 Then
c_rez = rst![p_sum]
End If
rez = rez + c_rez
End If
Next i
Else
MsgBox ("A bill without payments")
Exit Function
End If



Set rst= Nothing
Return

End Function

 

[vbaInet]

I don't know if the end_payment function is used solely to calculate rez? If it is then to return the value set the function to rez like this (from your code):

...
....

Set rstTashlumim = Nothing
end_payment = rez

Now, the function will contain rez.

Or if that's not the case, declare your rez variable as a global variable here:

Option Compare Database
Option Explicit

Dim rez as integer

Hope this helps

 

[krester]

Hi,
I added the code as you suggested "end_payment = rez" and it was accepted.
Now, when I run the code I get a new error: "return without GoSub".
I really don’t understand – what's wrong ?​

 

[vbaInet]

I believe the line that's being highlighted when the error occurs is "Return". Remove it.

 

[gemma-the-husky]

the return at the very bottom is superfluous - thats the error

out of interest - this probably doesnt do what you expect - this is dimming all these variables (expect rez, and aq) as variants - not as integers or stirngs

probably not producing an error - but its not recommended either.

Dim i, n, n_halvaa, c_rez, rez As Integer ', tb, w, q As String

 

[krester]

Dear vbaInet and gemma-the-husky,
Thank you so much! It's working!!!
Have a nice day – my day is already nice thanks to you ...​

By the way, Dave – what do you recommend to writte instead of -

Dim i, n, n_halvaa, c_rez, rez As Integer?

 

[pbaldy]

Dave is probably asleep. As he said, your method declares one Integer and the rest Variants. This would declare all as Integers:

Dim i As Integer, n As Integer, n_halvaa As Integer, c_rez As Integer, rez As Integer

 

[vbaInet]

krester: You're welcome. Glad to have made some input.

Paul: Do you have any idea why the declarations were designed to behave that way? I suppose for the Optional Explicit statement.