Pop up form/Required Fields

[hardhitter06]

Hey All,

I have a pop up for (department input) on my contract form (contract input). The user selects the acct number, if the account number isnt in the system, the user then clicks the add department button which will open up the department input pop up form where the user can enter the acct number with the corresponding department. Works perfect!

The problem is when I only enter 1-3 fields and not all 4 i get an error message telling me I'm missing all 4 and not just the 1-3 missing fields.

Here is my code and please help me if you can. Thank you in advance.

Private Sub Form_Unload(Cancel As Integer)
Dim blnError As Boolean
Dim strError As String

strError = "You are missing data for "

If IsNull(Me.[Account Number]) Or Me.[Account Number] = "" Then
blnError = True
strError = strError & "Account Number,"
End If

If IsNull(Me.Contact) Or Me.Contact = "" Then
blnError = True
strError = strError & " Contact,"
End If


If IsNull(Me.Department) Or Me.Department = "" Then
blnError = True
strError = strError & " Department,"
End If

If IsNull(Me.Address) Or Me.Address = "" Then
blnError = True
strError = strError & " Address,"
End If

If blnError Then
strError = Left(strError, Len(strError) - 1)
If MsgBox(strError & vbCrLf & "Are you sure you want to cancel." & vbCrLf & "If you do, the info will not be added.", vbQuestion + vbYesNo, "Close Confirmation") = vbNo Then
Cancel = True
End If
End If
End Sub

 

[Banana]

First, this shouldn't be in Unload. This is too late.

You need to have any data validation code in form's Before_Update.

 

[missinglinq]

Don't know what to tell you. I didn't see anything obvious so I went to the trouble of setting up a table with identical fields, pasted your code in, and tested it with every combination of three fields filled in and one empty (actually I tested just about every combination of fields filled/empty) and the code ran perfectly every time.

Linq

Edit: Not really, Banana! Granted validation is usually done in the BeforeUpdate event, but this code works! It's not going to let you leave the form until all fields are filled in, at which time the data will be re-written to the table. In today's world, the tiny bit of time to write the data to the table the first time is nothing!

 

[Banana]

Odd. I thought Unload event would fire *after* AfterUpdate, which I then reasoned that since data was already saved, we were now on a new record, which causes the code to return all four fields as empty even though the first record has only one field empty.

Guess I was wrong.

 

[hardhitter06]

Thank you both for your help. Still can't figure out what is wrong. Could it be possible that Account Number is spaced?

 

[Banana]

While it's good practice to have no spaces for any fields, object name and whatever, it's not a deal killer, since you have it enclosed it in a bracket.

One possible concern is that Access could be confusing the object for a reserved name; but I'm not sure if any of those could be reserved.

 

[hardhitter06]

I think I'm just going to change it to one of these 4 fields is missing instead of it figuring it out which one it actually is because its pretty obvious to the user if they get an error message and one box is empty.

 

[missinglinq]

No, Banana is correct; while not good practise, enclosing Account Number in brackets does away with any problems oon that account. Besides, I named it the same way in my mockup, which works! And no, none of the object names are Reserved Names.

The ways of Access are often dark, but never pleasant!

 

[hardhitter06]

If yours works, do you have any idea of why I can't get there.

 

[boblarson]

If yours works, do you have any idea of why I can't get there.

Have you set a breakpoint and stepped through the code to see what it is seeing at any one point so maybe it will show you what you are missing.

 

[hardhitter06]

I'm a little unclear on how to do that. I followed your notes from last time, put the breakpt at the first IF, then i tried typing in ? (me.account number) which jus gave me a message (something like, hasnt been created) and then I tried using F8 like you said but that didn't do nething.

Does there need to be data in it while i do this or no?

And am i missing something?

 

[boblarson]

You set the breakpoint and the close the form and that will fire the Unload event which then will stop where you set the breakpoint. Then you can put your mouse over the various items to see their current values and you hit F8 to step through the code and each time you can put your mouse over the items again to view their current values. You can also type a question mark, the object, and then enter to get their values.

Like

? Me.YourTextBox

or for variables:

? strYourVariableName

 

[hardhitter06]

Ok, I did the 4 fields and each one of them were "NULL". Now do I try and add values and check it again-if so, the only problem is, the popup form like freezes and I cant minimize/close out or anything to it.

 

[boblarson]

If you F8 all the way to the end it should close the form and let you back into things. So, yes, put values in to 3 of the 4 and see what happens.

 

[Banana]

As a FYI, you can set values in immediate windows as well.

 

[hardhitter06]

Correction, the 3 last fields r showing their value eqaul to null, but when account number is null and i go over it, it doesnt show me anything so something must be wrong with that line...any idea?

 

[Banana]

If you can't see the value from mouseover, you always can use immediate window as Bob explained:

?Me.[Account Number]

 

[hardhitter06]

Alright, they all say null even when I add some values in for 2-3 fields...I dont understand why this isnt working

 

[Banana]

My suspicion seem to be right.

Somehow you are moving to a new record before you reach the unload event.

Run this query when you're in unload after you've entered some values:

?Me.Newrecord

and see if it returns true.

 

[hardhitter06]

Do i have to move the breakpt?