Need cmb to change based on another cmb

[BullHalseyUSN]

Hello Experts!

Basically I need a combo box, when a record is opened on my form to look at another combo box and display a value based on the other combo box (box did that come out unclear!)

Here is the If block, which may make things clearer:

If Me![CmbRankOrRating].Column(1) = "Ensign" Or _
Me![CmbRankOrRating].Column(1) = "LTJG" Or _
Me![CmbRankOrRating].Column(1) = "LT" Or _
Me![CmbRankOrRating].Column(1) = "LCDR" Or _
Me![CmbRankOrRating].Column(1) = "CDR" Or _
Me![CmbRankOrRating].Column(1) = "CAPT" Then

So, If my Cmb has any of these values, then I'd like:

Me![CmbRate].Column(1) = "n/a"

to be displayed, and I'd like it to happen when I navigate from one record to another (My form displays one record at a time - it's, say, the entire history of Bob Smith or whatever).

Also, would someone care to rec. a good reference book for Access VBA, with practical examples? Many thanks!

Hope everyone in the East's enjoying the snow!

BH

 

[SBendBuckeye]

Hello Bull,

Sorry, I've got to run, but if you are a serious developer then you must have the Accessxxxx Developers Handbook by Getz, Litwin, etc. I believe it is published by Cybex. It is a large (4" thick), 2 volume set. It is excellent. Sometimes it will make your head spin but even if you are somewhat new to Access VBA it has many helpful items. I credit that book and Tek-Tips with most of my Access growth.

Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[BullHalseyUSN]

Sounds like a good book to have around, Buck!

 

[JonFer]

You can use the OnCurrent event of the form to initiate the rate combo box source update.

Do you have a table that lists the valid rates for each RankOrRating option? If so, use this table as the record source for the rate combo box and add a criteria of "Forms!formname.cmbRankOrRating" to it. Then you don't need any Case statements. Any time you have long case statements or If/Else/Then constructs, consider using a table with the information instead - you are using a relational database after all.

Are you adding records with the form as well? If so, with my approach you wouldn't be able to add a rate until you had entered a rank and then refreshed the rate combo box (manually or by going to another record and then back).

 

[SBendBuckeye]

I got a couple different versions of that book on Half Price eBay for significant savings, or also used in almost new condition.
Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[alr0]

Hi Bull,

There is also a three bundle set fin the Getz Litwin series that includes VBA language reference. It was about $100 (new)for all three and well worth it.

I also use the Knowledge Base on line from Microsoft. Once you get used to the search engine you will find many useful how-to articles as well as known bugs and work-arounds. This is particularly valuable as it is updated regularly, books are often completed before the program is available.

Best of luck,

alr