Need to require data entry before record submission

[malamute20]

Hello, I did not want to hijack a thread but I have a similar issue as that posted here:
ww.access-programmers.co.uk/forums/showthread.php?t=207813
(You'll have to add the h t t p : / / w, it won't let me post a real link since I'm a new user )

My boss wants me to be able to mandate that certain fields contain data before a form is submitted (will be emailed to company heads, needs to look good.)

I have tried the suggestion posted by Jeanette Cunningham in the previous thread - added "before update" events under all my controls and made them cancel if null, per the code provided.

I did do it using CODE, not macros or expressions, as that seemed to be the instruction.

What I experience is that nothing stops the form from submitting at all, everything goes through as though I had written no code at all. This is obviously somewhat frustrating.

I am hoping someone may be able to provide me with suggestions as to what I may be missing, so I can figure out how to complete this and make my boss happy.

I wanted to private message the person whose code I was using directly, and don't really see a way to do that on this site. If there's a way to do that I'd appreciate someone giving me a heads up there too.

Thanks a lot in advance, all!

 

[spikepl]

1. If you want help with code, post the code, BUT

I have tried the suggestion posted by Jeanette Cunningham in the previous thread - added "before update" events under all my controls and made them cancel if null, per the code provided.

that is not correct. To check whether or not the record is OK to save, you'd do the checking in the Form's BeforeUpdate event handler, as per the post you linked to. Not in the indivdual controls' events. (If user does not enter anything into a control, that control's BeforeUpdate event would not fire.)

 

[malamute20]

Right, I mispoke. Have it under Form... terminology is still not 100% for me.

Here's my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMissingInfo As String
Dim strMsg As String
If IsNull(Me.[Incident Date]) Then
Cancel = True
strMissingInfo = strMissingInfo & "Incident Date"
End If
If IsNull(Me.[Location]) Then
Cancel = True
strMissingInfo = strMissingInfo & "Location"
End If
If IsNull(Me.[Officer on-scene]) Then
Cancel = True
strMissingInfo = strMissingInfo & "Officer on-scene"
End If
If IsNull(Me.[Incident Start Time]) Then
Cancel = True
strMissingInfo = strMissingInfo & "Incident Start Time"
End If
If IsNull(Me.[Incident End Time]) Then
Cancel = True
strMissingInfo = strMissingInfo & "Incident End Time"
End If
If IsNull(Me.[Supervisor on desk]) Then
Cancel = True
strMissingInfo = strMissingInfo & "Supervisor on desk"
End If
If IsNull(Me.[Officer time arrived on scene]) Then
Cancel = True
strMissingInfo = strMissingInfo & "Officer time arrived on scene"
End If
If IsNull(Me.[Notifications]) Then
Cancel = True
strMissingInfo = strMissingInfo & "Notifications"
End If
If IsNull(Me.[Incident Type]) Then
Cancel = True
strMissingInfo = strMissingInfo & "Incident Type"
End If
If IsNull(Me.[Incident Description]) Then
Cancel = True
strMissingInfo = strMissingInfo & "Incident Description"
End If
If IsNull(Me.[Incident Status: Open/Closed]) Then
Cancel = True
strMissingInfo = strMissingInfo & "Incident Status: Open/Closed"
End If
If Len(strMissingInfo) <> "" Then
strMsg = "The following are required: " & strMissingInfo
MsgBox strMsg
End If

 

[spikepl]

Can you please put you code into code brackets to make it readable? Click Go advanced, select the code ,and press the #

 

[malamute20]

Ah I just realized I should probably mention - I have a command button that emails the form out, and that it is when that is pressed that I would like to send the check to make sure the fields contain data. I'm not sure if this matters, but considering the idiosyncrasies of coding... I guess it could be important.


*Edit: Code is now formatted as code.

 

[spikepl]

Your test for each field should be like this

Len(Me.SomeControl & vbNullString) = 0 'means there is nothing in the field

Right now, if your fields contain "" (i.e. zero-length strings), then your current test will fail.

 

[malamute20]

Your test for each field should be like this

Len(Me.SomeControl & vbNullString) = 0 'means there is nothing in the field

Right now, if your fields contain "" (i.e. zero-length strings), then your current test will fail.

When I enter that line,

Len(Me.[Incident Date] & vbNullString) = "0"

It returns, highlighting "Len" with "Compile error: Expected: Identifier"


*Edit: Nevermind I forgot the If / Then quantities.

Still, when it runs it doesnt stop the submission with a null field.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMissingInfo As String
Dim strMsg As String
If Len(Me.[Incident Date] & vbNullString) = 0 Then
Cancel = True
strMissingInfo = strMissingInfo & "Incident Date"
End If

If Len(strMissingInfo) <> "" Then
strMsg = "The following are required: " & strMissingInfo
MsgBox strMsg
End If

 

[spikepl]

If Len(Me.[Incident Date] & vbNullString) = 0 Then '...[COLOR=Red] not "0[/COLOR]"

 

[malamute20]

If Len(Me.[Incident Date] & vbNullString) = 0 Then '...[COLOR=red] not "0[/COLOR]"

Yes, tried that first. I'm sorry -- I added the quotes as an attempt to resolve the issue and forgot to remove them.

 

[spikepl]

Private Sub Form_BeforeUpdate(Cancel As Integer) 
Dim strMissingInfo As String Dim strMsg As String
[B][COLOR=Red]msgbox Len(Me.[Incident Date] & vbNullString)[/COLOR][/B] 
If Len(Me.[Incident Date] & vbNullString) = 0 Then 
[INDENT]Cancel = True 
strMissingInfo = strMissingInfo & "Incident Date" End If  
[/INDENT]If Len(strMissingInfo) <> "" Then 
[INDENT]strMsg = "The following are required: " & strMissingInfo 
MsgBox strMsg 
[/INDENT]End If

 

[malamute20]

Private Sub Form_BeforeUpdate(Cancel As Integer) 
Dim strMissingInfo As String Dim strMsg As String
[B][COLOR=red]msgbox Len(Me.[Incident Date] & vbNullString)[/COLOR][/B] 
If Len(Me.[Incident Date] & vbNullString) = 0 Then 
[INDENT]Cancel = True 
strMissingInfo = strMissingInfo & "Incident Date" End If  
[/INDENT]If Len(strMissingInfo) <> "" Then 
[INDENT]strMsg = "The following are required: " & strMissingInfo 
MsgBox strMsg 
[/INDENT]End If

I had to rewrite that to clear the red zones as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMissingInfo As String
Dim strMsg As String
MsgBox Len(Me.[Incident Date] & vbNullString)
If Len(Me.[Incident Date] & vbNullString) = 0 Then Cancel = True
strMissingInfo = strMissingInfo & "Incident Date"
End If
If Len(strMissingInfo) <> "" Then strMsg = "The following are required: " & strMissingInfo
MsgBox strMsg
End If
End Sub

It does not stop my command button from submitting the form and emailing it.

 

[spikepl]

It wasnt supposed to. It was supposed to tell you what the value is. Obviously it is not 0, so probably you have one or more spaces in your field

 

[malamute20]

It wasnt supposed to. It was supposed to tell you what the value is. Obviously it is not 0, so probably you have one or more spaces in your field

No sir, I've just attempted this several times, there is no data in the field at all.

This is the source of my confusion, it appears that even when I'm running working code, it is not running correctly.

*Edit: What DOES happen is, it submits my form, posts the email, and did not return any information about the value, or do anything else. It is still acting as though there is no code at all.

 

[spikepl]

Ok lat's take this very slowly.

You put a msgbox in to tell you what the value of
Len(Me.[Incident Date] & vbNullString)
is

run the code and reveal what the msgbox throws up on the screen if anything.

If nothing, then tour code is not exectuted, and probably not called. That means that in the property window of the form, the BeforeUpdate property is empty, and not containing the name of From_beforeUpdate as it should.

 

[spikepl]

The other thing is , if you have entered NOTHING anywhere, then there is nothing to save, so your form can get mailed just fine as is. If the form is not "dirty" then the BeforeUpdate does not fire. You should therefore run similar code from your Command button. But all this implies that you have records containg blank fields, and that should not be allowed in the first place.

 

[malamute20]

Ok lat's take this very slowly.

You put a msgbox in to tell you what the value of
Len(Me.[Incident Date] & vbNullString)
is

run the code and reveal what the msgbox throws up on the screen if anything.

If nothing, then tour code is not exectuted, and probably not called. That means that in the property window of the form, the BeforeUpdate property is empty, and not containing the name of From_beforeUpdate as it should.

Okay in the spirit of taking things slowly: I haven't been "running" the code, I've been exiting the VBA editor and seeing if my form works as intended. Also, my MSAcess makes a distinction between "code" and "macros" which is why I asked in OP if I was supposed to be entering "code" specifically.

What I've been doing to enter code is going to my Form, Design View, Property Sheet, Before Update, clicking the "..." dialogue, selecting "code" and entering code in the window that appears.



Assuming this is correct, how would I "run" the code? When I click "run" on the current screen it opens a "Macros" popup and requires that I enter a Macro name...

 

[spikepl]

see previous post #15

 

[spikepl]

To "run" the code on its own makes no sense - the code is supposed to be triggered by the form. If you see no msgbox it means the codse does not run, probably because #15

Enter anything in any field on the form. Then the code should run.

 

[malamute20]

The other thing is , if you have entered NOTHING anywhere, then there is nothing to save, so your form can get mailed just fine as is. If the form is not "dirty" then the BeforeUpdate does not fire. You should therefore run similar code from your Command button. But all this implies that you have records containg blank fields, and that should not be allowed in the first place.

Alright, when I enter a value in another field and hit my command button "Submit," it does return a "0" per the Lens command, and pops up an erorr that I have to debug. But it does that AFTER it submits the email, which I'm guessing means I need to run code on the command button as you said.

Then the question becomes, how can I run that code on the command button, when it already has macros telling it what to do when it's clicked?

 

[Xproterg]

Here's what I'm using as of right now. It absolutely works.

If IsNull(Me!control_name) Then
MsgBox "Opps... There is an empty field that must be filled in before proceeding. Please ensure the record has been completed properly, then try again."
Exit Sub
End If

If you want to make this process faster, instead of having to copy and paste this for each control, you can name each control the same name, just increment a number afterwards and loop throught he fields.

for i = 0 to [I]number_of_fields[/I]
[I]If IsNull(Me!control_name(i)) Then
MsgBox "Opps... There is an empty field that must be filled in before proceeding. Please ensure the record has been completed properly, then try again."
Exit Sub
End If
next i
[/I]

The code above will loop through your feilds and then stop with the message box as soon as it finds an empty one.