Setting a Field Default Value using my own function

[steveiz]

I have written a function that I want to use in the 'Default Value' of a field in a table. However, when I open the expression builder, I can see built in functions but not my own. Is there any reason for this?

 

[RickSpr]

Your function must be declared in a standard module with the Public scope. It should appear in the Expression Builder under Functions, in the subfolder with your database's name. In the second list box, select the module that contains the function, and then select the function name in the third list box.

In any event, you don't have to use the Expression Builder. You can just type the expression directly into the Default Value property box:
=MyFunc(param1, param2, etc.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[steveiz]

Thanks for your answer. What do you mean 'standard module in public scope'?

If I try and enter the function directly, I just get error 3388 when I try and save the table.

This is the code I have:

Option Compare Database
Dim db As Database, rst As Recordset, strSQL As String
Dim tbls As TableDefs, tbl As TableDef, propDesc As Property
Dim strTable As String, strTDesc As String, strField As String, strFDesc As String, strType As String
Dim iCtr As Integer, iCt2 As Integer, fldLoop As Field
Function Code_Default(strCodeName As String) As Integer
' This routine returns the index of a parameter code which has been marked as Default
Set db = CurrentDb
strSQL = "SELECT CodeID FROM sys_Codes "
strSQL = strSQL & "WHERE (((sys_Codes.FieldName)='" & strCodeName & "') AND ((sys_Codes.CodeDefault)=True));"
Set rst = db.OpenRecordset(strSQL)
If rst.RecordCount < 1 Then
'MsgBox &quot;Field: &quot; & strCodeName & vbCr & &quot;Value: &quot; & strCodeValue, , &quot;Error on Code&quot;
Code_Default = 0
Exit Function
End If
If rst.RecordCount > 1 Then
MsgBox &quot;Field: &quot; & strCodeName & vbCr & &quot;Value: &quot; & strCodeValue, , &quot;More than one Default found&quot;
Code_Default = 0
Exit Function
End If
rst.MoveFirst
intLocal = rst!CodeID
rst.Close
Code_Default = intLocal
End Function

 

[RickSpr]

The meaning of &quot;standard module&quot; is found in the help file by searching for &quot;standard modules&quot;. The meaning of &quot;public scope&quot; is found in the help file by searching for &quot;public modules&quot;.

As the message box for error 3388 tells you, Access couldn't find a public function with the name you put in the Default Value property. Making your function Public, as described in the help file, will probably solve the problem.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[steveiz]

Ok, I have made the function public but it still is not visible to the Default Value field.