Full Version: Before update not working
UtterAccess Forums > Microsoft® Access > Access Forms
smittyssluggers
I have a form that has the following code to verify that certain fields have been entered.
CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.grpObservationType = 2 Then
If Len(cboUnsafeDept & "") = 0 Then
MsgBox "Please enter the department with the unsafe condition"
Me.cboUnsafeDept.SetFocus
Me.cmdCloseObservFrm.Enabled = False
Cancel = True
Else
Me.cmdCloseObservFrm.Enabled = True
If Len(cboUnsafeArea & "") = 0 Then
MsgBox "Please enter the area within the department"
Me.cboUnsafeArea.SetFocus
Me.cmdCloseObservFrm.Enabled = False
Cancel = True
End If
End If
End If
End Sub

Nothing happens when cboUnsafeDept is 0, but once the user enters data in that field, the mesage correctly appears for cboUnsfeArea.
Any suggestions?
Jack Cowley
Try:
If Len(Me.cboUnsafeDept & vbNullString) = 0 Then
Also, is 0 a possible value for cboUnsafeDept? This may work better:
If IsNull(Me.cboUnsaveDept) Then
hth,
Jack
smittyssluggers
Thanks for the reply Jack.
tried both suggestions, but without success. To answer your question, 0 is the default until a value is entered. Any other suggestions? I'm positive I had this working before, and now it's driving me nuts.
Steve
Jack Cowley
I do not understand why it is not working. I would suggest you put a stop in your code then see what is happening as well as seeing what value is in the combo when you think it is zero or null.
th,
Jack
smittyssluggers
What do you mean by a "stop in the code"?
Jack Cowley
Before your first If statement put the word Stop. When your Before Update code fires the code editor will open and you can use the Step-Into icon to move though the code one line at a time and see what is happening. You can also hover over variables to see the value in the variable, which can be very useful if you want to know what is actually in the variable....
ack
smittyssluggers
Jack,
ou'll have to excuse my lack of knowledge in this area, as I am still learning.
I have added the "Stop" to the code as you described and when I "Step into" the code line-by-line, what should I be seeing? Each line does get highlighted, but I don't know what to look for.
Steve
Jack Cowley
No need to worry about your 'lack of knowledge' because everyone of us had to learn Access just as you are learning...
That you can tell is if the code is doing what you expect. For example if the value of your combo box SHOULD cause the If code to jump to the Else statement and it doesn't then you know that the If statement is not working as you expected. Let's assume that if the user select "Knowledge" in a combo box and this is your code:
If Me.MyCombo = "Knowledge" Then
...do something...
Else
...do something different...
End if
If the combo does not have "Knowledge" then the Else statement will be executed and if the combo does contain "Knowledge" then the first bit of code is executed.
Also, hovering the cursor over the variable for a moment or two a tiny window will open and tell you the value in that variable...
hth,
Jack
smittyssluggers
So with the original code listed above, if the "If" statements are true should the line of code with the true statements ever get hightlighted and never go to the Else statment? Because that is not happening. It highlights the If statements and skips the true statements directly to the Else. The variables are indicating that they are correct and that the true statments should be working. Pulling my hair out! I have been working on this way to long.
Jack Cowley
So you are saying that no matter what is in the combo box that the code jumps from this line to the Else statement:
If Len(cboUnsafeDept & "") = 0 Then
Are you sure that the correct column in the combo box is the bound column? What type of data type is the bound column (number, string or date)? I suspect it should be a number, namely the DepartmentID. If the user does NOT make a selection from the combo box then this code should work:
If IsNull(Me.cboUnSaveDept) Then
Be darn sure that name of your combo box is cboUnSafeDept and not something else.
At this point I do not know what to suggest, except to deletethe combo box then compact and repair the db. Go back to your form, add a new cboUnSaveDept and make sure it is named cboUnsafeDept and see it that fixes the problem.
If that fails the only thing left that I can think of is to compact and repair the db, remove sensitive data, zip and post the db with the max size not to exceed 500k. I cannot guarantee that I can find the problem but I will take a look.
Jack
smittyssluggers
I did not try changing the value to anything else as it seemed to be recognizing the "0" properly. And yes, I checked and double checked the spelling of the combo box. But you just jogged my memory about a change that I recently made that may have an impact on this. So I'm going to ask you how you would handle this situation.
I have a table (tblDepartment) that includes the following fields: PKDepartmentID, Department, and Active. If a record is entered for say DeptA, and later that Department is no longer an active location. I want the ability to change the status from Active to Inactive (which I have done through the use of a checkbox). But I still want the ability to see all the records on my form even for inactive departments. So in other words, the complete record needs to show on the form, but the inactive department should no longer be a valid choice in the combo box. How would you do this? How would you accomplish this? I really do appreciate your help!
Jack Cowley
Base the combo box on a query based on the tblDepartment and in the criteria line for the Active field put: False. (I am assuming that only checked records are active.) If you open this query you will only see active departments. Use this query as the Row Source for your combo box.

The new query for the combo box will have no effect on your table where you save the DepartmentID as the foreign key. If DeptA was active last year then all records that have that department will be shown. If DeptA is no longer active then you cannot select it from the combo box so it can't possibly be added to a new, current record.

I hope that made sense!!

Edit - I just had another thought. If a dept. is no longer active then looking at older records where the combo box shows the department names you will not see records that have been 'de-activated'. You may want to use code in the forms On Current event to determine if the record is a new one and then change the combo box Row Source to the query with the filtered data, but if it is not a new record then use a Row Source where the departments are not filtered.

Jack
Edited by: Jack Cowley on Wed Jun 7 16:38:28 EDT 2006.
smittyssluggers
I think that is similar to what I have, see below.
CODE
Private Sub cboUnsafeDept_GotFocus()
Me.cboUnsafeDept.RowSource = "SELECT * FROM tblDepartment WHERE Active = -1 ORDER BY tblDepartment.Department"
End Sub
Private Sub cboUnsafeDept_LostFocus()
Me.cboUnsafeDept.RowSource = "SELECT * FROM tblDepartment"
End Sub

But the only thing that I raised my eyebrows on was that I still needed to have an SQL query in the combo box Row Source to get this to work. Does that make sense?
Jack Cowley
Yes, that makes sense. Your combo box does need a Row Source. Just copy and paste your SQL ("SELECT * FROM tblDepartment") into the Row Source of the combo box and that should do the trick. One thing that you might want to consider is that if you are on an old record and you move to the combo box you may not have the previous department available in the combo box so the user might update an old record with current data, which maybe you do not want to do.
ack
smittyssluggers
I finally got it to work by using the code listed below. But I have a new question. How can I allow the user to have the option of saving the record (or not) when they click on the close form button? I really need it to be two-fold. If the record is not complete and the user wants to complete it, the code below should loop until required data has been provided. And the other option would be to let them choose to exit without saving the data. This function is new territory for me!
CODE

If Me.grpObservationType = 2 And cboUnsafeDept = 0 Then
    MsgBox "Please enter the department with the unsafe condition"
    Me.cboUnsafeDept.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    Else
    Me.cmdCloseObservFrm.Enabled = True
    If Len(cboUnsafeArea & "") = 0 Then
    MsgBox "Please enter the area within the department"
    Me.cboUnsafeArea.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    End If
    End If
Jack Cowley
Here is some untested code that would go in the Forms Before Update event. It loops through each control on the form and if any are blank it lets you know and gives you the option to continue or exit:
CODE
Dim stdResponse As Variant
Dim ctl As Control
    ' Enumerate Controls collection.
    For Each ctl In Me.Controls
        ' Check to see if control is text box or combo box.
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            With ctl
                If ctl.Value = "" Or IsNull(ctl.Value) Then GoTo 20
            End With
        End If
    Next ctl
Exit Sub
20:
stdResponse = MsgBox("You have not completed the " & ctl.Name & "." _
& vbCr & "This is a required field, do you want to complete it now?" _
& vbCr & vbCr & "Select YES to return to the form." & vbCr & vbCr _
& "Select NO to exit the form without saving the record.", vbYesNo, "Missing Data")
If stdResponse = vbYes Then
    ctl.SetFocus
    Exit Sub
Else 'vbNo
    Cancel = True
    Me.Undo
    DoCmd.Close acForm, "Form1"
End If

Remember - this code is UNTESTED.
hth,
Jack
smittyssluggers
The code worked, but it makes all empty controls required, and in this application they are not always required when the record is established. Anyway, I have other uses for that code so thanks. Hopefully I only have one last question for you. The BeforeUpdate keeps rearing its ugly head! I have included code that checks for multiple fields that under certain conditions will need to be completed. The problem is I think I have my "End If" in the wrong places. I want the code to check each of these conditions and give the user the opportunity to correct it. Right know, there are times that it displays multiple msgboxes one after another before the user can add the data. Can you see where I am going astray?
CODE
If Me.cboEmployee = 0 Then
    MsgBox "Please enter the Observers Name"
    Me.cboEmployee.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    Else
    Me.cmdCloseObservFrm.Enabled = True
If Me.cboEmpDept = 0 Then
    MsgBox "Please enter the Observers Department"
    Me.cboEmpDept.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    Else
    Me.cmdCloseObservFrm.Enabled = True
If Me.cboSupervID = 0 Then
    MsgBox "Please enter the Observers Supervisors Name"
    Me.cboSupervID.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    Else
    Me.cmdCloseObservFrm.Enabled = True
If Me.grpObservationType = 0 Then
    MsgBox "Please choose an Observation Type"
    Me.grpObservationType.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    Else
    Me.cmdCloseObservFrm.Enabled = True
If Me.grpObservationType = 1 And grpObserved = 0 Then
    MsgBox "Please choose who was observed (self or other)"
    Me.grpObserved.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    Else
    Me.cmdCloseObservFrm.Enabled = True
If Me.grpObservationType = 1 And grpObserved <> 0 Then
    If IsNull(Me.SafeComments) And IsNull(Me.UnsafeComments) Then
    MsgBox "You must make an entry in either of the comment sections before proceeding"
    Me.SafeComments.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    Else
    Me.cmdCloseObservFrm.Enabled = True
    End If
    End If
    
If Me.grpObservationType = 2 And cboUnsafeDept = 0 Then
    MsgBox "Please enter the department with the unsafe condition"
    Me.cboUnsafeDept.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    Else
    Me.cmdCloseObservFrm.Enabled = True
    If Len(cboUnsafeArea & "") = 0 Then
    MsgBox "Please enter the area within the department"
    Me.cboUnsafeArea.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    Else
    Me.cmdCloseObservFrm.Enabled = True
If Me.grpObservationType = 2 And optResolved = -1 Then
    If Not IsDate(ResolvedDate) Then
    MsgBox "Please enter the date the issue was resolved"
    Me.ResolvedDate.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    Else
    Me.cmdCloseObservFrm.Enabled = True
    If Len(cboResolveEmpID & "") = 0 Then
    MsgBox "Please enter the employee who is verfying completion"
    Me.cboResolveEmpID.SetFocus
    Me.cmdCloseObservFrm.Enabled = False
    Cancel = True
    Else
    Me.cmdCloseObservFrm.Enabled = True
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Jack Cowley
Each If - Then needs its own End If statement. This one, for example, is wrong:
If Me.grpObservationType = 2 And cboUnsafeDept = 0 Then
MsgBox "Please enter the department with the unsafe condition"
Me.cboUnsafeDept.SetFocus
Me.cmdCloseObservFrm.Enabled = False
Cancel = True
Else
Me.cmdCloseObservFrm.Enabled = True
If Len(cboUnsafeArea & "") = 0 Then
MsgBox "Please enter the area within the department"
Me.cboUnsafeArea.SetFocus
Me.cmdCloseObservFrm.Enabled = False
Cancel = True
Else
Me.cmdCloseObservFrm.Enabled = True
It should be:
If Me.grpObservationType = 2 And cboUnsafeDept = 0 Then
MsgBox "Please enter the department with the unsafe condition"
Me.cboUnsafeDept.SetFocus
Me.cmdCloseObservFrm.Enabled = False
Cancel = True
Else
Me.cmdCloseObservFrm.Enabled = True
If Len(cboUnsafeArea & "") = 0 Then
MsgBox "Please enter the area within the department"
Me.cboUnsafeArea.SetFocus
Me.cmdCloseObservFrm.Enabled = False
Cancel = True
End If
I'm sorry but I need to dash for a couple of hours, but treat each If -Then statement as a single batch of code and you should be OK.
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.