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

We're getting a bit off topic, but as you mention the OpenArgs mechanism, My Args demo at:

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

illustrates a module first developed by Stuart McCall, one of the best Access and Windows API programmers I've ever known, and later extended by me.  Stuart's original module passed the arguments as a tokenised list, using high ASCII characters as the tokens, and each value was extracted by its ordinal position in the list.  I developed it further in response to a request in a forum to allow it to pass named arguments.  The module is as below:

' module basArgs starts

'This module allows lists of values to be passed to a form or report by means of the

'OpenArgs mechanism.  Values can be passed individually as named arguments:

    'Dim args As String

    ' <add some named arguments>

    'AddArg args, "First", "Apples"

    'AddArg args, "Second", "Pears"

    'AddArg args, "Third", "Bananas"

'Or as an arguments list:

    'AddArgList args, "Oranges", "Peaches", "Grapefruit"

'The list is then passed to the form with:

    'DoCmd.OpenForm "frmMyForm", OpenArgs:=args

'In the form's or report's module the values can be extracted like so:

    'Dim args As String, i As Integer

    'args = Me.OpenArgs

    ' <get some named named arguments>

    'Debug.Print Arg(args, "First")

    'Debug.Print Arg(args, "Second")

    'Debug.Print Arg(args, "Third")

    ' <get some arguments by ordinal position>

    'Debug.Print Arg(args, 4)

    'Debug.Print Arg(args, 5)

    'Debug.Print Arg(args, 6)

    ' <list all arguments>

    'For i = 1 To ArgCount(args)

        'Debug.Print Argname(args, i), Arg(args, i)

    'Next i

    ' get count of arguments

    'Debug.Print ArgCount(args)

Private Const OFFSET As Long = 127

Private Const ASSIGNOP As String = "=="

Function Arg(buffer, idx) As Variant

    If IsNumeric(idx) Then

        i& = InStr(1, buffer, Chr(idx + OFFSET - 1))

        token$ = Chr(idx + OFFSET)

        i& = InStr(i&, buffer, ASSIGNOP) + 2

    Else

        i& = InStr(1, buffer, idx) + Len(idx) + 2

        token$ = Chr(Asc(Mid(buffer, InStr(1, buffer, idx) - 1, 1)) + 1)

    End If

    Arg = Mid(buffer, i&, InStr(i&, buffer, token$) - i&)

End Function

Function Argname(buffer, idx) As String

    i& = InStr(1, buffer, Chr(idx + OFFSET - 1))

    token$ = Chr(idx + OFFSET)

    Argname = Mid(buffer, i& + 1, InStr(i&, buffer, ASSIGNOP) - (i& + 1))

End Function

Function ArgCount(buffer) As Long

    ArgCount = Asc(Right(Chr(OFFSET) & buffer, 1)) - OFFSET

End Function

Sub AddArg(buffer, Argname, argval)

    If Len(buffer & "") = 0 Then buffer = Chr(OFFSET)

    If IsNumeric(Argname) Then Argname = ArgCount(buffer) + 1

    buffer = buffer & Argname & ASSIGNOP & argval & Chr(Asc(Right(buffer, 1)) + 1)

End Sub

Sub AddArgList(buffer, ParamArray Tokens())

    For i& = 0 To UBound(Tokens)

        AddArg buffer, i& + 1, Tokens(i&)

    Next

End Sub

' module basArgs ends