Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

proper form process 2

Status
Not open for further replies.
THWatson

THWatson

Technical User
Apr 25, 2000
2,601
CA
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.
Code: 
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
 
Sort by date Sort by votes
You could take out the "Thank you" messagebox(s) for making an entry.

You're only concerned if a needed value is not there, And if needed information is not there, then warn them that it's required.

Same for invalid entries. Say someone enters the letter "A" in Me.txtLabourCost

(To check that look into the keypress event and capturing KeyAscii)
 
Upvote 0 Downvote
CaptainD
I agree that taking out the "Thank You" pieces in the messages would have a benefit.

As for what you refer to as "invalid entries," let me explain a bit further.

This form is used to prepare a quote for an electrical project. It is made up of quote for Materials and quote for Labour. In some cases, the quote could be for Labour only. In certain cases, unusual but possible, the quote could be for Materials only.

Materials are entered in the subform, fsubMaterials. The control called "TotalMaterialsCost" is on fsubProjects and is grabbed from a sum control on the subform. The user does not enter any value in this control, so it can't be other than an amount, either $0.00 if no materials have been entered, or some amount if they have. Therefore, this can never be an invalid amount. The purpose in checking is to make sure the user didn't fail to enter materials when they should have been entered.

Labour costs are on fsubProjects. There is a field for Labour Rate and a field for number of hours. The control called txtLabourCost is the product of Rate x Hours. Therefore, this also cannot be an invalid entry, only $0.00 or some positive amount. The purpose in checking is to make sure the user didn't fail to enter appropriate Labout costs.

The Deposit control is a currency field, and therefore an A would not be accepted.

Tom
 
Upvote 0 Downvote
CaptainD
I have revised my code. Below is the revision.
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Description) Then
Call MsgBox("DESCRIPTION OF WORK TO BE DONE has been left blank." _
            & vbCrLf & "             Please enter a Description." _
            , vbExclamation, "Description needed")

        Cancel = True
        Me.Description.SetFocus
        Exit Sub
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
        
    Case vbNo
    Call MsgBox("Thank you. Please go to Materials subform and 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
        
    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
Call MsgBox("    DEPOSIT amount has been left blank." _
            & vbCrLf & "50% of Project will be entered as the Deposit amount." _
            & vbCrLf & "    Either let that amount stand or edit as desired." _
            , vbExclamation, "Deposit amount needed")
        Me.txtDeposit.SetFocus
        Me.txtDeposit = Me.txtTotalCost * 0.5
        Cancel = True
        Exit Sub
End If


If IsNull(Me.ProjectNbr) Then
Call MsgBox("     Project needs to be saved." _
            & vbCrLf & "Please press the SAVE PROJECT button." _
            , vbExclamation, "Project Save check")
Cancel = True
Me.cmdSaveProject.SetFocus
End If

End Sub

Tom
 
Upvote 0 Downvote
Howdy THWatson . . .
THWatson said:
[blue]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?[/blue]
Click to expand...
Bear in mind, the epitome of your validation determines wether to save a record or not! [purple]In this, you have to set the rules for which combinations of the four controls constitutes a save.[/purple] This is what I'm not getting from your post origination. Espcially in any 'No' answer to message boxes! . . . does the record get saved or not?

[blue]So whats the law(s) of saving a record! . . . per the four controls?[/blue]


Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886
 
Upvote 0 Downvote
THWatson . . .

Hit submit too soon! . . .

For you . . . [blue]Continue[/blue] should mean saving the record and moving on! . . .

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886
 
Upvote 0 Downvote
The AceMan1
I have modified the BeforeUpdate code, seeking to improve the process as well as make it clearer. The revised code is
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)

   On Error GoTo Form_BeforeUpdate_Error

If IsNull(Me.Description) Then
Call MsgBox("DESCRIPTION OF WORK TO BE DONE has been left blank." _
            & vbCrLf & "             Please enter a Description." _
            , vbExclamation, "Description needed")

        Cancel = True
        Me.Description.SetFocus
        Exit Sub
End If

If Me.TotalMaterialsCost = 0 Then
Select Case MsgBox("No Materials have been entered." _
                   & vbCrLf & "Should there be Materials costs?" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Materials cost check")
    Case vbYes
    Call MsgBox("Thank you. Please go to Materials subform and enter Materials.", vbExclamation Or vbDefaultButton1, "Enter Materials")
    Cancel = True
    Exit Sub
    
    Case vbNo
End Select
End If

If Me.txtLabourCost = 0 Then
Select Case MsgBox("No Labour charges have been entered." _
                   & vbCrLf & "Should there be Labour charges?" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Labour cost check")
    Case vbYes
    Call MsgBox("Thank you. Please enter Labour rate and # of hours.", vbExclamation Or vbDefaultButton1, "Enter Labour")
    Me.LabourRate.SetFocus
    Cancel = True
    Exit Sub
    
    Case vbNo
End Select
End If

If IsNull(Me.Deposit) Then
Call MsgBox("    DEPOSIT amount has been left blank." _
            & vbCrLf & "50% of Project will be entered as the Deposit amount." _
            & vbCrLf & "  Either leave that amount or edit as desired." _
            , vbExclamation, "Deposit amount needed")
        Me.txtDeposit.SetFocus
        Me.txtDeposit = Me.txtTotalCost * 0.5
        Cancel = True
        Exit Sub
End If


If IsNull(Me.ProjectNbr) Then
Call MsgBox("     Project needs to be saved." _
            & vbCrLf & "Please press the SAVE PROJECT button." _
            , vbExclamation, "Project Save check")
Cancel = True
Me.cmdSaveProject.SetFocus
End If

   On Error GoTo 0
   Exit Sub

Form_BeforeUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate of VBA Document Form_fsubProjects"

End Sub

All the issues being satisfied does not necessarily mean that the record will be saved. If it's a case of editing an existing record, it will be saved. However, if it's a NEW record, it won't...at least, not quite yet...as there is code behind a Save button.

You will notice that the last piece of the BeforeUpdate code checks for whether or not there is a project number. That is a calculation composed of Year + CustomerID + ProjectID, and is fixed when the Save button is pressed.

Here is the code behind the Save button...
Code: 
Private Sub cmdSaveProject_Click()
On Error GoTo Err_cmdSaveProject_Click

Me.ProjectNbr = Year(Date) & "-" & Me.CustomerID & "-" & Me.ProjectID
Me.Recalc

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveProject_Click:
    Exit Sub

Err_cmdSaveProject_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveProject_Click
    
End Sub

I hope that clarifies.

I have everything working properly now, but still wonder as to whether or not there is a better way (always searching for a better way). I'm making this database for one of my sons-in-law who is going to set up his own, part-time, electrical business. He's not terribly computer literate so I am trying, to the extent possible, to make sure he enters everything he needs to.

Please let me know if you spot anything.

Thanks.

Tom
 
Upvote 0 Downvote
You can"Click" the command button for them instead of asking them to do it.

Code: 
If IsNull(Me.ProjectNbr) Then
    Call Me.cmdSaveProject_Click
End If

OR, in the Private Sub Form_BeforeUpdate event if it's NULL set the value.

Code: 
If IsNull(Me.ProjectNbr) Then

    Me.ProjectNbr = Year(Date) & "-" & Me.CustomerID & "-" & Me.ProjectID

End If
 
Upvote 0 Downvote
Thanks, CaptainD. Good suggestions. I'll ponder which is best to use.

Tom
 
Upvote 0 Downvote
CaptainD
Are you sure you can Call the cmdSaveProject_Click() from the BeforeUpdate event?

I have tried...
Call Me.cmdSaveProject_Click() and this is not accepted when I attempt to compile the VBA code.

I have tried...
Call cmdSaveProject_Click() and this, while accepted when compiling, triggers the error routine

I have tried...
cmdSaveProject_Click() and this, again while accepted when compiling, triggers the error routine

Tom
 
Upvote 0 Downvote
I created a simple form based off a query and ran the following. It works as expected.

Code: 
Option Compare Database

Private Sub cmdToAutoClick_Click()

MsgBox "This message has been Auto Clicked", vbOKOnly, "Auto Clicker"

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call cmdToAutoClick_Click

End Sub

Private Sub Form_Load()

End Sub

When I enter a new record (Bound Form) and click the data control to update the table, it "Auto Clicks" the command button.
 
Upvote 0 Downvote
Two additional questions.

Did you step through your code and make sure you made it to the "Call cmdSaveProject_Click"?

Where or what threw the error?

Post your code.
 
Upvote 0 Downvote
CaptainD
In answer to your questions...

Where or what threw the error?
I explained that in the post back, where I asked whether or not it was possible to do what you suggested from the BeforeUpdate event.

Did you step through your code and make sure you are calling the cmdSaveProject_Click
Yes

Post your code
The code is posted a few posts back, addressed to TheAceMan1 - the message before you suggested calling the click command button.

I removed all code other than the code having to do with calling the command click. The message that is triggered is
The macro or function set to the BeforeUpdate or Validation property for this form is preventing the saving of data.

Tom
 
Upvote 0 Downvote
CaptainD
I am using Access 2000. There are things that cannot be done from the BeforeUpdate event.

Is it possible you are using a later version of Access?

Tom
 
Upvote 0 Downvote
CaptainD
Actually, to the code in the post to The AceMan1 I added the Call command, and removed the command button SetFocus line. So that piece of the code would read...

Code: 
If IsNull(Me.ProjectNbr) Then
Call MsgBox("     Project needs to be saved." _
            & vbCrLf & "Please press the SAVE PROJECT button." _
            , vbExclamation, "Project Save check")
Cancel = True
Call cmdSaveProject_Click
End If

Tom
 
Upvote 0 Downvote
No, I'm using Access 2000 as well.

I simulated your code and it still works. Did you step through your code?

Code: 
Option Compare Database

Private Sub cmdToAutoClick_Click()

MsgBox "This message has been Auto Clicked", vbOKOnly, "Auto Clicker"

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim vNull As Variant

vNull = Null
If IsNull(vNull) Then
Cancel = True
Call cmdToAutoClick_Click

End If
End Sub
 
Upvote 0 Downvote
>I removed all code other than the code having to do with calling the command click. The message that is triggered is
The macro or function set to the BeforeUpdate or Validation property for this form is preventing the saving of data

Cancel = True will stop the update

Plus, you are using Cancel = True to stop the update so that the user can click the command button to create the project number. If you invoke the click on the command button to do that for them, then you do not need to cancel the update action.
 
Upvote 0 Downvote
CaptainD
This is driving me bonkers. I am going to have to change some stuff. It just ain't workin'

I removed the Cancel = True[/color red] from 2 different spots, but I still get the same message I indicated previously.

When I step through the code, I see the watched expression on the call the Save button line is "Watch : : cmdSaveProject_Click : <Expression not defined in context> : Empty : Form_fsubProjects.Form_BeforeUpdate"

Tom
 
Upvote 0 Downvote
In the watch window I get the same thing. But stepping through the code, when I get to "Call cmdToAutoClick_Click" it "bumps" up to the click event and runs the code, in this case, the message box pops up. I click "OK"and it drops baack down to the Beforeupdate event.

If you still want to play with calling the click event, try a test form and do what I did just to see if it works.

On the other hand, to get your project moving, just let this run out in the BeforeUpdate event and you should be good.


Code: 
Sub Form_BeforeUpdate(Cancel As Integer)

   On Error GoTo Form_BeforeUpdate_Error

If IsNull(Me.Description) Then
Call MsgBox("DESCRIPTION OF WORK TO BE DONE has been left blank." _
            & vbCrLf & "             Please enter a Description." _
            , vbExclamation, "Description needed")

        Cancel = True
        Me.Description.SetFocus
        Exit Sub
End If

If Me.TotalMaterialsCost = 0 Then
Select Case MsgBox("No Materials have been entered." _
                   & vbCrLf & "Should there be Materials costs?" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Materials cost check")
    Case vbYes
    Call MsgBox("Thank you. Please go to Materials subform and enter Materials.", vbExclamation Or vbDefaultButton1, "Enter Materials")
    Cancel = True
    Exit Sub
    
    Case vbNo
End Select
End If

If Me.txtLabourCost = 0 Then
Select Case MsgBox("No Labour charges have been entered." _
                   & vbCrLf & "Should there be Labour charges?" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Labour cost check")
    Case vbYes
    Call MsgBox("Thank you. Please enter Labour rate and # of hours.", vbExclamation Or vbDefaultButton1, "Enter Labour")
    Me.LabourRate.SetFocus
    Cancel = True
    Exit Sub
    
    Case vbNo
End Select
End If

If IsNull(Me.Deposit) Then
Call MsgBox("    DEPOSIT amount has been left blank." _
            & vbCrLf & "50% of Project will be entered as the Deposit amount." _
            & vbCrLf & "  Either leave that amount or edit as desired." _
            , vbExclamation, "Deposit amount needed")
        Me.txtDeposit.SetFocus
        Me.txtDeposit = Me.txtTotalCost * 0.5
        Cancel = True
        Exit Sub
End If


[COLOR=blue]If IsNull(Me.ProjectNbr) Then
    Me.ProjectNbr = Year(Date) & "-" & Me.CustomerID & "-" & Me.ProjectID
End If[/color]

   On Error GoTo 0
   Exit Sub

Form_BeforeUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate of VBA Document Form_fsubProjects"

End Sub
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
679
Kennelbloke
Kennelbloke
TrekBiker
  • Locked
  • Question
Form Data Not Updating
Replies
6
Views
550
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Forcing Form Updates
Replies
2
Views
444
TrekBiker
TrekBiker
TinaS
  • Locked
  • Question
Quick one I think - question about Properties window in Form Design view
Replies
1
Views
255
dhookom
dhookom
renigar
  • Locked
  • Question
Trying to add hyperlinks to pdfs through form entry
Replies
0
Views
407
renigar
renigar

Part and Inventory Search

Sponsor

Back
Top