Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

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

0
Karen Schaefer
Asked:
Karen Schaefer
  • 6
  • 3
  • 2
2 Solutions
 
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
 
hnasrCommented:
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now