Solved

Limit Edit of current record if FieldName of Recordlock = true

Posted on 2014-02-06
11
528 Views
Last Modified: 2014-02-19
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
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 85
ID: 39840722
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
 
LVL 30

Accepted Solution

by:
hnasr earned 250 total points
ID: 39840725
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
 

Author Comment

by:Karen Schaefer
ID: 39842250
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39842300
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
 

Author Comment

by:Karen Schaefer
ID: 39842303
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
 

Author Comment

by:Karen Schaefer
ID: 39842317
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
 
LVL 85
ID: 39842332
The change the code accordingly. What I posted was just an example of what can be done with this sort of logic.
0
 

Author Comment

by:Karen Schaefer
ID: 39842349
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
 

Author Comment

by:Karen Schaefer
ID: 39842355
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
 

Author Comment

by:Karen Schaefer
ID: 39842483
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
 
LVL 30

Expert Comment

by:hnasr
ID: 39842840
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!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

695 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