Access Form open to next incomplete record

[DocBlack4444]

Hello,

I am building an Access Database that uses one table and two forms.

One user puts part of the information into one form as a request to be completed and another user opens their version of a form that allows them to "complete" the request by putting in their information.

I have split the database and sent all users the front end while I maintained the back end within a shared file. all updates are working just fine.

The issue that I am having is that when the second user opens the second form, the form always opens to the first record. What I want it to do is open to the first record that has a null in a specific field (the next request that I want them to process). If anyone can help me figure this out, it would be really appreciated.

[PhilOfWalton]

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]

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

[DocBlack4444]

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]

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]

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]

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

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]

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]

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

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]

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

[PhilOfWalton]

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:-

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]

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

[DocBlack4444]

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

[PhilOfWalton]

@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