Help! Question re: button label on form

[Macro16]

I have a form where the user chooses the name of a country from a drop-down menu, and then all remaining fields on the form populate with values that correspond to that country.

I have a main table that contains all the country names and an ID# that is assigned to each country. The drop-down uses a command to check this table to pull each country’s info.

Also on the form is a button that, when clicked, queries from a different table that contains more information on each country, and displays this information as a list. The button has a label that does not change according to each country; it always says “Click to see list”.

However, there are 3 possible kinds of lists, and I’d like the button’s label to change depending on the kind of list. Instead of always saying “Click to see list”, I want the button to say “Click to see Blue List” or “Click to see Red List”. The main table with the ID#s does contain a field called “Type of List”, so the 3 possible values are listed there.

Is it possible to have the labels change on the one button depending on what country the user selects from the drop-down?



Or, would I need to have a different button for each kind of list, and replicate the VB behind each button?

[ketbdnetbp]

You should be able to set the .Caption property of the button, when needed, and, as a result would only need one button. Once the country is selected, grab the "Type of List" (DLookup() or SQL, etc.) and put it into a variable.

Then, set the caption property of the button

Me!CommandButtonName.Caption = variable

Hope this helps,

Jim

[Macro16]

Thanks Jim,
I am trying to follow what you wrote, but I'm not terribly familiar with DLookups or variables. Where exactly does this info get entered?

And what exactly do I enter in the Caption's property field of my button?

Thanks again!

[ketbdnetbp]

Initially, set the caption property of the button in the OnCurrent Event of the form, use an [Event Procedure]/VBA, like:

dim st1 as string
st1 = "Click to See List"
Me!CommandButtonName.Caption = st1

This behavior will occur each time you navigate to another record.

Then, as you stated:
"I have a form where the user chooses the name of a country from a drop-down menu, and then all remaining fields on the form populate with values that correspond to that country.

I have a main table that contains all the country names and an ID# that is assigned to each country. The drop-down uses a command to check this table to pull each country’s info."

I assume the ID# field is part of the dropdown. If not, let me know.

So, in the AfterUpdate Event of the dropdown, you could use another [Event Procedure]/VBA, like:

dim st2 as string
dim st3 as string
st2 = DLookup("[Type of List]","NameofMainTable","[ID#]=" & [Forms]![FormName]![DropdownBoxName].Value)

st3 = "Click to see " & st2 & " list"
Me!CommandButtonName.Caption = st3


Hope that helps.

Jim

[Macro16]

Hi Jim,
The ID# field is actually not part of the dropdown, but the row source for the dropdown is a query that contains the ID#. Will this work?

[ketbdnetbp]

Add the ID# field to the last column of the dropdown (row source), increase column count property by 1, set the width of the last column to 0".

Then, change this:

st2 = DLookup("[Type of List]","NameofMainTable","[ID#]=" & [Forms]![FormName]![DropdownBoxName].Value)

to:

st2 = DLookup("[Type of List]","NameofMainTable","[ID#]=" & [Forms]![FormName]![DropdownBoxName].Column(Number of the column that contains ID#))

When finished, should look something like:

st2 = DLookup("[Type of List]","NameofMainTable","[ID#]=" & [Forms]![FormName]![DropdownBoxName].Column(4))

Remember, the columns are numbered beginning with 0, so column 1 is actually Column(0). Adjust accordingly.

See if that gets you desirable results.

All the best,

Jim

[Macro16]

Hi Jim,
I think I misspoke earlier - I just checked out the query in the row source again, and the ID# field is there. It's already included in the Column Count, and the width for the last column is already set to 0. Sorry for the confusion - I thought you were asking if the ID# field was displayed in the dropdown.

So now, I am trying to add the OnClick and AfterUpdate Events, but I am a bit unsure how to proceed because in the property of the dropdown there already are some events (I did not initially build this form; I've inherited it from someone else). There isn't an OnClick event currently, but there's a "BeforeUpdate" and an "AfterUpdate" embedded macro already in place.

I'm not sure about adding these additional events since so much else is already going on behind the scenes.

Also, when I click to "Build Event", it takes me to a window of code that already exists. How would I get to a place where I can enter new code from scratch?

[Macro16]

Edited to add to my last comment - The property of the button DOES currently have an OnClick command already - this command opens up another form view (with the actual list)

Can I also add in the command:
dim st1 as string
st1 = "Click to See List"
Me!CommandButtonName.Caption = st1

Is OnCurrent different from OnClick?

[ketbdnetbp]

Yes, on Click is different from OnCurrent. On click is usually associated with a button (Control). The OnCurrent Event is associated with the form. The code I gave you for OnCurrent:

dim st1 as string
st1 = "Click to See List"
Me!CommandButtonName.Caption = st1

will re-set the caption to "Click to See List" as you navigate to each record. So, you'll always start with "Click to See List".

The other code should be in the AfterUpdate Event of the dropdown. Once a selection is made, the button's caption property would be set to include the color designation. If you already have code/macro in that event, you could try putting the code in the BeforeUpdate event before any other code. However, if both the BU and AU events of the dropdown are tied to macros, I wouldn't be able offer much in the way of good advice, unless I could see the db firsthand. Is it possible for you to scrub the db of all sensitive data, compact and repair, then zip, and post.

All the best,

Jim