Solved

Limit Edit of current record if FieldName of Recordlock = true

Posted on 2014-02-06
11
512 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
  • 6
  • 3
  • 2
11 Comments
 
LVL 84
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
 
LVL 84

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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 84
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now