Subform action not functioning - OnDblClick

[Pendragon]

WinXP/Access03

Main Form - frmParticipant
Subform - frmPRT_RollUp
subSubform - frmPRT_RollUpSub (this is the Name property of the form)

frmPRT_RollUp manages records for the table PRT_RollUp. Through a couple
unbound combo boxes for data selection, records are added via a command
button.

frmPRT_RollUpSub is a datasheet view of table PRT_RollUp with Filter, Edits
& Deletions set to Yes and Additions set to No to prevent users from typing
information in directly into the datasheet.

In managing the records, if a user wants to delete a record from PRT_RollUp,
the user simply double-clicks on the record in frmPRT_RollUpSub.

Couple days ago this was working fine and dandy. Today it's not and I don't
believe I changed anything which would have impacted this code to work. Now
on the double click, the record is highlighted but nothing happens.

Here is the OnDblClick code:

Dim resp As Variant
Dim ctl As Control
ctl = Forms!frmParticipant!frmPRT_RollUp.Form!cboAddRollUp

resp = MsgBox("You are about to delete this participant from the commission
roll up list." & vbCrLf & vbCrLf & _
"Do you want to continue?", vbYesNo)

If resp = vbNo Then
DoCmd.CancelEvent
DoCmd.GoToControl ctl.Name
Else
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
DoCmd.GoToControl ctl.Name

End If

Exit Sub

What else should I be looking at for errors? I place a breakpoint on the
MsgBox line but that's not even being initiated.

Also, after an action is taking place, I would like the focus to go to the
control on the parent form. This is not happening either - I believe my
definition of ctl after the dim statement is syntactically correct -
comments/review of that is appreciated too.

Thanks in advance for any opinions/advice.

 

[Marshall Barton]

WinXP/Access03

Main Form - frmParticipant
Subform - frmPRT_RollUp
subSubform - frmPRT_RollUpSub (this is the Name property of the form)

frmPRT_RollUp manages records for the table PRT_RollUp. Through a couple
unbound combo boxes for data selection, records are added via a command
button.

frmPRT_RollUpSub is a datasheet view of table PRT_RollUp with Filter, Edits
& Deletions set to Yes and Additions set to No to prevent users from typing
information in directly into the datasheet.

In managing the records, if a user wants to delete a record from PRT_RollUp,
the user simply double-clicks on the record in frmPRT_RollUpSub.

Couple days ago this was working fine and dandy. Today it's not and I don't
believe I changed anything which would have impacted this code to work. Now
on the double click, the record is highlighted but nothing happens.

Here is the OnDblClick code:

Dim resp As Variant
Dim ctl As Control
ctl = Forms!frmParticipant!frmPRT_RollUp.Form!cboAddRollUp

resp = MsgBox("You are about to delete this participant from the commission
roll up list." & vbCrLf & vbCrLf & _
"Do you want to continue?", vbYesNo)

If resp = vbNo Then
DoCmd.CancelEvent
DoCmd.GoToControl ctl.Name
Else
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
DoCmd.GoToControl ctl.Name

End If

Exit Sub

What else should I be looking at for errors? I place a breakpoint on the
MsgBox line but that's not even being initiated.

Also, after an action is taking place, I would like the focus to go to the
control on the parent form. This is not happening either - I believe my
definition of ctl after the dim statement is syntactically correct -
comments/review of that is appreciated too.

If the code is not being executed, the first thing to check
is to make sure the event property contains:
[Event Procedure]

If it does, then click the code builder button [...] to make
sure it takes you to your code. If that also works, then
use the Debug - Compile menu item to make sure you project
compiles without error.

If all those conditions hold, you probably have a corrupted
project and should try decompiling the project.

 

[Pendragon]

Thanks Marshall. Not sure where it was, but I copied the code into a new form
and recreated the form. Now it works. Something must have happened to
corrupt the form.

Can you help me out on referencing the control on the parent form? Getting
stuck there. After the MsgBox yes/no reply, I would like the cursor to go
back to the first combo box on the parent form.

When I tried this method...

Dim ctl as String
ctl = Forms!frmParticipant!frmPRT_RollUp.Form!cboAddRollUp
....
DoCmd.GoToControl ctl

....the message is that the field doesn't exist in the current record.

When I tried this method...

Dim ctl as Control
ctl = forms!frmParticipant!frmPRT_RollUp.form!cboAddRollUp
....
DoCmd.GoToControl ctl.Name

....the message was Object variable or With block variable not set (Err 91).

Do I first have to set the focus back to the parent form and then go to the
specified control? I thought I would be able to use just one statement.

Thanks.

WinXP/Access03

Main Form - frmParticipant
Subform - frmPRT_RollUp
subSubform - frmPRT_RollUpSub (this is the Name property of the form)

frmPRT_RollUp manages records for the table PRT_RollUp. Through a couple
unbound combo boxes for data selection, records are added via a command
button.

frmPRT_RollUpSub is a datasheet view of table PRT_RollUp with Filter, Edits
& Deletions set to Yes and Additions set to No to prevent users from typing
information in directly into the datasheet.

In managing the records, if a user wants to delete a record from PRT_RollUp,
the user simply double-clicks on the record in frmPRT_RollUpSub.

Couple days ago this was working fine and dandy. Today it's not and I don't
believe I changed anything which would have impacted this code to work. Now
on the double click, the record is highlighted but nothing happens.

Here is the OnDblClick code:

Dim resp As Variant
Dim ctl As Control
ctl = Forms!frmParticipant!frmPRT_RollUp.Form!cboAddRollUp

resp = MsgBox("You are about to delete this participant from the commission
roll up list." & vbCrLf & vbCrLf & _
"Do you want to continue?", vbYesNo)

If resp = vbNo Then
DoCmd.CancelEvent
DoCmd.GoToControl ctl.Name
Else
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
DoCmd.GoToControl ctl.Name

End If

Exit Sub

What else should I be looking at for errors? I place a breakpoint on the
MsgBox line but that's not even being initiated.

Also, after an action is taking place, I would like the focus to go to the
control on the parent form. This is not happening either - I believe my
definition of ctl after the dim statement is syntactically correct -
comments/review of that is appreciated too.

If the code is not being executed, the first thing to check
is to make sure the event property contains:
[Event Procedure]

If it does, then click the code builder button [...] to make
sure it takes you to your code. If that also works, then
use the Debug - Compile menu item to make sure you project
compiles without error.

If all those conditions hold, you probably have a corrupted
project and should try decompiling the project.

 

[Marshall Barton]

Thanks Marshall. Not sure where it was, but I copied the code into a new form
and recreated the form. Now it works. Something must have happened to
corrupt the form.

The most common reason for a corrupted form (or report) is
editing the form's code when the form is not in design view.
You try real hard to develop a habit of always switching to
design view before touching the keyboard.

Just to reinforce that, I once corrupted a form by clicking
in the code window to look at a procedure and accidently
bumped the space bar. Took a Decompile to recover.

Can you help me out on referencing the control on the parent form? Getting
stuck there. After the MsgBox yes/no reply, I would like the cursor to go
back to the first combo box on the parent form.

When I tried this method...

Dim ctl as String
ctl = Forms!frmParticipant!frmPRT_RollUp.Form!cboAddRollUp
...
DoCmd.GoToControl ctl

...the message is that the field doesn't exist in the current record.

When I tried this method...

Dim ctl as Control
ctl = forms!frmParticipant!frmPRT_RollUp.form!cboAddRollUp
...
DoCmd.GoToControl ctl.Name

...the message was Object variable or With block variable not set (Err 91).

I can't say for sure, but I never expect those DoCmd things
to work. If you can not tell it which form object to
operate on, you never know where it's doing its thing. Much
of the DoCmd stuff and almost all of the RunCommand things
are just old fashioned mouse click on a menu simulators. At
least when you do it with the mouse you can see what you
selected before using the menu item.

Instead of using DoCmd.GoToControl ctl.Name, I strongly
recommend using (from a subform to a main form control):
Parent.controlname.SetFocus
or within the same form:
Me.controlname.SetFocus
or to some altogether different main form:
Forms!otherform.controlname.SetFocus

Instead of your sequence:
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
I recommend using something more like:
Dim strSQL As String
strSQL = "DELETE * FROM table WHERE pkfield = " &
Me.pkfield
CurrentDb.Execute strSQL

As Another example, instead of either the DoCmd or
RunCommand save record actions, use:
<form object>.Dirty = False