Button Captions and details from table

[naym]

Hi There

I am wandering if anyone has a solution to this problem i has basically have 2 tables 1 called departments, with the fields: Dep No, Dep Name, then i have another table called Items, with the fields: Item No. Item Name, Item Price, Department.

What i want to do is create say 10 command buttons and create an array of them i know sort of how to do it in visual basic and all the 10 command buttons name is the same and an aray is created but i dont know how to do it in access i know that it doesnt let you create an array but i have seen it done somehow in a project that i came across.

so basically what i would want to have done is on the form load the 10 department buttons captions are loaded from the table departments and when a department button is clicked its is linked to items where i ahve created say 20 command buttons called items.

I dont know it it makes sense what i have written but any help on this matter would be very apreciated it pr if anyone knows of any examples as i have been stuck on it a while

Many Thanks

 

[pbaldy]

It would be more common to use 2 combo boxes (and more efficient space-wise on the form). When you choose a department in the first, the second is loaded with the appropriate items. The technique is called "cascading" combo boxes, if you want to search on that. To try and create buttons dynamically would be difficult to maintain, given the changing quantity of them.

 

[naym]

Hi
Thanks Pbaldy for your reply and yes that would be a good idea but unfortunatly not in my case is i am using a touch screen pc and its basically used for retail and the buttons will be departments and the other buttons will be the items so the user just clicks the neccessary department selects the item and then the item is added to the subform i can do this manually with coding each button but i need it the pick the data from the neccessary tables and fill in the command buttons captions.

Many thanks

 

[naym]

Come on people surely somebody must know to get the captions of command buttons on a form loaded from a table??

 

[boblarson]

This has not been tested, but essentially (with some minor tweaking) should get you closer to your objective:

Dim rst As ADODB.Recordset
Dim intButtonNum As Integer

Set rst = New ADODB.Recordset

rst.Open "YourTableNameHere", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
intButtonNum = 1
Do Until rst.EOF
    Me.Controls("ButtonName" & CStr(intButtonNum)).Caption = rst("YourFieldNameHere")
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing

 

[pbaldy]

And of course you'll need to have as many buttons on the form as the maximum possible number of items, show/hide them as appropriate, and come up with a way of coding the buttons so that clicking on a particular button adds the appropriate item. That will be the tricky part, since it changes. You'll either need to dynamically change the actual code behind each button, or come up with system where button 7 adds the 7th item in the item list.

 

[naym]

Hi
Thanks Boblarson the code you provided kept on looping thtough and only showing the last depname but witha few changes and a for loop its done the trick now so thanks alot, now the only thing is to click on the department button and load the products in that particular item, if anyone has any ideas on that it would be very kind.