VBA Moron Wants Magic Bullet
February 20, 2010 6:22 PM Subscribe
Access 2007: Need code to grab the value _displayed_ in a combo box (as opposed to its actual value). Big, detailed explanation inside.
posted by Ys to Computers & Internet (5 answers total)
I have a form which involves a couple of combo boxes, and I would like to base some conditional coding based on the displayed contents of the combo boxes. However, the bound field of the combo box is not the control source of the combo box. How do I get my VBA code to recognize the displayed text, rather than going to the underlying number?
Specifics, if needed:
The table this form feeds back to has the following fields:
LMAutoID AutoNumber field
LMDate Date field
LMEmployee Number field (lookup box feeding from an employee table.)
LMLocation Number field (lookup box feeding from a cost allocation table)
LMEmergency Yes/No field
LMCount Number field
Basically, the LMEmployee has to submit daily LMCounts for any of 4 possible locations, and subdivide them out by whether it was (1) an emergency and (2) a water or a sewer call. The water/sewer differentiation has not been given a field because the cost allocation table lists water/sewer for each location option, making the distinction implicit in the LMLocation selection. Note that both lookup fields are rooted in their respective table's autoID field, for all they display a text value.
Because of the nature of the data collected (up to 16 LMCount entries for each date), the typing on trial was extremely repetitive. So far I've dealt with that by
(1) embedding the LM form in an employee form, so that the employee can pull up their own name and have that value forward to the LM form, so they don't have to type their own name for every entry of every day of the month.
(2) adding an unbound field at the end of the form with an "on exit" event procedure. Depending on whether the unbound field has been changed, the default value for the date field will either be the same date as the record just finished, or will advance by one day.
(3) setting the default value of LMEmergency to "no," since about 80% of the time, entries will be non-emergency only.
The final cheat that would make this data entry relatively painless would be an evaluation based on whether the entry was "Water" or "Sewer." Basically, the things almost always come in pairs. (...almost...) So if you did 5 line marking tickets for water at a location on December 7, you'd almost certainly be doing 5 sewer markings at that location on December 7. What I'd like to do is to set up something where every time a water record was created, the new record would basically default-in all the values from the exiting record, except for the MLLocation specification. The user could then specify the location (and again, the water-sewer distinction is implicit in the location selection), press [tab], and the entry, voila, would be done. Or [Esc] if there were no corresponding Sewer markings.
I'm not good at VBA. I only venture there when I've run out of SQL ideas. I tried making a (non-visible) combo-box which was sourced on MLLocation but displayed the cost allocation's water-sewer designation, but the problem is still the same:
me.MLLocation.value = the wrong field
The number that statement collects is the bound column's value, which happens to be the cost allocation's autoID number. This cannot (in my opinion --which may be uninformed) be meaningfully translated into the value I need, which is "Water" or "Sewer". Is there a way to simply copy the displayed string from the combo box and use that value as the basis for my if/then statement?
There is a whispering voice at the back of my brain that says, "rethink the tables, man!" But I don't think that parsing this table into multiple interdependent tables will result in easier data entry. I'm really hoping for a code fix, since there's so much cool stuff that can be accomplished that way, if you just know what to say!
Bonus points if you can figure a way to get the MLLocation field to fill in itself: The location names used in the LM table always start with "w" if it's water, and "s" if it's sewer. So a typical allocation record would be:
AutoID LocationName System LocationLookupName
1 Mercer Island Water wMI
3 Hells Gate Sewer sHG
5 Mercer Island Sewer sMI
6 Hells Gate Water wHG
This transfers to the LM table as:
Date Employee Location Emerg? Count
12/5/09 Dan The Man wMI No 4
12/5/09 Dan The Man sMI No 4