function error

[boycie]

Hi,

I have written the following function;

Private Function GetMaxValue(ByRef strTable As String) As Long

'gets the number of records from table which will allow us to determine how many records to add

Dim db As DAO.database
Dim rs1 As DAO.Recordset


Set db = CurrentDb()

Set rs1 = db.OpenRecordset("SELECT Max(" & strTable & ".ID) AS MaxOfID FROM" & " " & strTable & ";")

rs1.MoveFirst

GetMaxValue = rs1![MaxOfID]

MsgBox GetMaxValue

rs1.Close

Set rs1 = Nothing
Set db = Nothing

End Function

when I call the function and pass it a table name as follows;

Private Sub Command2_Click()

GetMaxValue ("t_2")

MsgBox GetMaxValue

End Sub

I get the error; 'argument not optional'. Can anyone see the problem?
It works though if the msgbox is within the function

thanks

 

[boblarson]

You have to combine these two lines:

GetMaxValue ("t_2")

MsgBox GetMaxValue


So

MsgBox GetMaxValue("t_2")

 

[boblarson]

And if you want to use it later on, you assign it to a variable:

Dim lngValue As Long

lngValue = GetMaxValue("t_2")

MsgBox lngValue

 

[boycie]

thanks Bob, i tried everything except that