Unknown Jet Error with Function

[xweyer]

I'm having a problem that I can't figure out. I've managed to pair it down to the bare essentials below.

In a module I have a public function and variable as below.

Public strObjName As String

Public Function GetObjName()
GetObjName = strObjName
End Function

In the On Click event of a button on a form I have
strObjName = Me.Name

And I've built this simple query
SELECT GetObjName() AS Expr1
FROM T_Obj_Name;

If I set a break point after strObjName has been set on the form and type "?GetObjName" in the immediate window
the name of the form is returned correctly. But if I try to run the query it fails with an "Unknown Jet Error". Clicking the help button displays "There was a type mismatch when creating a table validation rule or a CHECK constraint on a column. (Error 3072)"

I feel like I must be missing something obvious but the error message and the fact the function works in one instance and not the other makes no sense to me.

I know there are plenty of other ways of pulling the form's name but I'd like to understand what the issue is here.

 

[Remou]

Is this code in the module belonging to a form? If so, you will need to move the declaration of strObjName to a standard module.

http://lessthandot.com

 

[TheAceMan1]

How are ya xweyer . . .

Whats the point in adding a single value to a column in a query?

In any case, try adding the [blue]PrimaryKey[/blue] field in the query grid.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[xweyer]

Remou
Yes, the function is in a standard module. (The only part behind a form being the "On Click" code.)

TheAceMan1
The query I've posted here is just a minimalist version of the actual query I wish to create. My thought was to exclude any extraneous factors since I see no reason that the posted version should not return a record. (The same error does occur regardless of which other fields are included.)

My ultimate aim is to build a table off the query but I need to pull the name of the current object into the query in order to do so.

 

[Remou]

I suspect the name is going astray somewhere. Have you tried setting GetObjectName to a value to see if this is the case?

Public Function GetObjName()
   GetObjName = "MyForm"
End Function

FWIW, I tried your code and it worked form me.

http://lessthandot.com

 

[TheAceMan1]

xweyer . . .

I believe the problem is that [blue]the function doesn't reference any field in the table! Its like a rogue custom field that has nothing to do with the query. [/blue] This can be over come by passing any table field thru the function like so:

[blue]SELECT GetObjName([[purple][B][I]PrimarykeyFieldname[/I][/B][/purple]]) AS Expr1
   

Public Function GetObjName([purple][B][I]Dat[/I][/B][/purple])
   GetObjName = strObjName
End Function[/blue]

[blue]Note that the function doesn't use the passed value![/blue] Were just getting the query to believe the function is related to some field. This should result in the custom field displaying a single value down the column.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

xweyer . . .

[blue]?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]