Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Conditional data entry

    I have an Access 2010 form with a field that I want to be editable and to have a green background if the value in another field is one thing, but to be not editable (locked) and to have a grey background if the value in another field is another thing. Is it possible to make data entry conditional like this?

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    It is. You'd need to check the status of the other field, probably using the OnCurrent event, so that for each new record the status would be checked and your desired field's properties changed accordingly. Then you'd probably use similar code to the After Update event of the other field, to make sure that whenever that field is changed, you can check whether the first field properties need changing as well.

    In summary, this would be the way to do it.
    Rui
    -------
    R4

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. As I have not used events before and don't know what code to use, are you able to provide an example or suggest an online resource that shows how to do this? All I need is something that works like this in the [Value] field:
    IF [Status] = 0 THEN [Value] is editable and has background colour A ELSE [Value] is not editable and has background colour B.
    Last edited by Murgatroyd; 2013-11-13 at 23:18.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    This video shows how to add code to the AfterUpdate event: http://www.youtube.com/watch?v=KFMrSbmsrg4

    To add code to the onCurrent event, the process is pretty similar:

    1. Get the form in Design mode
    2. Click the top leftmost corner of the form (where you have a small black square) to select the whole form and then right-click it and select Properties from the popup menu. In the properties panel choose the Events tab. On Current is the first event on the list. Click it.
    3. There will be a button with ... at the right, click this one and choose the Code builder.

    You can then insert the code using the editor, which could go like this:

    Code:
    Private Sub status_AfterUpdate()
    Private Sub status_AfterUpdate()
    If status = 0 Then
        Value.Enabled = True
        Value.BackColor = 9145219
    Else
        Value.Enabled = False
        Value.BackColor = 0
    End If
    End Sub
    For the color values, you can use this as a reference: http://endprod.com/colors/

    This same code can be used both for the OnCurrent event and the AfterUpdate event of the Status control.
    Rui
    -------
    R4

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your further reply, much appreciated. The only thing I don't quite understand is why or whether both OnCurrent and AfterUpdate events are needed. The Status field cannot be edited in this form, so does that mean I only need an OnCurrent event for the Status field?

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    The OnCurrent event is triggered whenever you change to a new record (which includes when the form is opened). If the status field is not editable, then you will just need the OnCurrent event, yes.
    Rui
    -------
    R4

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks again. I followed your suggestion and it works fine. I couldn't find the VBA colour code for hex CCFFCC but found that Val = ("&H" & "CCFFCC") works.
    Last edited by Murgatroyd; 2013-11-14 at 20:06.

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Great .
    Rui
    -------
    R4

Posting Permissions

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