Need to require data entry before record submission

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
 
You can use Isnull, and get disappointed one day. Up to you.
 
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.

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.
 
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.
 
malamute20 said:
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.

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.
Click to expand...

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

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

OR

Code: 
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
 
Xproterg said:
That code doesn't make any sense. It either has to be

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

OR

Code: 
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.
Click to expand...



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.
 
K I tried to combine the two and now I've lost functionality.

Here's what I'm using:

Code: 
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 :mad:. Lol... I thought I had incorporated the two codes well. Hopefully you're still watching this.
 
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:

Code: 
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
 
Try using Else if


Code: 
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
 
Last edited:
Xproterg said:
Try using Else if


Code: 
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
Click to expand...


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?
 
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 :)
 
Xproterg said:
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 :)
Click to expand...

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.
 
malamute20 said:
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.
Click to expand...


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!

Code: 
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
 
Lol, boss will love the msgbox ;)

Yeah, that could do it. Thanks!
 
Xproterg said:
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!

Code: 
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
Click to expand...


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...
 
Why are you using the "before update" event? Trying to get people to update all the fields before the stuff saves?
 
Also, is there a way to write exceptions? I have about 25 fields, only about 20 require data always.
 
You must log in or register to reply here.

Similar threads

V
Need help with VBA to check for required fields
Replies
6
Views
377
MajP
MajP
Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
378
Mike Krailo
Mike Krailo
D
Solved Unbound form required using VBA
Replies
9
Views
655
Pat Hartman
P
mloucel
Changing my QUERY before opening the form
Replies
11
Views
457
Cotswold
Cotswold
N
Solved How to create new record from filtered record
2
Replies
33
Views
570
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom