Results 1 to 15 of 15

Putting texbox Datasource string in a query

  1. #1
    Paul H's Avatar
    Paul H is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    497

    Putting texbox Datasource string in a query

    Hello, the following piece of code runs fine when placed in a text box on my report which is fashioned to look like a letter.

    Code:
    ="Enclosed please find the above-referenced " & [Appl_Fname] & " " & [Appl_Lname] & "'s fingerprint card and 
    attached Virginia criminal record that has been researched by the FBI 
    and the Virginia State Police in pursuant to Virginia Code §37.2-416  (a), (b), and/or ©.  A
    s a result of this information and an additional review done by the
     DBHDS’ Background Investigations Unit (BIU) the above-referenced " & 
    [Appl_Fname] & " " & [Appl_Lname] & " ELIGIBLE for employment in a direct consumer care position with your organization."
    and comes out looking something like this.
    Click image for larger version. 

Name:	Letter Text 1a.JPG 
Views:	26 
Size:	37.1 KB 
ID:	37291

    Since I need four different versions of this same text depending on the type of letter we are sending out
    I have pasted this string into a table and use that field as the DataSource for that text box.


    Problem is, that doesn't work. It simply shows the full string without populating the parameters.

    Click image for larger version. 

Name:	Letter Text 1b.JPG 
Views:	26 
Size:	43.1 KB 
ID:	37292

    Before I scratch around too hard for another solution I thought I'd put it here and get your suggestions and/or questions.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,842
    That is exactly what you should expect. There are no parameters, just a bunch of characters in a field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,973
    My first take; don't store the beginning equal sign in the table.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,103
    simplest fix would be to do it in code so that the field references will work. I don't think Eval (or any other trick) will work because what you have in your table will be treated as literal text, including those references. Alternatively, splitting the table field into 3 or more parts: 1st part of text (before names) > 2nd part of text (between names) > 3rd part of text after names. Then concatenate via query that refers to the fields containing the names or a function. I'd just go the code route as it will evaluate the field references if they're on a form (or Dlookups).
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    (humor about talking to yourself apparently was too much for some)

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,102
    Here are 2 routines for consideration. The idea is to have a function that has your "boiler plate text"
    and within that text 2 unique strings to act as place holders for your FName and LName.
    I have simulated the text which would have to include line breaks (vbCrLf) for proper formatting, and the
    test routine has 4 samples of First name and Last name.
    I did not try this with a form text box,but the thinking is to call a function with the FName and LName and put the resultant text into the text box. Again not tested at form level, but it may give you some ideas. Some adjustment of line sizes may be needed as you test the routines.

    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: testStr
    ' Purpose: To manipulate a standard text string to incorporate first and last names based on another source (table/query)
    ' Procedure Kind: Function
    ' Procedure Access: Public
    ' Parameter FName (String): FirstName
    ' Parameter LName (String): LastName
    ' Return Type: String
    ' Author: Jack
    ' Date: 05-Feb-19
    '
    'using a standard text layout with 2 unique strings ("XK?A" and "XK?Z")
    'representing place holders for FName and LName and substituting these
    'with "real values" input as parameters to this function.
    ' ----------------------------------------------------------------
    Function testStr(FName As String, LName As String) As String
    10  On Error GoTo testStr_Error
        Dim str As String, working As String
    20  str = "Enclosed please find the above-referenced XK?A  XK?Z's fingerprint card and " & vbCrLf _
            & "attached Virginia criminal record that has been researched by the FBI " & vbCrLf _
            & "and the Virginia State Police in pursuant to Virginia Code §37.2-416  (a), (b), and/or ©.  " & vbCrLf _
            & "As a result of this information and an additional review done by the " & vbCrLf _
            & "DBHDS’ Background Investigations Unit (BIU) the above-referenced  XK?A  XK?Z " & vbCrLf _
            & "ELIGIBLE for employment in a direct consumer care position with your organization."
    30  working = str
    40  working = Replace(working, "XK?A", FName)
    50  working = Replace(working, "XK?Z", LName)
    60  testStr = working
    
    70  On Error GoTo 0
    80  Exit Function
    
    testStr_Error:
    
    90  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testStr, line " & Erl & "."
    
    End Function
    The test routine

    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: testit
    ' Purpose: Test routine to exercise the TestStr function.
    ' Procedure Kind: Sub
    ' Procedure Access: Public
    ' Author: Jack
    ' Date: 05-Feb-19
    ' ----------------------------------------------------------------
    Sub testit()
    
    10  On Error GoTo testit_Error
        Dim i As Integer
        Dim x(3, 1) As String
    20  x(0, 0) = "John"
    30  x(0, 1) = "Broskovich"
    40  x(1, 0) = "   Mary Anne"
    50  x(1, 1) = "Seashell-Smith   "
    60  x(2, 0) = "Antonio"
    70  x(2, 1) = "Ethan o'lamine"
    80  x(3, 0) = "Sue"
    90  x(3, 1) = "Hislegzoff"
    
    100 For i = 0 To 3
    
    110     Debug.Print testStr(Trim(x(i, 0)), Trim(x(i, 1)))
    120     Debug.Print vbCrLf & "        *****************  " & vbCrLf
    130 Next i
    
    140 On Error GoTo 0
    150 Exit Sub
    
    testit_Error:
    
    160 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testit, line " & Erl & "."
    
    End Sub
    The output

    Code:
    Enclosed please find the above-referenced John  Broskovich's fingerprint card and 
    attached Virginia criminal record that has been researched by the FBI 
    and the Virginia State Police in pursuant to Virginia Code §37.2-416  (a), (b), and/or ©.  
    As a result of this information and an additional review done by the 
    DBHDS’ Background Investigations Unit (BIU) the above-referenced  John  Broskovich 
    ELIGIBLE for employment in a direct consumer care position with your organization.
    
            *****************  
    
    Enclosed please find the above-referenced Mary Anne  Seashell-Smith's fingerprint card and 
    attached Virginia criminal record that has been researched by the FBI 
    and the Virginia State Police in pursuant to Virginia Code §37.2-416  (a), (b), and/or ©.  
    As a result of this information and an additional review done by the 
    DBHDS’ Background Investigations Unit (BIU) the above-referenced  Mary Anne  Seashell-Smith 
    ELIGIBLE for employment in a direct consumer care position with your organization.
    
            *****************  
    
    Enclosed please find the above-referenced Antonio  Ethan o'lamine's fingerprint card and 
    attached Virginia criminal record that has been researched by the FBI 
    and the Virginia State Police in pursuant to Virginia Code §37.2-416  (a), (b), and/or ©.  
    As a result of this information and an additional review done by the 
    DBHDS’ Background Investigations Unit (BIU) the above-referenced  Antonio  Ethan o'lamine 
    ELIGIBLE for employment in a direct consumer care position with your organization.
    
            *****************  
    
    Enclosed please find the above-referenced Sue  Hislegzoff's fingerprint card and 
    attached Virginia criminal record that has been researched by the FBI 
    and the Virginia State Police in pursuant to Virginia Code §37.2-416  (a), (b), and/or ©.  
    As a result of this information and an additional review done by the 
    DBHDS’ Background Investigations Unit (BIU) the above-referenced  Sue  Hislegzoff 
    ELIGIBLE for employment in a direct consumer care position with your organization.
    
            *****************
    Hope it's helpful.

    Good luck with your project.

    Update: I just noticed that I have 2 spaces between XK?A and XK?Z ---that should be reduced to 1 space.

  6. #6
    Paul H's Avatar
    Paul H is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    497
    OK, I have read all your responses and appreciate them. I formatted my strings here for presentation. There were no carriage returns as this was meant to wrap inside the text box. The string in the table is identical to the one in the text box. The string contained in the query field LetterText1 and LetterText is used as the Datasource for text box txtLetterText1. I can see using the query is just putting a literal string in the text box. I take it from your responses there is no way to make this string behave like the one that is embedded in txtLetterText1, even though letter for letter they are the same. Frustrating, but I guess not surprising.

    Eventually I will have code that emails these letters one at a time using a For Each, Next loop or a Do While. Anyway I'm thinking I will have to open up a recordset and use it to build a string that I will then insert into my text box. I'll have to work on it tomorrow and try not to dream off it tonight. I'll see what the morning brings.

  7. #7
    Gicu's Avatar
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    850
    Hello everyone,

    I have developed in the past a module doing the exact "pseudo-merge" Paul describes here. I have stored in a table the "boilerplate" text and used db fields delimited by pipe characters to mimic mail-merge fields. Then in the report itself I used a custom function in the control source for the textboxes that replaces the "placeholders" with the corresponding values from the supplied source.
    Click image for larger version. 

Name:	pseudo-merge.png 
Views:	20 
Size:	177.9 KB 
ID:	37297
    Code:
    Public Function vcProcessText(ByVal OAString As String, strTable As String) As String
    Dim a() As String
    Dim v As Variant, vFieldValue As Variant
    Dim sFieldName As String
    
    
    On Error Resume Next
    a() = Split(OAString, "|")
    vcProcessText = ""
    For Each v In a
        vFieldValue = ""
        sFieldName = Mid$(v, InStr(v, "|") + 1)
        
        If sFieldName = "" Then
            vFieldValue = ""
        Else
            vFieldValue = DLookup(sFieldName, strTable)
        End If
        vcProcessText = vcProcessText & IIf(vFieldValue = "", sFieldName, vFieldValue)
    Next v
    
    
    
    
    End Function
    Click image for larger version. 

Name:	pseudo-merge2.png 
Views:	20 
Size:	94.7 KB 
ID:	37298

    If interested let me know and I'll try to package all into a stand alone "utility" file on my website.

    Cheers,
    Vlad

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,102
    Paul,

    I have attached a sample database that uses a form and selects records from a table. The text in question populates a textbox on the form and substitutes "person names" in that boiler plate text.
    The population and length checking, trimming etc is done on the form's current event.

    Code:
    Private Sub Form_Current()
        Dim sFName As String
        Dim SLName As String
    10  sFName = Split(Me.ContactName, " ")(0)
    20  SLName = Split(Me.ContactName, " ")(1)
    
    30  If cntspaces(Me.ContactName) = 2 Then
    40      SLName = SLName & " " & Split(Me.ContactName, " ")(2)
    50  End If
    60  Me.Text13 = testStr(sFName, SLName)
    
    End Sub
    You can test/adjust the text box length and width to suit your purpose.

    Here's a sample of output (I have adjusted the text --is Eligible)
    Click image for larger version. 

Name:	PaulBoilerExample.PNG 
Views:	19 
Size:	34.1 KB 
ID:	37305

    Hope it's helpful.

    Good luck.
    Attached Files Attached Files
    Last edited by orange; 02-06-2019 at 01:30 PM.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,103
    Your report is probably based on a query field from what I read. Your table could have placeholder text instead of 'name' fields (anything not likely to be contained in normal use) such as [FN] for first name and [LN] for last name; e.g. ...above-referenced [FN] [LN] 's fingerprint...
    A calculated field in the query you already have should do the trick, like
    NewField: Replace(Replace([field1],"[FN]","ALFRED"),"[LN]","SMITH")

    then you reference that field in the report control. Note that I removed your concatenation, including spaces as I don't think they'd be needed anymore.
    Last edited by Micron; 02-06-2019 at 11:12 AM. Reason: correction
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    (humor about talking to yourself apparently was too much for some)

  10. #10
    Paul H's Avatar
    Paul H is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    497
    Well, I am trying to create a recordset that will allow me to build my string for the text box EXCEPT, I can't seem to open a recordset using the query name as the source. I keep getting parameter errors.
    This is the whole process, but I can't run it until I can open my recordset.
    Code:
                            strText1 = "Enclosed please find the above-referenced "
                            strText2 = "'s fingerprint card and attached Virginia criminal record " & _
                                       "that has been researched by the FBI and the Virginia State Police " & _
                                       "in pursuant to Virginia Code §37.2-416  (a), (b), and/or ©.  " & _
                                       "As a result of this information and an additional review done by the " & _
                                       "DBHDS’ Background Investigations Unit (BIU) the above-referenced "
                                       
                            strText3 = " ELIGIBLE for employment in a direct consumer care position with your organization."
                                                  
                            Set rst = CurrentDb.OpenRecordset(strReportQuery)
                            With rst
                            Do While Not .EOF
                                .MoveFirst
                                strAppl_Fname = !Appl_Fname
                                strAppl_Lname = !Appl_Lname
                                strAppl_name = strAppl_Fname & " " & strAppl_Lname
                                strLetterText1 = strText1 & strAppl_name & strText2 & strAppl_name & strText3
                                Debug.Print strLetterText1
                                .MoveNext
                            Loop
                            End With
    Here is the error message.
    Click image for larger version. 

Name:	too few parameters.JPG 
Views:	16 
Size:	13.9 KB 
ID:	37311

    I've declared all six parameters including the one in qryReport_ID in FROM clause. Here's the SQL.
    The query runs fine on its own, even without declaring the parameters
    Code:
    PARAMETERS [Forms]![frmReports]![cboOrganization] Text ( 255 ), [Forms]![frmReports]![txtFromDate] DateTime, [Forms]![frmReports]![txtToDate] DateTime, [Forms]![frmReports]![txtSSN] Text ( 255 ), [Forms]![frmReports]![cboTransType] Short;
    
    SELECT Organization.Sort, Applicant.OrgNum, Applicant.ContactID, Contact.Sal, Contact.ContactFname1, Contact.ContactLname1, Organization.Organization_Name, 
    Contact.Address1, Contact.Address2, Contact.City, Contact.State, Contact.PostalCode, Applicant.Appl_Eligibility, Applicant.NoVSPRecord, Applicant.BarrierCrime, 
    Applicant.DtRecvdFromFBI, Applicant.SSN, Applicant.Appl_Fname, Applicant.Appl_MI, Applicant.Appl_Lname, qryReport_ID.Report_Title, Applicant.TransType, tblFees.Description AS ApplicantType
    FROM qryReport_ID, ((Organization INNER JOIN Applicant ON Organization.OrgNum = Applicant.OrgNum) INNER JOIN Contact ON (Applicant.ContactID = Contact.ContactID) 
    AND (Applicant.OrgNum = Contact.OrgNum)) INNER JOIN tblFees ON Applicant.TransType = tblFees.ID
    WHERE (((Applicant.OrgNum) Like [Forms]![frmReports]![cboOrganization]) 
    AND ((Applicant.Appl_Eligibility)=False) 
    AND ((Applicant.NoVSPRecord)=True) 
    AND ((Applicant.BarrierCrime)=True) 
    AND ((Applicant.DtRecvdFromFBI) Between [Forms]![frmReports]![txtFromDate] And [Forms]![frmReports]![txtToDate]) 
    AND ((Applicant.SSN) Like [Forms]![frmReports]![txtSSN]) 
    AND ((Applicant.TransType)=[Forms]![frmReports]![cboTransType]))
    ORDER BY Organization.Sort, Applicant.ContactID, Applicant.Appl_Lname;
    Please help me get over this hump. I'll check back in the morning.

    Thanks,

    Paul

  11. #11
    Gicu's Avatar
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    850
    Dim prm as parameter, qdf as dao.querydef
    Set qdf= currentdb.querydefs("yourquery")
    For each prm in qdf.parameters
    Prm.value = eval(prm.name)
    Next prm
    Set rst=qdf.openrecordset

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,102

  13. #13
    Paul H's Avatar
    Paul H is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    497
    Orange, I did look at it, but wasn't sure how to adapt it. My first desire was to solve the Parameters issue.
    Using Gicu's solution got me around that leaving me this bit of unfinished code.
    This did work! I have a few other things to add to this, but this was a huge first step. Thanks everybody for your detailed replies.

    I'll be back with more soon.

    Code:
                            Dim prm As Parameter ', qdf As dao.QueryDef
                                
                                Set qdf = CurrentDb.QueryDefs(strReportQuery)
                                    For Each prm In qdf.Parameters
                                        prm.Value = Eval(prm.Name)
                                    Next prm
                                    
                                    
                     
                                
                            strText1 = "Enclosed please find the above-referenced "
                            
                            strText2 = "'s fingerprint card and attached Virginia criminal record " & _
                                       "that has been researched by the FBI and the Virginia State Police " & _
                                       "in pursuant to Virginia Code §37.2-416  (a), (b), and/or ©.  " & _
                                       "As a result of this information and an additional review done by the " & _
                                       "DBHDS’ Background Investigations Unit (BIU) the above-referenced "
                                     
                            strText3 = " ELIGIBLE for employment in a direct consumer care position with your organization."
                            
                            Set qdf = CurrentDb.QueryDefs(strReportQuery)
                            Set rst = qdf.OpenRecordset(dbOpenDynaset)
    
                            With rst
                            Do While Not .EOF
                                .MoveFirst
                                strAppl_Fname = !Appl_Fname
                                strAppl_Lname = !Appl_Lname
                                strAppl_name = strAppl_Fname & " " & strAppl_Lname
                                strLetterText1 = strText1 & strAppl_name & strText2 & strAppl_name & strText3
                                .MoveNext
                            
                            
                            DoCmd.OpenReport "rpt_Report_Letters", acViewDesign, , , acHidden
                            Reports!rpt_Report_Letters.RecordSource = strReportQuery
                            Reports!rpt_Report_Letters.lblReportName.Caption = Replace(strTitle, "_", " ")
                            Reports!rpt_Report_Letters.lbl_Letter_Text_1.Caption = strLetterText1
                            DoCmd.Close acReport, "rpt_Report_Letters", acSaveYes
                            Loop
                            End With

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,102
    Glad you have it resolved.

    My approach was based on these facts/options.

    You have a table of people (FName/LName) that you want to insert into a text (some paragraph(s)) at specific locations on a form(or report)
    You could read the "boiler plate" text from a file.
    Created a function and put the boiler plate text into a string variable that included placeholders for the FName and LNames. The function has 2 inputs (FName and LName) and returned a string. The returned string was the full text - boiler plate with FName and LName. A complementary function determined if the supplied Name from the tble of People to be processed contained one space (eg Mary Smith) 2 spaces (eg Claude del Gato) which would be passed to the first function.

    Anyway, glad you have it resolved.

  15. #15
    Paul H's Avatar
    Paul H is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    497
    Thanks again Orange. I'm always looking for alternatives.

    Paul

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

Similar Threads

  1. Execute SP from Access as datasource
    By jaryszek in forum Access
    Replies: 4
    Last Post: 01-18-2018, 03:32 AM
  2. QUERY in forms datasource wont work ??
    By ksor in forum Queries
    Replies: 3
    Last Post: 02-11-2017, 04:54 PM
  3. Replies: 10
    Last Post: 02-01-2014, 12:23 PM
  4. Help with putting QUERY in a Range
    By taimysho0 in forum Programming
    Replies: 3
    Last Post: 02-15-2012, 06:29 PM
  5. Using an sql string as the datasource for a textbox.
    By rghollenbeck in forum Queries
    Replies: 5
    Last Post: 10-08-2011, 07:09 PM

Posting Permissions

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