Use Function in MsgBox 1

[TimTDP]

I have the following function:

Public Function pubCurrentAgency() As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim CompanyName As String
Dim TradingName As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblAgency where Active = True")

pubCurrentAgency = rst!CompanyName

End Function

I want to use this function in a Msgbox like this:

MsgBox "No Agency Folder has been specified for " & pubCurrentAgency & "."

When I compile the database Access highlights "pubCurrentAgency" in the Msgbox and I get an error message "Ambiguous Name Detected"

What am I doing wrong?

 

[RoyVidar]

This probably means you have more than one sub, function (or perhaps module?) sharing the same name - only one "thing" within the scope can have the same name, I think. Do a search using the "Current Project" option to find out.

Roy-Vidar

 

[stix4t2]

Tim,
Remember to close that recordset at the end of the function.

rst.close

 

[Tigerlili3]

When you get past the ambiguous name error, you may also have a problem with rst!CompanyName. Whenever I have referenced the value in a particular field returned in a recordset, I have used the following syntax

rst.Fields(0)

The '(0)' is a zero-based index number where 0 would return the value of the first field from the left in your recordset, 1 would return the value of the second field from the left, and so on.