The issue I have is a bit complex (for me anyway) and I am looking for a right direction nudge.
I have a table with a list of contracts for my business. I have a working filter which filters out on the status, the legal adviser and the division. Filter works fine until it returns no records. The form then moves to expecting a new record and due to Required fields in the table, I can't refilter. I tried shutting the form off to additions but this has not solved the issue. I then thought that I should get a recordset before turning on the filter and based on the number of records returned evaluate whether to turn the filter on or not. I have never really had to work with recordsets and I am not sure if I have the right idea or not.
The process would be:
1. Open a recordset the same as as the filter will produce when it is turned on. I am assuming that if it returns 0 this will not give the issue with the form moving to a new record that I currently have.
2. Evaluate the number of records in the recordset (or look at EOF)
3. If the number of records is 0 don't turn the filter on and tell the user there are no records to return.
4. If the number of records is more than 0 turn the filter on.
Does this sound logical? I can work out the actual programming I think if the logical path seems right - probably with a lot of trial and error.