Lock records

[D Willett]

Apologies, I seem to be asking such alot this week. Although I have gained much ground on my own and this is an inherited database.
However, I'm stuck.

If my invoice number is > 0 with the form current event I should not allow edits or additions.
My code is locking the records but allowing new records to be added which isn't good...
I tried .NewRecord=block but it fails and errors...as its a read only property.

Code: Select all

Private Sub Form_Current()

    Dim bLock As Boolean
    
    If Nz(Me.txtInvoiceNumber > 0) Then
        bLock = False

    If Me.AllowEdits <> bLock Then
            Me.AllowEdits = bLock
            Me.AllowDeletions = bLock
            'Me.NewRecord = bLock
      
    With Me.[sbfStockAllocation].Form
            .AllowEdits = bLock
            .AllowDeletions = bLock
            '.NewRecord = bLock
    End With
        
    With Me.[sbfLabourBookings].Form
            .AllowEdits = bLock
            .AllowDeletions = bLock
            '.NewRecord = bLock
            
    End With
        
    With Me.[sbfOther].Form
            .AllowEdits = bLock
            .AllowDeletions = bLock
            '.NewRecord = bLock
         
    End With
 
    End If
End If
End Sub

[HansV]

The property that you want is AllowAdditions. Does this version do what you want?

Code: Select all

Private Sub Form_Current()
    Dim bLock As Boolean

    bLock = (Nz(Me.txtInvoiceNumber, 0) = 0)

    With Me
        .AllowEdits = bLock
        .AllowDeletions = bLock
        .AllowAdditions = bLock
    End With

    With Me.[sbfStockAllocation].Form
        .AllowEdits = bLock
        .AllowDeletions = bLock
        .AllowAdditions = bLock
    End With

    With Me.[sbfLabourBookings].Form
        .AllowEdits = bLock
        .AllowDeletions = bLock
        .AllowAdditions = bLock
    End With

    With Me.[sbfOther].Form
        .AllowEdits = bLock
        .AllowDeletions = bLock
        .AllowAdditions = bLock
    End With
End Sub

[D Willett]

Works perfectly. Thanks Hans

[D Willett]

Just need to revisit this:

Can I temporarily disable the forms current event.
I have an instance where a mistake has been made in the records and have to unlock the file and edit it.

I tried:

Code: Select all

Private Sub Command134_Click()
Dim tempPW As String
    tempPW = InputBox("Please enter password", "")
        If tempPW = "mmsg" Then
            Forms("frmSales").OnCurrent = ""
        Else
            Forms("frmSales").OnCurrent = "Form_Current"
        End If
End Sub

This doesn't work, the form has subforms for record entry and everything is still locked.??

[SteveH2508]

Make your form locking routine a separate procedure (perhaps pass in bLock as an argument).

Call it from Form_Current passing the value of bLock you want.

Call it from Command134_Click as well, passing in the value of bLock you require), conditional on the correct password being entered.

[D Willett]

Hi steve
Made the separate function and taken everything out of the current routine:

Private Sub LockAll()
.....block etc.....
End Sub

How do I call the routines from the current event and the command button?

Cheers

[SteveH2508]

Private Sub Form_Current()
Call LockAll
End Sub

[SteveH2508]

It might be easier if you recraft your procedure like this:

Code: Select all

    Private Sub Form_Current(argLock as Boolean)
        Dim bLock As Boolean

        bLock = argLock

        With Me
            .AllowEdits = bLock
            .AllowDeletions = bLock
            .AllowAdditions = bLock
        End With

        With Me.[sbfStockAllocation].Form
            .AllowEdits = bLock
            .AllowDeletions = bLock
            .AllowAdditions = bLock
        End With

        With Me.[sbfLabourBookings].Form
            .AllowEdits = bLock
            .AllowDeletions = bLock
            .AllowAdditions = bLock
        End With

        With Me.[sbfOther].Form
            .AllowEdits = bLock
            .AllowDeletions = bLock
            .AllowAdditions = bLock
        End With
    End Sub

Then you can do this:

Code: Select all

Private Sub Form_Current()
Call LockAll((Nz(Me.txtInvoiceNumber, 0) = 0))'this evaluates to True or False
End Sub

and this:

Code: Select all

Private Sub Command134_Click()
Dim tempPW As String
    tempPW = InputBox("Please enter password", "")
        If tempPW = "mmsg" Then
            Call LockAll(True)
        Else
            Call LockAll(False)
        End If
End Sub

I'm not clear as to which way round you want this to work
Air Code - not tested

[HansV]

Steve,

Didn't you mean

Code: Select all

    Private Sub LockAll(argLock as Boolean)

instead of

Code: Select all

    Private Sub Form_Current(argLock as Boolean)

[D Willett]

I saw that guys, as soon as I got the ambiguous name :-) Just testing it.

[D Willett]

Seems to be working guys.

Thanks very much for the help, have a great weekend.

[D Willett]

I've caused myself an issue. Because I have unlocked the form ( thats ok ) and I am editing an "Invoiced" record ( thats ok ), my invoice code needs changing so the RST is updated and not created as a new line.
I had a quick meddle with the code but it over writes other invoice lines instead.


This is the before code which adds a new invoice line in tblInvoice:

Code: Select all

Case "Invoice"
    If MsgBox("You Are About To Invoice This File, Do You Want To Continue?", vbYesNo, "Invoice") = vbYes Then

    Set RST = DB.OpenRecordset("tblInvoice")

    If DCount("*", "tblInvoice", "InvoiceNo = " & Me.txtInvoiceNumber.Value) > 0 Then
        MsgBox "This File Has Already Been Invoiced!", vbExclamation
    End If
    
    RST.AddNew
    RST!SalesOrderID = Forms!frmSales!SalesOrderID
    RST!VinylRetail = Forms!frmSales!txtVinylTotal
    RST!VinylGP£ = Forms!frmSales!txtVinylMarkup
    RST!LabourRetail = Forms!frmSales!txtLabourTotal
    RST!OtherRetail = Forms!frmSales!txtOtherTotal
    RST!Postage = Forms!frmSales!txtPostage
    RST!DiscountedSubTotal = Forms!frmSales!txtDiscountedTotal
    RST!MarkupP = Forms!frmSales!Markup
    RST!DiscountP = Forms!frmSales!txtDiscount
    RST!Discount£ = Forms!frmSales!txtDiscTotal
    RST!VAT£ = Forms!frmSales!txtVatAmount
    RST!VATP = Forms!frmSales!VAT1
    RST!TotalInCVAT = Forms!frmSales!TotalInCVAT
    RST.Update
    Set RST = Nothing
    Set DB = Nothing
    Me.Refresh
    
    Else
    End If

The code above needs to edit-update the existing invoice line, which it doesn't. You'll see I added the RST.Edit section which is wrong.

Code: Select all

Case "Invoice"
    If MsgBox("You Are About To Invoice This File, Do You Want To Continue?", vbYesNo, "Invoice") = vbYes Then

    Set RST = DB.OpenRecordset("tblInvoice")

    If DCount("*", "tblInvoice", "InvoiceNo = " & Me.txtInvoiceNumber.Value) > 0 Then
        MsgBox "This File Has Already Been Invoiced!", vbExclamation
        
    RST.Edit
    RST!SalesOrderID = Forms!frmSales!SalesOrderID
    RST!VinylRetail = Forms!frmSales!txtVinylTotal
    RST!VinylGP£ = Forms!frmSales!txtVinylMarkup
    RST!LabourRetail = Forms!frmSales!txtLabourTotal
    RST!OtherRetail = Forms!frmSales!txtOtherTotal
    RST!Postage = Forms!frmSales!txtPostage
    RST!DiscountedSubTotal = Forms!frmSales!txtDiscountedTotal
    RST!MarkupP = Forms!frmSales!Markup
    RST!DiscountP = Forms!frmSales!txtDiscount
    RST!Discount£ = Forms!frmSales!txtDiscTotal
    RST!VAT£ = Forms!frmSales!txtVatAmount
    RST!VATP = Forms!frmSales!VAT1
    RST!TotalInCVAT = Forms!frmSales!TotalInCVAT
    RST.Update
    Set RST = Nothing
    Set DB = Nothing
    Me.Refresh
    Exit Sub
    
    End If
    
    RST.AddNew
    RST!SalesOrderID = Forms!frmSales!SalesOrderID
    RST!VinylRetail = Forms!frmSales!txtVinylTotal
    RST!VinylGP£ = Forms!frmSales!txtVinylMarkup
    RST!LabourRetail = Forms!frmSales!txtLabourTotal
    RST!OtherRetail = Forms!frmSales!txtOtherTotal
    RST!Postage = Forms!frmSales!txtPostage
    RST!DiscountedSubTotal = Forms!frmSales!txtDiscountedTotal
    RST!MarkupP = Forms!frmSales!Markup
    RST!DiscountP = Forms!frmSales!txtDiscount
    RST!Discount£ = Forms!frmSales!txtDiscTotal
    RST!VAT£ = Forms!frmSales!txtVatAmount
    RST!VATP = Forms!frmSales!VAT1
    RST!TotalInCVAT = Forms!frmSales!TotalInCVAT
    RST.Update
    Set RST = Nothing
    Set DB = Nothing
    Me.Refresh
    
    Else
    End If

[HansV]

Which record do you want to edit? The one whose InvoiceNo equals Me.txtInvoiceNumber? If so:

Code: Select all

    Case "Invoice"
        If MsgBox("You Are About To Invoice This File, Do You Want To Continue?", _
                vbYesNo, "Invoice") = vbYes Then
            Set RST = Db.OpenRecordset("tblInvoice")
            ' Try to find invoice number
            RST.FindFirst "InvoiceNo = " & Me.txtInvoiceNumber.Value
            If RST.NoMatch Then
                ' If not found, add new record
                RST.AddNew
            Else
                ' If found, edit existing record
                MsgBox "This File Has Already Been Invoiced!", vbExclamation
                RST.Edit
            End If
            ' Set field values
            RST!SalesOrderID = Forms!frmSales!SalesOrderID
            RST!VinylRetail = Forms!frmSales!txtVinylTotal
            RST!VinylGP£ = Forms!frmSales!txtVinylMarkup
            RST!LabourRetail = Forms!frmSales!txtLabourTotal
            RST!OtherRetail = Forms!frmSales!txtOtherTotal
            RST!Postage = Forms!frmSales!txtPostage
            RST!DiscountedSubTotal = Forms!frmSales!txtDiscountedTotal
            RST!MarkupP = Forms!frmSales!Markup
            RST!DiscountP = Forms!frmSales!txtDiscount
            RST!Discount£ = Forms!frmSales!txtDiscTotal
            RST!VAT£ = Forms!frmSales!txtVatAmount
            RST!VATP = Forms!frmSales!VAT1
            RST!TotalInCVAT = Forms!frmSales!TotalInCVAT
            ' Update record
            RST.Update
            Set RST = Nothing
            Set Db = Nothing
            Me.Refresh
        End If

[D Willett]

Perfect Hans. Thanks again :-)