Double-click to bring up Form at Record location

  • Thread starter Thread starter Nick K.
  • Start date Start date
N

Nick K.

I have a combo box in a form. I have added a Double-click event that brings
up another form that contains the same records that are in the combo box.
This can be done using a Macro or VBA code. I have written code that will
move to a particular record matching the one on the combo box. This requires
passing an argument.

Is there an easy way to do this? I would have to add code to many many combo
boxes. What is the best way?

What about refreshing or requerying the the combo box after the called form
is closed?
 
Nick, I also use the interface you describe, and find that users love it,
i.e. double-click the combo to drill-down to the data behind it. There are 2
aspects to this: 1) opening the form; 2) requerying the combo.

1. Opening the form
Write a procedure that opens the target form, either using a WhereCondition
so it just opens with the one record, or else using code that opens the form
with all records and moves to the particular record. You probably want to
also have this code move your form to the new record if the combo is blank
or contains a value that is not matched. This routine probably needs to undo
the combo if there is a partially entered value that does not match
anything, so that this does not foul up requerying the combo later.

2. Requerying the combo
This one is a little more involved, because there could be several forms
open that contain a combo that needs to be brought up to date. Further, at
the time you are designing these forms you don't know what dependencies will
exist in the final product. So what we do is to call a generic function in
the AfterUpdate and AfterDelConfirm events of every form. Then late in the
project we actually write the NotifyCombos() function to handle the
dependencies. It's usually a long'n'messy one (because of the potential
number of dependencies) using Select Case, but each case is relatively
simple, and having them all in one place make maintenance of the database
simple.

IMHO, the results are worth the effort.
 
Hello Nick,

If I am understanding your question:

Alternatively to Allen's methods, you can pass data to the form you are
opening using the OpenArgs property.

The method would look like this:

DoCmd.OpenForm "Some Form", , , , , , [Some Control]

Openargs is available like a public variable and you can assign it to a
control on the form in one of the form Event Procecure, such as the Open
Event.

[That Field] = OpenArgs

You can also contruct a string to sent multiple values to the opening form
like:

DoCmd.OpenForm "Some Form", , , , , , [Some Control] & ";" & [Another
Control] & "; Hi Mom"

You can then Parse Openargs to extract what you want. I use a Function
Called Parse to do that. (someone sent that to me years ago).

To requery a combo, the easiest method I found is the use this code in the
Enter Event of the combo:

Me.ActiveControl.Requery

This will always give you an up to date list each time you enter the
combobox

God Bless,

Mark A. Sam
 
Back
Top