well, i didn't mean to base the report on a recordset, but rather to open a
recordset in code for the purposes of building the string.
base your report on a query that pulls the appropriate customers, but only
lists each customer once in the output (a Totals query may give you the
desired result), and does not list the back-ordered parts at all in the
output.
write another query that pulls all the back-ordered parts, and includes the
customer ID (whatever field holds the primary key value for each customer)
in each record. call it qryBackOrderedParts. that query will be the basis
for the recordset.
in the "form letter" report, add an unbound textbox control to the Detail
section, and set its' Visible property to No. call it txtParts. in the form
letter text, refer to txtParts instead of a part id field, as
="This is to inform you that " & [txtParts] & " back ordered......"
add the following code to the detail section's Print event procedure, as
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strParts As String
Dim i As Integer
strSQL = "SELECT PartFieldName FROM " _
& "qryBackOrderedParts WHERE CustomerID = " _
& Me!CustomerID & " ORDER BY PartFieldName"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rst.BOF And rst.EOF Then
rst.Close
Set rst = Nothing
Exit Sub
End If
rst.MoveFirst
Do
If Not (strParts = "") Then strParts = strParts & ", "
strParts = strParts & rst("PartFieldName")
i = i + 1
rst.MoveNext
Loop Until rst.EOF
If i = 1 Then
strParts = strParts & " is"
Else
strParts = strParts & " are"
End If
Me!txtParts = strParts
rst.Close
Set rst = Nothing
End Sub
in the above code, replace "PartFieldName" with the correct name of the part
field in the query, and replace CustomerID with the correct name of the
field that holds the primary key value identifying each customer.
hth
Thanks for the quick response. I've seen code for using a querey as the
reports recordset, no problem there and I also found a few Loops but
I'm still kind of a newb so if it's not too much to ask please forward
a link that closley describes your suggestion "dynamically building a
string" in a little more detail or just post some "pseudo-code" and
I'll figure it out.
Not lazy just confused.
Thanks again Tina.
Victor
well, you could try the following: open a recordset on a query that pulls
the parts for the specific customer, and dynamically build a string listing
all of the parts, by looping through the recordset. then set the value of an
unbound textbox control to the string, and refer to that control in the
letter text, rather than referring to [Part ID].
hth
Hi All, I have a report that is basically a form letter that contains
[Fileds] between lines of text.
ex. This is to inform you that [Part ID] is back ordered......
My issue is that I want to just generate (1) letter per back ordered
part even if there are many parts per customer. I know that I could
change the letter and show all parts at the bottom of the page in a
"grid" format but my employer has the final say and wants what they
want.
Is there a way to change the control source of an unbound textbox so
that parts 1 through ~n all can be printed individually?
Thanks in advance.
Victor
