Using filter to view only specific records in a form.

[bubai]

I use a form to input data in my table. However, I don't fill up all the fields at a time. I have to fill them up step by step at different stages. So, I need to apply some sort of filter to my form so that it shows only those records where I have at least one empty field or I have off/false value for Yes/no fields. I've seen 2 filter options in the form's property sheet, but not sure how to use them. Also, I know there are filter option in VBA but never used them.

[Welshgasman]

If you do not want to use VBA, just use the standard filter options from the ribbon?
TBH manually might be the better option in your case?

[Hipromark]

I think in that case you will need to use a Macro with if statements, so that way you can identify if a field is empty on each records and show the record according to that

You could use a saverecord function and then use the if statement, if you want a most atomated process you will need to use VBA.

But it all depends on the steps you are doing to fill out each record, I think it is not difficult to do it but will need to know more details to help you better

[bubai]

If you do not want to use VBA, just use the standard filter options from the ribbon?
TBH manually might be the better option in your case?

If VBA gives legitimate result, then I am not against it. And I think I am comfortable in using it.

[ArviLaanemets]

This is case where a calculated field in table may be best solution:

You have an boolean field e.g. RowLock in your table;
The control linked to this field is set Not Visible in your form;
The Form's Filter property is set to 'RowLock = FALSE', and FilterOn property is set to TRUE by default;
Form's BeforeUpdate event calculates a new value for RowLock, whenever any changes are made to any field of current record. When all conditions for record to be locked are filled, the value of hidden control is set to TRUE, otherwise to FALSE (NB! The value of control is changed, not the value of field!);
(Without checking it out I'm not entirely sure, do you have refresh the form after that, or does the filter apply to recently saved record without any additional actions. You have to check this out yourself.)

BeforeUpdate event practically needs a single row of code to calculate a new RowLock value, like

Me.txtRowLock = ((Control1 TrueExpression1) AND (Control2 TrueExpression2) AND ... )

You can also have an unbound combo box in form header, with RowSource as ValueList like '1, "not locked", 2, "locked", 3, "all"', and DefaultValue = 1. Form's BeforeUpdate event then takes into account the selected filtering type, and changing the filtering type by selecting another choice in combo fires it's AfterUpdate event, which resets form filtering too.

[bubai]

But I don't want some controls to be hidden and some not. I want all of them to be visible if any field of current record is empty and any yes/no field is false. Also I need the form to load only those specific records and I need the form to allow me to move through only those records. Not all records.

[ArviLaanemets]

But I don't want some controls to be hidden and some not. I want all of them to be visible if any field of current record is empty and any yes/no field is false.

This specific control is needed only for form to decide, which records are displayed. No need for you to see it at all. And in no way you must have even a possibility to edit it!

Also I need the form to load only those specific records and I need the form to allow me to move through only those records. Not all records.

And this solution does exactly this. Unless you add this unbound combo into header. When want and do this, then you can switch between displaying only unlocked records, displaying only locked records, or displaying all records.

[bubai]

Form's BeforeUpdate event calculates a new value for RowLock, whenever any changes are made to any field of current record.

How do I carry out this? What difference does it make to current record whether the Me.txtRowLock value is true or not? And how does it help to load the current record to the form?

BeforeUpdate event practically needs a single row of code to calculate a new RowLock value, like

Me.txtRowLock = ((Control1 TrueExpression1) AND (Control2 TrueExpression2) AND ... )

I think instead of AND there should be OR within the expressions as I need to see the record depending on any of the field's NULL value.

[ArviLaanemets]

Here is a simple working example!

[bubai]

Here is a simple working example!

I have a few questions.
1. Is RowLock a keyword which is automatically evaluated by Access?
2. Why the txtField1 is converted to 0 but txtField2 and txtField3 are converted to "" in the Before update event?
3. Is it necessary to use NZ function? Can't we evaluate with "Is Not Null"?
4. Do I have to evaluate every field's control or can I do it with few of them?
5. My form has some unbound controls, can I spare them from evaluation?

[ArviLaanemets]

1. Is RowLock a keyword which is automatically evaluated by Access?

RowLock is a name of table field I used in my example - you can use any other name you want (but don't use any words reserved by Access, like 'Lock').

2. Why the txtField1 is converted to 0 but txtField2 and txtField3 are converted to "" in the Before update event?

My bad there! At start I defined Field1 as numeric, but decided it will be easier to go with all 3 text fields. I forgot to edit the event.

3. Is it necessary to use NZ function? Can't we evaluate with "Is Not Null"?

You can't check field values in BeforeUpdate event, because at time when BeforeUpdate event is running, no changes to any of fields you did make are saved to table yet. So you have to check control's values instead. Ant to check is the control empty or not, Nz() is used.
Why I dropped using a numeric field in my example, is that then value 0 may be a valid one, so Nz(NumericControl,0) is not applicable. The workaround is to use some impossible value instead, like Nz(NumericControl,9999999999).

4. Do I have to evaluate every field's control or can I do it with few of them?

No limits of this kind at all.

5. My form has some unbound controls, can I spare them from evaluation?

Using them in evaluation is meaningless as best, or you'll mess your app up entirely! Entry locking is made entirely based on values in table row which will be saved as next step - i.e. in same row!

[bubai]

My bad there! At start I defined Field1 as numeric, but decided it will be easier to go with all 3 text fields. I forgot to edit the event.

Your bad turned out to be helpful to me....
Because, for numeric fields I used "Nz(Me.FK_ConsID, 0) > 0"
For Dates I used 'Nz(Me.Paid_Dt, "") <> "" ' and
For Checkbox controls I used no NZ function but (Me.Paid <> False) and they all seem to be working.

However, is there a way to see the records where RowLock value is unchecked/False? Or to see all records in this form?

[ArviLaanemets]

However, is there a way to see the records where RowLock value is unchecked/False?

Currently you just see records with RowLock = FALSE (i.e. unlocked records). Did you mean can you see locked records?

Or to see all records in this form?

For the form to display either locked or all records, an unbound check box in form header I described in end of my 1st post is a way to go. You select there what kind of records you want to see (not locked, locked or all). The number of selection (1, 2 0r 3) is the value according your selection attached to combo. The AfterUpdate event edits the filter condition and fires command FilterOn = TRUE to reset form's filter. And you have to change form's BeforeUpdate and AfterUpdate events too - the code must check combo's value, and then fire appropriate commands.

On fly the code for combo's AfterUpdate event will be something like

...
IF Me.cbbFormFilter = 1 THEN Me.Filter = 'RowLock = FALSE'
ELSEIF Me.cbbFilter = 2 THEN Me.Filter = 'RowLock = TRUE'
ELSEIF Me.cbbFilter = 3 THEN Me.Filter = ''
END IF
Me.FilterOn = TRUE
...

[bubai]

The AfterUpdate event edits the filter condition and fires command FilterOn = TRUE to reset form's filter. And you have to change form's BeforeUpdate and AfterUpdate events too - the code must check combo's value, and then fire appropriate commands.

I have put the following code to form's AfterUpdate and OnLoad events, where cboOption is the combobox to choose the filter options. I also placed the same code to cboOption's AfterUpdate event. When the form loads the cboOption remains blank. So I have used 1 to show locked record and 2 to show unlocked records. And when the combobox remains blank it refers to Else case and shows all records. I did not make any change to BeforeUpdate event as it just evaluates the RowLock field.

Select Case Me.cboOption
Case 1
    Me.Filter = "RowLock = FALSE"
    Me.FilterOn = True
Case 2
    Me.Filter = "RowLock = True"
    Me.FilterOn = True
Case Else
    Me.Filter = ""
    Me.FilterOn = False
End Select

But in the form's Property Sheet there is still RowLock = FALSE in the Filter property and 'Yes' in the Filter On Load property. Should I keep them now?
Is there any other correction required?

[ArviLaanemets]

I modified my earlier example.