Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63

    Allowing a field to be changed one time

    I am working on a project that logs temperature readings, all is working well except i need to put a block on a field named [1st reading] so that it can be changed one time then it is locked.

    The field is already populated in table as part of a batch append query with 0.0 as the reading. It must be allowed to be changed one time.

    The Field Name is [1st Reading] on the form [log]
    The field is named [1st Reading] in the Table [Data]

    My thought is to create a field named [lock] on the table [Data] to control this

    My thinking is that when a user goes to change data in the form the [1st Reading] field it will trigger the "on dirty" event to look to see if the
    [lock] field is null. If it is it will allow the change and also do a "send value" command to enter "L". in the [lock] field of the table.



    The next attempt to enter data in the [1st Reading] Field the routine will see the "L" value and will not allow a change and will present a message saying "this field is locked for editing"

    How do I do this?
    i can see a simple if then and send value command but I dont know how to do the if [lock] is null allow value and send the "L" value
    or the if [lock] is not null send message.

    Is there a better way?

    FYI the allow additions and dont allow edits wont work because the field is pre-populated with a value populated with a value

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If the pre-populating is done using default values, then could you use the after update event to lock the control(s) when the value<>default?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    AfterUpdate event will not lock the control when record is revisited. Also, all instances of the control will have the same setting until the next time form is opened. If you want to lock the control immediately when value is changed from 0, AfterUpdate will serve but will also need VBA in form Current event.

    Instead, should be able to use Conditional Formatting to enable/disable control based on the value <> default and is not null. Lock field not needed and no VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    You could also try

    Code:
    Private Sub Form_Current()
    
    
        If Me.FirstReading <> "0.0" Then
    
    
            Me.FirstReading.Enabled = False
    
    
        Else
    
    
            Me.FirstReading.Enabled = True
    
    
        End If
    
    
    End Sub
    Another idea may be to use the control tag property. set the tag to "Lock" then loop through the controls and disable those whose value <> 0.0. this way you could stop any of the readings from being changed.

  5. #5
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    Well the only problem would be that entering a value of 0.0 is also a possibility.
    The Default 0.0 is only there because its a number field. (I would prefer nothing in the field but im not sure how to do that.) Either way if a user enters 0.0 as the value the field would have to lock to prevent a change.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You can remove value from the field DefaultValue property in table design as well as in the control property.

    Access automatically sets 0 as DefaultValue for newly created number type field in table and I always remove it. It also sets AllowZeroLength to Yes for text field and since I don't allow empty string I change this to No.

    Conditional Formatting rule like: Expression Is Not IsNull([FirstReading])

    The formatting will take effect as soon as user advances to next control, could not go back and change the entry. If you want to allow user to correct before record is committed, then go with VBA in form Current event.
    Last edited by June7; 12-30-2018 at 05:41 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just to say I agree that my original suggestion doesn't work - I should have tried it first!

    I have since tried using CF and it worked fine to disable the control where value is not null (number or text field)

    For a number field, this also worked in CF : Value<>[FieldName].[DefaultValue]
    However, I couldn't get that CF rule to work for a text field where a default value was used
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Since I usually name control different from the field it is bound to, I use the control name, not field name.

    This works for text field:

    <> Replace([ControlName].DefaultValue, Chr(34), "")

    or

    <> Replace([ControlName].DefaultValue, """", "")

    However, my attempt to do similar with a date field and DefaultValue set to Date() fails. I tried CDate([ControlName].DefaultValue) as well as concatenating # delimiters. Neither helps.

    Referencing Date() function does work:
    <> Date()

    If you want to test against default value then just use literal value instead of referencing DefaultValue property.
    Last edited by June7; 12-31-2018 at 12:41 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Well the only problem would be that entering a value of 0.0 is also a possibility.
    The Default 0.0 is only there because its a number field.
    Wouldn't 0.0 default to 0? I assumed you were using a text datatype.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Add a boolean field, e.g. Locked to table. In form, the control bound to this field is set hidden;
    In BeforeUpdate event of form, check this field, and take actions depending the Reading field being empty, 0 or >0 - set the boolean field True or let it remain False (you can use AfterUpdate too, but then you need a variable to remember last updated record, and have to run an update query to set boolean field value for this record);
    In Current event of form, set Reading control locked/disabled or not locked/enabled, depending on value of boolean field.

    As result, user can edit Reading control when the record is edited 1st time (or when after previous editing the field was left unlocked because specific conditions) - in all other cases Reading control will be locked/disabled

  11. #11
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    davedinger

    What if the user enters an incorrect value by mistake?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  12. #12
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    Ummm Huh?
    Sorry your speaking over my head. can you put this in Novice Speak for me?

  13. #13
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    i understand your concern, but the customer wants it so that when the user leave the field their answer is final and it cant be changed. If a wrong value is entered they have to provide an explanation on another form...

  14. #14
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    Sorry about posting this to another forum, but I just didn't understand what you were saying, and I'm frustrated because I can see how it should work but I can't get it to work and i saw the thread dying out so i tried to post it to a different group of people for a new prospective... at this time I think youre solution is the closest to what I need, Can you please help?

    After reviewing and researching your response and Google University this is what I did. I think its close to what you were talking about... Could you please look this over and elaborate?

    On the table Thermometer readings i made a new field text field named [1st Lock]
    On the form Thermometer Readings I now have the fields [1st Lock] and [1st Reading]

    The [1st Reading] field on the Form [Thermometer Readings] is the Field I am trying to toggle locked or un locked

    This is the statement Im trying to use, but i cant get it right.

    If Me![1st Lock] = "Y" Then
    Me![1st Reading].Enabled = True
    Me![1st Reading].Locked = False
    Else
    Me![1st Reading].Enabled = False
    Me![1st Reading].Locked = True
    End If
    End Sub

    The error I'm getting is "Thermometer Log cant find the field '1st Reading' referred to in your expression"

    Am I On the right track?

  15. #15
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    What does your table structure look like? Can you post a screen shot of your relationship window?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Time/User Stamp for Changed Record
    By dweekley in forum Access
    Replies: 2
    Last Post: 04-25-2017, 06:39 AM
  2. Replies: 3
    Last Post: 03-03-2017, 09:43 PM
  3. Replies: 1
    Last Post: 04-10-2016, 06:05 PM
  4. Replies: 8
    Last Post: 01-30-2013, 01:13 PM
  5. Replies: 4
    Last Post: 08-22-2012, 11:49 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums