Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Unanswered: locking and unlocking a field based on data in the table

    I have a form that users will be using to enter forecasts. The form has is as a continuous form and shows future and will as past data. I can't just lock the field as I want them to be able to edit future records but not edit old past records.

    In pseudocode I am thinking something along the lines of.

    When field is selected
    Look at tuple that field is part of and check field called "locked"
    if field locked = yes (ie i have a field in the table called locked, type yes/no)
    then form_field = locked
    else form_field = unlocked


    Is there some other way I can do this, or could someone give me a hand with the code for the above?

    Cheers, Bentley

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    The problem that you have is that, with continuous forms, if you lock the control (the field they are trying to edit) then it will lock the same control in all records.

    So...how about this approach? I am assuming you have a date field that will tell you if the record is a "past" record or not. Then, if that is in the subform (hidden or not), write some code for the control's "BeforeUpdate" event. In pseudocode:

    When the user tries to update the field:

    If the date of the record is in the past:

    Generate a message telling the user they cannot edit the field

    Cancel the Update

    Undo the field


    Hope this helps!!
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Jun 2004
    Posts
    92
    Or you could do something like this. I used an option group and a field type of text because yes/no was being retarded for me and not working. This code would lock any controls and ignore labels on your form. It is true that on continuous forms that this will apply to all records, but as you click in the textboxes for each record, it will work properly.

    Code:
    Private Sub Form_Current()
        Dim aControl As Control
        
        For Each aControl In Me.Controls
        If TypeOf aControl Is Label Then
        Else
    'If option group [optgrp] value is 1 (true) then lock the controls on the record.
            If optgrp.Value = 1 Then 
                    aControl.locked = True
                Else
                    aControl.locked = False
            End If
        End If
        Next aControl
    End Sub

  4. #4
    Join Date
    Aug 2004
    Posts
    4
    thanks guys.

    1) I don't really want to set it by date, there are several different product lines and some I need to have updated only for months 6 in the future and onwards, others 3 months in the future and onwards.

    I guess I could add a date field to the product table.

    2) I was thinking of locking the field when I am in that field and then unlocking it if I click on a field that is a suitable date. If that is the case (or I use on update). What is the code I could use to do this? I am confused about how I take a field (in this case unitsales) and check another field in the same record (in this case locked)?

    Looking foward to you replies.

    Cheers, Bentley

  5. #5
    Join Date
    Jun 2004
    Posts
    92
    You mean like in the on focus action something like:

    if locked.value = yes then
    unitsales.locked = true
    else
    unitsales.locked = false
    end if

  6. #6
    Join Date
    May 2004
    Posts
    63
    rbentley,

    Have you thought about using conditional formatting ?

  7. #7
    Join Date
    Aug 2004
    Posts
    4
    Sionus,

    yes..
    if locked.value = yes then
    unitsales.locked = true
    else
    unitsales.locked = false
    end if

    question is (and probably a really stupid question at that) is how do I make it know about the locked field when i am selecting the different field on the same tuple?

    ie. I am selecting salestable.units and want to lock if salestable.locked = "locked".

    Cheers, Bentley

  8. #8
    Join Date
    Aug 2004
    Posts
    4
    Sak2004,

    no.. i haven't considered conditional formatting... tell me more ;-)

    Cheers, Bentley

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •