Need to require data entry before record submission (1 Viewer)

[spikepl]

What happens on screen is not always the same what happens in code. But I am not sure of macros.

I would kick out the macro (because I don't know how to use them) and write the code to do what the macro does and add checking for empty fields. If you dont know the code, then copy your form into a new form. There is a button in A2007 than can convert all macros on a form into VBA, so you can get code from there

 

[spikepl]

You can use Isnull, and get disappointed one day. Up to you.

 

[malamute20]

It looks like this section of code that I was using from the other person was intended to popup a message that indicates WHICH fields require data, hence the original Lens command in my code.

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

However, in testing it has not done that once.

 

[spikepl]

What is Lens command?

Len()= is a function, ie soemthing that takes soem input and outputs soemthing else.

Rigth now I dont know where you are at all. Has the code been triggered? If so did it complete? Last time you said there was an error., so obviously it did not reach the end. I cannot SEE what you see. I only see what you tell me.

 

[Xproterg]

It looks like this section of code that I was using from the other person was intended to popup a message that indicates WHICH fields require data, hence the original Lens command in my code.

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

However, in testing it has not done that once.

That code doesn't make any sense. It either has to be

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

OR

strMissingInfo = strMissingInfo & "Incident Date"
End If
If strMissingInfo <> "" Then
strMsg = "The following are required: " & strMissingInfo
MsgBox strMsg

Also, make sure that you assign an initial value to strMissingInfo, otherwise it's like you're saying to add "Incident Date" to nothing.


AND

To top it off, you may want to check the following link out.

http://allenbrowne.com/vba-NothingEmpty.html

 

[malamute20]

That code doesn't make any sense. It either has to be

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

OR

strMissingInfo = strMissingInfo & "Incident Date"
End If
If strMissingInfo <> "" Then
strMsg = "The following are required: " & strMissingInfo
MsgBox strMsg

Also, make sure that you assign an initial value to strMissingInfo, otherwise it's like you're saying to add "Incident Date" to nothing.

Right, thanks. I got your first code working, I'll play with this a bit.

The initial value for strMissingInfo is in there, I just cut it out to keep the copy small. It's in the OP full code though. If I can't figure it out from here I'll reply here today or tomorrow.

 

[Xproterg]

Cool bro.... good luck with it.

 

[malamute20]

K I tried to combine the two and now I've lost functionality.

Here's what I'm using:

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

When I run the form it pops nothing up . Lol... I thought I had incorporated the two codes well. Hopefully you're still watching this.

 

[malamute20]

I pulled out "exit sub" from the control line and added it to the final "if" statement.

Now, it WILL post the message requested... but for some reason, it ONLY does it when I submit the form and hit "save" on the code page. Without hitting "save" on the code page, it doesn't pop up... that's very odd.

My code now looks like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMissingInfo As String
Dim strMsg As String
If IsNull(Me![Incident Date]) Then
strMissingInfo = strMissingInfo & " Incident Date "
End If
If strMissingInfo <> "" Then
strMsg = "The following fields are required: " & strMissingInfo
MsgBox strMsg
Exit Sub
End If
End Sub

 

[Xproterg]

Try using Else if

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMissingInfo As String
Dim strMsg As String

If IsNull(Me![Incident Date]) Then
   strMissingInfo = strMissingInfo & "Incident Date"
   Exit Sub
      Else If Len(strMissingInfo) <> 0 Then
      strMsg = "The following fields are required: " & strMissingInfo
      MsgBox strMsg
End If
End Sub

 

[malamute20]

Try using Else if

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

Well, the problem with that is I actually have about 20 different controls which are referenced, not just "Incident Date." I've shortened the code for testing purposes, but when you extract it back out to all 20, in the format your provided it exits out before moving on to the next one. I guess I could write that entire code for each individual control, but is there a simpler way to do it and account for multiple fields?

 

[Xproterg]

Well you could add a ton of else if statements, but i guess that's not really more efficient.


If you haven't used the fieldnames in any other pieces of code, I'd just rename the fields like I suggested earlier, and use a loop for them. Then again, it's not that hard to copy and paste 20 times and change a few things.

I'm a messy programmer

 

[malamute20]

Well you could add a ton of else if statements, but i guess that's not really more efficient.


If you haven't used the fieldnames in any other pieces of code, I'd just rename the fields like I suggested earlier, and use a loop for them. Then again, it's not that hard to copy and paste 20 times and change a few things.

I'm a messy programmer

Hah! Me too. Yeah, will renaming the fields effect the display in any way? I dont care if the program is messy, I do care how the end result looks.

 

[Xproterg]

Hah! Me too. Yeah, will renaming the fields effect the display in any way? I dont care if the program is messy, I do care how the end result looks.

BOOM! Found a better solution, which I plan to use in the future. This loops though various controls, regardless of name! I'd hang on to this one. You can specify the field type at Typename(cCont) = "combobox" etc...

ENJOY!

Private Sub Command18_Click()
Dim cCont As Control
 
    For Each cCont In Me.Controls
        If TypeName(cCont) = "TextBox" Then
            If IsNull(cCont) Then
            MsgBox "Fucking Null! Please check the " & cCont.Name & " field!"
            Exit Sub
            End If
            
        End If
     Next cCont
 
End Sub

 

[malamute20]

Lol, boss will love the msgbox

Yeah, that could do it. Thanks!

 

[malamute20]

BOOM! Found a better solution, which I plan to use in the future. This loops though various controls, regardless of name! I'd hang on to this one. You can specify the field type at Typename(cCont) = "combobox" etc...

ENJOY!

Private Sub Command18_Click()
Dim cCont As Control
 
    For Each cCont In Me.Controls
        If TypeName(cCont) = "TextBox" Then
            If IsNull(cCont) Then
            MsgBox "Fucking Null! Please check the " & cCont.Name & " field!"
            Exit Sub
            End If
 
        End If
     Next cCont
 
End Sub

Gah there's a new problem. I can't seem to find any way to use code in a Before Update for the command box I use to submit the form. The Property Sheet for the command box contains no Before Update event...

 

[Xproterg]

Why are you using the "before update" event? Trying to get people to update all the fields before the stuff saves?

 

[malamute20]

Also, is there a way to write exceptions? I have about 25 fields, only about 20 require data always.

 

[malamute20]

Why are you using the "before update" event? Trying to get people to update all the fields before the stuff saves?

Yes and before it allows the option to send the form in email.