Macro in form stops working after opened with vba

0
votes

Please help me explain, why this happens, after that the solution should be easy :)

I have two forms, showing different data.

Form_1: there is a combo box (with names in it), where you can choose which company you wanna see, and an after-update macro searches the record (an [ID] field), and shows the information. (To be more complicated, this [ID] field is hidden, and used for subforms, where the actual infos appear.)

Form_2: this is a continuous form, each record is in connection with the companies shown in Form_1, but several record can belong to one company. There is a button for every record to open Form_1 with the information connected to it. The vba code of the button is:

Private Sub Button_Click()
DoCmd.OpenForm "Form_1", , , "[ID] = " & Me![ID]
End Sub

In the code, the same [ID] field is used, as described above: hidden and used for subforms. Both forms are working as needed, I am happy with them.

But after Form_1 is opened from Form_2 with the button, the combo box remains empty (actually I don't need it to be filled), and if I wanna use it to search for other items, it doesn't work, as if the macro wasn't loaded. The list of names appear, I can click on any of them, but the [ID] field is not refreshed (and of course neither the subforms). I have to close the form, and open it again from the side-list.

Why does the macro stop working? What should I change, to make it work?

Thanks for your help!

1
vbams-access
This sounds as if it would be better handled by a sub-form linked to your main form. Despite access complaining that you cant have a sub form linked to a continuous form, it's lying to to you. Just set it up and after it resets your forms view to single form , set it back to continuous. - Minty
The subforms are not linked to the continuous form. Form_1 is a single-record form, where the main form contains the [ID] field, which is connected to everywhere (refreshed by the combo-box macro, set by Form_2, and connected to the subforms. - barathg
This is difficult to visualize, you may need to use one fo the forms On_Current event to refresh the ID value on the top level form? Maybe a picture of your forms and some notes would help. - Minty
You reference a combobox macro and state that code is what fails but then you don't provide that code for analysis. The combobox used to enter filter criteria must be UNBOUND - since you say it is empty it would appear that is the case. - June7

1 Answers

0
votes

Form1 has the filter turned on to a specific key value, so attempts to find and reposition the form's current record will fail without explicitly resetting the filter.

The Where condition of the OpenForm command does not change the form's Record Source property, nor does it perform a simple search/reposition. Rather, it applies a form filter:

DoCmd.OpenForm "Form_1", , , "[ID] = " & Me![ID]

This state is indicated in several ways

  • On the Home ribbon (i.e. toolbar): The Toggle Filter button is active... is highlighted by a different color.
  • The form's navigation bar at the bottom of the form show "Filtered" highlighted with a little funnel icon.
  • The Access status bar shows "Filtered" on the right near other indicators.

Of course it's possible that all of those indicators are hidden, so you just need to be aware of what each command and parameter does.

Possible solutions:

  • Form1's ComboBox.AfterUpdate macro should turn off the filter before searching for a new ID value.
  • Form2's Button_Click event opens the form without applying a filter and instead runs code that does the same thing as the ComboBox.AfterUpdate method--searches and repositions the form's record rather than filtering it.
    • This can be achieved in multiple ways and is largely beyond the scope of this answer, but a hint is to make a Public method in the Form1 module that performs the search. Both the ComboBox.AfterUpdate method and the other button call that same public method so they have the same behavior.