Solved

Reset value of Combo back to original selection if criteria is met

Posted on 2014-02-24
6
313 Views
Last Modified: 2014-04-08
I am having difficulty in the placement of code for the best results.

 I need to verify that there is no unfinished invoices for the selected company (cboContract) if the user wishes to move to next record, exit form, etc.

if unfinished invoices exists then I need to force the user to finish the processing of these invoices (there is a hidden field ("RecordLock") is check when the invoices has been completed.

I placed on BeforeUpdate & Afterupdate of Combo, OnClose of the form, On the click of the Close button.  All options give me some results in handling the missing invoices processing, but not cleanly.

Errmsg
Looking for the best suggestion to prevent the user from leaving the form and current record without taken care of unpublished invoices.

if the user selects a different name in the Combocontract, then I need  the value to remain the same as it was prior to the attempt to change Contract names.

 using the combocontract.oldvalue

Private Sub ComboContract_BeforeUpdate(Cancel As Integer)
Dim curDB As DAO.Database
Dim strSQL As String, strCriteria As String
Dim rs As DAO.Recordset, rst As DAO.Recordset
Dim X As Integer, i As Boolean
   
   On Error GoTo ComboContract_BeforeUpdate_Error

Set curDB = CurrentDb

    UserAccess
    Forms![frmCodingSlip]![Sub1].Form.Visible = False
    
    If IsNull(Me.ComboContract.OldValue) <> True Then
        Me.ComboContract = Me.ComboContract.OldValue
    End If
    
'    If Me.ComboContract.Value = IsNull(Me.ComboContract) Then
'        Me.Filter = vbNullString
'        Me.FilterOn = False
'    Else
'        Me.Filter = "[ContractNumber] = '" & Me.ComboContract.Value & "'"
'        Me.FilterOn = True
'    End If
'
    gContractID = Nz(Me.ContractNumber, 0)
    'Checks for fieldname: RecordLock = False for specified Invoice
    X = DLookup("Recordlock", "TblInvoice", "IDInvoice = " & GetgInvID() & "")
    
    'Checks for fieldname: RecordLock = False for all Invoices for Contract
        strSQL = "SELECT RecordLock" & _
                " FROM tblinvoice" & _
                " WHERE (((ContractNumber)=" & Chr(34) & gContractID & Chr(34) & ") AND ((RecordLock)=-1))"
        
        Set rs = curDB.OpenRecordset(strSQL)

        If rs.RecordCount > 0 Then
            Select Case MsgBox("Sorry unable to make a different selection, until Unpublished Invoices have been processed/printed." _
                               & vbCrLf & "" _
                               & vbCrLf & "Do you wish to print UnPublished Invoices?" _
                               , vbOK Or vbCritical Or vbDefaultButton1, "Invoice Needs to be Printed")
    
                Case vbOK
                    Me.ComboContract.Value = Me.ComboContract.OldValue
                    Set rst = Me.RecordsetClone
                    strCriteria = rs.Fields("RecordLock") = False
                    rst.FindFirst strCriteria
                    Me.Bookmark = rst.Bookmark
                    
                    If Me.LblPrintNote.Visible = False Then
                        Forms![frmCodingSlip]![LblPrintNote].Visible = True
                    End If
                        
                        GoTo subPrint:
                
                Case vbCancel
                   DoCmd.RunCommand acCmdUndo
                   Exit Sub
            End Select
        End If
'           DoCmd.OpenReport "rptAPCodingSlip", acViewNormal, "IDInvoice = " & Chr(39) & gInvID & Chr(39) & ""
'           DoCmd.OpenReport "rptInvoiceActivity_Slip", acViewNormal, "ContractNo = " & Chr(34) & gContractID & Chr(34) & ""
subPrint:
            DoCmd.OpenReport "rptAPCodingSlip", acViewPreview, "IDInvoice = " & Chr(39) & gInvID & Chr(39) & ""
            DoCmd.OpenReport "rptInvoiceActivity_Slip", acViewPreview, "ContractNo = " & Chr(34) & gContractID & Chr(34) & ""
    
            'Updates Recordlock = True per contract/invoice
            If X = 0 Then
                DoCmd.RunSQL "Update tblinvoice Set RecordLock = -1 where IDInvoice = " & gInvID & ""
            End If
           Me.Repaint
   On Error GoTo 0
   Exit Sub

ComboContract_BeforeUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure ComboContract_BeforeUpdate of VBA Document Form_frmCodingSlip"
End Sub

Open in new window


Private Sub cmdPrintCodingSlip_Click()
Dim curDB As DAO.Database
Dim strSQL As String, strCriteria As String
Dim rs As DAO.Recordset, rst As DAO.Recordset
Dim X As Integer
   
   On Error GoTo cmdPrintCodingSlip_Click_Error

Set curDB = CurrentDb
        
UserAccess

gContractID = Nz(Me.ContractNumber, 0)

    'Checks for fieldname: RecordLock = False for specified Invoice
    X = DLookup("Recordlock", "TblInvoice", "IDInvoice = " & GetgInvID() & "")
    
    'Validates Users Acces and
    If gUser = "User" And X = -1 Then
        Call _
            MsgBox("Sorry, you are not AUTHORIZED to reprint this invoice.  Please contact the system admin for further assistance .", _
            vbCritical, "Not Authorized")
    Else
           'Checks for fieldname: RecordLock = False for all Invoices for Contract
        strSQL = "SELECT RecordLock" & _
                " FROM tblinvoice" & _
                " WHERE (((ContractNumber)=" & Chr(34) & gContractID & Chr(34) & ") AND ((RecordLock)=FALSE))"
        
        Set rs = curDB.OpenRecordset(strSQL)
        
        'Validates unpublished invoices - forcing user to process invoices
        If rs.RecordCount > 0 Then
            Select Case MsgBox("Sorry unable to process request until Unpublished Invoice(s) have been processed/printed." _
                               & vbCrLf & "" _
                               & vbCrLf & "Do you wish to print UnPublished Invoices?" _
                               , vbOK Or vbCritical Or vbDefaultButton1, "Invoice Needs to be Printed")
    
                Case vbOK
                    'Goto the first record where Invoice needs to be published.
                    Set rst = Me.RecordsetClone
                    strCriteria = rs.Fields("RecordLock") = False
                    rst.FindFirst strCriteria
                    Me.Bookmark = rst.Bookmark
                    
                    'Hides Printing label
                    Forms![frmCodingSlip]![LblPrintNote].Visible = True
                    'Prints reports
                    GoTo subPrint:
                Case vbCancel
                   DoCmd.RunCommand acCmdUndo
                   Exit Sub
            End Select
        End If
'           DoCmd.OpenReport "rptAPCodingSlip", acViewNormal, "IDInvoice = " & Chr(39) & gInvID & Chr(39) & ""
'           DoCmd.OpenReport "rptInvoiceActivity_Slip", acViewNormal, "ContractNo = " & Chr(34) & gContractID & Chr(34) & ""
subPrint:
            DoCmd.OpenReport "rptAPCodingSlip", acViewPreview, "IDInvoice = " & Chr(39) & gInvID & Chr(39) & ""
            DoCmd.OpenReport "rptInvoiceActivity_Slip", acViewPreview, "ContractNo = " & Chr(34) & gContractID & Chr(34) & ""
    
            If X = 0 Then
                DoCmd.RunSQL "Update tblinvoice Set RecordLock = -1 where IDInvoice = " & gInvID & ""
            End If
    End If
            Me.Repaint
   On Error GoTo 0
   Exit Sub

cmdPrintCodingSlip_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure cmdPrintCodingSlip_Click of VBA Document Form_frmCodingSlip"
End Sub

Open in new window

Private Sub Form_Close()
    cmdPrintCodingSlip_Click
End Sub

Open in new window

0
Comment
Question by:Karen Schaefer
  • 4
  • 2
6 Comments
 
LVL 84
Comment Utility
I would think the Form's BeforeUpdate event would be the most logical place. It fires immediately before the form attempts to write data, and you can validate there. For example:

Sub Form_Update(Cancel As Integer)
  If Me.Recordlock="SomeValue" Then
    Msgbox "You cannot save this form now"
    Cancel = True
  End If
End Sub

If you need to verify that all records for a specific customer have a particular value in the Recordlock field:

If Nz(DCount("SomeField", "YourTable", "RecordLock=False AND Customer='" & Me.txCustomer & "'"), 1) > 0 Then
  Msgbox "One or more Invoices are still open for this customer"
  Cancel = True
End If
0
 

Author Comment

by:Karen Schaefer
Comment Utility
Ok, I tried your suggest, add the following code to the BeforeUpdate of the subform, and then I attempt to add a new record - the BeforeUpdate is not activated.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Nz(DCount("RecordLock", "tblInvoice", "RecordLock=False AND ContractNumber='" & gContractID & "'"), 1) > 0 Then
        Select Case MsgBox("Sorry unable to process request until Unpublished Invoice(s) have been processed/printed." _
                           & vbCrLf & "" _
                           & vbCrLf & "Do you wish to print UnPublished Invoices?" _
                           , vbOK Or vbCritical Or vbDefaultButton1, "Invoice Needs to be Printed")
    
            Case vbOK
                'Goto the first record where Invoice needs to be published.
                Set rst = Me.RecordsetClone
                strCriteria = rs.Fields("RecordLock") = False
                rst.FindFirst strCriteria
                Me.Bookmark = rst.Bookmark
    
                'Hides Printing label
                If Forms![frmCodingSlip]![LblPrintNote].Visible = False Then
                    Forms![frmCodingSlip]![LblPrintNote].Visible = True
                End If
                'Prints reports
                cmdPrintCodingSlip_Click
            Case vbCancel
               DoCmd.RunCommand acCmdUndo
               Exit Sub
        End Select
      Cancel = True
    End If
End Sub

Open in new window

0
 

Author Comment

by:Karen Schaefer
Comment Utility
formName: = "frmCordingSlip:
Subform = "frmInvoiceSlip"

OK I still looking to solve the following:

Run validation code upon the  user selecting a new contract name (cbocontract), prevent them from moving to next record until the unpublished invoices have been resolved.
See attached.

 form scrren shot
then the drop down will not change values stay on the invoices that need attention - Need code to reset the value back.

Also automatically on load of the form where there are invoices needing attention goto the first record that is involved - Setfocus.  need code to accomplish this.

thanks for your assistances.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
Do you mean the BU event does not FIRE, or that it DOES fire, but your code does not catch the validation.

Unless you've got issues with Access (or you're working with Unbound forms), the BeforeUpdate event always fires, so if you're not working with unbound forms the first thing to do is determine whether the BU event actually fires.

Also automatically on load of the form where there are invoices needing attention goto the first record that is involved - Setfocus.  need code to accomplish this.
You've been here long enough to know better - ask this in a new question.
0
 

Author Comment

by:Karen Schaefer
Comment Utility
Ok which BeforeUpdate should have the code.

the Main Form or the subform.

since I also want to execute the validation code when the user changes the Contract Name, When they attempt to close the form.

Do you mean the BU event does not FIRE, or that it DOES fire, but your code does not catch the validation.

I tried to debug the code and it did not activate the BeforeUpdate code on the Mainform, when i attempted any of the above methods.
0
 

Author Closing Comment

by:Karen Schaefer
Comment Utility
thanks for your time - no longer need this problem resolved
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now