Labeling command buttons

[SemiAuto40]

I am trying to apply the caption property to a series of command buttons each based upon a field that I get from a recordset generated from a query. So hypothetically my 30+ command buttons would have names like "EFF-01, EFF-02, EFF-03... etc.. sometimes. Button one would have the caption of EFF-01, button two would have the caption of EFF-02, .... etc. The names would change per results from a query. All buttons would then have captions.



The form that I am trying to make is to simulate a touchscreen except for now with a mouse cursor. I am not able to assign a caption to my buttons. I have made my button names like cmd_Product_1, cmd_Product_2, etc...

In trying to reference my command buttons to assign the captions from my recordset info I made a string variable for cmd_Product_ and an integer variable to append to the end of the name. I believe the variable string name for my command buttons is working OK up until it comes time for me to assign the string to the control. I get a runtime error 91 "object variable not set".

Can someone point me to the solution to this please?

[pbaldy]

Hard to know why you get that error without seeing the code, including the line the error points to.

[SemiAuto40]

The code loading the form is as follows. DISCLAIMER: I don't pretend to know what I am doing since I am an amateur. So if the code is Elbonian spaghetti....

Private Sub Form_Load()

Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim ButtonNumber As Integer
Dim ButtonName As Control
Dim ButtonNameString As String

Set db = CurrentDb
'QUERY CODE TO OPEN AND USE CORRECT RECORDS.
strSQL = "SELECT tbl_Sample_ID_Codes.SampleIDCode FROM tbl_Sample_ID_Codes WHERE (((tbl_Sample_ID_Codes.ProductName) = 'EFFLUENT'))ORDER BY tbl_Sample_ID_Codes.SampleIDCode;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'INITIALIZE BUTTON COUNT AT ONE.
ButtonNumber = "1"

Do While Not rs.EOF

ButtonNameString = "cmd_Product_ID_Code_" & ButtonNumber ' & ".Caption"

ButtonName = ButtonNameString ' NOT ABLE TO NAME A CONTROL WITH A VARIABLE THUS FAR RUNTIME ERROR 91.

ButtonName.Caption = SampleIDCode 'set the command button caption to the value of the Sample Id Code.

' cmd_Product_ID_Code_1.Caption = SampleIDCode *** is the actual name of one of the command button controls and this works as it should. ***

ButtonNumber = ButtonNumber + 1
rs.MoveNext
Loop
rs.Close
Set db = Nothing
Set rs = Nothing
End Sub

[orange]

First thoughts - just looking at the code syntax -

= 'EFFLUENT'))ORDER should be = 'EFFLUENT')) ORDER<--- needs a space before Order
ButtonNumber = "1" should be ButtonNumber = 1 <-- no quotes on integer field

[pbaldy]

This will throw an error:

ButtonNumber = "1"

you don't want the quotes. You can refer to the control like:

Me("cmd_Product_ID_Code_" & ButtonNumber).Caption = "Whatever"

And when you're referring to a value from the recordset:

rs!SampleIDCode

[SemiAuto40]

Amazing how a true expert can reduce 10 lines of code down to 2 lines. Many thanks and appreciation to PBaldy.

[pbaldy]

Happy to help!