Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    10

    Unanswered: Creating a data string with VBA

    [Warning: Newbie here!]

    I am building a report based on a query. The query contains a number of fields that I would like to concatenate into one string using VBA. For example:

    length of service = years (calculated value)
    achieved level 1 = checkbox value
    achieved level 2 = checkbox value

    etc.

    The string would end up looking something like this:

    "Joe Doe, 10.1 years, Level One and Level Two"

    A textbox in the details sections would display the result.

    I can develop the VBA code to create the string, but being a newbie, I am at a loss where to put the code and then how to refer to it in the textbox.

    I am sure there is an easy and logical answer. Thanks for anyone's help.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    If it's a report, just have put a textbox on the report and then have the control source be like:
    =[fieldname1] & "," & [fieldname2] ....
    Me.Geek = True

  3. #3
    Join Date
    Aug 2009
    Posts
    10
    Quote Originally Posted by nckdryr
    If it's a report, just have put a textbox on the report and then have the control source be like:
    =[fieldname1] & "," & [fieldname2] ....
    Thanks, but the VBA code I want to use to create the string is a little involved. It would be too hard to fit it into a text box.

    Bill

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Then you can, on the OnFormat property (I think is the name) do something like:

    me.tboName = strConcatenated

    It's just probably going to be slower than if you could do it by control source.
    Me.Geek = True

  5. #5
    Join Date
    Aug 2009
    Posts
    10
    Thanks Nick.

    I found On Format in the Detail section. I will give it a try and let you know. Appreciate your help!

    Bill

  6. #6
    Join Date
    Aug 2009
    Posts
    10
    Nick - I must bve doing something wrong.

    On the On Format event in the report's detail section, I have placed this code:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    'code to build the member string.
    Dim xfirst As String
    Dim xlast As String
    Dim xmid As String
    Dim xnick As String
    Dim xnamestring As String

    xfirst = [Firstname] & " "
    xlast = [Lastname]
    xmid = [MiddleInit]
    xnick = [Nickname]
    If xnick = xfirst Then
    xnick = ""
    Else
    xnick = "(" & xnick & ") "
    End If

    xnamestring = xfirst + xnick & xlast
    Me.Buildname = xnamestring

    End Sub

    The textbox control on the report is named Buildname.

    When I run the report, nothing appears in the textbox. If I have as the control source for the Buildname textbox, there is still nothing showing on the report.

    The code showing is only a small portion of what the code will eventually be as I attempt build a complicated string.

    Bill

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Have you tried debugging to see what's going on? I suspect your variables aren't getting assigned properly. I think (off the top of my head at least) what you'd have to do is have these fields tied to controls on the report detail section, even if you choose to make them invisible, they need to hold the data. Then you should be able to do like
    Code:
    xfirst = me.Firstname
    xlast = me.Lastname
    xmid = me.MiddleInit
    xnick = me.Nickname
    
    xnamestring = xfirst & " " & iif(xnick = xfirst,"", "(" & xnick & ") ") & xlast
    Me.Buildname = xnamestring
    Give it a shot, see what happens. Cheers.
    Me.Geek = True

  8. #8
    Join Date
    Aug 2009
    Posts
    10
    Nick - I think the problem is that the Sub does not pass any information back to the report. Even if I assign the xnamestring = "Bill" nothing shows up in the report.

    Bill

  9. #9
    Join Date
    Aug 2009
    Posts
    10
    Nick - I found the problem. The On Format event does not fire in Report View. When I do a print Preview, the code is working.

    I found that to see the code work in Report View, I need to use the On Paint Event.

    Thanks for your help.

    Bill

Posting Permissions

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