Results 1 to 9 of 9
  1. #1
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114

    Append VBA Variable To Query Results In Text Box On Report

    I have a VBA variable that I am setting through code that I want to Append to the beginning of a textbox.



    I also want the text box to hold query fields.

    How can I set the text box to display in this format?
    (first the vba variable - then a line break, then query field and a line break, then query field 2)
    Code:
    VBA Variable
    Query Field 1
    Query Field 2

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    To append text already in a text box you simply set the text equal to itself before adding your new text.
    vbCrLf creates a new line. You can use it like so
    Code:
    Dim my_string As String
    my_string = "VBA Variable"
    Me.textbox_name = Me.textbox_name & vbCrLf & my_string & vbCrLf & "New line of text goes here" & vbCrLf & "And here's another line"

  3. #3
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by kd2017 View Post
    To append text already in a text box you simply set the text equal to itself before adding your new text.
    vbCrLf creates a new line. You can use it like so
    Code:
    Dim my_string As String
    my_string = "VBA Variable"
    Me.textbox_name = Me.textbox_name & vbCrLf & my_string & vbCrLf "New line of text goes here" & vbCrLf & "And here's another line"
    How would I append a VBA variable and query fields? Can I access query fields in VBA and do something like
    Code:
    Dim red As String
    red = "Red"
    Me.textbox_name = red & vbCrLf & " " & QueryName.Field1 & vbCrLf & " " & QueryName.Field2

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Ohh. You want to use a recordset object.

    If you're referring to fields from a form's recordset (or report's) it would look like this:
    Code:
    Public Sub mytest()
    On Error GoTo ErrHandler
        Dim red As String
     
        red = "Red"
        Me.textbox_name = red & vbCrLf & " " & Me!FIELD_1_NAME_HERE & vbCrLf & " " & Me!FIELD_2_NAME_HERE
    
    ExitHandler:
        Exit Sub
        
    ErrHandler:
        MsgBox Err.Description, vbExclamation, "Error " & Err.Number
        Resume ExitHandler
    End Sub
    If you're running a query from code it would look like this:

    Code:
    Public Sub mytest()
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim red As String
        
        Set db = CurrentDb
        Set rs = db.OpenRecordset(QUERY_NAME_OR_SQL_STRING_GOES_HERE)
        
        If Not (rs.BOF And rs.EOF) Then
            'rs isn't empty so the query must have returned records
            red = "Red"
            Me.textbox_name = red & vbCrLf & " " & rs!FIELD_1_NAME_HERE & vbCrLf & " " & rs!FIELD_2_NAME_HERE
    
        Else
            'query didn't return any records.
    
        End If
        
        rs.Close
    
    ExitHandler:
        Set rs = Nothing
        Set db = Nothing
        Exit Sub
        
    ErrHandler:
        MsgBox Err.Description, vbExclamation, "Error " & Err.Number
        Resume ExitHandler
    End Sub

  5. #5
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49
    If I'm understanding you want to have a String value that will be set dynamically and have this string value appear in a control on your report along with the data that would appear in the bound control.

    In order to really answer this, we need to know how is the string value set? Since this is on the report once we know how this variable is set I think we can help better.

  6. #6
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Yes! That looks exactly what I am after!!

    One more question, I am wanting to set the value of a text.box on a report from the button click event of a button on a form.

    Is this possible?

  7. #7
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by dashiellx View Post
    If I'm understanding you want to have a String value that will be set dynamically and have this string value appear in a control on your report along with the data that would appear in the bound control.

    In order to really answer this, we need to know how is the string value set? Since this is on the report once we know how this variable is set I think we can help better.
    This string value is set from a text box on a user form.

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by chalupabatman View Post
    Yes! That looks exactly what I am after!!

    One more question, I am wanting to set the value of a text.box on a report from the button click event of a button on a form.

    Is this possible?
    Why from the form's button click event specifically? Intuitively I'd think it would be more appropriate to set the text box value from an event triggered by the report.

    Assuming the form button launches the report, DoCmd.OpenReport accepts an optional OpenArgs parameter. You can pass your string to OpenArgs, then have your report display it on the text box.
    https://docs.microsoft.com/en-us/off...cmd.openreport

    Or when the report opens it can refer to the form (assuming it's still open) and get its info. The textbox itself can lookup info too without the need for coding.

    If you can be more specific about exactly what you're doing that would be helpful to us.

  9. #9
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by kd2017 View Post
    Why from the form's button click event specifically? Intuitively I'd think it would be more appropriate to set the text box value from an event triggered by the report.

    Assuming the form button launches the report, DoCmd.OpenReport accepts an optional OpenArgs parameter. You can pass your string to OpenArgs, then have your report display it on the text box.
    https://docs.microsoft.com/en-us/off...cmd.openreport

    Or when the report opens it can refer to the form (assuming it's still open) and get its info.
    DoCmd.OpenReport accepts an optional OpenArgs parameter - Seems to be exactly what I needed! Thanks!

    I believe what I can do is to use this to open the report - then in the form_open() event set the values from everything I'm after.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-18-2016, 04:50 PM
  2. Replies: 5
    Last Post: 04-27-2015, 02:40 PM
  3. Replies: 3
    Last Post: 05-02-2013, 10:36 AM
  4. Replies: 3
    Last Post: 03-11-2012, 08:24 PM
  5. passing a variable to an append query
    By Baroj Von Reich in forum Programming
    Replies: 4
    Last Post: 09-02-2011, 08:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums