Call Public Function

[Privateer]

Let me start by saying I got this thing to work by putting my function in a module, but I want the code behind a form. So the question is this: If a form is open and a function is public, shouldn't a query be able to reference that function? Must it be in a module?

I have written a public function that resides on the form. When I try to reference the function in a query, Access says the function can't be found. The query is run from code behind a command button on the form, so the form will always be open, meaning to me, that the function will be available. When I tested this in a QBE window, and fully qualify the location in the query: ObjDis: Forms("frmObjectMenu").GetOjbDis(....) Access really hated having a dot there. Never got this to work.

I am importing this form into many other databases and I wanted all the necessary code on the form. I was hoping to avoid importing a module for two lines of code or copying it to an existing module. Any information would be appreciated.

Thanks, Privateer

 

[vbaInet]

Must it be in a module?

Yes.

I have written a public function that resides on the form.

Let's see the function.

 

[Mile-O]

The module that comes with a form is called a Class Module. It's an enclosed world in there and one does not simply call the function within it as you would a function in a regular Module.

Calling the function from a query - form open or not - does not give the query access to the form's objects, properties, and methods.

If you want a public function to work in a query, put it in a separate module.

 

[Privateer]

Hey vbaInet
This is the code you helped me out with the other day. I was trying to fit this into an SQL string but the I gave up and decided to run it through a function. So here it is.

Public Function GetObjDis(CN As String, DN As String) As String
On Error Resume Next
'This function gets the description property for the object
GetObjDis = Application.CurrentDb.Containers("" & CN & "").Documents("" & DN & "").Properties("description")
End Function

 

[vbaInet]

I thought that you were going to save all that info into a table?

 

[Privateer]

I am, this function is part of an append query. The update button deletes all the records in the table and the query dumps everything in. Most of the info comes from the "MsysObjects" table, but I needed this function to get the description.

 

[vbaInet]

You can get it to work if you run the Containers code line in the Open event of the form and set the value of a textbox to the value returned from the code. Then use a reference to the textbox in the query.

But I don't think this is necessary. Why don't you run the append query in code instead?

 

[Privateer]

I do run the append query in code, but fitting the dot commands in the string was difficult.

Here is the string that I tried, you don't have the tblObjects and you can get rid of the insert line, but it will give you the idea. There are no errors on the string, at least as far as quotes are concerned, but it won't execute.

And thanks for the form option. I don't actually view the list of objects on a form, this is a quick and dirty exercise. The table is also used as the recordsource for the report, which I open in preview to see the results.

Forgot to mention, the container name for tables, queries and linked tables is tables, and that is what I get from the tblObjects.ContainerName field.


strSQL = "INSERT INTO [tblObjectInfo] ( ObjectName, ObjectTypeID, ForeignName, Source, ObjectDescription, Flags, CreatedOn, ModifiedOn ) " & vbCrLf & _
"SELECT [MsysObjects].[Name], [MsysObjects].[Type], [MsysObjects].[ForeignName], [MsysObjects].[Database], Application.CurrentDb.Containers." & [tblObjects].[ContainerName] & ".Documents(" & [MsysObjects].[NAME] & ").Properties(""""description"""") AS ObjDscp, [MsysObjects].[Flags], [MsysObjects].[DateCreate], [MsysObjects].[DateUpdate] " & vbCrLf & _
"FROM [MsysObjects] INNER JOIN [tblObjects] ON [MsysObjects].[Type] = [tblObjects].[ObjectTypeID] " & vbCrLf & _
"WHERE (" & Criteria & ") " & vbCrLf & _
"ORDER BY [MsysObjects].[Type];"

 

[vbaInet]

Ok, because you're passing it parameters, i.e. container and document type, it needs to be done via a function which resides in a Module.