Problem with Class Module

[JackKaptijn]

I have an Access 2007 database.
In it there are Class Modules but also Modules.
I have transferred all modules into Class Modules because of the use of variables and values in a multi user environment.

Her is an example of a class module which does a validation check on a bankaccount number:

Function ElfProef(ByVal Rekeningnummer As Variant) As Boolean
Dim blnElfProef As Boolean
Dim i As Integer, iTest As Integer
blnElfProef = False
Select Case Len(Nz(Rekeningnummer, ""))
Case 7
blnElfProef = True
Case 9
For i = 9 To 1 Step -1
iTest = iTest + (i * Val(Mid(Rekeningnummer, (10 - i), 1)))
Next i
If iTest Mod 11 = 0 Then
blnElfProef = True
End If
Case 10
For i = 1 To 10
iTest = iTest + (i * Val(Mid(Rekeningnummer, i, 1)))
Next i
If iTest Mod 11 = 0 Then
blnElfProef = True
End If
Case Else
End Select

ElfProef = blnElfProef

End Function

The problem.....
When I create a query you can include custom build functions.
But only functions in modules are visible and NOT functions in a Class Module.

Anyone a suggestion?

 

[jdraw]

I think you have a standard module.
Class modules are not something that is involved simply because you have a multi user set up.

What you have shown is a function that properly resides in a standard Module.

See this link for info on Class Modules
http://www.utteraccess.com/forum/Module-Class-Module-t1945450.html

Here is an M$0ft short tutorial
http://support.microsoft.com/kb/209968

 

[JackKaptijn]

Thanks for the links.
But it did not answer my question. I will give some extra information.

I have a Module in my Access database: see attachment Module.JPG
I also have a ClassModule: see attachment ClassModule.JPG
In a query you CAN use a function in the module: see Query1.JPG
But functions in a ClassModule ar NOT visible.

The funtion works perfectly in VBA. For example:

Private Sub Account_BeforeUpdate(Cancel As Integer)
Dim cls As New Bankcontroles

If Not IsNull(Me.Accountnumber) Then
If cls.ElfProef(Me.Accountnumber) = False Then
Me.Undo
MsgBox "Accountnumber is not valid."
End If
End If

Set cls = Nothing
End Sub

 

[jdraw]

The only question I saw in your post was

Anyone a suggestion?

My comment/response was you really don't have a class module. You have a function that could be stored in a standard module and invoked from a query or an event. You are using a User defined Type
see http://msdn.microsoft.com/en-us/library/office/gg278730.aspx

This is info about MsAccess Class Module.
http://www.techrepublic.com/article...odules-in-an-access-database-solution/5031814

I don't use Class modules, but have created them in the past.

I did some searching to see if I could find another explanation.
Here is one I found
http://www.tek-tips.com/viewthread.cfm?qid=574949

 

[thechazm]

Adding my 2 cents but you can just make some functions in a standard module that fetches the info from your class modules so the query can get the data. I do not think you can use a class module in queries due to you having to create the class object before using it. So if you make a private static function or just a regular function in a generic module then you should be able to create the class object and then return that to your query through your regular function.

 

[ChrisO]

I think this may be the problem…

>>I have transferred all modules into Class Modules because of the use of variables and values in a multi user environment.<<

That should not be a problem unless users are sharing a front end.

Have you split your database and given each user their own front end?

Chris.

 

[Pat Hartman]

I have transferred all modules into Class Modules because of the use of variables and values in a multi user environment.

I don't know where you got the idea that this was necessary or even desirable. This is not necessary and has no impact on whether the database is shared or not. Change the modules back to standard modules and they will work in queries.

Just to clarify for you - each user MUST have his own copy of the FE which is stored locally on his C: drive and the FE is connected via linked tables to the BE which is stored on a shared server. Multiple users should not share the same copy of the FE under any conditions. If one person has a problem that locks up his computer, that will affect all the other users sharing the FE and may corrupt the FE so best practice is - don't do it. The use of class modules (misguided though it was) has no impact on this advice.