var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Display concatenated text when form load
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
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
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
sqlStr = "Select * from SubjTbl where SubjID=" &
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
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.
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!