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.
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
Thanks all!
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
Thanks all!
When you end up making things this complicated, and then are looking at more complication to "fix" (band-aid) it, that's a good indication you've made a serious mistake in your design.
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.
Yeah, like that.
You're going to end up making something that's unmaintainable. Since you're either in requirements gathering or user acceptance testing ("the typing on trial was extremely repetitive"), that's going to mean that making necessary changes are going to be difficult and costly.
Your tables are full of implict data that needs to be parsed.
The location names used in the LM table always start with "w" if it's water, and "s" if it's sewer.
Jesus. What made this ever seem like a good idea? This is the road to failure.
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!
Listen to the whispers. Rethink your tables.
Your design is not worth your time -- or Mefi's time -- trying to salvage. Not trying to be cruel or harsh -- often the way we get to good design is by writing a first prototype (and often a second) that by sucking badly exposes what not to do.
You're lucky that you've managed to catch the problem early. Now use the time you've gained by doing it right. Don't try to fix this prototype; learn from it and then throw it away.
posted by orthogonality at 7:46 PM on February 20, 2010
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.
Yeah, like that.
You're going to end up making something that's unmaintainable. Since you're either in requirements gathering or user acceptance testing ("the typing on trial was extremely repetitive"), that's going to mean that making necessary changes are going to be difficult and costly.
Your tables are full of implict data that needs to be parsed.
The location names used in the LM table always start with "w" if it's water, and "s" if it's sewer.
Jesus. What made this ever seem like a good idea? This is the road to failure.
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!
Listen to the whispers. Rethink your tables.
Your design is not worth your time -- or Mefi's time -- trying to salvage. Not trying to be cruel or harsh -- often the way we get to good design is by writing a first prototype (and often a second) that by sucking badly exposes what not to do.
You're lucky that you've managed to catch the problem early. Now use the time you've gained by doing it right. Don't try to fix this prototype; learn from it and then throw it away.
posted by orthogonality at 7:46 PM on February 20, 2010
Response by poster: I can't say I disagree with you, Orthogonality; the problem I'm coming up against in terms of re-thinking the tables is time & inertia: This database has been used for 3 years, and has thousands of records in it. I was asked to streamline it (which I did & the users are VERY happy), but 2 weeks from the end of the project someone jumped in and said, can you integrate THIS? (hands me calendar with numbers plugged in). So here I am with a spatula and a bucket of plaster trying to patch something in that works with the existing system. On the other hand, it occurs to me I could just make the user type in "water/sewer" from a two option value-list & have done with it. That might be one Hell of a lot easier. (Going to try the .text/.string thing, though; want to know if it works!). Thanks for the thought AND the brutal honesty. Sometimes just talking about things that are stumping you with people who understand the concepts helps.
Do you have any specific thoughts on a different table setup?
posted by Ys at 5:40 AM on February 21, 2010
Do you have any specific thoughts on a different table setup?
posted by Ys at 5:40 AM on February 21, 2010
Best answer: I assume you know about using something like me.MLLocation.Column(1) to read the combobox text where there is an ID and text. Sorry if that's too simple and not at all what you are asking about, I haven't tried to get my head around the rest of your question.
posted by okbye at 9:00 AM on February 21, 2010
posted by okbye at 9:00 AM on February 21, 2010
Response by poster: .column worked a treat :) .text had some odd requirements. Still considering whether to tear it down & find a better way.
posted by Ys at 3:10 PM on February 22, 2010
posted by Ys at 3:10 PM on February 22, 2010
This thread is closed to new comments.
Or of course you can grab .value and run a quick little SELECT query on the appropriate table but that's pretty horrible considering that the form has already done it for you. Especially when there's a recordset instead of a table behind the form.
posted by polyglot at 6:46 PM on February 20, 2010