Button to save record and close form if certain requirements are met

[wrweaver]

I'm creating a work order submission form where I want the person submitting to have to fill out 6 fields before they can submit the saved record. I need a button that will check each of those fields and if one is isn't filled in, display a message box saying which field is missing. Then, if all the fields are filled in, the button will save the record, close the form, and tell them they have submitted the work order.

I have no idea where to start other than

Dim MyReply

MyReply = MsgBox("Do you wish to submit this Work Order?", vbYesNo)
If MyReply = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
If MyReply = vbNo Then
Exit Sub
End If

Any help would be greatly appreciated! Thank you!

 

[pbaldy]

Most of us do validation in the before update event:

http://www.baldyweb.com/BeforeUpdate.htm

 

[wrweaver]

So I enter that code in 6 times for each field?

 

[pbaldy]

Yes, and you can either break out after the first failed test or test all fields and report all failed tests to the user.

 

[wrweaver]

Okay Heres the code I have:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.SectorCombo & vbNullString) = 0 Then
MsgBox "Please Select a Sector"
Cancel = True
Me.SectorCombo.SetFocus
End If
If Len(Me.StationCombo & vbNullString) = 0 Then
MsgBox "Please Select a Station"
Cancel = True
Me.StationCombo.SetFocus
End If

If Len(Me.BuildingCombo & vbNullString) = 0 Then
MsgBox "Please Select a Building"
Cancel = True
Me.StationCombo.SetFocus
End If
End Sub

But then it tells me with different individual message boxes what is missing then gives me the error You can't save record at this time.

Microsoft Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?

 

[wrweaver]

How do I make it so it only has one message box that lists the needed fields? And how do i make it so that they can just close the form without it saving the record?

 

[mdlueck]

So I enter that code in 6 times for each field?

I prefer to put my validation on the Commit / Save button. If the validation passes, the code goes onto committing the record to the database. If not, then the invalid fields are turned red background and the form remains open.

Validation code only needs to be coded one time in this case.

 

[pbaldy]

How do I make it so it only has one message box that lists the needed fields? And how do i make it so that they can just close the form without it saving the record?

I'm on an iPad right now so don't have code handy, but basically you build a string variable and add to it with each failed test. Then you test the variable at the end, and only give the user one message box.