I'm attempting to construct my first database - I need to set the captions on a series of command buttons to text contained in one field of a table.
I know how to set the caption on form load - just how to get the text from the table.
Apologies, I'm sure this is a real easy thing to do, but I'm missing the point.
Essentially - what do I put after the = sign
For i = 1 To DCount("*", "tblEmployees")
Me("cmdName" & i).Caption =
If you are new to programming as well as Databases, then what you are proposing is vey ambitious for two reasons.
1) You will need to learn about Recordsets (can’t think of another way to do what you propose).
2) As you don’t know how many employees there are you will have to add controls programmatically??
That said, this code will do what you want (except for adding the controls). This code uses a DAO recordsets (I prefer ADO but am told DAO is better in Access!).
Dim rs As DAO.Recordset
Dim i As Integer
Set rs = CurrentDb.OpenRecordset("SELECT YourFieldName FROM tblEmployees Order By YourFieldName")
For i = 1 To rs.RecordCount
Me("cmdName" & i).Caption = rs("YourFieldName")
Set rs = Nothing
I would suggest an alternative method of using a ListBox OR ComboBox with a RowSource based on tblEmpoyee. You could then select the required employee from the list before clicking the (single) command button.
Would that do what you want, or and backing up the wrong tree!!??
Thanks for your response Mike. I initially had errors but a little more research showed me how to activate DAO.
To your suggestion that I use a list or combo box and then only one commend button - that will not work for me as the cmd button is also a heading of a column of other data that will be presented and I need to have one for each employee.
Regarding your concern about not knowing how many we have, right now we have 9 so I had planned to get around that by making 20 buttons, turning visibility to false in design veiw and then it's easy to make visible as many as I need. It's cumbersome but it works until we employ our 21st person. I do like the idea of just making as many buttons as needed on form load, much more elegant so thanks for that one.
Can you give me a push in the right direction to do this - what is the command to create a control?