Variable name not accepted in VBA in MS Access

Question

I have a SQL VBA query for updating values in several textboxes on a form on the click of a button.

The query takes the input from the multiple label name captions on the form. So caption of Label1 will be input for TextBox1, Label2 caption for Textbox2 etc.

I am trying to pass the label name through a variable to the query. However the following error is returned on the line where value of variable b is generated:

"Microsoft Access can't find the field '&labelname&'referred to in your expression.

My code is below. I want to use a variable so that later I can make it a function to accept the label name and return the recordset value. In this way I will be able to avoid approx. 150 lines of code as I have to update 20 to 25 textboxes with the input from same number of labels.

Private Sub Command111_Click()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim ssql As String
Dim labelname As String
Dim b As String
Set dbs = CurrentDb

'---------------------------------------------------------------------------    ----
labelname = "Label24"
b = [Forms]![Bal_Sheet]![& labelname &].Caption
ssql = "select sum(a.[Bal Fwd]) from Trial_Balance a,Act_Master b where a.GBOBJ = b.object and a.GBSUB = b.sub and b.Cat = " & "'" & b & "'"
Debug.Print ssql
Set rs = dbs.OpenRecordset(ssql, dbOpenDynaset)
[Forms]![Bal_Sheet]![Text1].Value = rs(0)
'-------------------------------------------------------------------------------
rs.Close
Set rs = Nothing
dbs.Close
End Sub

Lee Mac · Accepted Answer · 2018-03-04 16:18:34Z

2

Your expression:

b = [Forms]![Bal_Sheet]![& labelname &].Caption

Is not concatenating a string, as [Forms] and [Bal_Sheet] refer to objects.

Instead, you should use:

b = Forms("Bal_Sheet").Controls(labelname).Caption

answered Mar 4, 2018 at 16:18

Lee Mac

16.1k6 gold badges38 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Variable name not accepted in VBA in MS Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related