Solved

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

Posted on 2014-02-24
6
337 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
ID: 39883782
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
ID: 39883895
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
ID: 39884118
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39884183
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
ID: 39884197
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
ID: 39987224
thanks for your time - no longer need this problem resolved
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

679 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