Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Unanswered: Changing the name of a text field within a Do While Loop

    This seems very easy to me, but I can't find the correct way to do this.

    I have 24 text boxes on a form that I want to load data from an employee table. This is what I have so far and it works for the first text fields:

    Private Sub Form_Load()
    Dim icount As Integer
    icount = 0

    Dim rst1 As ADODB.Recordset
    Set rst1 = New ADODB.Recordset
    rst1.ActiveConnection = CurrentProject.Connection

    sqlstmt = "Select * from Employees"
    rst1.Open sqlstmt
    Do While Not rst1.EOF
    txtEmployee0 = rst1.Fields(0).Value
    txtEmpName0 = rst1.Fields(1).Value

    rst1.MoveNext
    icount = icount + 1
    Loop
    rst1.Close

    End Sub


    Each of the text box names end in a number

    txtEmployee0, txtEmployee1, txtEmployee2, all the way to 24.

    I would like to be able to do something like this:

    txtEmployee(icount) = rst1.fields(0).value

    but VB thinks the (icount) is a function. Is there a way to concatenate the textbox name and the value of icount?

    Thanks,

    Sherry

  2. #2
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    you can only change the NAME of a control in design view.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    True indeed. You can only change the name in design view. An alternative may be to stash an "alternate name" in the tag property.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    The tag property is great. I never used it until the past 3 months and now it changed the way I do things.

  5. #5
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    Thanks for the suggestion. I would like to try it but I don't understand how I would use it in the Do While Loop. If I give the first textbox's tag property a value of 0, how do I test it in the Do While loop?

    Thanks,

    Sherry

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    txtEmployee1.tag

    ???


    Also, to concatenate the control name, make use of the forms!form.controls() method:

    me.controls("concatenated control name here")
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...or are you simply trying to loop through the controls on your form and stuff their values into your recordset.

    this is easy in DAO, and i guess something similar exists in ADO. you need the field names the same as the control names for it to work with no effort at all:

    Dim rfld As DAO.Field

    For Each rfld In rst1.Fields
    Me(rfld.Name) = rfld
    Next

    ...and vice versa of course:
    rfld = Me(rfld.Name)


    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    I'm trying to take data from a table and place it into the controls on the form. I have 24 employees and I have 24 textboxes on my form. I want to put the employees into the textboxes without having to write code for each field name.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    exactly what i said a few minutes ago.
    ...now pray that someone knows how to do this in ADO

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    Thanks Teddy! The 'me.controls("concatenated control name here")' worked. My statements looks like this:

    Me.Controls("txtEmployee" + LTrim(Str(icount))) = rst1.Fields(0).Value
    Me.Controls("txtEmpName" + LTrim(Str(icount))) = rst1.Fields(1).Value



    Thanks to everyone else to sent suggestions.


    Sherry

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Sweet, glad I could help!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •