runtime error 450 in beforeupdate event, referring to all controls (1 Viewer)

Kryst51 · May 18, 2010

I have the following code in a continuous form's Before update event:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
[COLOR=red]If Len(Nz(Me.Controls, "")) = "" Then[/COLOR]
Dim Response As Integer
Response = MsgBox("You must fill in all fields", vbOK)
 
Me.TagNum.SetFocus
End If
 
End Sub

When I test the code I get the following error:

Runtime Error '450':

Wrong number of Arguments or invalid property assignment

and the red line in the code above is highlighted. I have googled this but most hits involve excel or give explanations that I don't understand. This is the first time I have ever tried to check all controls on a form at once, and would really prefer one error message to one for each control (that seems tedious) So any help in figuring this out would be helpful, as I can't even get to test my message box to see if I have it set up correctly.

boblarson · May 18, 2010

Re: runtimt error 450 in beforeupdate event, referring to all controls

Krystal:

You can't refer to .Controls that way.

What are you trying to do, actually?

Kryst51 · May 18, 2010

Re: runtimt error 450 in beforeupdate event, referring to all controls

boblarson said:
Krystal:

You can't refer to .Controls that way.

What are you trying to do, actually?

If any of the records fields on the form are not given a value, then I don't want it to update the record but force the user to input something.

Edit: I could do it after every after update event but that would be tedious and get annoying.

boblarson · May 18, 2010

I would put a TAG (say, for example DATA) on each control that you wish to check (it simplifies matters) and then you would use something like this:

Code:

Dim ctl As Control
Dim strError As String

For Each ctl In Me.Controls
   If ctl.Tag = "DATA" Then
      If Len(ctl.Value & "") = 0 Then
         strError = ctl.Name & ", " 
      End If
   End If
Next ctl

If strError <> "" Then
   If Right(strError, 2) = ", " Then
      strError = Left(strError, Len(strError)-2)
   End If
   MsgBox "You are MISSING DATA in these fields: " & vbCrLf & strError
   Cancel = True
End If

Kryst51 · May 18, 2010

Hi Bob,

Thank you so much for your help. I don't use "for...each" or anything that loops through stuff often, therefore they don't come to my mind, and as I am not comfortable with them, I often avoid them as much as possible.

So here's to becoming more comfortable with looping through stuff.

I took you suggestion, But it only partially works. It seems like it is only looping through once, as long as I put something into the first control it updates just fine, even if the other controls are empty. But if the first control is empty the message box pops up, and show that that field is missing information. Just for verification here are the steps I took. Also to reiterate this is a continuous form (I know that sometimes that makes a difference).

I have five controls where I put "DATA" in the tag property.

I then copy and pasted your code exactly as is, and to verify accuracy I will now copy and paste it from my vba window here.

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim strError As String
For Each ctl In Me.Controls
   If ctl.Tag = "DATA" Then
      If Len(ctl.Value & "") = 0 Then
         strError = ctl.Name & ", "
      End If
   End If
Next ctl
If strError <> "" Then
   If Right(strError, 2) = ", " Then
      strError = Left(strError, Len(strError) - 2)
   End If
   MsgBox "You are MISSING DATA in these fields: " & vbCrLf & strError
   Cancel = True
End If
End Sub

I definitely don't know enough about For... Each to figure out what is wrong. When I took the time to make sure I understood each part of what you posted, it made sense.

Edit: for some reason copy/paste did not maintain my line spacing

boblarson · May 18, 2010

To double check - If ANY of the controls are filled in, it is okay to let the update occur? Right now it is set so that if ANY control is not filled in it should not update the record.

Kryst51 · May 18, 2010

boblarson said:
To double check - If ANY of the controls are filled in, it is okay to let the update occur? Right now it is set so that if ANY control is not filled in it should not update the record.

No, if ANY of the controls are empty then the update should not ocurr. So your second statement is what should be happening.

But the way it is behaving is that if the first control is empty it will not update, but if the first control has a value (even if others do not) it will allow the update to ocurr.

boblarson · May 18, 2010

What SHOULD be happening is that, If a field is blank (null or empty string) then it should update strError with the name of the control. Later on, if strError is not an empty string then it should cancel the update.

However, I am wondering - are any of your controls you are checking option groups or Checkboxes? That could affect things.

Kryst51 · May 18, 2010

boblarson said:
What SHOULD be happening is that, If a field is blank (null or empty string) then it should update strError with the name of the control. Later on, if strError is not an empty string then it should cancel the update.

However, I am wondering - are any of your controls you are checking option groups or Checkboxes? That could affect things.

yes, there is one check box, but it doesn't have the tag "DATA" in it.

boblarson · May 18, 2010

Well, the only thing I can suggest is setting a breakpoint and F8 through the code to see where it might be falling down.

Kryst51 · May 18, 2010

After a couple of more tests....

It doesn't like my default value of zero in some of my text boxes (set up as numbers) it's accepting that, where if I delete the zero, it triggers the message box, but then the message box only shows that field, not the first one which is also empty. It is also not recognizing that I haven't picked a combo box option at all

boblarson · May 18, 2010

Kryst51 said:
After a couple of more tests....

It doesn't like my default value of zero in some of my text boxes (set up as numbers) it's accepting that, where if I delete the zero, it triggers the message box, but then the message box only shows that field, not the first one which is also empty. It is also not recognizing that I haven't picked a combo box option at all

Of course. It should be:

strError = strError & ctl.Name & ", "

How brainless of me.

Kryst51 · May 18, 2010

Thanks! I did that, and it still didn't work... But that ended up being user error, as afte I shut down the form and restarted, it began to work! Awesome!

boblarson · May 18, 2010

Whew! Glad it is working for you. I knew it should work as I have used that in the past.

Kryst51 · May 18, 2010

boblarson said:
Whew! Glad it is working for you. I knew it should work as I have used that in the past.

I have definitely learned several lessons today! And am the wiser for it thanks to you!

Lessons learned:

(A) Don't shy away from things you don't understand, they may save your life if you learn them.

(B) As our IT people are always saying, "Have you tried rebooting?"

boblarson · May 18, 2010

Kryst51 said:
(B) As our IT people are always saying, "Have you tried rebooting?"

Which is my favorite line from the BBC Series - The IT Crowd. Totally hilarious series and the first line basically in the first episode is:

"Have you tried turning it off and back on again?"

runtime error 450 in beforeupdate event, referring to all controls (1 Viewer)

Kryst51

Singin' in the Hou. Rain

boblarson

Smeghead

Kryst51

Singin' in the Hou. Rain

boblarson

Smeghead

Kryst51

Singin' in the Hou. Rain

boblarson

Smeghead

Kryst51

Singin' in the Hou. Rain

boblarson

Smeghead

Kryst51

Singin' in the Hou. Rain

boblarson

Smeghead

Kryst51

Singin' in the Hou. Rain

boblarson

Smeghead

Kryst51

Singin' in the Hou. Rain

boblarson

Smeghead

Kryst51

Singin' in the Hou. Rain

boblarson

Smeghead

Similar threads

Users who are viewing this thread