Solved

Limit Edit of current record if FieldName of Recordlock = true

Posted on 2014-02-06
11
517 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

776 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