• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 563
  • Last Modified:

Field value based on the combo box selection

Gd day,

In fact, there are two issues with the same logics behind. Hopefully, there will be no problem for Access Monsters:
Question 1:
There is a form based on the table. It has field [Due_Date] and it has field [Status]. Field [Status] is actually a combo box (2 colums: 1st p-key hidden; 2-nd text visible= "Outstanding" or "Closed".
Algorythm: If Combo is "Outstanding" then [Due_Date] can be whatever date or even empty.
If Combo is "Closed" then I want to be sure that the due date will be deleted automatically. Means, I need the field empty (not zero but empty). (people do forget to delete due date during closing of the case=> leading to closed case appears with due dates on the cumulative status etc.
Thanks in advance,

Question 2 is mostly similar:
Another table- another form. There is the same combo and 2 tick boxes.
Combo=" oustanding"=> [Tick1] and [Tick2] = whatever selected by the user
Combo="closed"=>[Tick1] and [Tick2]= not true

The code looks pretty simple. Dont understand why its not working.
Can split in two separate questions to award more points if necessary. Still dont understand where is the catch with these points (No time).
2 Solutions
Kelvin SparksCommented:
Use the After update event of the combo

Use code similar to the following

If Me.Status = Closed(replace with ID for closed" Then
     Me.DueDate = NULL
End If

Similarly for the second
If Me.Status = ClosedThen
   me.tick1 = False
End If

Please follow these steps:

1. Open your form in Design mode
2. Click on the Status combobox
3. Go to Properties and select Events tab
4. Pick the AfterUpdate event, and click the "..." button on the right
This will open the VBS editor in order to create an event handler for your component after its value has changed

5. Write the following code
Private Sub StatusCombo_AfterUpdate()
    If Me.StatusCombo.Value = 2 Then
        Me.DateField.Value = Null
End Sub

Open in new window

Note: I suppose your Status combo has another name than StatusCombo. Same thing with DateField. Please modify them in the code
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now