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

It's a rarety that I disagree with my friend Ken, but today is such a day.

There is a HUGE problem with the tag property, which is that the developer needs to keep the database design in sync with the form. There is a smaller problem with having more than one tag value but Split can handle that.

In bound forms the information whether a control is bound to a required field is already available: such forms have a RecordsetClone property, and this recordset has a Fields collection, and a Field has a Required property.

There are a few finer points, but this is the essence. Highly recommended if you are working on re-usable code you can just call from a form and get required field handling without further ado with Tag properties.

Answer 3

Sorry, I misunderstood your last post. It should be:

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

You can use a button's Click event procedure if you prefer, but it won't execute automatically when the user saves the record.

Answer 4

Always glad to have your input, Tom, and any opinion of yours is invariably well founded.  Do you have any suggestions for an alternative approach in a situation where one might wish to identify a subset of columns where there is no inherent property of each which would serve to identify the control?  One scenario  I have in mind is where the values entered into some, but not all, bound controls in a form are to be carried forward as the DefaultValue property to rows subsequently inserted in the current session.

Answer 5

Hi Ken, I had the OP's scenario in mind. Different scenarios like the one you are illustrating of course need different solutions, and I'm not opposed at all to using Tag property for that. In fact I'm working on an unbound scenario right now, and it relies very heavily on it. In most cases I would store data in querystring format: name=value&name2=value2&... so the property value is self-describing. I do the same thing with OpenArgs.