Using another forms field value

[Guest]

I have a large table of data with a form built onto it. To filter the data I
have another form with a combobox that is populated from a SQL statement
(which is a concatentation of three keyword fields in the underlying table).
After selecting an option in the combobox and clicking the search button the
original form opens filtering the data on the combobox selection.

On the filtered form I want to set the forms Caption to display the value
chosen in the combobox of the search form using the OnCurrent event. I can't
seem to catch the value of the search form's combobox. I have tried using:

Forms!frmName!cboBox

as per the Access Help Files, but I keep getting an error message saying
Access can't find the frmName, even though both forms are in the same
application. I've also tried using Me! in front of the above but to no avail.

It's probably simple but it's just bugging me now.

Thanks for any ideas.

 

[Rob Parker]

frmName must be open for the Forms!frmName!cboBox expression to work. Are
you closing the form after the search operation? If so, don't; instead,
simply hide the form if you don't want it to still be visible.

HTH,

Rob

 

[Guest]

Hi Rob,

Brilliant, thanks very much. That does lead me to a follow up question
though, if on the results form I want to close that form as well as the
frmName that is only set to .Visible = False, how can I do that. The
DoCmd.Close operation doesn't give me the option to specify which form to
close as does the DoCmd.OpenForm action. If I don't close the original form,
frmName, the next time I open it the previous selection is still there. I
guess I could use a line to clear the value in the combobox in the forms'
OnOpen event, but does this make for good programming practice to have the
form constantly open, albeit sometimes set to invisible?

Thanks for your help,
Ian.

 

[Rob Parker]

Hi Ian,

DoCmd.Close does give you options to specify the form (or other object) to
close. The full syntax is:

DoCmd.Close [ObjectType],[ObjectName],[SaveAction]

So when you close the results form, you can also close frmName, thus:

Private Sub Form_Close()
DoCmd.Close acForm, "frmName", acSaveNo
End Sub

Putting the code in the Close event of the results form will ensure that it
runs regardless of how the user closes the form.

BTW, leaving a form open and hidden is a not uncommon practice. There
should be no problem with leaving frmName open but hidden, and clearing the
combobox when the form is unhidden, if you'd rather do it that way.

HTH,

Rob