CODEWITHSUNDEEP
vbams-access
user6715722
user6715722

Reputation:

adressing object by variable

In my form I have a number of 8 text fields named with consecutive numbers (tbx_MyID1 to tbx_MyID8). I want to loop through all 8 text fields to create new records out of the text fields' values. So I am trying to store the text fields names in a variable and address the text fields by this variable. This only creates an error, saying the object does not exist.

Private Sub btn_Enter_Click()
    Dim db As Database
    Dim rs As Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl")

    Dim x As Integer
    x = 1
    While x < 9
        Dim y As String
        y = "tbx_MyID" & x
        rs.AddNew
        rs.Fields("ID") = Me!y.Value
        x = x+1
     Wend

    rs.Update
    rs.Close
    Set rs = Nothing
    db.Close 

End Sub

Upvotes: 1

Views: 69

Answers (2)

John Mo
John Mo

Reputation: 1326

Replace rs.Fields("ID") = Me!y.Value

with rs.Fields("ID") = Me.Controls("tbx_MyID" & x)

It will work because the default property of the underlying control type (a text box) is the "Text" property.

There are a lot of ways to reference controls dynamically on a form. It might be clearer to declare a variable for the text box, get a reference to it, and then specifically reference the Text property:

Dim ctl As TextBox
Dim x As Integer

x = 1
While x < 9
ctl = Me.Controls("tbx_MyID" & x)
    rs.AddNew
    rs.Fields("ID") = ctl.Text
    x = x+1
Wend

Upvotes: 2

Zack
Zack

Reputation: 2341

As noted previously, you need to access the form's Controls property.

Additionally, you probably want to store your text box prefix and the number of text boxes in constants, to make your code more readable.

Finally, even Microsoft suggests you replace While...Wend loops with the Do...Loop construct. So updated code might look something like this:

Option Explicit

Private Const TEXT_BOX_PREFIX As String = "tbx_MyID"
Private Const TEXT_BOX_COUNT As Integer = 8

Private Sub Command18_Click()
    Dim x As Integer
    Dim txtBox As Access.TextBox

    For x = 1 To TEXT_BOX_COUNT
        Set txtBox = Me.Controls(TEXT_BOX_PREFIX & x)

        Debug.Print txtBox.Value
        ' put your code to add/update records here.
    Next x
End Sub

If you clarify your question to indicate how specifically the text boxes relate to new records, I can update my answer to address that part.

Upvotes: 1

Related Questions