How do I get specific records only in a report? 2

[SatishPutcha]

Hi All

I haven't dealt with Access Reports before so forgive me if this sounds like a dumb question.

I have a report that has been created from a single-table database(without using wizard). But this report shows all the records.

Instead I want a specific record but not all. How do I do this? Also can I set a range like ID numbers 5 to 11 for printing? The primary key for the table is ID(autonumber).

Please help

REGARDS
struggling_freelancer

 

[LittleSmudge]

What you need to do is use the WHERE clause in the DoCmd.OpenReport method

Either set two controls on the form called lngFirstId and lngLastId and put the limits ( 5 and 11 ) into them
OR
Dim lngFirstID and lngLastId as long integer variables and populate them somehow appropriate to your needs, then :-


DoCmd.OpenReport "rptName", , , "ID Between " & lngFirstId & " AND " & lngLastId


.. .. will open the report as show just the records that match the Where clause criteria.

The Where clause will take and valid SQL WHERE statement.



'ope-that-'elps.

G LS

 

[DavidOlly]

To answer your first question you need to base the report on a query (not on the table) and put the criteria for which records you want to view in the query.

Same would work for the ID numbers. But is it only when you PRINT that you want to limit the ID nbrs or in PREVIEW also?

 

[LittleSmudge]

To answer your first question you need to base the report on a query (not on the table) and put the criteria for which records you want to view in the query.

NO you do not.

There is no good reason at all for inserting a query in the way of proceedings.

In the eternal spirit of conformity and re-usability, the Report itself should be as generic as possible. Using the WHERE caluse in the OpenReport method does all that and more.



G LS

 

[SatishPutcha]

Thanks LittleSmudge and DavidOlly for your advise. I will try out both the methods and see what happens.

DavidOlly: I want to preview and print only select records. But can I do it differently i.e. PREVIEW all records but PRINT just some?

REGARDS
struggling_freelancer

 

[RiverGuy]

You would have to use VBA for that. Two buttons, a Print one, Preview one.

DoCmd.OpenReport "Report", acViewNormal , , , "Criteria Syntxax" for the print

and a

DoCmd.OpenReport "Report", acViewPreview , , , "Criteria Syntxax" for the preview I believe.

I have never had 100% luck with specifying criteria with the DoCmd command, however.

 

[SatishPutcha]

I am using Access2000 and in the help file I went through the steps of "Create a form to enter report criteria".

I think I did everything as listed in there. But even then I am getting all the records when previewing. What could I be doing wrong?

Is there no other way except VBA?

REGARDS
struggling_freelancer

 

[RiverGuy]

I just looked at that help file. IMO, VBA is actually easier to work with than Macros. Maybe that's just me though.

 

[SatishPutcha]

I went ahead with the macro because it was the first helpful "help" I saw P-)

It is working now. I am able to preview/print only those record(s) which I specify in a form.

Is there an online tutorial that would give me info about the VBA or will the "HELP" file be enough?

Thanks LittleSmudge, DavidOlly and RiverGuy very much.

 

[RiverGuy]

I doubt that "help" will help you very much with VBA. There are some sites to help on VBA. Unfortunately, most that I came accross in the past were for Word/Excel/Outlook, and not much on Access. I don't have them bookmarked right now, but I'm sure a search on Google will bring up some good ones.

 

[LittleSmudge]

If you want help on VBA then you are already on what is "probobly the best site in the world."

Just try the Access VBA specific board forum705 for MACROS - avoid them like the plague.
They are a historic legacy and should NOT be learnt new now.
There is nothing I have come across in 10 years of Access development that cannot not be done quicker & easier - and execute faster in VBA than it can in a Macro.


& by Criteria Syntax - I assume that you mean the "WHERE Clause".
There should not be any element of "Luck" about it RiverGuy.
The actions/effects of the WHERE Clause are quite predictable / logical and can exactly define what you get.
Again the VBA coding board ( link above ) is the best place to seek advice on WHERE Clause problems.


'ope-that-'elps.

G LS