469,269 Members | 1,006 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,269 developers. It's quick & easy.

If item selected from dropdown activate check box ?

22
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub substatus_AfterUpdate()
  3.     If Status = 3 Then
  4.         Me.Check198 = -1
  5.     End If
  6. End Sub
  7.  
  8.  
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Check198_AfterUpdate()
  2. On Error GoTo Err_Check198_AfterUpdate
  3.     If [Check198] = -1 Then
  4.         Me.assignedtoRep = Now()
  5.         Me.[SubStatus] = 4
  6.     End If
  7.     DoCmd.Requery "SubStatus"
  8.     DoCmd.Requery "assignedtorep"
  9. Exit_Check198_AfterUpdate:
  10.     Exit Sub
  11.  
  12. Err_Check198_AfterUpdate:
  13.     MsgBox Err.Description
  14.     Resume Exit_Check198_AfterUpdate
  15.  
  16. End Sub
  17.  
Oct 20 '11 #1
12 5485
patjones
931 Expert 512MB
The AfterUpdate event for the check box will not fire as a result of setting the check box's status programmatically. You need to set the date field manually:

Expand|Select|Wrap|Line Numbers
  1. Private Sub substatus_AfterUpdate()
  2.     If Status = 3 Then
  3.         Me.Check198 = -1
  4.         Me.assignedtoRep = Now()
  5.     End If
  6. 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
Oct 20 '11 #2
johnnyc
22
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

Expand|Select|Wrap|Line Numbers
  1. Me.[SubStatus] = 4
to the code?
Oct 20 '11 #3
patjones
931 Expert 512MB
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
Oct 20 '11 #4
johnnyc
22
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
Expand|Select|Wrap|Line Numbers
  1. If Substatus = 3 Then
It does not work but I think that's what it should be unless Status is a function in VBA.
Oct 20 '11 #5
johnnyc
22
Pat,

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub substatus_AfterUpdate()
  2.  
  3.         If Me.SubStatus = 3 Then
  4.  
  5.         Me.ContractDeliverd = -1
  6.         Me.DeliveredDate = Now()
  7.  
  8. End Sub
Oct 20 '11 #6
patjones
931 Expert 512MB
Just before the If text, put this:

Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 20 '11 #7
johnnyc
22
Pat,

Nothing happened, here is my code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub substatus_AfterUpdate()
  2.         MsgBox Me.SubStatus
  3.         If Me.SubStatus = 3 Then
  4.  
  5.         Me.ContractDeliverd = -1
  6.         Me.DeliveredDate = Now()
  7.  
  8.          DoCmd.Requery "substatus"
  9. Exit_substatus_AfterUpdate
  10.  
  11. End Sub
Is there a way to check another way instead of "3" maybe the text?
Oct 20 '11 #8
patjones
931 Expert 512MB
Are you making sure to re-compile every time you make a modification in the code?
Oct 20 '11 #9
johnnyc
22
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!
Oct 20 '11 #10
patjones
931 Expert 512MB
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.
Oct 20 '11 #11
NeoPa
32,171 Expert Mod 16PB
You may find When Posting (VBA or SQL) Code illuminating Johnny (Not forgetting to follow the links contained therein).
Oct 21 '11 #12
johnnyc
22
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
Oct 21 '11 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Big Dave | last post: by
1 post views Thread by Rudy Ko | last post: by
5 posts views Thread by =?Utf-8?B?QnJlbmRlbiBCaXhsZXI=?= | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.