MS Access - Display message if field left empty

Question

Hi, Hope someone can help. 

I have a form containing quite a lot of text boxes and combo boxes. All of the fields are mandatory and must be filled in before saving the form. I am trying to implement a command button VBA on the OnClick property as seen below; 

Private Sub comCheck_Click()

If IsNull(txtInspectedQTY) Then 

MsgBox "You have to chose something in the inspected quantity field!", , "Incomplete Form!"

txtInspectedQTY.SetFocus

End If

End sub

All of the fields mandatory have the field 'required' set as yes.

1- Is there a way for the VBA to pick out all fields with the required Yes and pull up the comment to say "Incomplete form, Please fill in _________" ? 

2 - In addition to this if there is nothing incomplete, can my save command (ComSave) visible go to true? 

3 - Sometime a form might not get filled in all the way before the end of the day, so i would need an option to override the VBA code and save the record to come back to it. IS this possible? Maybe the save button is present all the time unlike in question 2. 

I hope i have made this as clear as possible and any help is appreciated. :)

Answer 1

If you want mandatory fields and want to be able to leave a particular record/form at the end of the day to return to tomorrow, a solution would be:

remove 'required' set as yes

Add "reqd" (or similar) to the tag property for each of the mandatory fields.

Create a table:

IncompleteIDS (autonumber)

IncompleteID (number)

Create a dialog form with 3 buttons (fdlgMand in this example)

Continue

Save and Exit

Discard

On the close button of your main form add something like:

Dim ctrl As control

For Each ctrl In Me.Controls

    If ctrl.Tag = "Reqd" Then

      If IsNull(ctrl) Then

           DoCmd.OpenForm "fdlgMand", , , , , , ctrl.Name

      Exit Sub

         Else

      DoCmd.RunSQL ("delete * from tblIncomplete")

      DoCmd.Close acForm, "Myform"

      Exit Sub

      End If

   End If

Next ctrl

If a mandatory field is not complete it will open fdlgMand

FdlgMand:

Discard button ... add some code to remove the record and delete all from tblIncomplete

Continue Button... returns to main form to continue

Save and Exit... On the onclick event add something like:

Dim rs As DAO.Recordset

        Set rs = CurrentDb.OpenRecordset("select * from tblincomplete;")

            With rs

                .AddNew

                    !incompleteRec = [Forms]![MyForm]![ID]

                .Update

            End With

               'Clean up

                rs.Close

                Set rs = Nothing

DoCmd.Close acForm, "MyForm"

DoCmd.Close acForm, "fdlgMand"

This saves the ID number of the incomplete form to tblIncomplete and closes fdlgmand and Myform

So you now have a record of which form is incomplete.

On the button to open your main form add something like the following:

Dim intIncomplete As Integer

intIncomplete = Nz(DLookup("incompleterec", "tblincomplete"), 0)

If intIncomplete > 0 Then

DoCmd.OpenForm "MyForm", , , "[ID] = " & intIncomplete

Else

DoCmd.OpenForm "MyForm"

End If

So when you return the following day and press the button to open your form it will take you directly to the form left incomplete the night before.

If you have multiple users and each may leave a form incomplete, add a simple login form and capture their ID to include in the dlookup.

You will need to tighten up the code but it will work.

Answer 2

You cannot make a column mandatory in the table design by setting its Required property to True (Yes), but also save the record for later completion while one or more of the required controls is Null.  You would have to set the Required property of the latter columns to False (No).  The user can be warned that these controls need completion later, however.

To enforce a value in all required controls, and warn a user if a control is left Null for later completion, first set the Tag property of the immediately mandatory controls to RequireMe.  Set the Tag property of the controls which can be left Null for later completion to WarnUser.  Then in the form's BeforeUpdate procedure put the following code:

Dim ctrl As Control

For Each ctrl In Me.Controls

    If ctrl.Tag = "RequireMe" Then

      If IsNull(ctrl) Then

          MsgBox "Incomplete form, Please fill in " & ctrl.Name, vbExclamation, "Invalid Operation"

          Cancel = True

          Exit Sub

      End If

  ElseIf ctrl.Tag = "WarnUser" Then

      MsgBox "Incomplete form, ctrl.Name & " must be completed later", vbInformation, "Warning"

  End If

Next ctrl

For an illustration of how to force a user to save a record only by means of a command button, and to enforce entries into certain controls in the form, you might like to take a look at SaveDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly. 

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

Answer 3

Hi Ken, Thanks for your help. 

Due to what you have stated i am not going to use the RequireMe function as the form could be stopped at any point. So using the code below i am receiving an error. 

Dim ctrl As Control

For Each ctrl In Me.Controls

    If ctrl.Tag = "WarnUser" Then

      MsgBox "Incomplete form, ctrl.Name & " must be completed later", vbInformation, "Warning"

  End If

Next ctrl

The error is on the "must" and states "compile error: expected end of statement"

Any clue?

Answer 4

Mea culpa!  I omitted a closing quotes character after the first argument:

MsgBox "Incomplete form", ctrl.Name & " must be completed later", vbInformation, "Warning"

Answer 5

So my code now is bellow;

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctrl As Control

For Each ctrl In Me.Controls

If ctrl.Tag = "WarnUser" Then

MsgBox "Incomplete form", ctrl.Name & " must be completed later", vbInformation, "Warning"

End If

Next ctrl

End Sub

I am receiving another error on the MsgBox line saying "type Mismatch"

Is there no way i can input the code the onclick of a command?