Load command buttons captions from Table

[naym]

Hi there
I am wandering if anyone could help me out on this matter, i ahve 2 tables at the moment, a departments table with the field depart no and name and a products table with prod no, name, price, department name, now say i have 10 departments then i would want to create 10 command buttons on a form and on the form load i would want the button captions of the 10 buttons to be loaded with the departments name. At the moment they all the buttons are coded manually but would like to change this as departments can change and so can products and the reason why i need it to be command buttons insteda of a combo box or listbox etc is that its going to be used on a touch screen pc and also there will be many departments etc.
i know you can do this in vb by using a array of buttons but access doesnt allow that, only thing is that i have seen this been done tho in access before.
Any help would be much appreciated on this matter or if anyone has a better idea to go about this thanks

 

[Remou]

You can programmatically set the caption of a command button at any time. You may wish to use the on load event:

Dim rs As DAO.Recordset
Set rs=CurrentDB.Openrecordset("Departments")

rs.MoveFirst
'You should check the count of departments.

For i=1 to 10
'Command buttons are named cmd1 to cmd10
   Me("cmd" & i).Caption=rs!DeptName
   rs.Movenext
Next

 

[naym]

HI
Thanks for the reply Remou but i have been trying it out somehow it doesnt seem to work, i dont know if i am doing something wrong but it comes up with all sorts of problems, do you have an example i can work by.
thanks

 

[MajP]

I think Remou gave you a working example.

If it does not work what happens?
Does the code stop?
Do you get an error?
Wrong captions?
missing reference?
Post your code and explain what happens.

 

[naym]

Hi MajP

There were a few errors , first of all it didnt reconise the dao.recordset for some reason but i am managed to figure it out and now when i load the form the items from the department load up, but only one prob, in vb you create an index say cmddep, now if you add any code to that any of the array buttons named cmddep will run the same code when clicked but in access you have to name each button such as cmddep0, cmddep1, cmddep2 etc so what happens there can i use again one subroutine or do i have to enter each code into the on click event of each button? and further that, i will have to enter each product code to each button if thats the case but surely there must be a better way like vb as seen as tho access is more or less built up on vb am i right people?

 

[MajP]

The DAO recordset was probably due to no reference to MS Data Access Objects.
Each button calls the same subroutine

Public sub dosomething (varSomevariable as variant)
do something
end sub

Private someButton1 onclick
call doSomething(someParameter)
end sub

Private someButton2 onclick
call doSomething(someParameter2)
end sub

 

[MajP]

if you need to save an additional property such as a product code use the Tag property of the control
For i=1 to 10
'Command buttons are named cmd1 to cmd10
with Me("cmd" & i)
.Caption=rs!DeptName
.Tag = rs!ProductCode
end with
rs.Movenext
Next

then when the button is clicked you can pass the product code if needed

Private someButton2 onclick
call doSomething(someButton2.Tag)
end sub

 

[naym]

Thanks Majp for the reply

Right i have got the department buttons working using the adodb connection which it picks the button captions up from the table departments then is where i have had a problem and i think you have cleared if i click a department button say fish i cant have one routine for all the buttons i have to have a sub routine and then each department button calls that subroutine right?? well by doing that i get it to work which brings me up on me second problem and that is when say you select the fish button the products command buttons get popolated but with all the prooducts not just the ones from the fish section only, i think i need a sql query but am still figuring that out at the moment but then am thinking if that starts working do i have to code each product button witha sub routine so when clicked it poplates say a subform or textbox with the product button caption as the name of the product and the price which i think can be the tag am i right in saying this or would there be a better way as say 200 command buttons or so would be a problem. i have the sample database working which gets the code from teh departments and populates the products etc if you would like that and see what i have done i am more then happy to mail it to you.
thanks any replies on this would be very much appreciated as ave nearly figured it out but not quite there yet.

 

[Remou]

You can use the tag property, for example:

[tt]DoCmd.OpenForm "Products",,,"Product='" & Me.cmd1.Tag & "'"[/tt]

 

[naym]

Yes thanks but that means i have to code every button with the code which is no point need to havea way where onec it is done th user can just add or change departsments as he wishes and i dnt want to open the form products it doesnt need to i can get the products listen from the bttons captions through the vba code. i have it working in visual basic and it works fine but just having a problem to get it working with access because you cant have one code to cover as a whole for all the departments or products buttons it has to be changed each button like cmddep1 cmddep2.. and so on and cmdproduct1, cmdproduct2 etc where as in vb you can have 10 command buttons called cmddep and thats it and only one subroutine for all of them.
anyone with any ideas please

 

[Remou]

You can use active control:

[tt]DoCmd.OpenForm "Products",,,"Product='" & Screen.ActiveControl.Tag & "'"[/tt]

Or Screen.ActiveControl.Caption

You may wish to look at the Customer Phone List form in the Northwind sample database. The example uses an Option Group.

 

[MajP]

I am not confident you really need to do it because we are only talking about 10 buttons which seems to me you could write one subroutine and pass a parameter/s from the click event. However,If you really want one event to react to any click then I would write a class module. Call the class module something like "ProjCmd". In the class use "with events" and define an On_Click event. Make the cmd button a property of the class. You could even then build a "ProjCmds" collection.

With this design on the forms load event you would do something like.

dim prjCmd1 as new ProjCmd
dim prjCmd2 as new ProjCmd
...
prjCmd3 as new ProjCmd

prjCmd1.cmdBtn = me.cmdBtn1
prjCmd1.project = someProjString
prjCmd1.cmdCaption = someCaptionString


This would difinetly be worthwhile if I had maybe 20 buttons all acting to multiple events. I think by the time you build the class and the collection, it would have been a lot easier to compartmentalize the form code. However, this is a great drill for improving your Access VBA coding by building your own classes and class collection.
If you build your own classes in VB this may be basic. If you do not this will take time to learn and understand. If interested Access Desktop Developers Handbook (Litwin and Getz) have great examples. This is the way I code most forms.