Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenating Field Name with string....

    This one is driving me mad because either it is staring me in the face, or it is more difficult than i think.....

    Access97, SR2. OK, three text boxes on a form, all identical, and all used to indicate the progress of a data import procedure. Let's call them: txtNorthImport, txtSouthImport and txtWestImport.

    Here's my code:
    ------------------------------------------
    Private Sub ImportQueries(strRegion As String)

    docmd.SetWarnings False

    Me.txt & "strRegion" & Import.Visible = True
    Me.txt & "strRegion" & Import.Visible = Now()

    My problem is that i don't seem to be able to get the syntax right. I've tried everything to concatenate the field name with the string variable, but it just won't have it.

    Any ideas greatly appreciated.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Concatenating Field Name with string....

    Hi,
    I think what you need is something like:
    Me!Controls("txt"&strRegion&"Import").Visible = True
    for the first line, but your second line confuses me - you can't set a control's Visible property to a Date/Time value. I presume you want something like:
    Me!Controls("txt"&strRegion&"Import").Text = Now()
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating Field Name with string....

    Hi Roy

    Thank for your advice. Between us, i got it, but it took your post to get my brain to be thinking a little wider!

    Here's what worked:

    Forms!OutofHours!("txtQuery" & strRegion & "Start").Visible = True
    Forms!OutofHours!("txtQuery" & strRegion & "Start").Text = Now()

    For some reason, it didn't like the Me.Controls bit, but the syntax for the field name was spot on. Cheers!!!!!

    PS. Didn't spot the .text error in the second line.....

  4. #4
    New Lounger
    Join Date
    May 2002
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating Field Name with string....

    I think me.controls(".... ") would work OK. The bang ("!") does not work between "me" and "controls".

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenating Field Name with string....

    Bangs are used with controls. The dot operator actually represents a method or property or a collection. You can use the dot ahead of a control name because controls are members of the form's default collection, although I prefer not to do that.

    There are several different methods of referencing a control:

    Me!MyControl
    Me.Controls("MyControl")
    Me("MyControl")

    ...along with the ever-popular

    [MyControl]

    All of them will work, but some are faster than others.

    Controls is (are?) the default collection of the form and report objects, so it isn't really necessary to specify the Controls collection except when you must have it to handle a string value passed as the control name. For example, you would actually need to reference the Controls collection if you use did something like this:

    Dim frm As Form

    Set frm = Me
    With frm
    .Controls("MyControl" & intIndex)=AnyValue
    Charlotte

Posting Permissions

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