|
|
|
date: Mon, 30 Jun 2008 03:46:00 -0700,
group: microsoft.public.access.modulescoding
back
Problem with Custom Objects in Access 2007
We have recently upgraded from Office 2003 to Office 2007.
The .mdb we've been using for inventory, which is jammed full of 4 years
worth of VBA
code, exhibited several problems after the upgrade. I've managed to solve
all of the
problems but one:
Whenever I declare a custom object in the code module of a subform the
subform, and
all of the controls on it, stop responding to events.
It worked perfectly in 2003 so I did some research and gathered that it
might be the
result of security restrictions. So I ...
1. Trusted the locations of the front and back end.
2. Enabled ALL macros.
3. Turned off Sandbox mode.
But still no luck.
If I comment out all references to the custom object in the subform code and
put
breakpoints in the On Open and On Load events I can see that it reaches those
procedures. But as soon as I add the object declaration, events are no
longer triggered.
The code for the clsCTRLcoll definition is ...
-------------------------------------------------------------------------------------
'defines the Collection Object.
Private objControl As clsCTRLcolMethods
Public objCollection As Collection
Private Sub Class_Initialize()
Set objCollection = New Collection
End Sub
Public Function Add(Rich As TextBox, Licn As TextBox, _
Comp As TextBox, Modl As TextBox, Drvr As ComboBox, Bar As Line, _
Box As Rectangle, Tkey As String) As MyRich
Set objControl = New clsCTRLcolMethods
Set objControl.objRich = Rich
Set objControl.objLicn = Licn
Set objControl.objComp = Comp
Set objControl.objModl = Modl
Set objControl.objDrvr = Drvr
Set objControl.objBar = Bar
Set objControl.objBox = Box
objControl.strID = Tkey
objCollection.Add objControl, Tkey
End Function
Private Sub Class_Terminate()
Set objCollection = Nothing
End Sub
---------------------------------------------------------------------------------------
Basically it's used to scoop up as many as 10 identical groups of
pre-designed controls
on a subform and add them to a collection.
The code for clsCTRLcolMethods ...
---------------------------------------------------------------------------------------
'Handles drag n drop events, Pull List and Tree-view population
Public WithEvents objRich As TextBox
Public WithEvents objLicn As TextBox
Public WithEvents objComp As TextBox
Public WithEvents objModl As TextBox
Public WithEvents objDrvr As ComboBox
Public WithEvents objBar As Line
Public WithEvents objBox As Rectangle
Public strID As String
Private Sub objRich_Click()
DoCmd.OpenReport "PullTruck", acViewPreview, , , , strID
End Sub
Private Sub objRich_OLEDragDrop(Data As TextBox.DataObject, Effect As Long,
Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim SQL As String, ItmX As Node, ItmY As ListItem, rsPull As Recordset
Dim DB As Database, intSt As Integer
Set DB = CurrentDb
If Data.GetFormat(ccCFText) Then
If Data.GetData(ccCFText) = "BoltL" Then
For Each ItmY In Forms!movement.PullLst.ListItems
With ItmY
If .Selected Then
SQL = "UPDATE Pull SET Pull.PullTruck = " & _
DLookup("TruckID", "Trucks", "LicNum = '" & objLicn
& "'") & _
", Pull.TruckIntern = '" & strID & "' " & _
"WHERE Pull.PullItem = " & CLng(.Text)
DoCmd.RunSQL SQL
End If
End With
Next ItmY
Else
On Error GoTo Err_objRich_OLEDragDrop
With Forms!movement.PullTree
If TypeName(.Nodes(.SelectedItem.Index)) <> "Nothing" Then
Set ItmX = .Nodes(.SelectedItem.Index)
If Left(ItmX.Key, 1) = "I" Then
SQL = "UPDATE Pull SET Pull.PullTruck = " & _
DLookup("TruckID", "Trucks", "LicNum = '" & objLicn
& "'") & _
", Pull.TruckIntern = '" & strID & "' " & _
"WHERE Pull.PullItem = " & CLng(Mid(ItmX.Key, 2))
DoCmd.RunSQL SQL
Else
FillTree ItmX
End If
End If
On Error GoTo 0
End With
End If
End If
'Line below populates the objRich objects in the collection with table
data.
'commented out external reference for testing purposes.
'Forms!movement!TruckSubf.Form.RchgAll
Exit_objRich_OLEDragDrop:
Exit Sub
Err_objRich_OLEDragDrop:
If Err.Number = 91 Then
MsgBox "No Item was selected", vbOKOnly
ElseIf Err.Number = 13 Then
Resume Exit_objRich_OLEDragDrop
Else
MsgBox Err.Description
End If
Resume Exit_objRich_OLEDragDrop
End Sub
Private Sub FillTree(ByRef Brnch As Node)
Dim N As Integer, N2 As Integer, Y As Integer, SQL As String
With Forms!Movement.PullTree
If Brnch.Children = 0 Then
N = Brnch.FirstSibling.Index
Do
SQL = "UPDATE Pull SET Pull.PullTruck = " & _
DLookup("TruckID", "Trucks", "LicNum = '" & objLicn & "'") & _
", Pull.TruckIntern = '" & strID & "' " & _
"WHERE Pull.PullItem = " & CLng(Mid(.Nodes(N).Key, 2))
DoCmd.RunSQL SQL
If .Nodes(N).Index = Brnch.LastSibling.Index Then Exit Do
N = .Nodes(N).Next.Index
Loop
Else
N = Brnch.Child.FirstSibling.Index
Do
FillTree .Nodes(N)
If .Nodes(N).Index = .Nodes(N).LastSibling.Index Or _
.Nodes(N).Children = 0 Then Exit Do
N = .Nodes(N).Next.Index
Loop
End If
End With
End Sub
------------------------------------------------------------------------------------
As soon I add the declaration ...
Dim MyCtrlCol As New clsCTRLcoll
To the top of the subform's (TruckSubf) code module, the subform stops
generating events.
When I remove the line, the subform and all of the controls on it, behave as
they should.
Minus the functionality my object provides.
The main form, upon which "Trucksubf" sits is called "Movement"
I have List and Treeview controls on the main form from which items are
dragged onto
controls in the subform. The controls on the subform are supposed to be
gathered into
my custom object's collection when the subform loads.
The reason I'm doing it this way is so that I can treat a large number of
groups of
controls as something resembling a control array (Which are not supported in
VBA).
This way I only have to write one set of methods to address all of the
events I'm
interested in.
After close to 30 hours of digging, I'm stumped. I'm guessing it's some kind
of scope
issue or Access 2007 is just plain broken in this respect.
Any ideas?
date: Mon, 30 Jun 2008 03:46:00 -0700
author: Raybo58
Re: Problem with Custom Objects in Access 2007
Found a solution? I was wondering if it was because public objects
of forms are not handled the same way in 2007. Did you move the
objects to out of the form modules into public modules?
(david)
"Raybo58" wrote in message
news:E52B364B-7633-420A-B94C-627CC5282956@microsoft.com...
> We have recently upgraded from Office 2003 to Office 2007.
>
> The .mdb we've been using for inventory, which is jammed full of 4 years
> worth of VBA
> code, exhibited several problems after the upgrade. I've managed to solve
> all of the
> problems but one:
>
> Whenever I declare a custom object in the code module of a subform the
> subform, and
> all of the controls on it, stop responding to events.
>
> It worked perfectly in 2003 so I did some research and gathered that it
> might be the
> result of security restrictions. So I ...
>
> 1. Trusted the locations of the front and back end.
>
> 2. Enabled ALL macros.
>
> 3. Turned off Sandbox mode.
>
> But still no luck.
>
> If I comment out all references to the custom object in the subform code
> and
> put
> breakpoints in the On Open and On Load events I can see that it reaches
> those
> procedures. But as soon as I add the object declaration, events are no
> longer triggered.
>
>
> The code for the clsCTRLcoll definition is ...
> -------------------------------------------------------------------------------------
> 'defines the Collection Object.
> Private objControl As clsCTRLcolMethods
> Public objCollection As Collection
>
> Private Sub Class_Initialize()
> Set objCollection = New Collection
> End Sub
>
> Public Function Add(Rich As TextBox, Licn As TextBox, _
> Comp As TextBox, Modl As TextBox, Drvr As ComboBox, Bar As Line, _
> Box As Rectangle, Tkey As String) As MyRich
>
> Set objControl = New clsCTRLcolMethods
> Set objControl.objRich = Rich
> Set objControl.objLicn = Licn
> Set objControl.objComp = Comp
> Set objControl.objModl = Modl
> Set objControl.objDrvr = Drvr
> Set objControl.objBar = Bar
> Set objControl.objBox = Box
> objControl.strID = Tkey
> objCollection.Add objControl, Tkey
> End Function
>
> Private Sub Class_Terminate()
> Set objCollection = Nothing
> End Sub
> ---------------------------------------------------------------------------------------
>
> Basically it's used to scoop up as many as 10 identical groups of
> pre-designed controls
> on a subform and add them to a collection.
>
>
> The code for clsCTRLcolMethods ...
> ---------------------------------------------------------------------------------------
> 'Handles drag n drop events, Pull List and Tree-view population
> Public WithEvents objRich As TextBox
> Public WithEvents objLicn As TextBox
> Public WithEvents objComp As TextBox
> Public WithEvents objModl As TextBox
> Public WithEvents objDrvr As ComboBox
> Public WithEvents objBar As Line
> Public WithEvents objBox As Rectangle
> Public strID As String
>
> Private Sub objRich_Click()
> DoCmd.OpenReport "PullTruck", acViewPreview, , , , strID
> End Sub
>
> Private Sub objRich_OLEDragDrop(Data As TextBox.DataObject, Effect As
> Long,
> Button As Integer, Shift As Integer, X As Single, Y As Single)
> Dim SQL As String, ItmX As Node, ItmY As ListItem, rsPull As Recordset
> Dim DB As Database, intSt As Integer
> Set DB = CurrentDb
> If Data.GetFormat(ccCFText) Then
> If Data.GetData(ccCFText) = "BoltL" Then
> For Each ItmY In Forms!movement.PullLst.ListItems
> With ItmY
> If .Selected Then
> SQL = "UPDATE Pull SET Pull.PullTruck = " & _
> DLookup("TruckID", "Trucks", "LicNum = '" & objLicn
> & "'") & _
> ", Pull.TruckIntern = '" & strID & "' " & _
> "WHERE Pull.PullItem = " & CLng(.Text)
> DoCmd.RunSQL SQL
> End If
> End With
> Next ItmY
> Else
> On Error GoTo Err_objRich_OLEDragDrop
> With Forms!movement.PullTree
> If TypeName(.Nodes(.SelectedItem.Index)) <> "Nothing" Then
> Set ItmX = .Nodes(.SelectedItem.Index)
> If Left(ItmX.Key, 1) = "I" Then
> SQL = "UPDATE Pull SET Pull.PullTruck = " & _
> DLookup("TruckID", "Trucks", "LicNum = '" & objLicn
> & "'") & _
> ", Pull.TruckIntern = '" & strID & "' " & _
> "WHERE Pull.PullItem = " & CLng(Mid(ItmX.Key, 2))
> DoCmd.RunSQL SQL
> Else
> FillTree ItmX
> End If
> End If
> On Error GoTo 0
> End With
> End If
> End If
>
> 'Line below populates the objRich objects in the collection with table
> data.
> 'commented out external reference for testing purposes.
> 'Forms!movement!TruckSubf.Form.RchgAll
>
> Exit_objRich_OLEDragDrop:
> Exit Sub
>
> Err_objRich_OLEDragDrop:
> If Err.Number = 91 Then
> MsgBox "No Item was selected", vbOKOnly
> ElseIf Err.Number = 13 Then
> Resume Exit_objRich_OLEDragDrop
> Else
> MsgBox Err.Description
> End If
> Resume Exit_objRich_OLEDragDrop
> End Sub
>
> Private Sub FillTree(ByRef Brnch As Node)
> Dim N As Integer, N2 As Integer, Y As Integer, SQL As String
>
> With Forms!Movement.PullTree
> If Brnch.Children = 0 Then
> N = Brnch.FirstSibling.Index
> Do
> SQL = "UPDATE Pull SET Pull.PullTruck = " & _
> DLookup("TruckID", "Trucks", "LicNum = '" & objLicn & "'")
> & _
> ", Pull.TruckIntern = '" & strID & "' " & _
> "WHERE Pull.PullItem = " & CLng(Mid(.Nodes(N).Key, 2))
>
> DoCmd.RunSQL SQL
> If .Nodes(N).Index = Brnch.LastSibling.Index Then Exit Do
> N = .Nodes(N).Next.Index
> Loop
> Else
> N = Brnch.Child.FirstSibling.Index
> Do
> FillTree .Nodes(N)
> If .Nodes(N).Index = .Nodes(N).LastSibling.Index Or _
> .Nodes(N).Children = 0 Then Exit Do
>
> N = .Nodes(N).Next.Index
> Loop
> End If
> End With
> End Sub
> ------------------------------------------------------------------------------------
>
> As soon I add the declaration ...
>
> Dim MyCtrlCol As New clsCTRLcoll
>
> To the top of the subform's (TruckSubf) code module, the subform stops
> generating events.
>
> When I remove the line, the subform and all of the controls on it, behave
> as
> they should.
>
> Minus the functionality my object provides.
>
> The main form, upon which "Trucksubf" sits is called "Movement"
>
> I have List and Treeview controls on the main form from which items are
> dragged onto
> controls in the subform. The controls on the subform are supposed to be
> gathered into
> my custom object's collection when the subform loads.
>
> The reason I'm doing it this way is so that I can treat a large number of
> groups of
> controls as something resembling a control array (Which are not supported
> in
> VBA).
> This way I only have to write one set of methods to address all of the
> events I'm
> interested in.
>
> After close to 30 hours of digging, I'm stumped. I'm guessing it's some
> kind
> of scope
> issue or Access 2007 is just plain broken in this respect.
>
>
> Any ideas?
>
>
>
date: Thu, 17 Jul 2008 18:35:16 +1000
author: david
Re: Problem with Custom Objects in Access 2007
No. I had the code in public modules to begin with. Compiling revealed that a
few of the Access object references in my code prefered more explicit typing
in 2007. In one case I had most of the controls on a form named the same
thing as fields in my tables and the ride got a little bumpy. After nailing
these issues down, everthings works better than it did in 2003.
"david" wrote:
> Found a solution? I was wondering if it was because public objects
> of forms are not handled the same way in 2007. Did you move the
> objects to out of the form modules into public modules?
>
> (david)
>
>
> "Raybo58" wrote in message
> news:E52B364B-7633-420A-B94C-627CC5282956@microsoft.com...
> > We have recently upgraded from Office 2003 to Office 2007.
> >
> > The .mdb we've been using for inventory, which is jammed full of 4 years
> > worth of VBA
> > code, exhibited several problems after the upgrade. I've managed to solve
> > all of the
> > problems but one:
> >
> > Whenever I declare a custom object in the code module of a subform the
> > subform, and
> > all of the controls on it, stop responding to events.
> >
> > It worked perfectly in 2003 so I did some research and gathered that it
> > might be the
> > result of security restrictions. So I ...
> >
> > 1. Trusted the locations of the front and back end.
> >
> > 2. Enabled ALL macros.
> >
> > 3. Turned off Sandbox mode.
> >
> > But still no luck.
> >
> > If I comment out all references to the custom object in the subform code
> > and
> > put
> > breakpoints in the On Open and On Load events I can see that it reaches
> > those
> > procedures. But as soon as I add the object declaration, events are no
> > longer triggered.
> >
> >
> > The code for the clsCTRLcoll definition is ...
> > -------------------------------------------------------------------------------------
> > 'defines the Collection Object.
> > Private objControl As clsCTRLcolMethods
> > Public objCollection As Collection
> >
> > Private Sub Class_Initialize()
> > Set objCollection = New Collection
> > End Sub
> >
> > Public Function Add(Rich As TextBox, Licn As TextBox, _
> > Comp As TextBox, Modl As TextBox, Drvr As ComboBox, Bar As Line, _
> > Box As Rectangle, Tkey As String) As MyRich
> >
> > Set objControl = New clsCTRLcolMethods
> > Set objControl.objRich = Rich
> > Set objControl.objLicn = Licn
> > Set objControl.objComp = Comp
> > Set objControl.objModl = Modl
> > Set objControl.objDrvr = Drvr
> > Set objControl.objBar = Bar
> > Set objControl.objBox = Box
> > objControl.strID = Tkey
> > objCollection.Add objControl, Tkey
> > End Function
> >
> > Private Sub Class_Terminate()
> > Set objCollection = Nothing
> > End Sub
> > ---------------------------------------------------------------------------------------
> >
> > Basically it's used to scoop up as many as 10 identical groups of
> > pre-designed controls
> > on a subform and add them to a collection.
> >
> >
> > The code for clsCTRLcolMethods ...
> > ---------------------------------------------------------------------------------------
> > 'Handles drag n drop events, Pull List and Tree-view population
> > Public WithEvents objRich As TextBox
> > Public WithEvents objLicn As TextBox
> > Public WithEvents objComp As TextBox
> > Public WithEvents objModl As TextBox
> > Public WithEvents objDrvr As ComboBox
> > Public WithEvents objBar As Line
> > Public WithEvents objBox As Rectangle
> > Public strID As String
> >
> > Private Sub objRich_Click()
> > DoCmd.OpenReport "PullTruck", acViewPreview, , , , strID
> > End Sub
> >
> > Private Sub objRich_OLEDragDrop(Data As TextBox.DataObject, Effect As
> > Long,
> > Button As Integer, Shift As Integer, X As Single, Y As Single)
> > Dim SQL As String, ItmX As Node, ItmY As ListItem, rsPull As Recordset
> > Dim DB As Database, intSt As Integer
> > Set DB = CurrentDb
> > If Data.GetFormat(ccCFText) Then
> > If Data.GetData(ccCFText) = "BoltL" Then
> > For Each ItmY In Forms!movement.PullLst.ListItems
> > With ItmY
> > If .Selected Then
> > SQL = "UPDATE Pull SET Pull.PullTruck = " & _
> > DLookup("TruckID", "Trucks", "LicNum = '" & objLicn
> > & "'") & _
> > ", Pull.TruckIntern = '" & strID & "' " & _
> > "WHERE Pull.PullItem = " & CLng(.Text)
> > DoCmd.RunSQL SQL
> > End If
> > End With
> > Next ItmY
> > Else
> > On Error GoTo Err_objRich_OLEDragDrop
> > With Forms!movement.PullTree
> > If TypeName(.Nodes(.SelectedItem.Index)) <> "Nothing" Then
> > Set ItmX = .Nodes(.SelectedItem.Index)
> > If Left(ItmX.Key, 1) = "I" Then
> > SQL = "UPDATE Pull SET Pull.PullTruck = " & _
> > DLookup("TruckID", "Trucks", "LicNum = '" & objLicn
> > & "'") & _
> > ", Pull.TruckIntern = '" & strID & "' " & _
> > "WHERE Pull.PullItem = " & CLng(Mid(ItmX.Key, 2))
> > DoCmd.RunSQL SQL
> > Else
> > FillTree ItmX
> > End If
> > End If
> > On Error GoTo 0
> > End With
> > End If
> > End If
> >
> > 'Line below populates the objRich objects in the collection with table
> > data.
> > 'commented out external reference for testing purposes.
> > 'Forms!movement!TruckSubf.Form.RchgAll
> >
> > Exit_objRich_OLEDragDrop:
> > Exit Sub
> >
> > Err_objRich_OLEDragDrop:
> > If Err.Number = 91 Then
> > MsgBox "No Item was selected", vbOKOnly
> > ElseIf Err.Number = 13 Then
> > Resume Exit_objRich_OLEDragDrop
> > Else
> > MsgBox Err.Description
> > End If
> > Resume Exit_objRich_OLEDragDrop
> > End Sub
> >
> > Private Sub FillTree(ByRef Brnch As Node)
> > Dim N As Integer, N2 As Integer, Y As Integer, SQL As String
> >
> > With Forms!Movement.PullTree
> > If Brnch.Children = 0 Then
> > N = Brnch.FirstSibling.Index
> > Do
> > SQL = "UPDATE Pull SET Pull.PullTruck = " & _
> > DLookup("TruckID", "Trucks", "LicNum = '" & objLicn & "'")
> > & _
> > ", Pull.TruckIntern = '" & strID & "' " & _
> > "WHERE Pull.PullItem = " & CLng(Mid(.Nodes(N).Key, 2))
> >
> > DoCmd.RunSQL SQL
> > If .Nodes(N).Index = Brnch.LastSibling.Index Then Exit Do
> > N = .Nodes(N).Next.Index
> > Loop
> > Else
> > N = Brnch.Child.FirstSibling.Index
> > Do
> > FillTree .Nodes(N)
> > If .Nodes(N).Index = .Nodes(N).LastSibling.Index Or _
> > .Nodes(N).Children = 0 Then Exit Do
> >
> > N = .Nodes(N).Next.Index
> > Loop
> > End If
> > End With
> > End Sub
> > ------------------------------------------------------------------------------------
> >
> > As soon I add the declaration ...
> >
> > Dim MyCtrlCol As New clsCTRLcoll
> >
> > To the top of the subform's (TruckSubf) code module, the subform stops
> > generating events.
> >
> > When I remove the line, the subform and all of the controls on it, behave
> > as
> > they should.
> >
> > Minus the functionality my object provides.
> >
> > The main form, upon which "Trucksubf" sits is called "Movement"
> >
> > I have List and Treeview controls on the main form from which items are
> > dragged onto
> > controls in the subform. The controls on the subform are supposed to be
> > gathered into
> > my custom object's collection when the subform loads.
> >
> > The reason I'm doing it this way is so that I can treat a large number of
> > groups of
> > controls as something resembling a control array (Which are not supported
> > in
> > VBA).
> > This way I only have to write one set of methods to address all of the
> > events I'm
> > interested in.
> >
> > After close to 30 hours of digging, I'm stumped. I'm guessing it's some
> > kind
> > of scope
> > issue or Access 2007 is just plain broken in this respect.
> >
> >
> > Any ideas?
> >
> >
> >
>
>
>
date: Fri, 18 Jul 2008 16:06:00 -0700
author: Raybo58
|
|
