proper form process 2

[THWatson]

Using Access 2000

Form is called frmCustomers. On this is a subform called fsubProjects, and on this subform is an additional subform called fsubMaterials.

Behind the BeforeUpdate event is the following code that checks for the completion of 4 different items.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Description) Then
Select Case MsgBox("DESCRIPTION OF WORK TO BE DONE has been left blank." _
                   & vbCrLf & "           Do you want to enter a DESCRIPTION?" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Description check")
    Case vbYes
        Cancel = True
        Me.Description.SetFocus
        Exit Sub
    Case vbNo
        Call MsgBox("Thank you. Please continue.", vbExclamation Or vbDefaultButton1, "Continue project")
End Select
End If

If Me.TotalMaterialsCost = 0 Then
Select Case MsgBox("No Materials have been entered." _
                   & vbCrLf & " Is this a ""Labour Only"" project?" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Labour only check")
    Case vbYes
    Call MsgBox("Thank you. Please continue.", vbExclamation Or vbDefaultButton1, "Project continue")
    
    Case vbNo
    Call MsgBox("Thank you. Please enter Materials.", vbExclamation Or vbDefaultButton1, "Enter Materials")
    Cancel = True
    Exit Sub
End Select
End If

If Me.txtLabourCost = 0 Then
Select Case MsgBox("No Labour have been entered." _
                   & vbCrLf & " Is this a ""Materials Only"" project?" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Materials only check")
    Case vbYes
    Call MsgBox("Thank you. Please continue.", vbExclamation Or vbDefaultButton1, "Project continue")
    
    Case vbNo
    Call MsgBox("Thank you. Please enter Labour rate and # of hours.", vbExclamation Or vbDefaultButton1, "Enter Labour")
    Me.LabourRate.SetFocus
    Cancel = True
    Exit Sub
End Select
End If

If IsNull(Me.Deposit) Then
Select Case MsgBox("          DEPOSIT amount has been left blank." _
                   & vbCrLf & "Do you want to enter 50% of Project as the  DEPOSIT amount?" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Deposit check")
    Case vbYes
        Me.txtDeposit.SetFocus
        Me.txtDeposit = Me.txtTotalCost * 0.5
        Cancel = True
    Case vbNo
        Call MsgBox("Thank you. Please enter Deposit amount.", vbExclamation Or vbDefaultButton1, "Continue project")
        Me.txtDeposit.SetFocus
        Cancel = True
End Select
End If

This works to some extent. However, if the user desires to enter values in one of the 4 items for which things are checked, then the process continues again with the other 3.

Two questions...
1. Is there a way to have the checking occur only once, and if, say, a No is answered to the question "Do you want to enter a Description?" this message box does not appear again?
2. Is there a better way to do this entire process?

Thanks for your help.

Tom

 

[THWatson]

CaptainD
I think that, for the moment, I am going to do as you suggest - put the ProjectNbr assignment into the BeforeUpdate event and eliminate the "Save" button completely. THAT WORKS, so don't try to fix what ain't broke might be a good way to go here.

I may fool around later with trying to make calling of the Click command work. Actually, I tried running the Call Click from a separate command button entirely and that worked with no problem, but, for whatever reason that I haven't yet been able to reconcile it doesn't work in the BeforeUpdate command.

On the other hand, I did get it working but only following the error message. In other words, the error message came up and when I closed the error message, the Save process worked. There is something in there somewhere that is blocking the process from working smoothly.

Thanks for hanging in there on this with me. Much appreciated.

Tom