[AccessD] Classes, Recordsets, and problems inbetween - SOMEPROGRESS

John Colby jcolby at colbyconsulting.com
Mon May 12 12:37:59 CDT 2003


Marcus,

Create two classes - ClsA and ClsB - cutting and pasting in the code below.
Then create a module, cutting and pasting the code for the module below.

Explanation:

Module one Init():

Sets a NEW Cls A
Sets a NEW cls B - Note this is a local DIM inside the function.  I did this
to point out and demonstrate that the initialized pointer mclsB is stored
correctly inside of ClsA.  Even though it loses scope when Module1 Init()
finishes running, ClsB still stays in existence because a pointer to it
exists in ClsA.  ClsA stays in existence since it is a module level variable
in Module1.

Calls ClsA's Init() passing in ClsB

Module One Test():

Calls a method in ClsA which displays the name of ClsA
Calls a method of ClsA which calls a method of ClsB which displays ClsB's
name

'***************

'Module1
Option Compare Database
Option Explicit
Dim mclsA  As clsA
Function Init()
Dim mclsB As clsB
    Set mclsA = New clsA
    Set mclsB = New clsB
    mclsA.Init mclsB
End Function

Function Test()
    MsgBox mclsA.Name
    MsgBox mclsA.ChildClassName
End Function

'***************

ClsA has two methods, one is an Init() which can be called by anyone with
access to the pointer to CslA.  The Init() is passed a pointer to ClsB.
ClsB MUST BE INITIALIZED already.  While this isn't strictly true, for our
purposes it is.

The Init() of ClsA simply takes the pointer to ClsB and stores it into a
class level variable
Name() returns ClsA's name
ChildClassName calls mclsChild.name to get the name of whatever class is
pointed to by mclsChild

'***************
'ClsA
Option Compare Database
Option Explicit
Const mcstrName As String = "ClsA"

Dim mclsChild As clsB

Function Init(lclsChild As clsB)
    Set mclsChild = lclsChild
End Function
Private Sub Class_Initialize()
    MsgBox "Initializing " & mcstrName
End Sub
Public Function Name() As String
    Name = mcstrName
End Function
Public Function ChildClassName() As String
    ChildClassName = mclsChild.Name
End Function

'***************
ClsB does nothing but return it's own name
'***************
'ClsB
Option Compare Database
Option Explicit

Const cstrName As String = "ClsB"
Private Sub Class_Initialize()
    MsgBox "Initializing " & cstrName
End Sub
Public Function Name() As String
    Name = cstrName
End Function

'***************


John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Marcus
Tewksbury
Sent: Monday, May 12, 2003 10:59 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Classes, Recordsets, and problems inbetween -
SOMEPROGRESS


Jim,

How do I declare my class?  From memory past I remember in OO needing a
declaration of all the classes methods and properties at the top of the
class.  Is this the case with VBA?

Right now - in the form code - I have something like...

Dim oCampaignInvoice As New CampaignInvoice
Call oCampaignInvoice.Load_byInvoiceOrder(oInvoice, Me.cboOrderID)

As you suggested in a later email - I was able to create a (Search)rst
property for my class - which I could set, and then access from within the
Load functions (without passing in any parameters).

Also, in a later email I suggested that passing in a recordset as a variant
and then trying to recast to a recordset doesn't work.  The variant is
actually a multidimensional array.  It bombed when I loaded data.

I'll try playing around with passing in clones.

And to John Colby - could you please explain more about the parameters were
using.  Specifically what I am trying to accomplish is passing a dataclass
(inclusive of recordset) to another data class.

Open to comment from anyone!  My reason for passing one dataclass to another
- is to build the SQL for say retrieving all InvoiceDetails for a given
Invoice.

Any more thoughts out there?

- Tewks


>From: "Jim Lawrence (AccessD)" <accessd at shaw.ca>
>Reply-To: accessd at databaseadvisors.com
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] Classes, Recordsets, and problems inbetween -
>SOME	PROGRESS
>Date: Mon, 12 May 2003 02:38:54 -0700
>
>Hi Marcus:
>
>I have been using code like:
>
>Dim rsExpiredCards As New ADODB.Recordset
>
>Set rsExpiredCards = ExpiredCards().Clone
>With rsExpiredCards
>	....
>
>Public Function ExpiredCards() As ADODB.Recordset
>
>	Set ExpiredCards = New ADODB.Recordset
>	..... fill recordset
>
>End function
>
>HTH
>Jim
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Marcus
>Tewksbury
>Sent: Sunday, May 11, 2003 9:22 PM
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] Classes, Recordsets, and problems inbetween -
>SOME PROGRESS
>
>
>Well - figured out how to get a recordset in, but now, how to get user
>defined classes?
>
>Public Sub Load_byInvoiceDetail(ByRef msrst As Variant)
>
>     Dim sstrSQL As String
>     Dim sflgNotFirst As Boolean
>
>     Dim xInvoiceDetail As ADODB.Recordset
>     Set xInvoiceDetail = msrst
>
>Ok - so now I can pass in a recordset as a variant and then cast (sort of)
>a
>local variable to it.
>
>The calling routine looks like...
>
>     Dim oOrderDetail As New OrderDetail
>     oOrderDetail.Load_byInvoiceDetail (oInvoiceDetail.mrstGet()) <-
>mrstGet
>returns the underlying recordset for the class
>
>     While Not oOrderDetail.EOF
>         oOrderDetail.FilledFlag = False
>         oOrderDetail.MoveNext
>     Wend
>
>     Dim oInventory As New Inventory
>     oInventory.Load_byOrderDetail (oOrderDetail.mrstGet())
>
>So, while this will work for recordsets - it doesn't for user defined
>classes - I still get the same error.
>
>So, should I be making the switch to VB.net?
>
>
>
>
> >From: "Marcus Tewksbury" <tewksbum at hotmail.com>
> >Reply-To: accessd at databaseadvisors.com
> >To: accessd at databaseadvisors.com
> >Subject: RE: [AccessD] Classes, Recordsets, and problems inbetween
> >Date: Sun, 11 May 2003 23:11:45 -0500
> >
> >In this case it seems to be something specifically with recordsets - with
> >InvoiceDetail I am only passing in an integer - and I;ve tried New'ing
>the
> >recordset in the initialize - I'll give a look into referencing, but I
> >haven't had a problem with any other ADO issue until now!
> >
> >Ugh - this is frustrating!
> >
> >
> >>From: "Haslett, Andrew" <andrew.haslett at ilc.gov.au>
> >>Reply-To: accessd at databaseadvisors.com
> >>To: "'accessd at databaseadvisors.com'" <accessd at databaseadvisors.com>
> >>Subject: RE: [AccessD] Classes, Recordsets, and problems inbetween
> >>Date: Mon, 12 May 2003 13:32:56 +0930
> >>
> >>lol - we've only finally got overloading and inheritance into VB.Net!
> >>Very
> >>doubtful we can do that in VBA.
> >>
> >>I was about to ask if you've tried passing byRef so that ones out the
> >>door.
> >>
> >>Another wild stab -> but I suppose this is still worth asking.  Haven't
> >>forgot the references to ADO??
> >>
> >>Perhaps try declaring mrst with a NEW instance:
> >>
> >>Private mrst As *NEW* ADODB.Recordset
> >>
> >>...but why would it work for InvoiceDetail and not OrderDetail??
> >>
> >>Cheers,
> >>A
> >>
> >>-----Original Message-----
> >>From: Marcus Tewksbury [mailto:tewksbum at hotmail.com]
> >>Sent: Monday, 12 May 2003 1:28 PM
> >>To: accessd at databaseadvisors.com
> >>Subject: Re: [AccessD] Classes, Recordsets, and problems inbetween
> >>
> >>
> >>Yeap
> >>
> >>mrst has been declared...
> >>
> >>Option Compare Database
> >>Option Explicit
> >>
> >>Private mlngInvoiceDetailID As Long
> >>Private mlngInvoiceID As Long
> >>Private mlngOrderDetailID As Long
> >>Private mlngProductID As Long
> >>Private mintShipped As Integer
> >>Private mintCanceled As Integer
> >>Private mintBackOrdered As Integer
> >>Private mcurExtendedPrice As Currency
> >>Private mflgSubstituteFlag As Boolean
> >>Private mdteDateAdded As Date
> >>Private mdteLastUpdated As Date
> >>Private mflgActiveFlag As Boolean
> >>
> >>Private mrst As ADODB.Recordset  <- Here
> >>Private mflgAddFlag As Boolean
> >>Private mflgDirtyFlag As Boolean
> >>
> >>The problem seems to be directly related to passing a recordset into the
> >>class - through a sub or function.  I've tried making
> >>
> >>Public Function Load_byInvoiceDetail
> >>
> >>a sub and function - and have tried passing byVal and ByRef.  For
>testing
> >>I
> >>changed the paramter to (xrst as ADODB.recordset) and then passed in a
> >>newly
> >>
> >>created, plain recordset and it still bombs.
> >>
> >>Of course, another question that stems from this - if you look at what
>my
> >>code is trying to accomplish - can you overload the initialize method?
> >>That
> >>
> >>would be helpful!
> >>
> >>Thanks a million,
> >>
> >>- Tewks
> >>
> >>
> >>
> >>
> >>
> >> >From: "Michael R Mattys" <michael.mattys at adelphia.net>
> >> >Reply-To: accessd at databaseadvisors.com
> >> >To: <accessd at databaseadvisors.com>
> >> >Subject: Re: [AccessD] Classes, Recordsets, and problems inbetween
> >> >Date: Sun, 11 May 2003 23:09:37 -0400
> >> >
> >> >Marcus,
> >> >
> >> >Has mrst been set = to an ADO recordset
> >> >in the Declarations section?
> >> >
> >> >Mike Mattys
> >> >
> >> >----- Original Message -----
> >> >From: "Marcus Tewksbury" <tewksbum at hotmail.com>
> >> >To: <accessD at databaseadvisors.com>
> >> >Sent: Sunday, May 11, 2003 10:48 PM
> >> >Subject: [AccessD] Classes, Recordsets, and problems inbetween
> >> >
> >> >
> >> > > Hi everyone,
> >> > >
> >> > > Ok - here is my issue - I thought I'd be really fancy and start
> >> > > encapsulating my tables in data classes - using properites and all
> >>that
> >> >good
> >> > > stuff.  One issue that I am having is trying to pass in recordsets
> >>(or
> >> >other
> >> > > classes that also contain recordsets) into a class always gives me
>a
> >> >Type
> >> > > Mismatch or 438 error.
> >> > >
> >> > > The class files and code behind all reside in the same project.
> >> > >
> >> > > Any thoughts?  I'm stumped!
> >> > >
> >> > >
> >> > > Here are 2 snippets
> >> > >
> >> > >     Dim oInvoiceDetail As New InvoiceDetail
> >> > >     oInvoiceDetail.Load_byInvoiceID (Me.InvoiceID)
> >> > >
> >> > >     Dim oOrderDetail As New OrderDetail
> >> > >     oOrderDetail.Load_byInvoiceDetail (oInvoiceDetail) <-CRASHES
>HERE
> >> > >
> >> > >     While Not oOrderDetail.EOF
> >> > >         oOrderDetail.FilledFlag = False
> >> > >         oOrderDetail.MoveNext
> >> > >     Wend
> >> > >
> >> > > and ...
> >> > >
> >> > > Public Function Load_byInvoiceDetail(ByVal xInvoiceDetail As
> >> >InvoiceDetail)
> >> > >
> >> > >     Dim sstrSQL As String
> >> > >     Dim sflgNotFirst As Boolean
> >> > >
> >> > >     sstrSQL = "SELECT * FROM OrderDetail"
> >> > >
> >> > >     While Not xInvoiceDetail.EOF
> >> > >         If Not sflgNotFirst Then
> >> > >             sstrSQL = sstrSQL + " WHERE OrderDetailID = " &
> >> > > xInvoiceDetail!OrderDetailID & ")"
> >> > >             sflgNotFirst = True
> >> > >         Else
> >> > >             sstrSQL = sstrSQL + " OR (OrderDetailID = " &
> >> > > xInvoiceDetail!OrderDetailID & ")"
> >> > >         End If
> >> > >         xInvoiceDetail.MoveNext
> >> > >     Wend
> >> > >
> >> > >     With mrst
> >> > >         .CursorLocation = adUseClient
> >> > >         .CursorType = adOpenDynamic
> >> > >         .LockType = adLockBatchOptimistic
> >> > >         .Source = sstrSQL
> >> > >         .Open
> >> > >     End With
> >> > >
> >> > >     Call Scatter
> >> > >
> >> > > End Function
> >> > >
> >> > > _________________________________________________________________
> >> > > MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
> >> > > http://join.msn.com/?page=features/virus
> >> > >
> >> > > _______________________________________________
> >> > > AccessD mailing list
> >> > > AccessD at databaseadvisors.com
> >> > > http://databaseadvisors.com/mailman/listinfo/accessd
> >> > > Website: http://www.databaseadvisors.com
> >> >
> >> >_______________________________________________
> >> >AccessD mailing list
> >> >AccessD at databaseadvisors.com
> >> >http://databaseadvisors.com/mailman/listinfo/accessd
> >> >Website: http://www.databaseadvisors.com
> >>
> >>_________________________________________________________________
> >>The new MSN 8: smart spam protection and 2 months FREE*
> >>http://join.msn.com/?page=features/junkmail
> >>
> >>_______________________________________________
> >>AccessD mailing list
> >>AccessD at databaseadvisors.com
> >>http://databaseadvisors.com/mailman/listinfo/accessd
> >>Website: http://www.databaseadvisors.com
> >>
> >>IMPORTANT - PLEASE READ ********************
> >>This email and any files transmitted with it are confidential and may
> >>contain information protected by law from disclosure.
> >>If you have received this message in error, please notify the sender
> >>immediately and delete this email from your system.
> >>No warranty is given that this email or files, if attached to this
> >>email, are free from computer viruses or other defects. They
> >>are provided on the basis the user assumes all responsibility for
> >>loss, damage or consequence resulting directly or indirectly from
> >>their use, whether caused by the negligence of the sender or not.
> >>_______________________________________________
> >>AccessD mailing list
> >>AccessD at databaseadvisors.com
> >>http://databaseadvisors.com/mailman/listinfo/accessd
> >>Website: http://www.databaseadvisors.com
> >
> >_________________________________________________________________
> >MSN 8 with e-mail virus protection service: 2 months FREE*
> >http://join.msn.com/?page=features/virus
> >
> >_______________________________________________
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
>
>_________________________________________________________________
>MSN 8 with e-mail virus protection service: 2 months FREE*
>http://join.msn.com/?page=features/virus
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list