Question concerning DblClick & DoCmd.OpenForm functions in Access

[RichG]

I am running into a problem when trying to open a form by double-clicking on
a combo-box within Access.

I have a form named “Applications†that has (among other objects) a
combo-box named “Support_Goup_Name†that links to a field called
“Support_Goup_Name†within table called “Support_Groupsâ€. I also have a
separate form named “Support_Groups†that displays a combo-box named
“Support_Goup_Nameâ€.

The Applications form is used to add applications and support groups to a
given system, and I need to be able to choose either from an existing support
group (this works now), or add a new support group on demand (does not yet
work).

I am trying to make use of the “DblClick†and “DoCmd.OpenForm†functions to
allow a user to double-click on the “Support_Goup_Name†combo-box to
dynamically create a new record as required. After searching for code
examples, I found a code snippet on the Microsoft site. I then altered the
snippet with what I thought might be the appropriate modifications as follows…

Private Sub Support_Group_Name_DblClick(Cancel As Integer)
Dim lngSupport_Group As Long
If IsNull(Me![Support_Group_Name]) Then
Me![Support_Group_Name].Text = ""
Else
lngSupport_Group = Me![Support_Group_Name]
Me![Support_Group_Name] = Null
End If
DoCmd.OpenForm "Support_Groups", , , , , acDialog, "GotoNew"
Me![Support_Group_Name].Requery
If lngSupport_Group <> 0 Then Me![Support_Group_Name] = lngSupport_Group
End Sub

When I double-click on the combo-box after entering this snippet, the system
displays the following error dialogue box…

Run-time error '13':
Type mismatch

When I launch the debugger, it then highlights following line within the
“Else†loop…

lngSupport_Group = Me![Support_Group_Name]

Does anyone know what I may be doing wrong?

Any assistance would be much appreciated…

 

[Jeff Boyce]

Typical combobox design uses a hidden (width=0) first column that contains
the ID of the record being displayed. Have you looked into the NotInList
property of the combobox? It's designed to help do what you described.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[RichG]

No, I was not aware of this.

Can you offer any suggestions as to how this would be used?

In other words, how would I use this to accomplish my goal?
--
RichG

Typical combobox design uses a hidden (width=0) first column that contains
the ID of the record being displayed. Have you looked into the NotInList
property of the combobox? It's designed to help do what you described.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I am running into a problem when trying to open a form by double-clicking on
a combo-box within Access.

I have a form named “Applications†that has (among other objects) a
combo-box named “Support_Goup_Name†that links to a field called
“Support_Goup_Name†within table called “Support_Groupsâ€. I also have a
separate form named “Support_Groups†that displays a combo-box named
“Support_Goup_Nameâ€.

The Applications form is used to add applications and support groups to a
given system, and I need to be able to choose either from an existing support
group (this works now), or add a new support group on demand (does not yet
work).

I am trying to make use of the “DblClick†and “DoCmd.OpenForm†functions to
allow a user to double-click on the “Support_Goup_Name†combo-box to
dynamically create a new record as required. After searching for code
examples, I found a code snippet on the Microsoft site. I then altered the
snippet with what I thought might be the appropriate modifications as follows…

Private Sub Support_Group_Name_DblClick(Cancel As Integer)
Dim lngSupport_Group As Long
If IsNull(Me![Support_Group_Name]) Then
Me![Support_Group_Name].Text = ""
Else
lngSupport_Group = Me![Support_Group_Name]
Me![Support_Group_Name] = Null
End If
DoCmd.OpenForm "Support_Groups", , , , , acDialog, "GotoNew"
Me![Support_Group_Name].Requery
If lngSupport_Group <> 0 Then Me![Support_Group_Name] = lngSupport_Group
End Sub

When I double-click on the combo-box after entering this snippet, the system
displays the following error dialogue box…

Run-time error '13':
Type mismatch

When I launch the debugger, it then highlights following line within the
“Else†loop…

lngSupport_Group = Me![Support_Group_Name]

Does anyone know what I may be doing wrong?

Any assistance would be much appreciated…

 

[RichG]

Jeff - never mind - I got it now.

Thank you SO MUCH for your help...
--
RichG

Typical combobox design uses a hidden (width=0) first column that contains
the ID of the record being displayed. Have you looked into the NotInList
property of the combobox? It's designed to help do what you described.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I am running into a problem when trying to open a form by double-clicking on
a combo-box within Access.

I have a form named “Applications†that has (among other objects) a
combo-box named “Support_Goup_Name†that links to a field called
“Support_Goup_Name†within table called “Support_Groupsâ€. I also have a
separate form named “Support_Groups†that displays a combo-box named
“Support_Goup_Nameâ€.

The Applications form is used to add applications and support groups to a
given system, and I need to be able to choose either from an existing support
group (this works now), or add a new support group on demand (does not yet
work).

I am trying to make use of the “DblClick†and “DoCmd.OpenForm†functions to
allow a user to double-click on the “Support_Goup_Name†combo-box to
dynamically create a new record as required. After searching for code
examples, I found a code snippet on the Microsoft site. I then altered the
snippet with what I thought might be the appropriate modifications as follows…

Private Sub Support_Group_Name_DblClick(Cancel As Integer)
Dim lngSupport_Group As Long
If IsNull(Me![Support_Group_Name]) Then
Me![Support_Group_Name].Text = ""
Else
lngSupport_Group = Me![Support_Group_Name]
Me![Support_Group_Name] = Null
End If
DoCmd.OpenForm "Support_Groups", , , , , acDialog, "GotoNew"
Me![Support_Group_Name].Requery
If lngSupport_Group <> 0 Then Me![Support_Group_Name] = lngSupport_Group
End Sub

When I double-click on the combo-box after entering this snippet, the system
displays the following error dialogue box…

Run-time error '13':
Type mismatch

When I launch the debugger, it then highlights following line within the
“Else†loop…

lngSupport_Group = Me![Support_Group_Name]

Does anyone know what I may be doing wrong?

Any assistance would be much appreciated…

 

[RichG]

Jeff:

I spoke too soon - I do not have it after all.

I added the following entry...

MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue

It initially seemed to work, but is not now.

Can you offer any additional assistance?

--
RichG

Typical combobox design uses a hidden (width=0) first column that contains
the ID of the record being displayed. Have you looked into the NotInList
property of the combobox? It's designed to help do what you described.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I am running into a problem when trying to open a form by double-clicking on
a combo-box within Access.

I have a form named “Applications†that has (among other objects) a
combo-box named “Support_Goup_Name†that links to a field called
“Support_Goup_Name†within table called “Support_Groupsâ€. I also have a
separate form named “Support_Groups†that displays a combo-box named
“Support_Goup_Nameâ€.

The Applications form is used to add applications and support groups to a
given system, and I need to be able to choose either from an existing support
group (this works now), or add a new support group on demand (does not yet
work).

I am trying to make use of the “DblClick†and “DoCmd.OpenForm†functions to
allow a user to double-click on the “Support_Goup_Name†combo-box to
dynamically create a new record as required. After searching for code
examples, I found a code snippet on the Microsoft site. I then altered the
snippet with what I thought might be the appropriate modifications as follows…

Private Sub Support_Group_Name_DblClick(Cancel As Integer)
Dim lngSupport_Group As Long
If IsNull(Me![Support_Group_Name]) Then
Me![Support_Group_Name].Text = ""
Else
lngSupport_Group = Me![Support_Group_Name]
Me![Support_Group_Name] = Null
End If
DoCmd.OpenForm "Support_Groups", , , , , acDialog, "GotoNew"
Me![Support_Group_Name].Requery
If lngSupport_Group <> 0 Then Me![Support_Group_Name] = lngSupport_Group
End Sub

When I double-click on the combo-box after entering this snippet, the system
displays the following error dialogue box…

Run-time error '13':
Type mismatch

When I launch the debugger, it then highlights following line within the
“Else†loop…

lngSupport_Group = Me![Support_Group_Name]

Does anyone know what I may be doing wrong?

Any assistance would be much appreciated…

 

[Jeff Boyce]

You kept the double-click. Use the LimitToList property and NotInList event
.... Access HELP has sample code for the NotInList event.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff:

I spoke too soon - I do not have it after all.

I added the following entry...

MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue

It initially seemed to work, but is not now.

Can you offer any additional assistance?

--
RichG

Typical combobox design uses a hidden (width=0) first column that contains
the ID of the record being displayed. Have you looked into the NotInList
property of the combobox? It's designed to help do what you described.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I am running into a problem when trying to open a form by

double-clicking
on

a combo-box within Access.

I have a form named “Applications†that has (among other objects) a
combo-box named “Support_Goup_Name†that links to a field called
“Support_Goup_Name†within table called “Support_Groupsâ€. I also have a
separate form named “Support_Groups†that displays a combo-box named
“Support_Goup_Nameâ€.

The Applications form is used to add applications and support groups to a
given system, and I need to be able to choose either from an existing support
group (this works now), or add a new support group on demand (does not yet
work).

I am trying to make use of the “DblClick†and “DoCmd.OpenFormâ€

functions
to

allow a user to double-click on the “Support_Goup_Name†combo-box to
dynamically create a new record as required. After searching for code
examples, I found a code snippet on the Microsoft site. I then

altered
the

snippet with what I thought might be the appropriate modifications as follows…

Private Sub Support_Group_Name_DblClick(Cancel As Integer)
Dim lngSupport_Group As Long
If IsNull(Me![Support_Group_Name]) Then
Me![Support_Group_Name].Text = ""
Else
lngSupport_Group = Me![Support_Group_Name]
Me![Support_Group_Name] = Null
End If
DoCmd.OpenForm "Support_Groups", , , , , acDialog, "GotoNew"
Me![Support_Group_Name].Requery
If lngSupport_Group <> 0 Then Me![Support_Group_Name] = lngSupport_Group
End Sub

When I double-click on the combo-box after entering this snippet, the system
displays the following error dialogue box…

Run-time error '13':
Type mismatch

When I launch the debugger, it then highlights following line within the
“Else†loop…

lngSupport_Group = Me![Support_Group_Name]

Does anyone know what I may be doing wrong?

Any assistance would be much appreciated…