The fields are date, code, particulars and amount.
I suggest you research 'reserved words' for Access. You must not (I think CANNOT) use Date for a field name. There is a big list and you should be aware of them. Also, research on object naming conventions and adopt some sort of habit because Command31 is a useless name, but it's what I will have to work with.
This code should work for any form, but it relies on two conditions.
1) You have entered a tag value for each control that you want to enforce an entry by the user. This is done on the property sheet for each control. You can do this for any number of controls all at once by clicking on them while holding down the shift key then entering the tag. I suggest to use text only - dates and numbers may introduce problems. For this code I have used
reqd for the tag but you can use whatever you want provided it is not a reserved name.
required is a reserved name. If you use something else, you have to change what I have in the function call.
2) The control's label must be attached, otherwise the code will fail when it can't get the member of its collection (Controls(0)). The work around would be to get the control name itself, but not real useful if you allow names like Text15.
One of the best places to put the call to this function is the button click event that attempts to process the user input. If you want to check a form that is loaded but is not the one with the button, replace Me with the proper form reference. So for your event, the code should look like this (in addition to whatever else you need for the event):
Code:
Private Sub Command31_Click()
Dim svMsg As String
svMsg = CheckRequired(Me, "reqd")
If svMsg <> "" Then
MsgBox svMsg
Exit Sub
End If
End Sub
To be able to use this function for all forms in your project, it must be in a standard module, not a form or report module.
Code:
Function CheckRequired(frm As Form, svTag As String) As String
Dim ctl As Control
Dim svList As String, svMsg As String
'ensure function and list = empty string to start
CheckRequired = ""
svList = ""
svMsg = "You must provide data for:" & vbCrLf
On Error Resume Next 'if controltype doesn't allow attached label, move to the next control
'A Select Case block could process only certain types; this is more concise & works just as well.
'iterate through all controls on the form that was passed to the function
For Each ctl In frm
If ctl.Tag = svTag Then 'look for the tag value passed to the function
If IsNull(ctl) Or ctl = "" Then svList = svList & " - " & ctl.Controls(0).Caption & vbCrLf
End If
Next ctl
If svList <> "" Then 'if list not empty, concatenate it to message; set function = message value
svMsg = svMsg & svList
CheckRequired = svMsg
End If 'otherwise, function still = ""
End Function
Please ensure you have understood all the instructions to implement this if you have problems. If you have real basic questions (about property sheets or where to see reserved names) please do some research. Don't post back just saying "It doesn't work". Be specific and include any error message number and message text. If need be, ensure you have the database option to break on all errors (or at least unhandled errors) or introduce your own code break in order to know on which line the error occurs. The code I wrote is not 'air code'. It works, and I hope it works for you too. What it does not do is ensure that the user entered data complies with any database requirement, such as not entering text where a number is required. That has to be dealt with in the rest of your code and/or table properties.
Good luck!
