CreateControl command Crashes Access

[chammock]

I have a process application with these details.

I have a starting form that collects a few details and then opens a
detailed form with lots of command buttons being created in code. To
create the command buttons, the form is cleaned up to remove any
command buttons from a previous session and recreate them in code and
assign click events to each button.

Clicking one of the command buttons opens another form and allows the
user to collect more data. Once the data is collected, it returns back
to the main form with the command buttons updated to reflect the new
data, i.e. if a user saves a record related to the command button that
button then becomes disabled, as each user can only have one record
associated with a command button.

On the main form, there is an Edit mode button that opens the form in
design mode, disables all the command buttons and then re-enables only
those buttons that represent the current users records. Clicking a
command button takes them back to the record they saved and allows them
to update or delete it. This is a single user system at a time, but
more than one user can use it over time.

Once in Edit mode, the same button then becomes a Back to Inspection
Mode button. The intent is to open the same form with all the command
buttons in design mode, delete all the command buttons and then rebuild
the form to represent the current status of all the buttons for this
user. In fact, this is really just the same process as takes place
initially, since I cleanup the form to start with to take care of any
prior session buttons or code.

Going Back to Inspection Mode opens the form fine in design mode and
runs a Function to delete all the command buttons and cleanup the code.
When I try to recreate all the command buttons at this time it appears
to get to the point where it executes this code.

MsgBox "StrStatus: " & strStatus
Set ctl = CreateControl(strSideTemplate, acCommandButton, , , ,
rs![FastenerXCoordinate] * 1440, rs![FastenerYCoordinate] * 1440)

The message box fires correctly with the right information, but at that
point I cannot step through the execution of the code. Even with a
break point on the Set ctl line, Access says it cannot break and I have
to hit the continue button. Then next thing that happens is that Access
crashes. I am not 100% sure where it crashes so here is some more of
the code around that area.

Do While Not rs.EOF
strStatus = GetFastenerStatus(intTailNumberID,
rs![FastenerID], intInspectorID, strPanel)
'MsgBox "StrStatus: " & strStatus
Set ctl = CreateControl(strSideTemplate, acCommandButton, , , ,
rs![FastenerXCoordinate] * 1440, rs![FastenerYCoordinate] * 1440)
ctl.Caption = rs![FastenerNumber]
ctl.Height = 0.275 * 1440
ctl.Width = 0.275 * 1440
ctl.FontSize = 7
ctl.TabStop = False
ctl.Name = "vbacmd" & rs![FastenerNumber]

If strStatus = "Complete" Then
'set control to be disabled and set tooltip
ctl.Enabled = False
ctl.ControlTipText = "Complete"
ElseIf strStatus = "AlreadyInspectedByThisInspector" Then
'set control to be disabled and set tooltip
ctl.Enabled = False
ctl.ControlTipText = "This inspector has already inspected
this hole"
ElseIf strStatus = "NeedsRepair" Then
'set control to be disabled and set tooltip
ctl.Enabled = False
ctl.ControlTipText = "Needs repair and reinspection"

ElseIf strStatus = "RequiresLevel3" Then
'set control to be disabled and set tooltip
If intInspectorLevel <> 3 Then
ctl.Enabled = False
ctl.ControlTipText = "Requires Level 3 inspector"
End If
Else
End If
'For each command button create the Click event
Set mdl = frm.Module
' Add event procedure.
lngReturn = mdl.CreateEventProc("Click", "vbacmd" &
rs![FastenerNumber])
' Insert text into body of procedure.
mdl.InsertLines lngReturn + 1, vbTab & "Call
PerformValidation(" & intTailNumberID & "," & intInspectorID & ", " &
rs![FastenerID] & ", """ & strSideTemplate & """)"

rs.MoveNext
Loop

In reality, I am running the same Functions to get Back to Inspection
Mode as I run to get to Inspection Mode to start with. I have checked
to see if I am leaving any objects open, etc. with no success. I have
tried to run the code through another form as an intermediate step with
no success.

I have tried so many things and cannot get it to not crash. I am on
about the 4th day of working this single issue. I know this could be
something trivial but it is hard to troubleshoot. If anyone has the
heart, I could send them a copy of the mdb to look at and possibly do a
phone call. I am willing to PayPal to someone if they can help solve
this issue as I know we all have work to do.

Thanks.

 

[Allen Browne]

This approach is not going to work.

There is a finite number of controls Access can handle on a form (a few
hundred.) If you are deleting controls and creating them again, this number
will quickly be exceeded, and your algorithm will fail. Compacting the
database between creates does reset the number of controls again, but is not
a satisfactory workaround.

If you really must have a button for each record, you might consider
creating a continuous form bound to the table. Placing a single command
button on the form will then give you a stack of buttons (one above the
other) in continuous view.

CreateControl() is useful only for wizard-type interfaces where you are
helping a user to design their own form. It is not useful for a runtime
process. It will prevent you from turning your application into an MDE. It
causes bloat and instability in the application. There has to be a better
approach to whatever you are trying to do.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a process application with these details.

I have a starting form that collects a few details and then opens a
detailed form with lots of command buttons being created in code. To
create the command buttons, the form is cleaned up to remove any
command buttons from a previous session and recreate them in code and
assign click events to each button.

Clicking one of the command buttons opens another form and allows the
user to collect more data. Once the data is collected, it returns back
to the main form with the command buttons updated to reflect the new
data, i.e. if a user saves a record related to the command button that
button then becomes disabled, as each user can only have one record
associated with a command button.

On the main form, there is an Edit mode button that opens the form in
design mode, disables all the command buttons and then re-enables only
those buttons that represent the current users records. Clicking a
command button takes them back to the record they saved and allows them
to update or delete it. This is a single user system at a time, but
more than one user can use it over time.

Once in Edit mode, the same button then becomes a Back to Inspection
Mode button. The intent is to open the same form with all the command
buttons in design mode, delete all the command buttons and then rebuild
the form to represent the current status of all the buttons for this
user. In fact, this is really just the same process as takes place
initially, since I cleanup the form to start with to take care of any
prior session buttons or code.

Going Back to Inspection Mode opens the form fine in design mode and
runs a Function to delete all the command buttons and cleanup the code.
When I try to recreate all the command buttons at this time it appears
to get to the point where it executes this code.

MsgBox "StrStatus: " & strStatus
Set ctl = CreateControl(strSideTemplate, acCommandButton, , , ,
rs![FastenerXCoordinate] * 1440, rs![FastenerYCoordinate] * 1440)

The message box fires correctly with the right information, but at that
point I cannot step through the execution of the code. Even with a
break point on the Set ctl line, Access says it cannot break and I have
to hit the continue button. Then next thing that happens is that Access
crashes. I am not 100% sure where it crashes so here is some more of
the code around that area.

Do While Not rs.EOF
strStatus = GetFastenerStatus(intTailNumberID,
rs![FastenerID], intInspectorID, strPanel)
'MsgBox "StrStatus: " & strStatus
Set ctl = CreateControl(strSideTemplate, acCommandButton, , , ,
rs![FastenerXCoordinate] * 1440, rs![FastenerYCoordinate] * 1440)
ctl.Caption = rs![FastenerNumber]
ctl.Height = 0.275 * 1440
ctl.Width = 0.275 * 1440
ctl.FontSize = 7
ctl.TabStop = False
ctl.Name = "vbacmd" & rs![FastenerNumber]

If strStatus = "Complete" Then
'set control to be disabled and set tooltip
ctl.Enabled = False
ctl.ControlTipText = "Complete"
ElseIf strStatus = "AlreadyInspectedByThisInspector" Then
'set control to be disabled and set tooltip
ctl.Enabled = False
ctl.ControlTipText = "This inspector has already inspected
this hole"
ElseIf strStatus = "NeedsRepair" Then
'set control to be disabled and set tooltip
ctl.Enabled = False
ctl.ControlTipText = "Needs repair and reinspection"

ElseIf strStatus = "RequiresLevel3" Then
'set control to be disabled and set tooltip
If intInspectorLevel <> 3 Then
ctl.Enabled = False
ctl.ControlTipText = "Requires Level 3 inspector"
End If
Else
End If
'For each command button create the Click event
Set mdl = frm.Module
' Add event procedure.
lngReturn = mdl.CreateEventProc("Click", "vbacmd" &
rs![FastenerNumber])
' Insert text into body of procedure.
mdl.InsertLines lngReturn + 1, vbTab & "Call
PerformValidation(" & intTailNumberID & "," & intInspectorID & ", " &
rs![FastenerID] & ", """ & strSideTemplate & """)"

rs.MoveNext
Loop

In reality, I am running the same Functions to get Back to Inspection
Mode as I run to get to Inspection Mode to start with. I have checked
to see if I am leaving any objects open, etc. with no success. I have
tried to run the code through another form as an intermediate step with
no success.

I have tried so many things and cannot get it to not crash. I am on
about the 4th day of working this single issue. I know this could be
something trivial but it is hard to troubleshoot. If anyone has the
heart, I could send them a copy of the mdb to look at and possibly do a
phone call. I am willing to PayPal to someone if they can help solve
this issue as I know we all have work to do.

Thanks.

 

[TC]

Owww! As Alan said, creating the controls at runtime aint the way to
go!

Your code looks pretty good, you've obviously written code before. I
think you've just got hung-up on createcontrol. You clearly have
sufficient coding skills to do it /without/ creating the buttons
through code.

Use Alan's idea to get a button per record. (Just have a button, along
with the fields, in the form that you put in a subform control.) That
button will only have s single Click event, even though there is a
physical instance of the button for each record in the subform. But
from that Click event, you can tell what record was actially clicked.

One problem there, is that you can't have a different caption on each
button. If you change the caption on "the" button (because there is
only one instance of the control that you can see *through code*), the
caption will change on *all* of the physical instances. So if you want
different captions, you'd have to use a textbox (instead of a button),
bind it to a function (which returns the text to display as the
caption), and use its format properties to make it look like a button.
Then you'd use the textbox'es Click event, to handle the clicks.

Hope that helps,

TC (MVP Access)
http://tc2.atspace.com