Re: Change Control Source

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


<tr_international@xxxxxxxxxxx> wrote in message
news:1154229723.455425.106890@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

tina wrote:
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


<tr_international@xxxxxxxxxxx> wrote in message
news:1154215984.090610.122200@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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




.

Relevant Pages

  • Re: Help ... Creating an invoice
    ... So what you will want is a query that will return the required information ... for a report to use. ... for just the records for that customer. ... > if I want that order to be placed on the invoice? ...
    (microsoft.public.access.forms)
  • Re: Help ... Creating an invoice
    ... That query should use the customerID value to filter> for just the records for that customer. ... >> if I want that order to be placed on the invoice? ... My original answer was that you would> automatically generate a report including all orders for that customer. ...
    (microsoft.public.access.forms)
  • Re: Report / Sub-Report Problerm
    ... the customer account in the mainform, so equipment that does not match the ... It might be easier to do this with just one report instead of a report and ... Create a query using just tblCustomers and tblProductGenerics. ... this combination of customer and product exists in the tblCustomerEquipment ...
    (microsoft.public.access.reports)
  • Re: move data from list box or combo box to another list
    ... employees is because each employee gets paid commission on the service they ... The customer and dates are ... Lets address your problem with your report which seems to be your main ... Oh yeah and also you should/could have a filter in your query so that your ...
    (microsoft.public.access.forms)
  • Re: move data from list box or combo box to another list
    ... Hey Roger! ... Lets address your problem with your report which seems to be your main ... Oh yeah and also you should/could have a filter in your query so that your ... report will only show the data for one customer and one order. ...
    (microsoft.public.access.forms)