Button Caption help

[Jim Brooks]

Hi to all I require help for what maybe a simple problem. I have a form
frmPettyCash and on the form are 3 buttons Cmd1, Cmd2 and Cmd3 which open the
spending reports for 3 employees. There is a table tblEmployees with fields
EmployeeID and EmployeeName with the 3 employee names. What I would like to
do is link the EmployeeNames to the button caption ie Employee 1 to
Cmd1.Caption, Employee2 to Cmd2.Caption etc. so that in the future if I
change an employee name in the employee table the caption on the
corresponding button will change also. Any help greatly appreciated.
Thanks in advance
Jim Brooks

 

[BruceM via AccessMonster.com]

Why not just use a combo box or list box? Instead of the command button
Click code you could run the code in the combo box After Update event.

What you describe can probably be done, but the details are unclear. What do
you mean about changing an employee name in the employee table? Would you
replace one name with another?

For an Employee table you would do well to have something like this:

tblEmployee
EmployeeID (primary key, or PK)
FirstName
LastName
etc.

EmployeeID would probably be a Number field (table design view), or maybe
autonumber, but in any case an unchanging value. If the Employee last name
changes some day you can just change the LastName value. All instances of
storing the Employee information involve storing the number only, so old
records will reflect the new name. It's sort of like SSN at the IRS, which
is the same no matter your name, where you live, etc.

In general you will need a way to share EmployeeID between the command button
and the label, maybe by inserting EmployeeID into the Tag property of the
command button, but if you're going to do that you may as well just change
the Employee name in the label caption. Again, I expect it can be done, but
it could well be more work than it's worth to you.

 

[KARL DEWEY]

Why not use text box instead of label and use DLookup as source?

 

[John Spencer]

In the load event of the form you could set the caption on each button based
on looking up the relevant value.

If you have three employee records with these two fields you should be able to
do it.
Employee Table
EmployeeName
ButtonNumber (1,2,3) for the three buttons

In the Form's load event you would need three statements

Me.Cmd1.Caption= DLookup("EmployeeName","EmployeeTable","ButtonNumber=1")
Me.Cmd2.Caption= DLookup("EmployeeName","EmployeeTable","ButtonNumber=2")
Me.Cmd3.Caption= DLookup("EmployeeName","EmployeeTable","ButtonNumber=3")

Although I think this would not be the best way to handle this situation, it
can be done. I would rather have a control (a combobox or a listbox) that
listed the employees and then have code in the after update event of the
control or the click event of a button that does whatever the buttons are
doing now based on a value in the combobox/listbox.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jim Brooks]

Thanks to all for the advice I removed the buttons and replaced them with
list boxes and used the onclick event to load my reports and it works fine.
Still learning.
Thanks again
regards Jim Brooks

 

[BruceM via AccessMonster.com]

You will find your approach much easier to maintain as the employees change
over time. The Click event in a text box fires as soon as you click the box.
In a combo box the Click event seems to function more like an After Update
(in Access 2003, anyhow), but even so I would use the After Update event, as
it will fire only when a selection is made. I suspect the Click event could
produce unexpected behavior in some instances.

Thanks to all for the advice I removed the buttons and replaced them with
list boxes and used the onclick event to load my reports and it works fine.
Still learning.
Thanks again
regards Jim Brooks

In the load event of the form you could set the caption on each button based
on looking up the relevant value.

[quoted text clipped - 25 lines]

.