Checking for empty fields

[Jarlisle]

I am very new to access and using vb in access and here's my problem. I don't even know where to start.

I have a database that is going to be used for doing surveys. What I would like done is when a user is done making entries they will click a submit button to copy the answers to the table, but I would like the vb code to check to see if any of the answers were left blank and give them the option to leave it blank or go fill it in.

Also do you know how to make it so that the user only sees the form and doesn't have access to the tables.

The site won't let me post the Access file so I hope that won't impede the help that you can provide.

My table fields are: ID Number, Question 1, Question 2, Question 3, Pleased, Grateful, Indebted, Softhearted, Tender, Happy, Resentful, Mad, Annoyed, Warm, Hurt, Moved, Obligated, Empathetic, Compassion, Upset, Sympathetic, Angry.

[OBP]

The first point that I would like to make is that Access will automatically Save the data to the Table as it is entered or edited, so the Submit button is not actually necessary.
You can use some simple VBA code in the Form's "Before Update" event procedure to inform the user that there is one or more Blank fields with a Yes/No message box asking them if they want to enter the data. However if they go back and enter data in a field and there is still a blank field they will be asked again and this can get a bit wearing.
The other point is from the design of your table it looks like the "Pleased, Grateful, Indebted, Softhearted, Tender, Happy, Resentful, Mad, Annoyed, Warm, Hurt, Moved, Obligated, Empathetic, Compassion, Upset, Sympathetic, Angry" are mutually exclusive, i.e. only one will be ticked, so that may prompt the VBA code to ask the question as well.
If those field responses are mutually exclusive it may be better to have those choices as an Option Group.

[Jarlisle]

Thanks for the reply OBP.
Can you give me any hints or starting points to start the VBA code in the form's "Before Update" event?
As far as the fields "Pleased, Grateful, Indebted, Softhearted, Tender, Happy, Resentful, Mad, Annoyed, Warm, Hurt, Moved, Obligated, Empathetic, Compassion, Upset, Sympathetic, Angry", they all have a scale of 1-10 that the user will check based on the questions associated with those fields.
I'm getting better with macros in Excel, although I can't write it exclusively without recording the macro first, but I don't understnad VBA in Access yet.

[geekgirlau]

On a simple level, you can check a single field by doing the following:


If IsNull(me.Pleased) Then
...
End If


However this can get very tedious very quickly. Another way is to use the control tag to mark all the fields you want to check.

In the design view for your form, give each control you want to check the same Tag (view Properties, and go to the "Other" tab). Then your code can loop through all of the controls.


Dim ctl As Control
Dim strMsg As String

' loop through every control on the form
For Each ctl In Me.Controls
' look for a particular tag
If ctl.Tag = "Check" Then
' create a list of the empty questions
If IsNull(ctl) Then
strMsg = strMsg & "- " & ctl.Name & vbCrLf
End If
End If
Next ctl
' did we find any unanswered questions?
If strMsg <> "" Then
If vbNo = MsgBox("The following questions have not been answered" & vbCrLf & vbCrLf & _
strMsg & vbCrLf & vbCrLf & "Do you want to proceed anyway?", _
vbQuestion + vbYesNo + vbDefaultButton2, "Unanswered Questions") Then
Cancel = True
End If
End If

[OBP]

geekgirlau, that is a really neat piece of coding. :thumb

[geekgirlau]

I missed VB's ability to create control groups, so I use tags a fair bit as a replacement. I haven't check Access 2007 yet to see if you can group controls as per VB ... :think: