Access Form open to next incomplete record

**PhilOfWalton** · Sep 25 '18, 10:54 PM

Both forms should have an identical underlying recordsource (particularly the sort order), but in the RecordSource for form 2, you need to set the criteria that your specific field IsNull.

Phil

**DocBlack4444** · Sep 25 '18, 10:55 PM

I am pretty new to Access, so please go easy.

**DocBlack4444** · Sep 25 '18, 11:02 PM

The record source is the same for both forms. The first form puts in 8 fields and the second reads those fields and puts in 3 more. I want the second form to open to the next record that does not already have those 3 fields filled in. That was the Null part I was talking about.

**PhilOfWalton** · Sep 25 '18, 11:07 PM

I'll be as gentle as I can.

Are you using a Query as the RecordSource, if so, do you know how to use the Query Design to create the query?

Phil

**twinnyfo** · Sep 26 '18, 08:50 AM

No need for a query for the record source for the second form. Just set the Filter to [FieldName] Is Null. Just make sure the FilterOn property is set to “Yes”.

Hope this hepps!

**PhilOfWalton** · Sep 26 '18, 02:41 PM

Twinnyfo is correct, but you need to consider exactly what you mean by three fields being Null. Do you mean all 3 fields being Null, or any one of the 3 fields being Null.
I rather suspect the latter option.

If so the filter will look like

Code:

Private Sub Form_Open(Cancel As Integer
    Me.Filter = "[FieldName1] Is Null Or [FieldName2] Is Null Or [FieldName3] Is Null"
    Me.FilterOn = True
End Sub

Phil

**DocBlack4444** · Sep 26 '18, 09:34 PM

Yes, all three fields would be Null in my ideal world to be honest.

Now comes the go easy on me part. How would I take that line of code and make the form to the work? The name of the Form is: "Request - MSA" and the fields that read Null are: "Work Completed By", "Date Completed" and "Work Comments".

**DocBlack4444** · Sep 26 '18, 09:54 PM

OK, so I went to Load event for the form and added a Code event: I tried to put in the code as follows:

Code:

Private Sub Form_Open(Cancel As Integer)
    Me.Filter = "[Work Completed By] Is Null"
    Me.FilterOn = True
End Sub

and it gave me an error: the expression On Load you entered as the event property setting produced the following error: Expected: Line number or label or statement or end of statement.

I am so lost right now.

**twinnyfo** · Sep 27 '18, 10:31 AM

You must have something in your OnLoad Event that is causing that error.

**PhilOfWalton** · Sep 27 '18, 03:49 PM

I am confused.

The code you posted is on the OnOpen of the form, but you are referring to the OnLoad.

The Filter needs to be applied on the OnOpen Event

So:-

Code:

Private Sub Form_Open(Cancel As Integer)

    Me.Filter = "[Work Completed By] is Null AND [Date Completed] is Null AND [Work Comments] is Null"
    Me.FilterOn = True
    
End Sub

This assumes you have Controls NAMED EXACTLY "Work Completed By", "Date Completed" and "Work Comments".

This should show the form only when all 3 controls are blank. Change the "AND"s to "OR"s to show the form if any one control is Null.

I Don't like the name of your Form with a minus sign in the middle. Use an underscore if you must, but what's the matter with "RequestMSA "?

Phil

**Rabbit** · Sep 27 '18, 04:13 PM

Looks like you copied the line numbers, you have to get rid of those

**DocBlack4444** · Sep 27 '18, 06:07 PM

That worked PERFECTLY!!!!! Thank you so much for the help.

**PhilOfWalton** · Sep 27 '18, 06:10 PM

@twinnyfo

I suspect as Rabbit pointed out post No 9 did have line nos in the code. I think your edit of the post may have removed them.

@DocBlack4444

Please when you post code or SQL copy it from your Db, then use the [CODE/] that you see at the top of the box, and PASTE your VBA between the "CODE" and "/CODE".
Then we know exactly what you have.

Similarly, if someone provides you with code, Copy it from the Bytes Website and paste it into your module.

Phil