Run Time Error 2439

[spectrolab]

Hi all,

I have a report which has the following code in the On Open command:

Private Sub Report_Open(Cancel As Integer)

Dim dbs As Database, rst As Recordset
Dim TBLLoop As Integer, strColName As String
Dim intRecs As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("LU_ColumnHeadingsFull", dbOpenDynaset)
If rst.BOF Then GoTo rptexit  'no column headings!
rst.MoveLast
intRecs = rst.recordcount
rst.MoveFirst
strColName = rst![columnname]
Me("TXT1").ControlSource = strColName
Me("TXT1").Visible = True
Me("Lbl1").ControlSource = "=" & Chr(39) & strColName & Chr(39)
Me("Lbl1").Visible = True

If intRecs > 1 Then     'If more than one record, process more columns
    For TBLLoop = 2 To intRecs
        rst.MoveNext
        strColName = rst![columnname]
        Me("TXT" & TBLLoop).ControlSource = "= MakeResult([" & strColName & "])"
        Me("TXT" & TBLLoop).Visible = True
        Me("Lbl" & TBLLoop).ControlSource = "=" & Chr(39) & strColName & Chr(39)
        Me("Lbl" & TBLLoop).Visible = True
    Next TBLLoop
    
End If

rptexit:
rst.Close
Exit Sub

End Sub

Which uses the following public function

Public Function MakeResult(result As Variant, Accuracy As Single, AllowNegs As Boolean) As String
'Function used to display results
    If IsNull(result) Then
        MakeResult = "-"
        Exit Function
    End If
    If result < Accuracy / 2 And AllowNegs = False Then
         MakeResult = "X"
    Else
        Select Case Accuracy
            Case 0.1
                MakeResult = Format(result, "0.0")
            Case 0.01
                MakeResult = Format(result, "0.00")
            Case 0.001
                MakeResult = Format(result, "0.000")
            Case 0.0001
                MakeResult = Format(result, "0.0000")
            Case 0.00001
                MakeResult = Format(result, "0.00000")
        End Select
    End If

End Function

When I try to run the report, I get a Run Time error (2439)
The expression you entered has a function containing the wrong number of arguments.

On debug, the line

Me("TXT" & TBLLoop).ControlSource = "= MakeResult([" & strColName & "])"

is highlighted.
MakeResult works fine for other uses I have for it (creating a .SIF file), but in this instance it falls over.

Any ideas what might be wrong?

 

[boblarson]

You're calling the function that requires 3 arguments:
1. result
2. Accuracy
3. AllowNegs

But you're only providing one with strColName

 

[spectrolab]

Thanks Bob,

I have played around with it and I have got the following code to sort of work, but it isn't looking up the fields in the table like it is supposed to, any ideas?

Private Sub Report_Open(Cancel As Integer)

Dim dbs As Database, rst As Recordset
Dim TBLLoop As Integer, strColName As String
Dim intRecs As Integer
Dim Accuracy As Single
Dim AllowNeg As Boolean

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("LU_ColumnHeadingsFull", dbOpenDynaset)
If rst.BOF Then GoTo rptexit  'no column headings!
rst.MoveLast
intRecs = rst.recordcount
rst.MoveFirst
strColName = rst![columnname]
Me("TXT1").ControlSource = strColName
Me("TXT1").Visible = True
Me("Lbl1").ControlSource = "=" & Chr(39) & strColName & Chr(39)
Me("Lbl1").Visible = True

If intRecs > 1 Then     'If more than one record, process more columns
    For TBLLoop = 2 To intRecs
        rst.MoveNext
        strColName = rst![columnname]
        Accuracy = rst!Detection
        AllowNeg = rst!AllowNegs
        Me("TXT" & TBLLoop).ControlSource = "= MakeResult(" & strColName & ", Accuracy, AllowNeg)"
        Me("TXT" & TBLLoop).Visible = True
        Me("Lbl" & TBLLoop).ControlSource = "=" & Chr(39) & strColName & Chr(39)
        Me("Lbl" & TBLLoop).Visible = True
    Next TBLLoop
    
End If

rptexit:
rst.Close
Exit Sub

End Sub

This is the table, columns don't line up, but you get the idea.

ID	ColumnName	Units	Detection	Method	AllowNegs
1	Fe	%	0.01	XRF101	0
2	SiO2	%	0.001	XRF101	0
3	Al2O3	%	0.001	XRF101	0
4	TiO2	%	0.001	XRF101	0
5	MnO	%	0.001	XRF101	0
6	CaO	%	0.001	XRF101	0
7	P	%	0.001	XRF101	0
8	S	%	0.001	XRF101	0
9	MgO	%	0.001	XRF101	0
10	K2O	%	0.001	XRF101	0
11	V2O5	%	0.001	XRF101	0
12	Na2O	%	0.001	XRF101	0
13	LOI1000	%	0.01	LOIGRA	1
14	Cr	%	0.0001	XRF101	0
15	Co	%	0.0001	XRF101	0
16	Ni	%	0.0001	XRF101	0
17	Cu	%	0.0001	XRF101	0
18	Zn	%	0.0001	XRF101	0
19	As	%	0.0001	XRF101	0
20	Pb	%	0.0001	XRF101	0
21	Ba	%	0.0001	XRF101	0

 

[petehilljnr]

I think this is what you want - given your table in your initial post.

Private Sub Report_Open(Cancel As Integer)

Dim dbs As Database, rst As Recordset
Dim TBLLoop As Integer, strColName As String
Dim intRecs As Integer
Dim Accuracy As Single
Dim AllowNeg As Boolean

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("LU_ColumnHeadingsFull", dbOpenDynaset)
If rst.BOF Then GoTo rptexit  'no column headings!
rst.MoveLast
intRecs = rst.recordcount
rst.MoveFirst
strColName = rst![columnname]
Me("TXT1").ControlSource = strColName
Me("TXT1").Visible = True
Me("Lbl1").ControlSource = "=" & Chr(39) & strColName & Chr(39)
Me("Lbl1").Visible = True

If intRecs > 1 Then     'If more than one record, process more columns
    For TBLLoop = 2 To intRecs
        rst.MoveNext
        strColName = rst![columnname]
        Accuracy = [B]rst!Units[/B]
        [B]AllowNeg = Nz(rst!AllowNegs,False)[/B]
        Me("TXT" & TBLLoop).ControlSource = [B]"= MakeResult(" & strColName & ", " & Accuracy & "," &  AllowNeg & ")"[/B]
        Me("TXT" & TBLLoop).Visible = True
        Me("Lbl" & TBLLoop).ControlSource = "=" & Chr(39) & strColName & Chr(39)
        Me("Lbl" & TBLLoop).Visible = True
    Next TBLLoop
    
End If

rptexit:
rst.Close
Exit Sub

End Sub

 

[spectrolab]

Thanks very much for all your help, it works great!