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?

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

But you're only providing one with strColName

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

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 = rst!Units
AllowNeg = Nz(rst!AllowNegs,False)
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

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