generate report from form record 1

[aaronjonmartin]

Hi all,

Sorry if this is difficult to understand but here goes. I have a form which displays my clients record by record, what i want from this form is that when i am looking through my clients i may wish to send a standard letter out to a particular client (the data displayed by the form includes address, etc). So what i would like to set up is a button on my form which when pressed brings up a report which has standard text in it (i.e. the actual body of the letter) and the name and address data from the record I was viewing. Then i would simply print and send out my standard letter.

Is this possible? Can anybody give me any information as to how i could set this up?

Thanks for any help in advance.

Aaron

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[MakeItSo]

Hi Aaron,

It's relatively simple:

- Create a new report based on a query that contains all fields you need.
- Use labels to put any standard text you require on the report
- Use bound textboxes wherever data shall be dynamically inserted (i.e. client name, product a.s.o)
- In your form, add a command button and put this in its OnClick() event:

DoCmd.OpenReport "YourReportName",acViewPreview, ,[Customer-ID=" & me.CustomerID


Replace "Customer-ID" with the respective field name in your table and and "Me.CustomerID" with the one in your form

Tell me, if you have any problems.

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[aaronjonmartin]

MakeItSo,

Thankyou for the response. Tried to follow your advice and here is what i got:

Created a query containing my fields (CVID, firstname, surname, address1, address2, etc). Created a report with the report wizard based on this query. This displayed the fields i wanted, i then added the standard text. I then went into my form created the command button and added your code like this:

DoCmd.OpenReport "stand_letter_try",acViewPreview,,[CVID=" & me.CVID

Is the syntax for the above code correct? It didnt like it so i had to add a closing bracket. When i click the button on the form i get the following error Run-time error '2465': Microsoft Access can't find the field '|' referred to in your expression. Any ideas? Thanks again for the help

Aaron

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[MakeItSo]

Yepp, I do have an idea and it was my mistake:

DoCmd.OpenReport "stand_letter_try",acViewPreview,,"[CVID]=" & me!CVID


replace your command line with this, that should solve the error.
;-)
Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[aaronjonmartin]

That worked a treat Andy,

Thanks again for your help.

Aaron

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[ammujr]

HI, I Followed the thread you had written for aaronjonmartin, but when I press the button I get an Error.
My field SO NO is a text variable
Error:
Data Type Miss matched in critertia expression.


this is my code:

Private Sub Command54_Click()
On Error GoTo Err_Command54_Click

Dim stDocName As String
stDocName = "Product Quality Survey"
DoCmd.OpenReport stDocName, acViewPreview, , "[SO NO]=" & Me![SO NO]
Exit_Command54_Click:
Exit Sub

I am not exactly sure what i did wrong.

 

[MakeItSo]

If it's a text variable, you have to enclose the value in single quotes:
DoCmd.OpenReport stDocName, acViewPreview, , "[SO NO]='" & Me![SO NO] & "'"

That should remove the error.

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos