I have different brand names set up as toggle buttons. I would like to be able to click on "Tea/Juice" and have all the green buttons be 'clicked'. I know I have done this in the past in the On Click event for Tea/Juice button, I just can't remember how.
Next step will be to click On-Hand Catalog and get a report with the on-hand quantities for only the clicked buttons. Is there a better way to do something like this? I don't actually have the report built yet, so now is the time to change everything if there is a better way.
When I depress Tea/Juice, I would like all 7 buttons under it to be True. Kinda like a Group Toggle, though I still need to be able to select individual toggles as needed, so I can't put them all under the same toggle. I'm just trying to avoid clicking 7 buttons when I need a report with those brands.
I know the toggle buttons are pretty but perhaps a combo and a multi-listbox would be more functional. The combo would be used to filter the list box. When you select water, the listbox shows only water. If you just select water but leave the listbox null, that effective selects all options. Otherwise you can select one or more and limit the selection.
If you are welded to this design, you can build a table that lists the control names for each major category. Your codd would open a query and use the controlname field to select all the sub items.
There are a thousand ways to do this. Each one has its own particular brand of tedium. But what you want to do with it will guide our advice on how to get there from here.
1. Do you ever want to click the Tea/Juice button and then UNclick one of the selected items?
2. Will you ever want to click more than one "select whole column" button at the same time?
3. How rare or how likely is it that your list of items will change to add a new item, drop an old item, or change one item to another item?
4. You have a double-barreled column with Coca Cola/Energy and it appears that you have two selectable categories in the same column? Are you stuck on the format and spacing or can we offer variations of appearance?
The best advice for you depends on desired functionality and we need to be clear on what you intend for your interface before we go too far down this garden path.
So presumably adding another brand such as Pepsi or another drink type such as diet requires a redesign of your form?
Can understand the appeal of using buttons In terms of user interaction but perhaps you could look at using cascading multi select list boxes or cascading sub forms so you can still get the button ‘look’ and not have a redesign every time your catalog changes
All depends on what options you want users to have to generate their report
Ok, so let's assume you are wedded to the report design, and, I admit, with all it's programming shortcomings, it does look nice. Maybe the client insists. All the other suggestions above are important, but as a last resort, if you've got no options design-wise, why not put it on rails?
That is, name your top buttons uniformly e.g. 'tglCoffee', 'tglJuices', 'tglWaters', 'tglCoCola', 'tglEnergy' - all 9 letter names.
Then, name all your individual buttons similarly e.g. 'tgl_CoffeeCorePower', 'tgl_CoffeeDunkinDonuts', 'tgl_CoffeeMcCafe', etc
followed by 'tgl_JuicesFuzeTea', 'tgl_JuicesGoldPeakTea' etc you get the idea, just don't forget the underscore. You've got to name them something, after all, why not make it work for you?
Then after update of each of your top buttons, e.g. for tglCoffee_AfterUpdate:
Code:
goToggle "Coffee"
and for tglJuices_AfterUpdate:
Code:
goToggle "Juices"
The goToggle function would look like this:
Code:
Private Function goToggle(parName As String)
Dim ctl As Control
For Each ctl In Me.Controls
If Left(ctl.Name, 10) = "tgl_" & parName Then
ctl = Me("tgl" & parName)
End If
Next
End Function
And if you want to produce a filter for your stock catalog, it could look like this:
Code:
Private Function goCreateFilter() As String
' Note: this assumes that the name of the button has a suffix that matches to the product.
' This is a debatable practice (!), so it's included for medicinal purposes only.
Dim ctl As Control, strFilter As String
For Each ctl In Me.Controls
If Left(ctl.Name, 4) = "tgl_" Then
If ctl.Value = True Then
strFilter = strFilter & IIf(Len(strFilter) = 0, "", ", ") & "'" & Mid(ctl.Name, 11) & "'"
End If
End If
Next
If Len(strFilter) > 0 Then
goCreateFilter = "WHERE type In (" & strFilter & ")"
End If
End Function
This way, you can add or remove as many buttons as you want, as long as they 'fit on the rails' like the others. Put LOTS of comments in so that the next person knows how to work with it.
The only issue I would bring up regarding AuntieJack's approach (which isn't bad) is that you have a limit of 768 controls that can be added (and removed) on a form during its lifetime. Therefore, you would have to estimate how many changes you expect. Also, let's say you reach that limit. You would get an error very few of us ever see, related to the form's control limit. If you forget about this limit, it will hit you like a ton of bricks.
jdraw's question and my questions are all relevant BEFORE you start trying to implement this interface long-term. If you have a design document that spells out everything, great. However, given the way you asked the question, I'm going to assume you don't have such a thing. In this forum, it is rare to see that one has been created, so you aren't alone.
My father-in-law was a contract carpenter. His rule was "measure twice, cut once." Our variant of that rule is "design twice, implement once." If I were in your shoes, I would tread lightly before taking this much farther along the way. Take the time to clarify desired look & feel issues, behavior issues, end goals... design before you continue to code.
There are a thousand ways to do this. Each one has its own particular brand of tedium. But what you want to do with it will guide our advice on how to get there from here.
1. Do you ever want to click the Tea/Juice button and then UNclick one of the selected items?
2. Will you ever want to click more than one "select whole column" button at the same time?
3. How rare or how likely is it that your list of items will change to add a new item, drop an old item, or change one item to another item?
4. You have a double-barreled column with Coca Cola/Energy and it appears that you have two selectable categories in the same column? Are you stuck on the format and spacing or can we offer variations of appearance?
The best advice for you depends on desired functionality and we need to be clear on what you intend for your interface before we go too far down this garden path.
Answers to your questions:
1. Rare, but it might happen. If so, I could just click the ones needed, and not start with the Group.
2. Yes,
3. It is possible the list might add or drop items, I do not foresee changing one to another.
4. Coca Cola/Energy was put in the same column mostly for space issues. Clicking on CC would enable just the two CC buttons, while clicking on Energy would enable the four Energy buttons. Moving them to their own column is not a problem. Same with the Powerade button. It is it's own group.
What I'm trying to achieve is an inventory of our promotional /giveaway items. The last database I made was 20+ years ago, so I'm working with ancient knowledge using forms and queries to do everything. I am trying to learn VBA now. I am open to changing the interface to whatever would be easiest to use. I would be the primary end-user.
I will have forms for data entry on new products, customers (whoever is receiving the promotional item; could be a customer, could be an employee), incoming inventory and outgoing inventory.
I am trying to make an inventory of our promotional/giveaway items. What I was asking with this question was how to automate the On-Hand Quantity Report to only show certain brands of product.
Ultimately, what I need to track is:
On-Hand Quantities (Received - Outgoing +/- Correction)
Branding (to limit report if someone only wants to see what is available for a certain brand)
Which employee requested the item
Who received the item (customer name or employee name) There could be more than one item per request.
Which supervisor approved the request (only four people are able to approve)
Dates In/Out
Location in inventory
Does the item need to be returned? Is this only for promotion or giveaway?
This is what I currently have for tables/queries/forms
When I depress Tea/Juice, I would like all 7 buttons under it to be True. Kinda like a Group Toggle, though I still need to be able to select individual toggles as needed, so I can't put them all under the same toggle. I'm just trying to avoid clicking 7 buttons when I need a report with those brands.
@theDBguy just gave you the answer to this in post #2. did you not want to implement it for some reason?
on the click event of the master toggle button, if true, set the other ones (below them) to be true, as well. as indicated in post 2
...Having said that, IF you're doing this because you already have code in the lower buttons' Click events, and you're hoping to trigger that code , that's probably not a good direction to go, in my experience.
Rather, write a sub like ButtonCheck, which - all in one procedure - evaluates whether either 1) the master is clicked, or 2) the subs are clicked, and then acts accordingly.
IF what I mentioned about you wanting to trigger the lower buttons' Click events is true, then if you keep going down that path, you may find yourself later writing code to actually call those Click events, 'artificially' firing them, which in my experience is a bad road to start upon and indicates improperly modularized code.
Yes, I'm trying to implement that. Sorry, but this (to me) doesn't tell me how to click Button A and have Button B, Button C, and Button D be set to true.
Yes, I'm trying to implement that. Sorry, but this (to me) doesn't tell me how to click Button A and have Button B, Button C, and Button D be set to true.
You go into button A's click event, or change event, if it has one (I never use toggle buttons, but it will still be the same, basically) and write code.
Code:
if me.buttonA = True then
Me.ButtonB = True
Me.ButtonC=True
Me.ButtonD = True
else
Me.ButtonB = false
Me.ButtonC=false
Me.ButtonD = false
end if
You go into button A's click event, or change event, if it has one (I never use toggle buttons, but it will still be the same, basically) and write code.
Code:
if me.buttonA = True then
Me.ButtonB = True
Me.ButtonC=True
Me.ButtonD = True
else
Me.ButtonB = false
Me.ButtonC=false
Me.ButtonD = false
end if
Ok, so let's assume you are wedded to the report design, and, I admit, with all it's programming shortcomings, it does look nice. Maybe the client insists. All the other suggestions above are important, but as a last resort, if you've got no options design-wise, why not put it on rails?
That is, name your top buttons uniformly e.g. 'tglCoffee', 'tglJuices', 'tglWaters', 'tglCoCola', 'tglEnergy' - all 9 letter names.
Then, name all your individual buttons similarly e.g. 'tgl_CoffeeCorePower', 'tgl_CoffeeDunkinDonuts', 'tgl_CoffeeMcCafe', etc
followed by 'tgl_JuicesFuzeTea', 'tgl_JuicesGoldPeakTea' etc you get the idea, just don't forget the underscore. You've got to name them something, after all, why not make it work for you?
Then after update of each of your top buttons, e.g. for tglCoffee_AfterUpdate:
Code:
goToggle "Coffee"
and for tglJuices_AfterUpdate:
Code:
goToggle "Juices"
The goToggle function would look like this:
Code:
Private Function goToggle(parName As String)
Dim ctl As Control
For Each ctl In Me.Controls
If Left(ctl.Name, 10) = "tgl_" & parName Then
ctl = Me("tgl" & parName)
End If
Next
End Function
And if you want to produce a filter for your stock catalog, it could look like this:
Code:
Private Function goCreateFilter() As String
' Note: this assumes that the name of the button has a suffix that matches to the product.
' This is a debatable practice (!), so it's included for medicinal purposes only.
Dim ctl As Control, strFilter As String
For Each ctl In Me.Controls
If Left(ctl.Name, 4) = "tgl_" Then
If ctl.Value = True Then
strFilter = strFilter & IIf(Len(strFilter) = 0, "", ", ") & "'" & Mid(ctl.Name, 11) & "'"
End If
End If
Next
If Len(strFilter) > 0 Then
goCreateFilter = "WHERE type In (" & strFilter & ")"
End If
End Function
This way, you can add or remove as many buttons as you want, as long as they 'fit on the rails' like the others. Put LOTS of comments in so that the next person knows how to work with it.
Jack, I am the client, so I'm willing to go with whatever works. The toggle buttons was my first thought that would work since I'm a complete newbie to VBA.
@theDBguy just gave you the answer to this in post #2. did you not want to implement it for some reason?
on the click event of the master toggle button, if true, set the other ones (below them) to be true, as well. as indicated in post 2
...Having said that, IF you're doing this because you already have code in the lower buttons' Click events, and you're hoping to trigger that code , that's probably not a good direction to go, in my experience.
Rather, write a sub like ButtonCheck, which - all in one procedure - evaluates whether either 1) the master is clicked, or 2) the subs are clicked, and then acts accordingly.
IF what I mentioned about you wanting to trigger the lower buttons' Click events is true, then if you keep going down that path, you may find yourself later writing code to actually call those Click events, 'artificially' firing them, which in my experience is a bad road to start upon and indicates improperly modularized code.
Isaac, I'm just starting out. Nothing is done yet with the lower buttons. If there is a better way to do this, I'm all for that. As I mentioned earlier, my programming knowledge is 20+ years old and I feel like I'm reading a foreign language with all the VBA code though I am trying. I guess I thought by clicking the individual buttons or master/group buttons would be the easiest way to set up what I needed with choosing which branding to select.
Isaac, I'm just starting out. Nothing is done yet with the lower buttons. If there is a better way to do this, I'm all for that. As I mentioned earlier, my programming knowledge is 20+ years old and I feel like I'm reading a foreign language with all the VBA code though I am trying. I guess I thought by clicking the individual buttons or master/group buttons would be the easiest way to set up what I needed with choosing which branding to select.
Fair enough. The buttons are very nice looking, so I don't blame you for weighing that pretty heavy in whatever calculation has to be made on how to design it.
Having said that, Pat has made good points, so you might want to go about it that way instead, if you're not married to the toggle button approach. The biggest downside of the toggle buttons IMHO is scaling. If there is any chance that brands will be added and removed with any degree of regularity, you should probably use Pat's approach. But if brands will 'never' change, just about, the buttons look awfully nice
Scalability = Imagine brands automatically filling up a combobox list correctly even as they come and go .... and you being able to give db users the ability to maintain them on their own screen (add, delete, mark inactive, etc) = Scales infinitely better than hard coded buttons.
