VBA variable to reference one of many possible class objects How do I pointer in VBA?

M

mdlueck

Sr. Application Developer
Local time
Today, 08:59
Joined
Jun 23, 2011
Messages
2,649
With Access 2007 - I am taking special care to construct an area of an application such that the classes and forms will work for multiple different same DB schema tables. This is for the administrative interface of look-up / pick list fields in this application. Thus the reason that the DB schema is the same for multiple different tables.

At one spot of the code, I need to provide the ID value from DB record located in a DB table class object. "Oops, can't hard code that!" Otherwise the code is not portable across the tables.

Is there some way in VBA to point to a class object from a different static variable name, such that I may type in that static name (pointer), and what ever object the pointer is pointing at actually receives the events? If so, I could use a Select Case statement to handle the various different class objects which the form is capable of supporting.

Suggestions how to pointer in VBA...???

Oh.. on the receiving side of the Class Method, I do receive the object ByRef. So I suppose I could simply list all of the various class types supported each in a different Case of the Select... each time typing the correct Class Object for that particular Case as the arg that time to the same class\method being called. hhhhmmm.... I believe that would work. If anyone has other suggestions, please post. Thank you.
 
You can implement an interface in VBA. Then you can instantiate any type that implements that interface and assign it to a variable of that interface's type.

So you could define an ICompany interface, and implement it in your cCustomer and cSupplier classes. The you can write code that manipulates the ICompany object, and at runtime that ICompany might contain a cCustomer or a cSupplier, but it wouldn't matter which, because they both implement ICompany. Does that make sense?

This way you can have many different classes that for a certain subset of operations you handle exactly the same way.

Mark
 
Sounds promising. My Access 2007 book does not seem to mention Interface in its index. I quickly searched the Internet and found references to using such with DLL calls, not VBA classes.

Perhaps could you kindly point me at a code example so I could have a look? Thanks!
 
Try searching on Implements, which is the keyword you use to implement an interface. And I'll see if I can find any references.
 
Here, check this out, see if that makes any sense. It's a cool concept if you can get your head around it.
 
Thank you for finding where it is documented, lagbolt.

After posting, I realized that I had already taken care of the need for a pointer another way. I actually have one class which is capable of dealing with any standard pick list table. So I do not have n number of different classes, that one class is capable itself of dealing with n different tables. Thus I am able to "hard code" the object name into the Form and ValidationClass code. fffeeewww.... :cool:
 
All right, today it is officially time for VBA to do pointers!
biggrin.gif


I can download data from BE tables as I do that code in the button which is opening the Universal Pick List Editor so it (the button) knows which DB object it is suppose to use in order to populate the FE temp table.

Today it is time for the class which handles that FE temp table to save changes back to the BE DB. Now I need a handle (pointer) to that BE DB table object the button used to populate the FE temp table. This is now common / shared code, so my plan was to have the button pass into the shared class the DB object the class should use when the class needs to save changes back to the BE DB. I coded as follows:

Code on the button which is opening the Pick List editor form:
Code: 
  Dim strDocName As String

  strDocName = "adminpicklistsstandard"

 'Prepare objects to open a Multiple Items form
  If Not ObjMultipleItemsFormsInit.Prepare(Me, "partsbuyercodeselect") Then
    Call errorhandler_MsgBox("Form: Form_adminpicklists, Subroutine: btnPickBuyerCode_Click(), Failed: ObjMultipleItemsFormsInit.Prepare()")
    GoTo Exit_btnPartsBuyerCode_Click
  End If

  'Download the current picklist values into the FE temp table
  Call ObjPartsBuyerCodeTypeTbl.EmptyLocalTmpTbl
  Call ObjPartsBuyerCodeTypeTbl.RefreshLocalTmpTbl

  'Prep the correct Admin PickList Object
  Call ObjAdminPickListStandardTbl.Clear
  ObjAdminPickListStandardTbl.FormCustomCaption = Me.btnPartsBuyerCode.Caption
  ObjAdminPickListStandardTbl.FETempTableName = "tmptblqry_partsbuyercodetype"
  ObjAdminPickListStandardTbl.RecordSource = "frmqryclsObjPartsBuyerCodeTypeTbl_RefreshLocalTmpTbl"[COLOR=Red][B]'Bang right here!!!
  ObjAdminPickListStandardTbl.BEDBTbl = ObjPartsBuyerCodeTypeTbl[/B][/COLOR]

  'This call must come after the object is completely set up
  Call ObjAdminPickListStandardTbl.PostRefreshLocalTmpTbl
  
  'Open the parts dialog
  flgInitArchitecture = True
  DoCmd.OpenForm strDocName
So in this DB class I added the following to hopefully receive the BE DB Object:

Code: 
'Other class attributes not DB table related
Dim ObjBEDBTbl As Object


'ObjBEDBTbl API's
Public Property Get BEDBTbl() As Object
On Error GoTo Err_BEDBTbl

  BEDBTbl = ObjBEDBTbl

Exit_BEDBTbl:
  Exit Property

Err_BEDBTbl:
  Call errorhandler_MsgBox("Class: clsObjAdminPickListStandardTbl, Property: Get BEDBTbl()")
  Resume Exit_BEDBTbl

End Property

Public Property Let BEDBTbl(ByVal newBEDBTbl As Object)
On Error GoTo Err_BEDBTbl

[B][COLOR=Red]'Blows up right here...
  ObjBEDBTbl = newBEDBTbl[/COLOR][/B]

Exit_BEDBTbl:
  Exit Property

Err_BEDBTbl:
  Call errorhandler_MsgBox("Class: clsObjAdminPickListStandardTbl, Property: Let BEDBTbl()")
  Resume Exit_BEDBTbl

End Property
And finally how I am trying to test that I have a valid point to a DB class object...

Code: 
Public Sub test()

Debug.Print ObjBEDBTbl.test

End Sub
I am entering into the immediate window:

Code: 
ObjAdminPickListStandardTbl.test
and the DB object itself I have added a test method to, so I expect that to fire.

The red LOC blows up in the setter with the following error message:
Code: 
Date: 20120504 Time: 11:14:10 UserID: c_mlueck
AppErrorMsg: Class: clsObjAdminPickListStandardTbl, Property: Let BEDBTbl()
Error Source: Fandango_FE
Error Number: 91
Error Description: Object variable or With block variable not set
MessageText: Error not found.
I suppose it is something easy, like declaring the class type to be Object (since it could be any one of the DB class objects) is not agreeable to VBA.

Suggestions please?
 
Last edited:
When you assign an object to a variable you need to use the 'Set' keyword, so...
Code: 
dim rst as dao.recordset
[COLOR="DarkRed"]set[/COLOR] rst = currentdb.openrecordset("YourTable")
...and...
Code: 
Public Property [COLOR="DarkRed"]Set[/COLOR] BEDBTbl(ByVal newBEDBTbl As Object)
  [COLOR="DarkRed"]Set[/COLOR] ObjBEDBTbl = newBEDBTbl
End Property
 
lagbolt said:
When you assign an object to a variable you need to use the 'Set' keyword
Click to expand...

Bing bing bing bing!!!! :D Correct solution!!! Working much better, and the instance of the class does appear to survive the pointerization! Woo hoo!!
 
Gotta love it when a plan comes together. :)
 
So, for people coming along finding this thread later...

1) Access 2007 VBA does support the concept of "pointers"
2) When passing custom class types, use Object as the type to successfully receive it
3) Remember to use Set keyword when transferring the object to/from a storage variable
4) Once successfully received, you may use that storage variable name as if it were the original object itself... same syntax, just that the object is known in that context as a different (pointer) name.
 
Public Property Let BEDBTbl(ByVal newBEDBTbl As Object)
On Error GoTo Err_BEDBTbl

'just a thought

if newBEDBTbl is nothing then
....
end if



'Blows up right here...
ObjBEDBTbl = newBEDBTbl

'second thought
'maybe usage is
SET ObjBEDBTbl = newBEDBTbl

Exit_BEDBTbl:
Exit Property
Click to expand...

----
couple of thoughts. I expect the byval is ignored when passing an object, but i shouldn't think that matters

maybe test it to see if it is nothing first, and maybe you need to use SET with objects - see above
 
All right, cruising right along, and bumped into another type of pointer related topic I do not remember coding such a solution in VBA before.

Code: 
  'Fetch the values found in the FE temp table
  'and send them directly to the real DB object for the BE table
  ObjBEDBTbl.id = Nz(adoRStt!id, 0)
  ObjBEDBTbl.sort = Nz(adoRStt!sort, 0)
  ObjBEDBTbl.active = Nz(adoRStt!active, False)
  ObjBEDBTbl.title = Nz(adoRStt!title, vbNullString)

  'Detect INSERT / UPDATE based on the presence of a SQL Server ID value
  If ObjBEDBTbl.id = 0 Then
    'INSERT mode
    Call ObjBEDBTbl.Insert
  Else
    'UPDATE mode
    'Look up the object of the pre-edit state of this record by SQL Server ID
    Set thisOldRec = ObjAdminPickListStandardItems.Item(CStr(ObjBEDBTbl.id))

    'Step through each field looking for differences
    For intFieldCounter = 1 To CInt(strCheckColListArray(0))
      'Obtain the next field name to be checked
      strThisField = strCheckColListArray(intFieldCounter)
[COLOR=Red]Debug.Print "strThisField: " & strThisField[/COLOR]
      
    Next intFieldCounter

    If UpdateFlg = True Then
      Call ObjBEDBTbl.Update
    End If
  End If
Where the red Debug LOC is, I need to use the strThisField variable in order to compare one field of the DB object to the value of that same field in the thisOldRec object. In Rexx / Object Rexx I would use the VALUE BIF in order to obtain the actual object in memory that is named the same as the result of the string concatenation VALUE is evaluating. What is the VBA equivalent?

I need to compare:
Code: 
If ObjBEDBTbl.[B]sort[/B] <> thisOldRec.[B]sort[/B]
  UpdateFlg = True
  Exit For
End If
Where the sort wound be able to be variablized to refer to each column to be compared.

In Rexx I would use:

Code: 
if Value('ObjBEDBTbl.' || strThisField) \= Value('thisOldRec.' || strThisField) then
 
Well I came across Eval() while searching for a solution. It looked similar to VALUE in Rexx. Thus I coded:

Code: 
    'Step through each field looking for differences
    For intFieldCounter = 1 To CInt(strCheckColListArray(0))
      'Obtain the next field name to be checked
      strThisField = strCheckColListArray(intFieldCounter)
Debug.Print "strThisField: " & strThisField
      [COLOR=Red]If Eval("ObjBEDBTbl." & strThisField) <> Eval("thisOldRec." & strThisField)[/COLOR]
        UpdateFlg = True
        Exit For
      End If
    Next intFieldCounter
However, VBA will not accept the red LOC, siting "Syntax Error" and nothing more specific than that... hhhmmm..........

Any suggestions as to a syntax which VBA will accept? TIA!
 
I found that red LOC was missing a trailing Then keyword, oops! ;)

So, code updated as follows, Eval still not able to do what I am looking for...

Code: 
    'Step through each field looking for differences
    For intFieldCounter = 1 To CInt(strCheckColListArray(0))
      'Obtain the next field name to be checked
      strThisField = strCheckColListArray(intFieldCounter)
Debug.Print "strThisField: " & strThisField
Debug.Print "ObjBEDBTbl." & strThisField
Debug.Print "thisOldRec." & strThisField
[COLOR=Red]Debug.Print Eval("ObjBEDBTbl." & strThisField)[/COLOR]
Debug.Print Eval("thisOldRec." & strThisField)
      If Eval("ObjBEDBTbl." & strThisField) <> Eval("thisOldRec." & strThisField) Then
        UpdateFlg = True
        Exit For
      End If
    Next intFieldCounter
Immediate window output:
Code: 
strThisField: sort
ObjBEDBTbl.sort
thisOldRec.sort
Resulting in the error:
Code: 
Date: 20120508 Time: 13:17:02 UserID: c_mlueck
AppErrorMsg: Class: clsObjPartsTbl, Function: Priv_CommitToBEFetch(), Values: id=2 title=120002 / Joe User
Error Source: Fandango_FE
Error Number: 2482
Error Description: Fandango FE can't find the name 'ObjBEDBTbl' you entered in the expression.
MessageText: The UPS service could not access the specified Comm Port.
Gotta love Access error messages, the MessageText obviously is bogus for this error. Suggestions please?
 
Last edited:
I think eval requires a function specification (inside inverted commas), not a method of an object, unfotunately
 
You must log in or register to reply here.

Similar threads

MajP
Using A Collection Class to Raise Common Event
2
Replies
33
Views
748
Pat Hartman
P
jwcolby54
The Garbage collector in VBA
Replies
7
Views
528
jwcolby54
jwcolby54
jwcolby54
The message class - RAISING events
2 3
Replies
46
Views
1,062
jwcolby54
jwcolby54
J
Search for possible declarations
Replies
9
Views
498
sonic8
S
Uncle Gizmo
How to Simplify MS Access Form Searches Using Class Modules and Collections to Pair Text Boxes and Option Groups for Efficient SQL Query Generation
2
Replies
25
Views
1,359
MarkK
MarkK

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom