By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,478 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Need VBA Code to check a Yes/No box based off a Combo box selection

100+
P: 117
Looking for help on code to check the yes no box if a certain selection is picked from the combo box. For example if I pick "Purchased" from the combo it will put a check in the Yes/No box labeled "Purchased Component". Seems like it should be easy, but I'm having a brain fart and seem to think of how to do it.

Thanks for all the help.
2 Weeks Ago #1
Share this Question
Share on Google+
11 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,128
DJ,

As usual, we ask our posters to submit their work so far. We would much prefer to troubleshoot than create.

Also, if your current procedure produces any errors, it would be helpful to know those, as well.
2 Weeks Ago #2

100+
P: 117
Have done code yet, as I'm not sure where to start other than in the after update on the form; after that not sure what to do.
2 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,128
Well then....

Without getting into specifics, which would be impossible based upon the information given, you would have a procedure in the AfterUpdate event of the Combo box. Check the value of the combo box and based upon its value, set the value of the other Check boxes.

I can't give you more, because I don't know the content of your combo box, nor the specific names of your controls.
2 Weeks Ago #4

100+
P: 117
Combo Name=RejectAreaCodeID
Yes/No Box Name=Purchased Component?
Form record source Name=QRY-NewTag

Combo box has around 5 entries in it, but only need the code to work when "Purchased"(ID Number 11) is pick from the dropdown.
2 Weeks Ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,128
DJ - the implication here is that you will try to implement the instructions above into a solution that might work. I know you've been around here long enough to know the general procedures.

We typically ask the posters to try to build their own solutions and then we will gladly troubleshoot.
2 Weeks Ago #6

100+
P: 117
Expand|Select|Wrap|Line Numbers
  1. If [REJECT AREA]![Reject Code Name] = "Purchased" Then
  2.  
  3.         [Reject tags]![Purchased Component?] = True
  4.  
  5.     End If
I tried this and got an error I have never seen before when debugging it.

Compile error:

Qualifier must be Collection

Highlights this part of the code
Expand|Select|Wrap|Line Numbers
  1. ![Purchased Component?] =
2 Weeks Ago #7

twinnyfo
Expert Mod 2.5K+
P: 3,128
So..................

Let's start with some good advice, based upon years of experience, and offered to grant yourself and otherss some sanity when reviewing your code.

Just based off your code, I have no idea what [REJECT AREA], [Reject Code Name], [Reject tags] and [Purchased Component?] represent. This is the first challenge with trying to figure out your code. We'll come back to this in a moment.

In a previous post, you stated:
Combo Name=RejectAreaCodeID
Yes/No Box Name=Purchased Component?
Form record source Name=QRY-NewTag
This is not helpful for either sets of information you have provided, as the only consistent element between the two is Purchased Component?, which apparently is a Check Box?

So, here is the advice part (you will hear me telling all folks on this forum the same thing): find a good naming convention for your database objects and use it for every object in your database. At least you have made an attempt at that for your Query Name.

For example, I have no idea what the name of your Form is. But if you had a form named frmComponents, then you and I can automatically make some general assumptions about things without needing additional information. First, we know that we are talking about a Form object--this is helpful, because we know what forms do and how they work. Second, we also have an idea that this Form deals with Components. Now, greater detail might be required to help us understand what aspect of Components this Form deals with, but it gets us headed in the right direction before we even start troubleshooting.

So, here is brief naming convention that might get you started:

frm - Forms
rpt - Reports
cbo - Combo Box
txt - Text Box
chk - Check Box
lbl - Label
qry - Query
tbl - Table
cmd - Command Button

If you search for Database Naming Conventions, you will find plenty to choose from. Just pick one and stick with it. It will save you (and especially others) many headaches.

Second, try (at all costs) to avoid spaces and special characters in your naming convention. Having a Question mark may be an acceptable character in MS Access's system, but it is not wise, as the question mark may also be a wildcard. This can cause problems down the road. Avoiding spaces makes it easier to refer to things in VBA (especially when working with Field Names within recordsets).

Now, based upon the code you provided, it is literally impossible for me to troubleshoot.

However, I ask you to make some changes to your naming convention and then update your code to reflect those naming changes and repost those changes. I'll be glad to take a look once things make more sense.

Thanks.
2 Weeks Ago #8

NeoPa
Expert Mod 15k+
P: 31,273
What I'm observing here is that DJ seems mostly happy to let Twinny do all the heavy lifting for the work in his own project. That's a real problem and I will continue to observe.
@DJ.
Please. Prove me wrong.
2 Weeks Ago #9

100+
P: 117
So after a little more research and walking away from the project I got it figured out.

Expand|Select|Wrap|Line Numbers
  1. Private Sub REJECT_CODE_NAME_AfterUpdate()
  2.  
  3.      If Me.[REJECT AREA].Value = "Purchased" Then
  4.  
  5.         Me.[Purchased Component?].Value = True
  6.  
  7.         Else
  8.  
  9.         Me.[Purchased Component?].Value = False
  10.  
  11.     End If
  12.  
  13. End Sub
You have now been proved wrong @NeoPa - All I was looking for was for direction, not someone to do the heavy lifting.

And thank you Twinnyfo for your naming strategy. This was an older Database that I made before this group got me fixed on how to make them correctly. I am in the process or redoing this one as an upgrade from all of the stuff I learned here and other research I have been doing.

I the newer version it worked right away, but when I tried it on the older version is where I had the problem, but now have it.

I'm only updating the old one as a stop gap till I get the newer version completed to my liking.
2 Weeks Ago #10

twinnyfo
Expert Mod 2.5K+
P: 3,128
One of NeoPa's tricks:

Expand|Select|Wrap|Line Numbers
  1. Private Sub REJECT_CODE_NAME_AfterUpdate()
  2.     Me.[Purchased Component?] = _
  3.         (Me.[REJECT AREA] = "Purchased")
  4. End Sub
Also, no need to use the .Value Property, as it is the default.
2 Weeks Ago #11

NeoPa
Expert Mod 15k+
P: 31,273
DJRhino:
You have now been proved wrong @NeoPa - All I was looking for was for direction, not someone to do the heavy lifting.
It's a step in the right direction. The data doesn't lie. Your approach in this thread was to allow Twinny to do all the hard work as evidenced by the various posts. If you don't understand that then the future doesn't look very bright.

If your new approach shows consistently then I'll be happy to accept you've turned over a new leaf.
2 Weeks Ago #12

Post your reply

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