If item selected from dropdown activate check box ?

[johnnyc]

Hello,

I have a drop down menu with four items. Then below that I have a check box and a date field. Currently if you are to click on the checkbox the date field updates with today's date.

However I would like it if a user checks the third option down "Contract Delivered" to then update the checkbox. Check itself which then will also update the date field.

Here is my logic and I'm new to VBA Access, so any help in regards to syntax or logic is greatly appreciated.

Private Sub substatus_AfterUpdate()
    If Status = 3 Then
        Me.Check198 = -1
    End If
End Sub

The above code is in the AfterUpdate event of my drop down field, I have also placed it under the Click event and no luck.

Here is my code for the check box which works fine:

Private Sub Check198_AfterUpdate()
On Error GoTo Err_Check198_AfterUpdate
    If [Check198] = -1 Then
        Me.assignedtoRep = Now()
        Me.[SubStatus] = 4
    End If
    DoCmd.Requery "SubStatus"
    DoCmd.Requery "assignedtorep"
Exit_Check198_AfterUpdate:
    Exit Sub

Err_Check198_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Check198_AfterUpdate
    
End Sub

[patjones]

The AfterUpdate event for the check box will not fire as a result of setting the check box's status programmaticall y. You need to set the date field manually:

Private Sub substatus_AfterUpdate()
    If Status = 3 Then
        Me.Check198 = -1
        Me.assignedtoRep = Now()
    End If
End Sub

As a side note and completely optional but highly suggested piece of advice, give your controls sensible names. If your project grows to anything beyond the simplest form, you'll be happy that you did.

Pat

[johnnyc]

Pat,

Thank you so much for the help. So you're saying that even if the code worked and the check box was clicked it will not set off the date.. I was not aware. Also thank you for your advice I will make sure to get better names on the controls and labels.

I've tried the code above but it does not seems to work. Would I be better placing this code in a different event such as On Change or On Click or is this the right event?

Also I was just guessing on the Status = 3 and Me.Check198 = -1. Will that choose the third item from the drop down list and will that -1 set the check box to marked. None of those are happening.
And do I need to perhaps mess with or add

Me.[SubStatus] = 4

to the code?

[patjones]

The check box AfterUpdate event will fire only if the user actually clicks the check box. This is my understanding of how the event works, and I tested it in my sandbox database. The other experts might have more insight into this matter than I do.

As for making a selection from the combo box...that code should work. Where does this variable "Status" come into the picture? I don't see it defined anywhere, and you're not showing where it gets its value from...I think this may be the crux of the problem.

Pat

[johnnyc]

Pat,

You're right, the check box only works if it is clicked then the date shows up. I want to see if I can have the program check it and then fill in the date once the third option from the dropdown menu Substatus has been selected.

And you just pointed out a flaw, Status is another drop down menu above Substatus drop down menu. Status has no relevance. So should my code be

If Substatus = 3 Then

It does not work but I think that's what it should be unless Status is a function in VBA.

[johnnyc]

Pat,

Here's my latest code, I feel like were getting closer but it does not work:

Private Sub substatus_AfterUpdate()
        
        If Me.SubStatus = 3 Then
        
        Me.ContractDeliverd = -1
        Me.DeliveredDate = Now()
      
End Sub

[patjones]

Just before the If text, put this:

MsgBox Me.SubStatus

This will tell you what value the combo box is actually returning. If it's not "3", and in particular if it's not even a number, then it should be instructive as to what is happening here.

[johnnyc]

Pat,

Nothing happened, here is my code:

Private Sub substatus_AfterUpdate()
        MsgBox Me.SubStatus
        If Me.SubStatus = 3 Then
        
        Me.ContractDeliverd = -1
        Me.DeliveredDate = Now()
        
         DoCmd.Requery "substatus"
Exit_substatus_AfterUpdate
      
End Sub

Is there a way to check another way instead of "3" maybe the text?

[patjones]

Are you making sure to re-compile every time you make a modification in the code?

[johnnyc]

Pat,

I just found out that I needed to compile. The compile button was never added to my VBA layout and I had no idea I needed to compile each time. So after a few too many compile errors and getting around them it seems that some of my code was lost when I opened the ".accdr" file. I am re writing all my updates and will put in the above code, compile and see if it works. Thank you for that btw!

[patjones]

It's actually in the Debug menu up top. Another debugging tip for you to look into is the concept of breakpoints, which stops the code execution at an executable line that you pick out ahead of time.

I'm getting ready to leave the office. I will check back in with the thread later tonight or in the morning.

[NeoPa]

You may find When Posting (VBA or SQL) Code illuminating Johnny (Not forgetting to follow the links contained therein).

[johnnyc]

Learning is great. I have followed your instructions from the post about variable declaration.I will debug all weekend and come back to this post once I debug. Thank you both