Report Creation With Query

[simonmarkjones]

Hi all,

I create a report to act as a receipt to customers.
The report displays all the customer payment details and then i print
this.
This works fine. However, i now want to add some more information to
this page. But i cant seem to do it.

The report uses a query to pull in the data from the form.I have a drop
down on my form called costcode1
This shows 3 colmns when you click on it.
1 Colmn shows the cost code, the second colmn shows a description of
what that code relates to.

In my query to create the report i want to be able to pull in that
second colmn and not the costcode which is displayed on the form. As
the code would be meaningless to our customers.

How can i do this? Do i do it in the query?

Many thanks in advance!!!

[Allen Browne]

Yes, create a query that contains both the table your form uses as its
RecordSource, and also the table that the combo uses as its RowSource.

In query design view, you will see a line joining the 2 tables. Double-click
this line. Access pops up a dialog offering 3 choices. Choose the one that
says:
All records from [Your form's table], and any matches from [Your combo's
table.]

This way, all records are included in the report, even if the combo was left
blank. If you want more info on that, see:
The Query Lost My Records
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<si************@gmail.com> wrote in message
news:11********************@g47g2000cwa.googlegrou ps.com...

I create a report to act as a receipt to customers.
The report displays all the customer payment details and then i print
this.
This works fine. However, i now want to add some more information to
this page. But i cant seem to do it.

The report uses a query to pull in the data from the form.I have a drop
down on my form called costcode1
This shows 3 colmns when you click on it.
1 Colmn shows the cost code, the second colmn shows a description of
what that code relates to.

In my query to create the report i want to be able to pull in that
second colmn and not the costcode which is displayed on the form. As
the code would be meaningless to our customers.

How can i do this? Do i do it in the query?

Many thanks in advance!!!

[simonmarkjones]

There are no lines.

My form uses CustomerPaymentsDetails table as record source

And combo box uses table costCodePlanning as record source.

costCodePlanning has 3 colmns - department, CodeDescription, Code.

When a selection is made it is stored in CustomerPaymentsDetails under
colmn called costcode1.

How do i get these lines you speak of?

Cheers

[Allen Browne]

So CustomerPaymentDetails.costcode1 matches costCodePlanning.Code?

In query design, click on costcode1 in the CustomerPaymentDetails, and drag
it onto Code in costCodePlanning. Access will give you the join.

I imagine it would be a good idea to create this relationship between the 2
tables. You can do that in the Relationships window (Relationships on Tools
menu.) Then Access will automatically give you the joins in your queries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<si************@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

There are no lines.

My form uses CustomerPaymentsDetails table as record source

And combo box uses table costCodePlanning as record source.

costCodePlanning has 3 colmns - department, CodeDescription, Code.

When a selection is made it is stored in CustomerPaymentsDetails under
colmn called costcode1.

How do i get these lines you speak of?

Cheers

[simonmarkjones]

Excellent this worked fine!!

Thanks for that.

So now in my query i've selected another field CodeDescription from
costcodePlanningBuildingControl table.

When i go to print the receipt however it prints every description on
20 different pages.

So do i need to put something in the criteria? I tried
[Forms]![SavedRecordsForm]![CostCode1] But this then didnt print
anything in the textboxes.

Sorry im still learning. Hope you can help

Cheers

[Allen Browne]

See:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<si************@gmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Excellent this worked fine!!

Thanks for that.

So now in my query i've selected another field CodeDescription from
costcodePlanningBuildingControl table.

When i go to print the receipt however it prints every description on
20 different pages.

So do i need to put something in the criteria? I tried
[Forms]![SavedRecordsForm]![CostCode1] But this then didnt print
anything in the textboxes.

Sorry im still learning. Hope you can help

Cheers

[simonmarkjones]

Hi thanks for the help

I've put the following code onto my form

Private Sub btnPrintReceipt_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[RECEIPT NUMBER] = """ & Me.[RECEIPT NUMBER] & """"
DoCmd.OpenReport "Customer Receipt2", acViewPreview, , strWhere
End If
End Sub

It've set RECEIPT NUMBER to be the primary key in the
CustomerPaymentDetails table. The form is called Customer Receipt2
And i've done what you said earlier about joining a line between the
tables.

The report opens now but there is nothing in the text boxes. They are
now all empty!

Please help.

Thanks very much for the help so far.
Nearly there

[Allen Browne]

When you open your table in design view, what is the Data Type of your
RECEIPT NUMBER field?

It is is Number (not Text), lose the extra quotes, i.e.:
strWhere = "[RECEIPT NUMBER] = " & Me.[RECEIPT NUMBER]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<si************@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Hi thanks for the help

I've put the following code onto my form

Private Sub btnPrintReceipt_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[RECEIPT NUMBER] = """ & Me.[RECEIPT NUMBER] & """"
DoCmd.OpenReport "Customer Receipt2", acViewPreview, , strWhere
End If
End Sub

It've set RECEIPT NUMBER to be the primary key in the
CustomerPaymentDetails table. The form is called Customer Receipt2
And i've done what you said earlier about joining a line between the
tables.

The report opens now but there is nothing in the text boxes. They are
now all empty!

Please help.

Thanks very much for the help so far.
Nearly there

[simonmarkjones]

Hi
I'm afraid it is text. So i put the extra quotes in.

RECEIPT NUMBER
BCCA - 00001
BCCA - 00002
BCCA - 00003

Thanks for the swift reply!!!!

[Allen Browne]

Just above the DoCmd.OpenReport line, add this line:
Debug.Print strWhere

When the report opens with no records, press Ctrl+G to open the Immediate
Window, and look at the filter string you have applied. Can you see why this
yields no match?

If you mock up a query, and use that expression in the WHERE clause, does
the query return a match?

Your example shows a dash in the value, so you may have struck this bug:
Query Returns no Records with an Indexed Field That Contains Dashes
at:
http://support.microsoft.com/default...b;en-us;271661

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<si************@gmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

Hi
I'm afraid it is text. So i put the extra quotes in.

RECEIPT NUMBER
BCCA - 00001
BCCA - 00002
BCCA - 00003

Thanks for the swift reply!!!!

[simonmarkjones]

Sorry i've just realised what my problem was i had put something in the
criteria for CodeDescription and nothing had that criteria so nothing
was being displayed.
Thankyou sooooo much for your help it all works great now!!!

Cheers Allen!! :)

[simonmarkjones]

Ah i removed what was in the criteria and now its diplaying all the
CodeDecripts on 20 pages!!

:(

Ahhhh

[Lyle Fairfield]

God does this to posters who do not tell us at the beginning that
"i had put something in the criteria for CodeDescription".
On the second occurrence it's Thunderbolts!

[Allen Browne]

Keep working on it.
You will build the desired string, just like the WHERE clause of a query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<si************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Ah i removed what was in the criteria and now its diplaying all the
CodeDecripts on 20 pages!!

:(

Ahhhh

[simonmarkjones]

Sorry Lyle but i did say

So do i need to put something in the criteria? I tried
[Forms]![SavedRecordsForm]![CostCode1] But this then didnt print
anything in the textboxes ..Yesterday.

I just forget where i put things as i'm new to programming and access.
And having a time getting my head round it all.

Do i need something in criteria or not? Not sure now as im soo
oconfused :S

[simonmarkjones]

If i say [Forms]![SavedRecordsForm]![CostCode1_combobox] in the
criteria then do i have to say that its the first colmn of the dropdown
combobox?

Seems logical to me.

I've got this for my button on my form.
Private Sub btnPrintReceipt_Click()

Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[RECEIPT NUMBER] = """ & Me.[RECEIPT NUMBER] & """"
Debug.Print strWhere

DoCmd.OpenReport "Customer Receipt2", acViewPreview, , strWhere
End If

The rowsource for my CostCode1_combobox is

SELECT costcodePlanningBuildingControl.code,
costcodePlanningBuildingControl.CodeDescription,
costcodePlanningBuildingControl.Department FROM
costcodePlanningBuildingControl ORDER BY
costcodePlanningBuildingControl.Department,
costcodePlanningBuildingControl.CodeDescription;

My query i have
Field: CodeDescription
Table: costcodePlanningBuildingControl

And costcodePlanningBuildingControl

has these values...

Building Control Application Fees 7221000 94275B
Building Control Application Fees(RG) 7221000 94275G
Building Control Site Inspection Fees 7221000 94240B
Building Control Seminar Fees 7221000 43239E
Building Control Building Notice Fees 7221000 94279B
Building Control OS fees 7221000 93014F
Building Control Photocopying/Info Fees 7221000 94232B
Building Control Leisure Services 4223060 94998
Planning Conservation Income 7211200 94286
Planning Personal Phone Calls 7211300 41312B
Planning Planning Applications 7211300 94270G
Planning HM Land Registry Funds 7211300 46038G
Planning Minor Amendment Fee(£75) 7211300 94276G
Planning PD Enquiry(£40) 7211300 94277G
Planning Economic Development Business Directory 7244001 94498F
Planning Leisure-Hway Searches 1444000 94412G
Planning Notices & Reps 7211300 94237B
Planning Written Enquiry Fees 7211300 94237G
Planning Misc Income 7211300 99099
Planning Mayoral Book 7211300 94309F

[Allen Browne]

That will refer to whichever is the Bound column of your combo.

I will bow out now, and let you take it from there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<si************@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
If i say [Forms]![SavedRecordsForm]![CostCode1_combobox] in the
criteria then do i have to say that its the first colmn of the dropdown
combobox?

[simonmarkjones]

Thanks guys.

I've sorted it! Finally i think!

I added code and code description to the query

Put [Forms]![SavedRecordsForm]![CostCode1_combobox] as criteria for
code. And then wrote code description on the form but not code.

YAY! I can relax now PHEW!!!!

Thanks again!!!! :D

[simonmarkjones]

Monring all!

Now i need to get the description of each code for 3 dropdown combo
boxes.
I've just realised if i use code description in the query i will only
look for the first code description based upon the code shown in
CostCode1_combobox

I've got two more... CostCode2_combobox CostCode3_combobox and i want
to display the descriptions of these in seperate text boxes.

I've sorted the relationships for all 3 now.

Is this at all possible??

Many thanks :)