Appeal to M. Barton, T. MacDermott, J. Czernik, and any open-minded people

[Meirans-Mertens]

I already posted the following question on 12/01/03 ("Deleting entries in
combo boxes," from Meirans-Mertens) but got only negative results. I'm
appealing to the people mentioned in the subject line because they helped me
in the past with complicated questions, and I'm relying now on their
open-minded attitude and reasoning skills. I would also appreciate the input
of anyone with similar inclinations.

The question: Is it possible to have a VB event procedure such that, on
double-clicking (for example, or right-clicking) an entry in a combo box in
a form, the user
would have the opportunity to delete the selected entry in the underlying
lookup table? And if this is possible, would you be willing to tell me what
the code might be? (This, perhaps, could be a variant on a feature available
in one of the Access 97 wizards, Music Collection, where users can
double-click a combo-box to open a subform and add some data relevant to the
field where the combo box resides.)

If this is not possible, so be it and I'll use the alternative method I
mentioned in my previous posting (i.e., make the lookup table available to
the user), or try to work out something from the Access 97 wizard. But
please, if anyone other than the good people mentioned above does not want
to get involved with this problem, for any reason whatsoever, or has no
constructive answer to it, just leave this post unanswered. No one
addressing
this forum needs haughty, flippant, and condescending responses like the
ones I got from my 12/01/03 posting. Let's stick to the problem and be
professional about it. Or business-like. If someone thinks that the solution
to this problem should be renumerated, please let me know.

Thanks in advance.

Nicolas Mertens.

 

[Paul Rohorzka]

Hi Nicolas!

I already posted the following question on 12/01/03 ("Deleting entries in
combo boxes," from Meirans-Mertens) but got only negative results. I'm
appealing to the people mentioned in the subject line because they helped me
in the past with complicated questions, and I'm relying now on their
open-minded attitude and reasoning skills. I would also appreciate the input
of anyone with similar inclinations.

I'm not one of those, but I hope to be able to contribute something
useful although.

The question: Is it possible to have a VB event procedure such that, on
double-clicking (for example, or right-clicking) an entry in a combo box in
a form, the user
would have the opportunity to delete the selected entry in the underlying
lookup table? And if this is possible, would you be willing to tell me what
the code might be? (This, perhaps, could be a variant on a feature available
in one of the Access 97 wizards, Music Collection, where users can
double-click a combo-box to open a subform and add some data relevant to the
field where the combo box resides.)

Use a bound but hidden column holding the value of the primary key.
The Value-property of the listbox gives you the value of this
primary key for the currently selected row. With this value you
can feed a delete-query on your lookup table. Is not very complicated.

HTH,
Paul

 

[Dirk Goldgar]

I already posted the following question on 12/01/03 ("Deleting
entries in combo boxes," from Meirans-Mertens) but got only negative
results. I'm appealing to the people mentioned in the subject line
because they helped me in the past with complicated questions, and
I'm relying now on their open-minded attitude and reasoning skills. I
would also appreciate the input of anyone with similar inclinations.

The question: Is it possible to have a VB event procedure such that,
on double-clicking (for example, or right-clicking) an entry in a
combo box in a form, the user
would have the opportunity to delete the selected entry in the
underlying lookup table? And if this is possible, would you be
willing to tell me what the code might be? (This, perhaps, could be a
variant on a feature available in one of the Access 97 wizards, Music
Collection, where users can double-click a combo-box to open a
subform and add some data relevant to the field where the combo box
resides.)

If this is not possible, so be it and I'll use the alternative method
I mentioned in my previous posting (i.e., make the lookup table
available to the user), or try to work out something from the Access
97 wizard. But please, if anyone other than the good people mentioned
above does not want to get involved with this problem, for any reason
whatsoever, or has no constructive answer to it, just leave this post
unanswered. No one addressing
this forum needs haughty, flippant, and condescending responses like
the ones I got from my 12/01/03 posting. Let's stick to the problem
and be professional about it. Or business-like. If someone thinks
that the solution to this problem should be renumerated, please let
me know.

Thanks in advance.

Nicolas Mertens.

Both of your previous respondents were giving you good advice with
regard to the question you originally asked, in which you wanted to
replace the normal, standard behavior of the Delete key or the Ctrl+X
combination to perform this function. They said, "Don't do it," and
they were right.

However, what you are asking now is not to replace a standard UI
behavior but to add a new behavior to a non-standardized event. This is
perfectly reasonable, in my view (though I use the DblClick event in
combo boxes for something else in all my applications). Here's how you
might do it:

'----- start of example code -----
Private Sub Property_DblClick()

Dim strPropertyName As String

strPropertyName = Me!Property & ""

If Len(strPropertyName) = 0 Then
Exit Sub
End If

If MsgBox( _
"Do you want to delete '" & strPropertyName & _
"' from the list of properties?", _
vbYesNo + vbQuestion, _
"Delete Property?") _
= vbYes _
Then

Me!Property = Null

CurrentDb.Execute _
"DELETE FROM [Property Names] " & _
"WHERE [Property Name] = " & _
Chr(34) & strPropertyName & Chr(34), _
dbFailOnError

Me!Property.Requery

End If

End Sub
'----- end of example code -----

Aside from any errors I may have made in the above "air code", there are
two potential problems with this solution. Both only arise if there are
other records in the form's recordsource table with this same property
name. First, if there is an enforced relationship between [Property
Names].[Property Name] and the Property field in the table on which the
form is based, then you won't be able to delete the record from
[Property Names] if there are other records with this Property Name,
unless you also have Cascade Deletes set for the relationship (which I
wouldn't recommend).

Second, even if you don't have an enforced relationship, if you delete
the property name from the table, and hence from the combo box's list,
then depending on how you have the combo box set up you may have
problems with the display of existing records that have that property
name. You won't if the combo box only has one column, the property
name.

 

[Marshall Barton]

I already posted the following question on 12/01/03 ("Deleting entries in
combo boxes," from Meirans-Mertens) but got only negative results. I'm
appealing to the people mentioned in the subject line because they helped me
in the past with complicated questions, and I'm relying now on their
open-minded attitude and reasoning skills. I would also appreciate the input
of anyone with similar inclinations.

The question: Is it possible to have a VB event procedure such that, on
double-clicking (for example, or right-clicking) an entry in a combo box in
a form, the user
would have the opportunity to delete the selected entry in the underlying
lookup table? And if this is possible, would you be willing to tell me what
the code might be? (This, perhaps, could be a variant on a feature available
in one of the Access 97 wizards, Music Collection, where users can
double-click a combo-box to open a subform and add some data relevant to the
field where the combo box resides.)

If this is not possible, so be it and I'll use the alternative method I
mentioned in my previous posting (i.e., make the lookup table available to
the user), or try to work out something from the Access 97 wizard. But
please, if anyone other than the good people mentioned above does not want
to get involved with this problem, for any reason whatsoever, or has no
constructive answer to it, just leave this post unanswered. No one
addressing
this forum needs haughty, flippant, and condescending responses like the
ones I got from my 12/01/03 posting. Let's stick to the problem and be
professional about it. Or business-like. If someone thinks that the solution
to this problem should be renumerated, please let me know.

Let me try to add to what others have said, which were not
flippant nor condescending. They were trying to present you
with thoughtful issues that should be seriously considered.
But, it's your program and you can do what you want.

I would also strongly suggest that you think long and hard
about deleting this data. Dirk's caution about the form's
record source table having references to the lookup table's
entries is something that really needs to be dealt with.

In this kind of situation, I never delete the records from
the lookup table. Instead, I set a Yes/No field in th
elookup table so the combo box can filter them out of the
list it presents to the users. This way, the main table's
data in existing records is still valid if only used for
hostoric purposes.

If you want to go this route, add a Yes/No field named
Inactive (or some such) to the look up table. Then you can
replace the Delete query with an Update query in Dirk's
procedure to set the field to False. The combo box's Row
Source would then be a select query that filters out the
inactive data:
SELECT fielda, fieldb FROM lookup WHERE Inactive = False

 

[Fred Boer]

Dear Dirk:

perfectly reasonable, in my view (though I use the DblClick event in
combo boxes for something else in all my applications). Here's how you

Hmm... It's never occurred to me that I could use DblClick in a combobox...
What *do* you use the DblClick event in comboboxes for in all your
applications?

Thanks!
Fred Boer

 

[Dirk Goldgar]

Dear Dirk:


Hmm... It's never occurred to me that I could use DblClick in a
combobox... What *do* you use the DblClick event in comboboxes for in
all your applications?

I have a standard function, "fncOpenRelatedForm", that opens the form
used for editing the related table. There are various optional
arguments to support (a) opening the form filtered to show a specific
record -- generally the one that matches the value in the combo box, (b)
opening the related form in dialog mode, and (c) requerying the combo
box when the form is closed.

The "standard" behavior I generally implement is that double-clicking
the combo box opens a form to edit the lookup table, showing the record
that relates to the current value in the combo box. Where feasible, the
form is opened in dialog mode and the combo box is requeried after the
form is closed so that the combo's list reflectes any changes made on
the form.

 

[Fred Boer]

Thanks Dirk:

Interesting! I currently use the standard "notinlist" process to add a new
item to a list, and for one or two comboboxes I use a command button to
actually open a new form to edit the lookup table data. For other comboboxes
I have no way (short of opening the tables) to edit the underlying table
data. If I am understanding you correctly, I could simply use a doubleclick
event to open the lookup table for editing, correct? And I assume that users
would understand this through user training? Or is there some other way you
would tell users to "Double-click to edit list..."

Thanks!
Fred

 

[Dirk Goldgar]

Thanks Dirk:

Interesting! I currently use the standard "notinlist" process to add
a new item to a list,

That's a good way to do it when you can, but it's not adequate when the
table in question has other required fields than the NewData itself.
Also, I like to use the DblClick event to let the user drill down to the
detail data in the related parent table. For example, if the combo box
is for choosing customer, double-clicking it brings up the Customers
form with all the details about that customer.

and for one or two comboboxes I use a command
button to actually open a new form to edit the lookup table data.

I just use this method to avoid having to put a comand button beside
each combo box. Call me lazy -- but also I save a little "form real
estate".

For
other comboboxes I have no way (short of opening the tables) to edit
the underlying table data. If I am understanding you correctly, I
could simply use a doubleclick event to open the lookup table for
editing, correct?

Correct, though I wouldn't open the table directly, but rather a form
based on the table.

And I assume that users would understand this
through user training? Or is there some other way you would tell
users to "Double-click to edit list..."

So far, I've always handled it by user training -- a simple statement,
"Double-click things to bring up the details," seems to get the idea
across -- but it wouldn't be a bad idea to have the control-tip say so.
I just don't happen to have implemented that.

 

[Fred Boer]

Thanks again, Dirk:

That's a good way to do it when you can, but it's not adequate when the
table in question has other required fields than the NewData itself.

Exactly! It was *just* that situation that caused me to create the "command
button" for one or two comboboxes...

I just use this method to avoid having to put a comand button beside
each combo box. Call me lazy -- but also I save a little "form real
estate".

Correct, though I wouldn't open the table directly, but rather a form
based on the table.

Of course, I meant a form based on a table, I was careless in expressing
myself there...

So far, I've always handled it by user training -- a simple statement,
"Double-click things to bring up the details," seems to get the idea
across -- but it wouldn't be a bad idea to have the control-tip say so.
I just don't happen to have implemented that.

Yes, I was thinking about a control-tip...

Thanks again, Dirk! I am going to have a go at implementing this; it looks
like a much better way to manage the task!

Fred

P.S. And if I do this, I can use it as an excuse to put off learning about
Access security... which is what I've told myself I should be doing! <g>

 

[Dirk Goldgar]

I am going to have a go at implementing this; it
looks like a much better way to manage the task!

Take note, by the way, of the advantage of using a Function rather than
a Sub procedure to do this -- you can call it directly from the
OnDblClick property of the control, using an expression of the form
"=FunctionName(<argument list>)", thereby avoiding the need to create an
actual event procedure for all those controls.

 

[Fred Boer]

<Chuckle> Dirk, you fool... you've given me the opening I needed! <g> I was
just saying to myself... hmmm... how could I get Dirk to show me his
function?...

I know how to use a function in the way you describe; I do it for my
navigation buttons. However, I don't know how you would go about making this
function... uh... can't find the word I need... dang it.. you know... not
limited to a specific subprocedure, reusable... How would the function know
which "edit form" to load? I assume you wouldn't use the same form for every
combobox, since I suppose the structure of each underlying table would be
different, so the forms used to edit them would be different. Is the answer
in "<argument list>"? Do I do something like
=fncOpenRelatedForm("Frm_EditCustomer"), and for each control simply
substitute the correct form name as the argument of the function?

Thanks!
Fred



how do you make it you can make it

 

[Dirk Goldgar]

<Chuckle> Dirk, you fool... you've given me the opening I needed! <g>
I was just saying to myself... hmmm... how could I get Dirk to show
me his function?...

I know how to use a function in the way you describe; I do it for my
navigation buttons. However, I don't know how you would go about
making this function... uh... can't find the word I need... dang it..
you know... not limited to a specific subprocedure, reusable... How
would the function know which "edit form" to load? I assume you
wouldn't use the same form for every combobox, since I suppose the
structure of each underlying table would be different, so the forms
used to edit them would be different. Is the answer in "<argument
list>"? Do I do something like
=fncOpenRelatedForm("Frm_EditCustomer"), and for each control simply
substitute the correct form name as the argument of the function?

Yes, exactly. Okay, you asked for it; here's the function I'm
currently using:

'----- start of code ------
Function fncOpenRelatedForm(FormName As String, _
Optional CriteriaField As String = "", _
Optional CriteriaValue As Variant, _
Optional DialogMode As Boolean = False, _
Optional RequeryMe As Variant)

' This function is called from various locations (usually control
events on forms)
' to open a form, normally a form that is related to the current
one. Arguments
' are:
' FormName : the name of the form to be opened.
' CriteriaField : (optional) the name of the field in the form's
recordsource
' by which we want to filter the form.
' CriteriaValue : (optional) the value for which we want to
filter the
' criteria field. This value must be passed
with appropriate
' delimiters (e.g., quotes, # signs) if the
field is a text
' or date field.
' DialogMode : (optional) If True, open the form in dialog
mode; i.e., modal.
' RequeryMe : (optional) If specified, this is a Form or
Control object to
' be requeried after the related form
(presumably opened in
' dialog mode) has been closed.
'
' Usage note: If you pass an object to be requeried in RequeryMe,
but specify
' DialogMode as False, the function will loop until the form is
closed. This
' is an inefficient and CPU-intensive process, and should be
avoided.
'
' Requires: Function IsLoaded(FormName As String) As Boolean
'
' Copyright © 2000-2003 Dirk Goldgar. All rights reserved.
' License is granted to use this function in your own code,
' provided the copyright notice remains intact.

On Error GoTo Err_fncOpenRelatedForm

Dim strLinkCriteria As String
Dim intWindowMode As Integer

If Len(CriteriaField) = 0 _
Or IsMissing(CriteriaValue) _
Or IsNull(CriteriaValue) _
Then
strLinkCriteria = ""
Else
strLinkCriteria = CriteriaField & "=" & CriteriaValue
End If

If DialogMode Then
intWindowMode = acDialog
Else
intWindowMode = acWindowNormal
End If

DoCmd.OpenForm FormName, _
WhereCondition:=strLinkCriteria, _
WindowMode:=intWindowMode

If IsObject(RequeryMe) Then

' If we were given an object to requery, but were
' told not to open the related form in dialog mode,
' then we must resort to the inefficient expedient
' of looping until the form is closed again.
If intWindowMode <> acDialog Then
Do While IsLoaded(FormName)
DoEvents
Loop
End If
RequeryMe.Requery
End If

Exit_fncOpenRelatedForm:
Exit Function

Err_fncOpenRelatedForm:
subDisplayAndLogError "fncOpenRelatedForm", _
Err.Number, Err.Description, _
"OpenForm", FormName, _
"Criteria", strLinkCriteria

Resume Exit_fncOpenRelatedForm

End Function
'----- end of code ------

Here's an example of an expression to call the function from the
OnDblClick property of a control named "HomeCostCtr":


=fncOpenRelatedForm("frmCostCenters","CostCtrCode",("'"+[HomeCostCtr]+"'
"),True,[HomeCostCtr])

Note that you could conceivably make the function require fewer -- or
no -- arguments by implementing a naming convention for the related
forms and extracting the necessary information from the combo box
itself, accessed via Screen.ActiveControl. However, I haven't gone to
all that trouble up to now, and it seems less flexible anyway.

 

[Ken Snell]

Fred -

I've used the DblClick in a combo box to do things like display a popup
"Find" form that lets the user easily find an entry by searching on the
other columns of the combo box -- for example, I display in the dropdown box
a part number, part description, and manufacturer, with part number being
the bound column, and the popup form allows the user to do a search by part
description if the person doesn't know the part number). I also use it for
easy display of "lookup" information (such as the number of parts in
inventory at the moment).

 

[Fred Boer]

Dear Ken:

More interesting suggestions for an event I'd never even considered using!
You people are just so darn clever!

Thanks!

Fred

 

[Ken Snell]

Running out of form real estate tends to make you creative...as you know!

--
Ken Snell
<MS ACCESS MVP>

Dear Ken:

More interesting suggestions for an event I'd never even considered using!
You people are just so darn clever!

Thanks!

Fred

 

[Meirans-Mertens]

Hello Marshall:

First of all, thank you for responding to my inquiry. For the moment,
however, I will use Dirk Goldgar's solution because it is exactly what I was
hoping for and because it works really nicely (within the restrictions he
mentions). Naturally, I will keep your contribution in my files for future
use.

Your comments--and those of Dirk Goldgar--about the answers to my previous
posting make me think that I may have interpreted these answers incorrectly.
Thank you for making me think twice about this.

Please, let me know whether I can be of any help in return.

Sincerely,

Nicolas.

 

[Meirans-Mertens]

Hello Paul, and thank you for your response. For the moment, however, I will
use Dirk Goldgar's solution because it applies more directly to what I had
in mind. (Please, try his code, you'll see how nicely it works, within the
restrictions he mentions.) However, I'm keeping your solution in my files
because I'm sure that I will be able to use it in the future.

Thanks again for your contribution, and thanks also for your nice
"inclinations."

Nicolas.

 

[Meirans-Mertens]

Hello Dirk:

Thanks a lot for your code, it is exactly what I was hoping for. Thanks also
for your comments, which make me understand better than before that using
the Delete key or the Ctrl+X combination to delete an entry in a combo box
is to extend too much the standard behavior of either. On the other hand, I
appreciate that you find "perfectly reasonable" my wish to use a combo-box
in a non-standard manner. Finally, thank you for mentioning the potential
problems with your solution. For the moment, these problems do not apply to
my application (no enforced relationships, and only one column in the lookup
table and in the
combo-box), so that, when you delete an entry in the combo-box, the entry in
question disappears from the combo-box list and from the underlying table,

but< the name of the entry remains in the field of previous data entry.

Furthermore, once the list has been altered, there is no problem in
generating reports (previous data remain intact). So,
all in all, your solution works beautifully.

Thanks again for everything, and please don't hesitate to let me know if I
can be of any help.

Sincerely,

Nicolas.

I already posted the following question on 12/01/03 ("Deleting
entries in combo boxes," from Meirans-Mertens) but got only negative
results. I'm appealing to the people mentioned in the subject line
because they helped me in the past with complicated questions, and
I'm relying now on their open-minded attitude and reasoning skills. I
would also appreciate the input of anyone with similar inclinations.

The question: Is it possible to have a VB event procedure such that,
on double-clicking (for example, or right-clicking) an entry in a
combo box in a form, the user
would have the opportunity to delete the selected entry in the
underlying lookup table? And if this is possible, would you be
willing to tell me what the code might be? (This, perhaps, could be a
variant on a feature available in one of the Access 97 wizards, Music
Collection, where users can double-click a combo-box to open a
subform and add some data relevant to the field where the combo box
resides.)

If this is not possible, so be it and I'll use the alternative method
I mentioned in my previous posting (i.e., make the lookup table
available to the user), or try to work out something from the Access
97 wizard. But please, if anyone other than the good people mentioned
above does not want to get involved with this problem, for any reason
whatsoever, or has no constructive answer to it, just leave this post
unanswered. No one addressing
this forum needs haughty, flippant, and condescending responses like
the ones I got from my 12/01/03 posting. Let's stick to the problem
and be professional about it. Or business-like. If someone thinks
that the solution to this problem should be renumerated, please let
me know.

Thanks in advance.

Nicolas Mertens.

Both of your previous respondents were giving you good advice with
regard to the question you originally asked, in which you wanted to
replace the normal, standard behavior of the Delete key or the Ctrl+X
combination to perform this function. They said, "Don't do it," and
they were right.

However, what you are asking now is not to replace a standard UI
behavior but to add a new behavior to a non-standardized event. This is
perfectly reasonable, in my view (though I use the DblClick event in
combo boxes for something else in all my applications). Here's how you
might do it:

'----- start of example code -----
Private Sub Property_DblClick()

Dim strPropertyName As String

strPropertyName = Me!Property & ""

If Len(strPropertyName) = 0 Then
Exit Sub
End If

If MsgBox( _
"Do you want to delete '" & strPropertyName & _
"' from the list of properties?", _
vbYesNo + vbQuestion, _
"Delete Property?") _
= vbYes _
Then

Me!Property = Null

CurrentDb.Execute _
"DELETE FROM [Property Names] " & _
"WHERE [Property Name] = " & _
Chr(34) & strPropertyName & Chr(34), _
dbFailOnError

Me!Property.Requery

End If

End Sub
'----- end of example code -----

Aside from any errors I may have made in the above "air code", there are
two potential problems with this solution. Both only arise if there are
other records in the form's recordsource table with this same property
name. First, if there is an enforced relationship between [Property
Names].[Property Name] and the Property field in the table on which the
form is based, then you won't be able to delete the record from
[Property Names] if there are other records with this Property Name,
unless you also have Cascade Deletes set for the relationship (which I
wouldn't recommend).

Second, even if you don't have an enforced relationship, if you delete
the property name from the table, and hence from the combo box's list,
then depending on how you have the combo box set up you may have
problems with the display of existing records that have that property
name. You won't if the combo box only has one column, the property
name.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Paul Rohorzka]

Hi Nicolas!

Hello Paul, and thank you for your response. For the moment, however, I will
use Dirk Goldgar's solution because it applies more directly to what I had
in mind. (Please, try his code, you'll see how nicely it works, within the
restrictions he mentions.) However, I'm keeping your solution in my files
because I'm sure that I will be able to use it in the future.

BTW, the solutions are conceptional identical.

Ciao,
Paul