Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013

    Unanswered: Display concatenated text when form load

    Hi all,
    Beginner in Access VBA. Strugging how to display a concatenated text when the form is open, and cannot figure out why my code is not working?
    Here is my code:

    Private Sub form_current()

    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim TblName As String
    Dim sqlStr As String
    Dim sqlStr1 As String

    Set dbs = CurrentDb()
    sqlStr = "Select * from SubjTbl where SubjID=" & Me.SubjID
    Set rst = dbs.OpenRecordset(sqlStr)

    sqlStr1 = "Select HIPAAConsentStatText from VL_HIPAAConsentStat where HIPAAConsentStat=" & rst!HIPAAStat
    MsgBox sqlStr1
    Set rst2 = dbs.OpenRecordset(sqlStr1)
    Forms.mainform.HIPAAText = "Status:" & rst2.[HIPAAConsentStatText] & " On " & rst.[HIPAAStatDate] & vbCrLf _
    & "Signed On " & rst.[HIPAASignedDate] & vbCrLf & _
    "Initially Rcvd on " & rst.[HIPAADateRcvdInitial]

    Set rst = Nothing
    Set rst2 = Nothing

    Set dbs = Nothing

    End Sub

    Note: HIPAAText is a textbox on main form. I want to display the concatenated string in that textbox when the form is openned.

    thank you! Any help or hints will be appreciated

  2. #2
    Join Date
    Jan 2009
    Kerala, India
    sqlStr = "Select * from SubjTbl where SubjID=" & Me.SubjID
    If your mainform doesn't have any Record Source then the Text Control SubjID will be empty when you open the form and the expression Me.SubjID will return null value to the SQL Where condition.

    Try it out, after setting a constant value into the SujID Textbox on the form by inserting the expression =4 in the Control Source property, save the Form and open it in normal view.

    You may try running the code by a Command Button click Event, after setting a value into the textbox manually. (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Apr 2004
    metro Detroit
    I think vbCrLf is the problem. If you want to have multiple lines in the textbox you can set its text format to Rich Text and use html tags.

  4. #4
    Join Date
    Jan 2013
    Thank you for both you to answer my questions, I really appreciate your help. As Apr said, the problem is due to the difference between compile time and runtime, -- the choose of dot and bang. The dot has early binding and is resolved at compile time, the bang is resolved at runtime. When form just load, the rst2.[HIPAAConsentStatText] has no value yet, that causes the problem. I am so glad to have all of you to discuss with and make progress in VBA!
    Thank you.

Posting Permissions

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