Exit Sub

[carmenv323]

I am trying to do a BeforeUpdate event to check if the Project # field has been populated, if not go back to it otherwise continue to the btnClick event.


What is happening is that the pop-up box comes up if the project number is not populated, but it then moves to the btnClick event and gives me a debug error and highlights the Me.Dirty=False line:

Run-time error '3021':
No current record.

What should I do to have it focus on the project number and not go to the btnClick event? I think I should add an "if record not found" line on the btnClick event - what do you suggest/recommend?

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
   ' Check for a blank value in the Unit Cost field.
    If IsNull(Me![txtProjectNumber]) Then

       ' Alert the user.
       MsgBox "Project Number Must Be Entered to Capture Deliverables Checklist Location." & vbNewLine & vbNewLine & "Please Enter Project Number and try again." & _
       vbNewLine & vbNewLine & "If you are unsure of the Project Number at this time, enter TBD in the Project Number field."

      ' Cancel the update.
      Cancel = True
     
    End If
      Exit Sub
     
End Sub

=================================

Private Sub btnSubmitAndClose_Click()
'
    bSave = True
    If Me.Dirty Then
        Me.Dirty = False        ' Save the changes
    End If
  
    DoCmd.SetWarnings False
   
    DoCmd.OpenQuery "qryAppendMultipleReq" 'Append rows from Multiple Requests to Requests table
    DoCmd.RunSQL "Update tblRequests Set tblRequests.[cboEstimateStatus] = 'Pending Request' where tblRequests.[txtProjectNumber] = Forms!frmRequests!txtProjectNumber"
   
 
    DoCmd.RunSQL "Delete * From tblMultipleRequests2"
 
    DoCmd.Close acForm, "frmRequests"
   
    DoCmd.SetWarnings True
   
    DoCmd.OpenForm "MainMenu"
   
End Sub

[Bob Fitz]

Can you post a copy of the db

[carmenv323]

I'm sorry, but it's saying that the file is too big. is there another way to share?

[Bob Fitz]

I'm sorry, but it's saying that the file is too big. is there another way to share?

Compact and Repair the db. Then zip it and attach the zip file.

[Welshgasman]

You shouldn't need that Exit Sub.
Plus code does not move to another event of it's own free will

You would need to set focus back to the errant control?

[kd2017]

It's throwing an error because the save is canceled so it can't set Me.Dirty = False. You need to catch and handle the error in your button click method. Also, I don't think the Exit Sub line is necessary in the BeforeUpdate method.

Private Sub btnSubmitAndClose_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    
    '********************* WHY NOT START OFF THE SUB WITH SOME ERROR CHECKING?
    If IsNull(Me!txtProjectNumber) Then Err.Raise 1001, , "Project Number is blank"
    
    bSave = True '<------------------ WHAT'S THIS? IT'S NOT USED IN THIS ROUTINE OR DECLARED
    If Me.Dirty Then
        Me.Dirty = False        ' Save the changes
    End If
  
    '*********************CHANGED EXECUTION OF SQL FROM DoCmd.RunSQL TO DAO TO GET RID OF HANDLING WARNING FLAGS
    'DoCmd.SetWarnings False
    'DoCmd.OpenQuery "qryAppendMultipleReq" 'Append rows from Multiple Requests to Requests table
    'DoCmd.RunSQL "Update tblRequests Set tblRequests.[cboEstimateStatus] = 'Pending Request' where tblRequests.[txtProjectNumber] = Forms!frmRequests!txtProjectNumber"
    'DoCmd.RunSQL "Delete * From tblMultipleRequests2"
    
    Set db = CurrentDb
    db.Execute "qryAppendMultipleReq", dbFailOnError
    db.Execute "Update tblRequests Set tblRequests.[cboEstimateStatus] = 'Pending Request' where tblRequests.[txtProjectNumber] = " & Me!txtProjectNumber, dbFailOnError
    db.Execute "Delete * From tblMultipleRequests2", dbFailOnError
 
    DoCmd.Close acForm, "frmRequests"
   
    DoCmd.OpenForm "MainMenu"
    
ExitHandler:
    'DoCmd.SetWarnings True
    Set db = Nothing
    Exit Sub
    
ErrHandler:
   MsgBox Err.Description, , "Error #" & Err.Number
   Resume ExitHandler
End Sub

[carmenv323]

I have attached the file here

[carmenv323]

I'll try that.

I used the Exit Sub in the BeforeUpdate so it would get out of the loop and set focus to the project number field.

With this option, I wouldn't need a BeforeUpdate event?

[kd2017]

Exit sub is used to stop the execution of the sub early, since there are no lines of code between exit sub and end sub there is no reason to have it there.

I would definitely leave the BeforeUpdate event error checking, that's your last line of defense! But as you've seen some kind of error checking is needed in the button click event as well in this particular situation.

[carmenv323]

It seems to not be looking at the BeforeUpdate at all, seems redundant now...but regardless of that,

When a project number isn't populated, it pops up the error fine, I added the set focus to the code. however, when it is populated I get a "Data type mismatch in criteria expression"

any idea?

[Bob Fitz]

I have attached the file here

Does the db that I have attached below work for you.

[Welshgasman]

It seems to not be looking at the BeforeUpdate at all, seems redundant now...but regardless of that,

When a project number isn't populated, it pops up the error fine, I added the set focus to the code. however, when it is populated I get a "Data type mismatch in criteria expression"

any idea?

If project number is not really a number, you need to surround it with single quote '

[kd2017]

When a project number isn't populated, it pops up the error fine, I added the set focus to the code. however, when it is populated I get a "Data type mismatch in criteria expression"

any idea?

If project number is not really a number, you need to surround it with single quote '

Welshgasman called it. Your project number field in the tblRequests is a Short Text datatype so change this line to:

db.Execute "Update tblRequests Set tblRequests.[cboEstimateStatus] = 'Pending Request' where tblRequests.[txtProjectNumber] = '" & Me!txtProjectNumber & "'", dbFailOnError

It seems to not be looking at the BeforeUpdate at all, seems redundant now...but regardless of that,

That's correct. The button stopped execution of the code as soon as it saw that ProjectNumber was blank. You can remove this line and let it fall to the BeforeUpdate event if you want

If IsNull(Me!txtProjectNumber) Then Err.Raise 1001, , "Project Number is blank"

You can put a breakpoint at the top of your sub and then pressing F8 execute the code line by line to see exactly what's happening.

As far as redundancy goes the BeforeUpdate event will always execute before saving the record, you can use it to catch user input errors (data validation) no matter what button is pressed when records are trying to be saved. You needed some kind of code in the button sub to catch the error from post #1 (not data validation).

[carmenv323]

I updated the code, it removed the error but it's not updating the table

The Me.Requery is because it's a subform so I want it to requery itself to a blank form when the record is saved, just FYI.

Private Sub btnSubmitAndClose_Click()
'
On Error GoTo ErrHandler
    Dim db As DAO.Database
   
    If IsNull(Me!txtProjectNumber) Then Err.Raise 1001, , "Project Number Must Be Entered to Capture Deliverables Checklist Location." & vbNewLine & vbNewLine & "Please Enter Project Number and try again." & _
       vbNewLine & vbNewLine & "If you are unsure of the Project Number at this time, enter TBD in the Project Number field."
       Me.txtProjectNumber.SetFocus
 
    If Me.Dirty Then
        Me.Dirty = False        ' Save the changes
    End If
  
       
    Set db = CurrentDb
    db.Execute "qryAppendMultipleReq", dbFailOnError
 
    db.Execute "Update tblRequests Set tblRequests.[cboEstimateStatus] = 'Pending Request' where tblRequests.[txtProjectNumber] = ' " & Me!txtProjectNumber & "'", dbFailOnError
 
    db.Execute "Delete * From tblMultipleRequests2", dbFailOnError

   
ExitHandler:
    'DoCmd.SetWarnings True
    Set db = Nothing
    Exit Sub
   
ErrHandler:
   MsgBox Err.Description, , "Error #" & Err.Number
   Resume ExitHandler
    Me.Requery
 
End Sub

[Welshgasman]

Well you are never going to get to the requery unless you have an error?
In fact NEVER get to it.