Limit Edit of current record if FieldName of Recordlock = true

Still having an issue - see http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28355556.html

I am using the following code on Current of the subform:
the code compiles correctly, how the setting of the AllowEdits does not seem to do anything, I am still able to edit the current record.

what am I missing?


Private Sub Form_Current()
'Guser is a global function returning user login

'It is currently commented out not sure it is needed at this time.

    'If gUser = "user" And Nz(Me.RecordLock, 0) = -1 Then
    If Nz(Me.RecordLock, 0) = -1 Then
        Me.AllowEdits = False
    Else
        Me.AllowEdits = True
    End If
    gInvID = Nz(Me.IDInvoice)
    Me.ContractNumber = gContractID
End Sub

Open in new window

Karen SchaeferAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
hnasrConnect With a Mentor Commented:
Try the dirty event:

Private Sub Form_Dirty(Cancel As Integer)
     If booleanCondition Then ' assuming value True (-1)  or False (0)
        Cancel = True
    End If
End Sub


Private Sub Form_Dirty(Cancel As Integer)
     If Nz(Me.RecordLock, 0) = -1 Then ' assuming value True (-1)  or False (0)
        Cancel = True
    End If
End Sub
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you determined if your "If Nz(Me.Recordlock, 0) = -1 Then" line is actually running as expected? Put a breakpoint there and see what the value of Me.Recordlock is ...
0
 
Karen SchaeferAuthor Commented:
Scott,  It does return a value of -1 if on Current.

HNasr:

I tried both your suggestions, however, neither prevent the record from being editable.

any other Ideas?

K

I have tried the following on both Form Current and OnDirty.

Form Current - works for the first time the record is activiated, but after click various fields it seems to stop working I am able to edit the record even though Field - Recordlock =-1
Private Sub Form_Current()
    Dim strSQL As String
    
    If gUser = "user" And Nz(Me.RecordLock, 0) = -1 Then
        Me.AllowEdits = False
    Else
        Me.AllowEdits = True
    End If
    
    strSQL = "Select VendorName, AgencyID, AgencyPID, Currency, ContractNumber from tblinvoice" & _
            " Where ContractNumber = " & Chr(34) & gContractID & Chr(34) & "" & _
            " GROUP BY VendorName, AgencyID, AgencyPID,Currency, ContractNumber" & _
            " ORDER BY VendorName"
    Me.cboVendorName.RowSource = strSQL
    gInvID = Nz(Me.IDInvoice)
    Me.ContractNumber = gContractID
End Sub

Open in new window

Private Sub Form_Dirty(Cancel As Integer)
     If Nz(Me.RecordLock, 0) = -1 Then ' assuming value True (-1)  or False (0)
        Cancel = True
    End If
End Sub

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Scott,  It does return a value of -1 if on Current.
If RecordLock = -1, then the for would ALLOW edits. Is that what you want?

If you want the user to be able to edit the form when RecordLock <> 0:

Me.AllowEdits = Nz(Me.Recordlock, 0) =0

So if Recordlock is Null or 0, you'd allow edits. If it's anything else, you would not allow edits.

If you want the user to be able to edit the form ONLY when RecordLock = -1:

Me.AllowEdits = Nz(Me.Recordlock, 0) =-1
0
 
Karen SchaeferAuthor Commented:
Ok, the issue seems to be happening on change from 1 Contract/Vendor combo - changing the record source for the subform.  What do I need to do, to prevent the changing the code to prevent the edit ability of the subform?
0
 
Karen SchaeferAuthor Commented:
thanks for the suggestion, however, it just the opposite.

if me.recordlock = -1

I DO Not want them to edit the record. lock the record preventing any edit of the fields, however, if me.recordlock = 0 then allow Edits.

K
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The change the code accordingly. What I posted was just an example of what can be done with this sort of logic.
0
 
Karen SchaeferAuthor Commented:
the record locks for the first time the subform is updated however, after I attempt to edit/Add a single record then the fields become editable again now matter what the Recordlock value equals.
0
 
Karen SchaeferAuthor Commented:
Where should the code be placed so that it affects every record, that on focus of the record it will evaluated the status of the RecordLock Field?

K
0
 
Karen SchaeferAuthor Commented:
This is what I have so far and seems to be working, except now when I need to add a record (RecordLock <> -1) it is creating multiple blank records.

Note I changed to locking the field and setting allow edits to false.

So why is it creating a blank record?

screenshot
Private Sub Form_Current()
    Dim strSQL As String
    
    If Nz(Me.RecordLock, 0) = -1 Then
        Me.cboVendorName.Locked = True
        Me.AgencyID.Locked = True
        Me.AgencyPID.Locked = True
        Me.InvoiceDate.Locked = True
        Me.InvoiceNumber.Locked = True
        Me.Currency.Locked = True
        Me.AllowEdits = False
        Me.AllowDeletions = False
    Else
        Me.cboVendorName.Locked = False
        Me.AgencyID.Locked = True
        Me.AgencyPID.Locked = True
        Me.InvoiceDate.Locked = False
        Me.InvoiceNumber.Locked = False
        Me.Currency.Locked = True
        Me.AllowEdits = True
        Me.AllowDeletions = True
    End If
    
    strSQL = "Select VendorName, AgencyID, AgencyPID, Currency, ContractNumber from tblinvoice" & _
            " Where ContractNumber = " & Chr(34) & gContractID & Chr(34) & "" & _
            " GROUP BY VendorName, AgencyID, AgencyPID,Currency, ContractNumber" & _
            " ORDER BY VendorName"
    Me.cboVendorName.RowSource = strSQL
    gInvID = Nz(Me.IDInvoice)
    Me.ContractNumber = gContractID
End Sub

Open in new window

0
 
hnasrCommented:
If not resolved upload sample database demonstrating the issue, Specify the form to look at.
0
All Courses

From novice to tech pro — start learning today.