Checking for blank fields when closing form 2

[learnasugo]

I wrote code to check for blank fields that I want populated, but it doesn't seem to work. Can anyone help me out here? Thanks.

Private Sub Form_Close()
Dim strmsg As String

If IsNull(LOEmpID) Then
strmsg = "Please enter your employee ID"

ElseIf IsNull(LOLast) Then
strmsg = "Please enter your last name"

ElseIf IsNull(MMSJob#) Then
strmsg = "Please enter the MMS Job#"

ElseIf IsNull(Client) Then
strmsg = "Please enter the name of the Client"

ElseIf IsNull(StartMeter) Then
strmsg = "Please enter the start meter"

ElseIf IsNull(EndMeter) Then
strmsg = "Please enter the end meter"

ElseIf IsNull(Carton) Then
strmsg = "Please enter the carton number"

ElseIf IsNull(Printer) Then
strmsg = "Please enter the printer number"

ElseIf IsNull(ISerialNum) Then
strmsg = "Please enter the serial number of the input machine"

ElseIf IsNull(OSerialNum) Then
strmsg = "Please enter the serial number of the output machine"
End If

learnasugo

 

[PHV]

The events triggered when you close a form are, in order:
Unload ? Deactivate ? Close
So put your checking code in the UnLoad event procedure of the form, playing with the Cancel variable and with calls to MsgBox and SetFocus.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[learnasugo]

Putting my code in on Unload event procedure helped with one of my other codes, but not with this one. Is it in the code itself?

learnasugo

 

[PHV]

The code you posted do nothing but assign value to a not used string variable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[learnasugo]

Ok, I changed my code and the first message box is coming up, but then it is closing the form and saving the record. Do I need the Cancel = True after every if statement?

Private Sub Form_Unload(Cancel As Integer)
Dim strmsg As String

If IsNull(LOEmpID) Then
MsgBox "Please enter your employee ID"

ElseIf IsNull(LOLast) Then
MsgBox "Please enter your last name"

ElseIf IsNull(MMSJob#) Then
MsgBox "Please enter the MMS Job#"

ElseIf IsNull(Client) Then
MsgBox "Please enter the name of the Client"

ElseIf IsNull(StartMeter) Then
MsgBox "Please enter the start meter"

ElseIf IsNull(EndMeter) Then
MsgBox "Please enter the end meter"

ElseIf IsNull(Carton) Then
MsgBox "Please enter the carton number"

ElseIf IsNull(Printer) Then
MsgBox "Please enter the printer number"

ElseIf IsNull(ISerialNum) Then
MsgBox "Please enter the serial number of the input machine"

ElseIf IsNull(OSerialNum) Then
MsgBox "Please enter the serial number of the output machine"
Cancel = True

learnasugo

 

[learnasugo]

OHHHHHH NOOOOOOOO!!! I put the cancel=true after every if then statement and that made it work great!! Now all of the sudden it won't let me fill out some of the fields (3 of them are in the code for being required or it won't close). The message down on the task bar is: Cannot enter value into blank field on 'one' side of outer join.

What the heck did I do??????

learnasugo

 

[AccessGuruCarl]

Hi learnasugo

Does it work if everything is there?
Sounds like a bad query or SQL string to me

Look at the beginning of your code or what the forms record source properties are...

Create an Update or Append Query with the same criteria and try running it. Do you still get the error?

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

I hope this helps...

 

[learnasugo]

It worked fine when I first put that code in and tested it. Then I closed the form and opened it again to test it again, that's when I started having problems. It wouldn't even let me put information in two of the fields that are required in the code (LOEmpID and LOLast). So I get stuck in a never ending loop. The program tells me that I need to enter this information but the program won't let me enter information in these two fields. I finally got out of it by commentting the code out for those two fields (I still had the code window open), and it worked fine. What is an outer join (I've heard of it before I just don't remember what it is)?

learnasugo

 

[PHV]

Remember, I suggested you the SetFocus method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AccessGuruCarl]

What is the forms properties - record source show?

Inner & Outer Joins are a way of linking records in different tables.

Did you create a test query based on your form?
Did that work? I bet it didn't! Same error.

If it did, change the query to SQL View. Here's how...
In design view - click -> View -> View SQL

Copy the SQL
Add this code

dim strSQL as String
dim booReturn as Boolean
  booReturn = False
  strSQL = "   paste code   "

   ... Your If Then Else Code
   ... booReturn = True

If booReturn = True Then
  DoCmd.RunCode strSQL
  booReturn = False
End If

Save the form. Try it.

Make sure you are using the SetFocus method for before each ElseIf statement as mentioned by PHV.

Your strSQL should look something like this, not knowing your table relationships, here is basic Insert or Update.

strSQL = "Insert INTO YourTable(LoEmpId, LoLast, MMSJob#) Values (txtLoEmpId.Value, txtLoLast.Value, MMSJob#)

or 

strSQL = "Update YourTable SET lngLoEmpID = " & txtLoEmpId.Value & " Where lngLoEmpID = " & txtLoEmpId.Value

If you can get the Query to work
This should solve your problem...
I hope this helps

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[learnasugo]

Would I still need to set it up in SQL View if the query works? The query going into the form works. I'm going to take a stab in the dark (from what I have learned from this thread) and say that it has something to do with the relationship between these two tables and the code that is causing the problem. I have the setfocus between the msgbox and the cancel=true line (don't know if the placement of that is a problem).

The relationship between the two tables is one to many through a junction table.

Here is the current code that is giving me problems.


If IsNull(LOEmpID) Then
MsgBox "Please enter your employee ID"
LOEmpID.SetFocus
Cancel = True

ElseIf IsNull(LOLast) Then
MsgBox "Please enter your last name"
LOLast.SetFocus
Cancel = True

If IsNull(MMSJob#) Then
MsgBox "Please enter the MMS Job#"
[MMSJob#].SetFocus
Cancel = True

ElseIf IsNull(Client) Then
MsgBox "Please enter the name of the Client"
Client.SetFocus
Cancel = True

ElseIf IsNull(StartMeter) Then
MsgBox "Please enter the start meter"
StartMeter.SetFocus
Cancel = True

ElseIf IsNull(EndMeter) Then
MsgBox "Please enter the end meter"
EndMeter.SetFocus
Cancel = True

ElseIf IsNull(Carton) Then
MsgBox "Please enter the carton number"
Carton.SetFocus
Cancel = True

ElseIf IsNull(Printer) Then
MsgBox "Please enter the printer number"
Printer.SetFocus
Cancel = True

ElseIf IsNull(ISerialNum) Then
MsgBox "Please enter the serial number of the input machine"
ISerialNum.SetFocus
Cancel = True

ElseIf IsNull(OSerialNum) Then
MsgBox "Please enter the serial number of the output machine"
OSerialNum.SetFocus
Cancel = True

End If

learnasugo

 

[AccessGuruCarl]

Hi learnasugo,
Sorry for delay, I got tied up.

The SetFocus is fine if your not getting error something like You can't reference a field if it doesn't have focus.

You can try and put the setfocus before the 1st If and before each IfElse also. It won't hurt...

Regarding SQL View
I'm not sure - Depends on the query.
Try setting the forms Record Source to the QueryName

Good Luck

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[learnasugo]

I have the record source for the form set to the query name. What I can't figure out is why those first two option (LOEmpID and LOLast) are the only two fields giving me a problem. The only thing that I can think of is because they are from a different table than the other fields. The tables go like this:

LaserOperator (Table Name)
LOID (PK)
LOEmpID
LOFirst
LOLast
LOAddress
LOCity
LOState
LOZip
LOPhone.....

LaserOperatorInfo (Table Name)
LaserID (PK)
MMSJob#
Client
StartMeter
EndMeter
Carton
Printer
ISerialNum
OSerialNum....

LaserOpJunct (Junction Table)
LaserOpJunctID
LaserID
LOID

For those first two fields I keep getting the error message: Cannot enter value into blank field on 'one' side of outer join. Can anyone tell me why??

learnasugo

 

[AccessGuruCarl]

Now I see your problem...

You are only working with Primary Keys---
You need to add a Foriegn Key to one of the tables.

Not knowing what your really trying to do. I'll guess and say put the foreign key in the LaserOperatorInfo table to create a relationship to the LaserOperator table.

So now your tables look like this...

LaserOperator (Table Name)
LOID (PK)
LOEmpID
LOFirst
LOLast
LOAddress
LOCity
LOState
LOZip
LOPhone.....

LaserOperatorInfo (Table Name)
LaserID (PK)
LOID (FK) Data Type: Number
MMSJob#
Client
StartMeter
EndMeter
Carton
Printer
ISerialNum
OSerialNum....

This will create a 1 to Many relationship between the table.

2 ways to go about the next step.

1. Open you query that the form is based on, delete the junction table from it. If a relationship between the other 2 tables is there(they'll be joined with a line)
save the query. Open the form, and try it. Otherwise click the PK in LaserOperator table and drag it to the FK in LaserOperatorInfo. Now save it. Open the form.

2. To do this Goto Tools -- Relationships and add the 2 tables. Now click the PK in LaserOperator table and drag it to the FK in LaserOperatorInfo, this will start the relationship wizard.

And you can now get rid of the Junction table. If you need this table, you need to add it to the relationship also. But I'd try to eliminate it if your not using LaserOpJunctID for anything...

Just a note...

You will most likely need to delete all the data in the tables to create the relationship, Or remove error messages when you use the form the 1st time.

Recreate your query based on the 2 tables and everything should work fine.


Here is another way you can check for the nulls without all the If Then Statements.

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo MY_ERROR
Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            If IsNull(ctl) Then
                MsgBox ctl.Name & " is a required field, " & vbCr & _
                    "Please enter the required information to continue.", _
                    vbInformation, "Required Field"
                ctl.BackColor = 13303807    'Light Yellow
                ' Make sure you set the lost focus event
                ' for each textbox to set them back to default.
                    ' me.backcolor = vbwhite    'or  16777215
                ctl.SetFocus
                Cancel = True  
                Exit Sub
            End If
        End If
    Next ctl
Exit Sub
MY_ERROR:
MsgBox Err.Number & vbCr & vbCr & Err.Description
End Sub

This will actually run faster, and I believe the correct way to accomplish it if these are the only textboxes on the form. Otherwise keep the original code...

You may want to consider using brackets if you want to include the # symbol in your field names.

Your code...
ElseIf IsNull(MMSJob#) Then
MsgBox "Please enter the MMS Job#"
New code...
ElseIf IsNull([MMSJob#]) Then
MsgBox "Please enter the MMS Job#"

This will prevent any unforseen errors when evaluating your fields, especially if you have field name like
'24 GW' (without the qoutes)

Hope this helps, you may want to pickup a book on building relational databases. Table structure and design is critical if you don't want the problems you just en-countered...

Good Luck, keep me posted on how your doing...


AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[learnasugo]

Your suggestion did work, but my boss would have a fit if he lost the data that he has in the query (I tested it on a copy of the file). When I tried it I tried to copy the data and then paste it back in but that didn't work. Is there a way to save the data, or at least put it back in?

learnasugo

 

[AccessGuruCarl]

Yes, the data can be replaced.
It requires a few queries and possible temp tables.

Did you keep or delete the Junction Table?

How large is the database?
Is it less than 3MB?

Is the data 'business sensitive' can you email a copy?

If both are yes, send me a copy at accessgurucarl@excite.com

I'll take a look at what you need to do, and point you in the right direction.

Email the specfic info "names" needed and/or required for the tables, queries and forms that your working with. You may be able to just 'add the field into the required table' then update that field with the correct data. If the tables are small(ony a few records) this will be the easiest way to make the changes. Once you have a match in each table, everything should work.

If you email the db... Place a post here, letting me know you emailed a copy. Otherwise it could be weeks before I check that account. Account used for Tek-Tips only...


AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[learnasugo]

The front end is 5.31 MB. I zipped up the front end and the back end and will e-mail to you. Thank you

learnasugo