Message Box

[mrkshpntf]

I have a form with multiple text boxes where data is to be entered by the user.

Once the data is entered, the user clicks on a 'save' button to save the data entered.

I would like the users to get a message when they click the 'save' button if they have left even one text box empty
before the data is saved.

Thanks for you help!

mrkshpntf.

 

[Crowley16]

something like this placed at the top of the save button's click event?

dim emp as boolean
emp = false

dim ctl as variant
for each ctl in me.controls
   if ctl.controlType = ? then 'I can't remember the id for text box, use F1 to find it
      if len(ctl.value & "") < 1 then
         emp = true
      end if
   end if
next ctl

if emp then
   if msgbox("empty text box, continue?", vbyesno) = vbno then
      exit sub
   endif
endif

--------------------
Procrastinate Now!

 

[mrkshpntf]

crowley16,

Thanks for your response.

Can you please explain what 'emp' and 'ctl' represent?

I can understand your code but am a little confused about 'emp' and 'ctl'.

Thanks for your help.

mrkshpntf.

 

[TheAceMan1]

How aree ya mrkshpntf . . .

You don't really need the save button!

In the [blue]Tag[/blue] property of the textboxes of interest enter a question mark ? (no quotations please). Then in the [blue]Before Update[/blue] event of the form, copy/paste the following:

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, ctl As Control
   
   DL = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" And Trim(ctl & "") = "" Then
         Msg = "'" & ctl.Name & "' is Required!" & DL & _
               "Please enter a value or hit Esc to abort the record . . ."
         Style = vbInformation + vbOKOnly
         Title = "Required Data Missing! . . ."
         MsgBox Msg, Style, Title
         ctl.Name.SetFocus
         Cancel = True
         Exit For
      End If
   Next[/blue]

The code takes you to all missing data (one by one) until all are corrected or the record is aborted . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[monkeysee]

The AceMan1

This is exactly what I am looking for! However, after following your instructions, I get an error message:
"Run-Time error 438
Object doesn't support this property or method

when pressing debug, the following line is highlighted:
If ctl.tag = "?" and trim(ctl & ")=" then

how can i fix this? really anxious to put this in action!

thanks for your help!

 

[TheAceMan1]

monkeysee . . .

Hmmmmmmm . . . perhaps parenthesis are in order:

[blue]Change:
   If ctl.Tag = "?" And Trim(ctl & "") = "" Then
To:
   If [purple][b]([/b][/purple]ctl.Tag = "?"[purple][b])[/b][/purple] And [purple][b]([/b][/purple]Trim(ctl & "") = ""[purple][b])[/b][/purple] Then[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[djj55]

Hello,
You asked about Crowley16's

Can you please explain what 'emp' and 'ctl' represent?

emp is a place holder for a flag
ctl is used while looping thru the controls on the form

Good luck
djj

 

[monkeysee]

The AceMan1

Just now getting back to this. I changed the code to use the parenthesis as you suggested, but I am still getting the RunTime Error 438
with the Debug window highlighting this part of the code:

If (ctl.Tag = "?") And (Trim(ctl & "") = "") Then

Greatly appreciate and help you may be able to give, Also, if I don't hear from you prior, Have a Happy Thanksgiving!

 

[PHV]

If ctl.Tag = "?" Then
If Trim(ctl & "") = "" Then
...
End If
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

I've got an almost clone of AceMan's code (which I halfway think I from him) which also fails in this manner. Not sure that I ever tried it. The problem lies with

Trim(ctl & "") = ""

althoughI have no idea why this fails! But if you replace

If ctl.Tag = "?" And Trim(ctl & "") = "" Then

with

If ctl.Tag = "?" And IsNull(ctl) Then

the code works!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

[blue]I get an error message:
"Run-Time error 438
Object [purple]doesn't support this property or method[/purple][/blue]

The error is saying you don't have a tag property!

Whats the exact name of the property where you put the question marks? . . . and by chance are we talking smart tags here?

And so we know where on the same page, post the code as you have it! (don't type it . . . [blue]copy/paste it[/blue])

See Ya! . . . . . .

Be sure to see thread181-473997

 

[missinglinq]

As I said, AceMan, I copied and pasted your code into a working form, with two controls' tags (dumb tags, I guess, at least not "smart!")set to ? (not quotes!)and got the same error! Hacked around with it and isolated the problem to the

And Trim(ctl & "") = "" Then

portion of the code. Diddled around some more, and finally replaced it with

If ctl.Tag = "?" And IsNull(ctl) Then

and it performs flawlessly now! DOn't know why the

And Trim(ctl & "") = "" Then

doesn't work; looks like it ought to!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PHV]

Have you tried my suggestion ?

 

[missinglinq]

Yes, PHV, your suggestion works as well!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PHV]

Yes, PHV, your suggestion works as well
And did you understand why ?

 

[missinglinq]

Obviously not, or I'd have suggested it!

Where do you suppose the OP went?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[JoeAtWork]

And Trim(ctl & "") = "" Then

doesn't work; looks like it ought to!

You know, I often see advice given to use the Trim function as a way to avoid errors if the control is Null. The thing I never liked about that method is that you are relying on what is essentially a "side effect" of the function.

Trim was never meant as a way to deal with potential Null values. It was meant to trim space characters from the edges of a string. But at some point a few curious programmers discovered this use for it to handle Null values in controls, and started using it on a regular basis. I guess because it takes a few less key strokes then Nz.

But I never took up this habit because:
1. That's not what it was designed for, and thus you can not be sure that this behaviour will always occur
2. It makes the code harder to read (i.e. you have to know about this quirky little behaviour to understand why there's Trim function there)

So I feel a little vindicated today.

 

[missinglinq]

You know, Joe, I too see this frequently, as well as seeing it and IsNull being used together, but I've always simply used IsNull for this purpose and have never had any problems! I always wonder if I've just been lucky, or if some of my "default" behaviors keep me out of trouble. I suppose IsNull would not give the expected result if a numeric field was left blank (no data was entered) and in the Table Design View its Deafult had been left at Access' default value of zero, but I never do that! With no Default Value set, the field will evaluate as Null if it's left blank, if it has a value that is then deleted or if it has a value that is deleted thru backspacing.

I grew up in a development environment of 20 mb hard drives where memory was so precious that it was considered a major coup if someone could reduce the size of routine from 200 lines to 198 lines! Those days are long gone, and I'm a firm believer in code that is "self-commenting!" I'd much rather write a couple of extra lines of code, if it makes clearer what is being done, than using shorter but more cryptic code. The iif() is a nice little function and clear in meaning, if you understand the syntax, if used by itself.

Me.RunProcedure = iif(MyCheck = True, "Yes", "No")

is certainly simpler than

If MyCheck = True Then
Me.RunProcedure = "Yes"
Else
Me.RunProcedure = "No"
End If

But 12 nested iff statements (and yes, I've seen this posted!) is as cryptic as any code I've ever seen, and would take a page of single-spaced commenting to explain, so what's advantage is gained by this?

There are a lot of "undocumented" ways to use functions which are handy. I use a lot of them, yself, but if they obscure the meaning of the code I avoid them.

Of course, someone will come along and explain to the world why you and I don't have a clue about all this, and how their method of doing this is the only possible way!

Well, as my Signature says, There is ALWAYS more than one wayto skin a cat!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[JoeAtWork]

I suppose IsNull would not give the expected result if a numeric field......

Well, it would give the expected resulted, providing that the programmer understands what the results should be. I.e. if they are strictly testing if the value is Null, then at the time they are testing before the record is inserted it really is returning the correct result. The unexpected behaviour comes from the fact that the table design assigns a default value - and if that is the wrong behaviour then it is a flaw in the database design.

But 12 nested iff statements (and yes, I've seen this posted!) is as cryptic as any code I've ever seen... so what's advantage is gained by this?

Nothing, that's just bad coding style and the perpetrator should be sentenced to harsh labor at a penal colony.

There are a lot of "undocumented" ways to use functions which are handy.

VBA coders are probably safe in that their language is "frozen", therefore these "undocumented" behaviours will probably never change. But if there were ever a new version of VBA coming out, programmers may find some of their code getting broken because some of the "quirky" behaviour they relied upon has changed.

Personally, I prefer to play it safe and use functions only for their intended purposes.

 

[TheAceMan1]

To All . . .

I was requested as cook for the holidays and had to get busy. I've been monitoring the thread although meal time preparation & cooking became full time. Now that its over I can comitt my full attention.

I'm going to explain why things are the way they are, but I'd first like to say the code I posted was simply quick & dirty . . . as it proved to be. Normally giving more full thought, I would've posted as [blue]PHV[/blue] suggested. A check of threads where I've posted the same but in more proper logical format proves this out ([blue]missinglinq[/blue] I'm sure if you triple check you'll find this to be so!).

In any case, the reason . . .

[blue]If ctl.Tag = "?" And Trim(ctl & "") = "" Then[/blue]

. . . fails is because of the following:

[blue][tt]Quote Microsoft: When several operations occur in an expression, each part is evaluated and resolved in a predetermined order called operator precedence.

When expressions contain operators from more than one category, arithmetic operators are evaluated first, comparison operators are evaluated next, and logical operators are evaluated last. Comparison operators all have equal precedence; that is, they are evaluated in the left-to-right order in which they appear. Arithmetic and logical operators are evaluated in the following order of precedence:

Priority1 Priority2 Priority3
Arithmetic Comparison Logical
********************************** ***************************** *********
Exponentiation (^) Equality (=) Not
Negation (-) Inequality (<>) And
Multiplication and division (*, /) Less than (<) Or
Integer division (\) Greater than (>) Xor
Modulus arithmetic (Mod) Less than or equal to (<=) Eqv
Addition and subtraction (+, -) Greater than or equal to (>=) Imp
String concatenation ([red]&[/red]) Like
Is[/tt][/blue]

If we take a good look at that line of code and the [blue]Priority1[/blue] column (in order of precedence from top to bottom), we come up with ampersand [red]&[/red] [blue]as the first priority[/blue]. This causes the 2nd logical expression [blue]Trim(ctl & "") = ""[/blue] to be evaluated 1st! . . . and herein lies the problem. Not only can this be any control without the tag property set, but it could be any other object on the form! . . . A command button will surely fail here!

Best I can do is offer my apologies for the confusion and correct the code.

So . . . [blue]mrkshpntf[/blue] here ya go:

[blue]Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, ctl As Control
   
   DL = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      [purple][b]If ctl.Tag = "?" Then
         If Trim(ctl & "") = "" Then[/b][/purple]
            Msg = "'" & ctl.Name & "' is Required!" & DL & _
                  "Please enter a value or hit Esc to abort the record . . ."
            Style = vbInformation + vbOKOnly
            Title = "Required Data Missing! . . ."
            MsgBox Msg, Style, Title
            ctl.Name.SetFocus
            Cancel = True
            Exit For
         End If
      End If
   Next[/blue]

[blue]Cheers All! . . . and a happy ThanksGiving! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997