Syntax Error in Access VBA

[krish05]

Hi All,

I am a novice in MS Access and VBA. I have written this code below to get a cumulative sum when a certain condition is satisfied. Calling this function in a Access query design. But it throws an error saying it contains a syntax error. Could anyone help rectify this?

Public Function TotalFx(CASE_ID As String, ELM_ID As Integer) As Double
Dim rs As Recordset
Dim TotalFx As Double

Set rs = CurrentDb.OpenRecordset("SELECT [Fx S] FROM [Query_Loads_Sum_6] WHERE [CASE_ID] = '" & CASE_ID & "' AND [ELM_ID] < " & ELM_ID)

Do While Not rs.EOF
TotalFx = TotalFx + rs("[Fx S]")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

TotalFx = TotalFx
End Function

 

[welshgasman]

Put the sql into a string variable, then you can debug.print it.
With me, anything with ID in the name is a number>, and an autonumber at that?
So you have Case_ID as a string and ELM_ID as a number?

Walk through your code using F8. You can set a breakpoint on the very first executable line and call that function from the immediate window.
I dare say you should be using

rs![Fx_S]

or

rs!Fields("[Fx_S]")

See here for how to do basic debugging. https://www.youtube.com/results?search_query=debug+access+vba

 

[Micron]

Always indicate which line raises the error to help focus answers. Methinks it's this line
TotalFx = TotalFx + rs("[Fx S]") because the brackets are not part of the field name, but by putting them in quotes, you're inferring that they are. Try

TotalFx = TotalFx + rs("Fx S")

Best practice is to not have spaces in any object name.
EDIT - I tested WGM's methods and they work too.

 

[krish05]

Put the sql into a string variable, then you can debug.print it.
With me, anything with ID in the name is a number>, and an autonumber at that?
So you have Case_ID as a string and ELM_ID as a number?

Walk through your code using F8. You can set a breakpoint on the very first executable line and call that function from the immediate window.
I dare say you should be using

rs![Fx_S]

or

rs!Fields("[Fx_S]")

See here for how to do basic debugging. https://www.youtube.com/results?search_query=debug+access+vba

Thank you for your reply and suggestions @welshgasman! I have updated the code with your suggestions, but the main error was defining "TotalFx" twice.

 

[krish05]

Always indicate which line raises the error to help focus answers. Methinks it's this line
TotalFx = TotalFx + rs("[Fx S]") because the brackets are not part of the field name, but by putting them in quotes, you're inferring that they are. Try

TotalFx = TotalFx + rs("Fx S")

Best practice is to not have spaces in any object name.
EDIT - I tested WGM's methods and they work too.

Thank you for your suggestions @Micron. The main error was related to definition of "TotalFx"