I only need One Record

[aronw]

Is the a simple way of generating a report that just displays the record i'm viewing in my form rather than all records?

TIA

Aron

 

[Darrylles]

Hi aron,

When you run the report (from your from), your report 'Record Source' property should be a query selecting a fields in the table but where the table primary key is = to the key on the calling form.

e.g:

"Select * from mytable where pk = forms!callingform.pk"

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Darrylles]

Hi again,

Sorry, use this.....

"Select * from mytable where pk = " & forms!callingform.pk

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[aronw]

Thanks,

I have been using wizards to create forms and reports. If this is a big problem / limitation please let me know.

So I create a query which includes the PK? (which happens to be a unique ID that access generated - there is no other unique data available unfortunately. All tables have this 'auto-number' type ID Prim. key field and that is the only way of relating the tables I can see)

I'm not sure how to create this query you are talking about. Help!

TIA

Aron (Novice)

 

[JustMaybe]

Hi, don't know if i can help...but here's a more novice view..

Create a query by selecting all the fields you want to appear on your report. (these fields will also be displaying your data on your form)

then simply go to reports
- 'create new'.....
- use the wizard...selecting all the fields and then simply choosing your intiial display (can play with that later!)

WHen you make this query you need to include the Primary Key of the table you have selected these fields from. This doesn't have to be actually displayed on the form itself.

If you create a button (using a wizard) on your form choose the function 'preview report'

By pressing this button..the record that you have displayed on your form should 'in theory' be displayed on your report

This worked for me..but no scenario is the same... any little niggles and let me know.

Hope this makes a bit of sense!
Sarah

 

[JustMaybe]

oh yer...

TO make a query

go to queries and 'new'

It is simpler to go to design view.
Select the table(s) you need to get the relevant fields from .....then simply choose your fields by selecting them in the boxes below

 

[aronw]

little niggle JustMaybe,

I created a query as you suggested, PK from main table and then added relevant fields from other table. Now I have a query. This query still displays all the records tho. at this point.

I go to report wizard and base it on this new query I have created. The report then also displays all the records.

Do you know of any report property settings or something that will link the report to the current form record?

TIA

Aron

 

[Maquis]

Hi Aron,

Here's a different approach to use. Create your report like normal and have it include all records.

Then from your form, just open the report with a filter. Create a button on the form which your user can click to open the report. In the button click event just put this code to open the report with just the current record showing.

Docmd.openreport "reportname",,,"pk = " & me!formfieldPK

Just replace the fields with your fieldnames. Maq
<insert witty signature here>

 

[aronw]

Thanks Maq,

Docmd.openreport &quot;reportname&quot;,,,&quot;pk = &quot; & me!formfieldPK

Turns into:

Docmd.openreport &quot;AppointmentReport&quot;,,,&quot;ID = &quot; & me!Company NameID

does it?

My PK ID is not on the form, does this matter? The problem is that ID is an auto number type that Access generated due to the fact I have no unique data. Is this an issue?

TIA

Aron (Novice)

 

[Maquis]

The primary key needs to be on your form for this to work. You can place it in a hidden textbox somewhere on the form.

The me![Company NameId] refers to a field on your form. (The brackets are required if there is a space in your control name).

For example, if you have a textbox on the form called CompanyId which has Company NameId as its control source then the proper syntax would be:

Docmd.openreport &quot;AppointmentReport&quot;,,,&quot;ID = &quot; & me!CompanyId Maq
<insert witty signature here>

 

[aronw]

When I insert the line:

DoCmd.OpenReport &quot;AppointmentReport&quot;, , , &quot;ID = &quot; & Me![Company Name]

into the click event of my button and run it, I get a runtime error.

It says that I have a syntax error missing operator in query expression '(ID = 123Cornish.com Plc)'

Doh, please help

TIA
Aron

 

[Maquis]

I assumed from your earlier posts that your primary key was a number. If it's a text field then you need quotes around the field. Is Company Name really your primary key?????

If so then this should work...

DoCmd.OpenReport &quot;AppointmentReport&quot;, , , &quot;ID = '&quot; & Me![Company Name] & &quot;'&quot;


If not then change the me![Company Name] to reference your primary key field.
Maq
<insert witty signature here>

 

[aronw]

Yee Haa, Thanks

Aron