Share via

How can I modify a form's controls when the recordset changes and the view is continuous forms?

Anonymous
2021-05-11T22:52:32+00:00

What is the appropriate form event that will allow me to access the properties of a control when the recordset is changed and the default view is "continuous forms"?

In my case I have a form whose default view is continuous forms. The form's recordset can be set from a combo box on the form's page header which basically applies a filter to the records in a table. After the filter is applied, the qualifying records display in the detail section as individual forms.

The problems is I do not know how to access the controls on the individual forms.  I cannot find an event that fires when a new filter is applied to the form and the new records are displayed.

For example, as a new recordset is displayed I need to evaluate the values of certain controls and conditionally modify their properties such as check a checkbox or display text in red based on the value of that record. 

How can I modify a form's controls when the recordset changes and the view is continuous forms?

Thanks

Dave

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2021-05-12T13:49:28+00:00

    I saw your other responses Specifically this quote:

    " It is not possible to evaluate each of the 10 records in the new recordset and determine, whether the checkbox should be checked for that individual record. "

    No this is not possible using just the form. But that has nothing to do with whether its a continuous form, datasheet or single form. Access does not process each record as it displays a new recordset.

    But that capability does exist if you use code. Once a selection is made in the combobox, you would use the After Update event of the combo to clone the new recordset, loop through each record and evaluate each record setting the boolean field to yes or no.

    Looping is one of the most important tools of the programmer. So the functionality you are looking for is an application processing issue, not a forms issue. But the capability exists in Access, just not where you are looking for it.

    1 person found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2021-05-12T06:24:02+00:00

    I need an event that fires as the records are output to the form, with no further action required by the user,  

    Hi Dave,

    I hope I do understand your question in the right way.

    You have a continuous form with a number of records, and the you want to change the recordset to a different recordset, probably using a a different filter from a combobox.

    When accepting the new value of the combobox, you could run some code that creates a recordset with the new selection, loop through the records, and adhust any values.

    After that you can assign the SQL-string of that recordset to the RecordSource of the form. Or in your case, change the filter condition of the form to the new condition.

    Fun to work in this way. 

    Imb.

    1 person found this answer helpful.
    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-05-12T01:34:15+00:00

    Thank you

    I was disappointed to hear this but it appears I will not be able to accomplish what I want.

    I tried playing with various events but can't find any that fire as each record (form) is being spit out to the forms detail section. For example, I used code similar to that below for different events but nothing fires for individual records:

     Private Sub Form_Current()

      Debug.Print "FIRE Form_Current()"

     End Sub

    In the early days of web development we used to blend our VBA/ADO code with the HTML and after opening a recordset we could evaluate each record individually as we looped through it. As a result we had an opportunity to make formatting changes based on the data values returned but before an individual record was spit out to the page.. I imagine Access is also looping through a recordset and spitting out records (forms) but apparently they have not provided us with a way to evaluate each record and modify bound control properties before the record is displayed.

    As a hack I might be able to alter my data to give me the values I desire for my controls. For example, I could add a field in the record set that evaluates to -1 if the checkbox for that record should be checked. Then I just set the control source of the checkbox to the field value returned. But now I'm mixing database code with interface code which usually leads to complications and problems down the road.

    If anyone has a better idea, please let me know.

    0 comments
  2. DBG 11,711 Reputation points Volunteer Moderator
    2021-05-11T23:53:39+00:00

    You said:

    "The form's recordset can be set from a combo box on the form's page header which basically applies a filter to the records in a table."

    If you're changing the record source of the form, as Scott said, you should be able to use the Current event. 

    But, if you're merely applying a Filter, maybe you could try the OnApplyFilter event. 

    Sent from phone...

    0 comments
  3. Anonymous
    2021-05-11T23:48:25+00:00

    Hi Dave, I'm an independent adviser and will try to help.

    A Continuous form is actually a single set of controls that can be displayed multiple times. So there is no way to address the properties of control for a specific record. Anything you do to a control is reflected in all rows of the Continuous form.

    The only way I know of to do anything along the lines of what you want is to use Conditional formatting. Conditional formatting is applied after the record is displayed. This can set the control to red, but it won't allow you to check a checkbox.

    When a recordset changes the form is requeryed and reformatted.

    The On Current event is triggered when the focus is placed in a different row. So, if you can use that to set the values of any bound control.

    Iv you need any further clarification on this please feel free to ask.

    0 comments