Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 20th, 2006, 01:02 PM
Authorized User
 
Join Date: Mar 2006
Location: Phoenix, AZ, USA.
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access Report Filter

Hey Everyone,

I'm in the middle of tying to decypher an existing Access Database and of course the guy who designed this cluster F*%$ is no longer around and of course no docs were ever written. At any rate this database has several linked tables/text files that import data and it currently has several reports that display information on the status of products that are either complete, in progress and/or ready to ship. Currently these reports displays all Delivery Routes with no option to filter these reports by Route Nunmber. I need to find the easiest way of adding a combo box and displaying filtered repots by Route Number.

What are my options in doing this?

Thanks,

Robert


 
Old March 20th, 2006, 03:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Similar question got asked last week at:

http://p2p.wrox.com/topic.asp?TOPIC_ID=41104

The code is already written for you in the MS's Reports Sample Database. It loads a pop-up form (with combo boxes) that select criteria, then opens the report.

Bob

 
Old March 20th, 2006, 07:30 PM
Authorized User
 
Join Date: Mar 2006
Location: Phoenix, AZ, USA.
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bob,

Thanks for the info. Although it looks like allot more than I need for what I'm trying to do. Maybe I did not word everything correctly. The existing form basically generates the report from a SQL Statement and of course currently displays all information in that report. What I was hoping to do is simply add a pulldown menu with the selections 1 through 6 corresponding to Route Numbers for our deliveries. That way we can have a more detailed report by delivery route. Is there not a simple way to use a filter or wherecondtion appended to the docmd.open.report function that is currently used to view/print reports?

Thanks,

Robert


 
Old March 20th, 2006, 08:23 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Sure. Place a combobax and a command button on a form and place the following behind the command button:

Code:
Private Sub cmdPrintReport_Click()
    DoCmd.OpenReport "Report1", acViewPreview, , "[Field1] = '" & Me.cboCriteria & "'"
End Sub
[Field1] needs to exist in the query/table the report is using as a records source, and needs to be the bound column of the combo box.

HTH,

Bob

 
Old March 20th, 2006, 08:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

If you criteria value is numeric use:

DoCmd.OpenReport "Report1", acViewPreview, , "[Field1] = " & Me.cboCriteria

Theprevious version should be used for a string criteria.

Bob

 
Old March 21st, 2006, 11:18 AM
Authorized User
 
Join Date: Mar 2006
Location: Phoenix, AZ, USA.
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bob,

Again, thanks for the info. I'm very new to using VB so I ahve allot of catching up to do. Can you suggest some good references to help me out? I'm also working on getting more up to speed on Visual Studio stuff, so I'm sure that all of this will help out allot in many ways. Can you suggest some good references that wuld help me out in regards to the vb coding?

Thanks,

Robert


 
Old March 21st, 2006, 11:57 AM
Authorized User
 
Join Date: Mar 2006
Location: Phoenix, AZ, USA.
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bob,

I used the following code:

Private Sub cmdSewn_Click()

DoCmd.OpenReport "rptSummarySewn", acPreview, , "[STOP] = '" & Me.cmbRouteNum & "'"

End Sub

I get a data mismatch error. It may be having problems because the field "STOP" is in a linked database. The data is being read ok from the pulldwon box ok as i can see it in the debugger. The pulldown data comes from a table that simply contains the values 1 through 6 and is formatted as a number. The stop field as I mentioned comes from a linked database. Any ideas that may help?

Thanks

Robert



 
Old March 21st, 2006, 03:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Robert,

Try:

DoCmd.OpenReport "rptSummarySewn", acPreview, , "[STOP] = " & Me.cmbRouteNum

As far as references go, these are the books I seemed to return to the most when learning VBA/VB6:

Access and VBA:

By Ken Getz et. al.:
VBA Developer's Handbook, 2nd Edition
Access 2000 Developer's Handbook Volume 1: Desktop Edition
Access 2000 Developer's Handbook, Volume 2: Enterprise Edition

In my opinion the books listed above are the indispensible Access/VBA books, whether beginner or expert (though Mr. Getz saw the writting on the wall when .NET came out and writes pretty much exclusively on .NET topics now. Access hasn't added a significant programmability feature since version 2K, so these are still "current".)

Access and VB6

By John Connell (Wrox):
Beginning Visual Basic 6 Database Programming
(Pretty good, but a lot of wizard, widget, and pre-fab data access control coverage, mostly DAO though some ADO. Not great on unbound apps.)

VB6

By Harvey M. Deitel, Paul J. Deitel, Tem R. Nieto
Visual Basic 6 How to Program
(Decent general reference of VB6 language capabilities)

SQL Server and VB6

By Thearon Willis (Wrox):
Beginning SQL Server 2000 for Visual Basic Developers
(A favorite book! Super coverage of basic N-tier development and user-defined data access classes. I've adapted a lot of his techniques for Access/VB6/ADO development too.)

Visual Basic 6.0 Business Objects (Wrox)
by Rockford Lhotka
(Advanced, but really cool. Introduced Rocky's CSLA Framework, which has quite a following now - see http://www.lhotka.net/. He's gone on to write VB.NET and C# versions of the CSLA Framework (for Appress) and is currently working on an edition for .NET 2K5)

ADO

Programming ADO
by David Sceppa
(Really takes you inside ADO, especially some of its "black box" aspects, like the ADO Cursor Engine).

Just keep in mind that these are all COM technologies, and Microsoft has stopped active development on all of them (some of them, like VBA and DAO, years ago). If I was learning Windows app development at this point in time, I'd be concentrating a lot on Visual Studio .Net too.

HTH,

Bob

 
Old March 22nd, 2006, 01:22 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Microsoft Office Access 2003 Bible has been a decent reference for me.

That and these forums.

 
Old March 22nd, 2006, 05:38 PM
Authorized User
 
Join Date: Mar 2006
Location: Phoenix, AZ, USA.
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bob,

Thanks, that worked much better. Most of the commands I can dig up ok, what usually throws me off is the aditional syntax that goes along with it. Btw, now that I seem to be going in the right direction, what is the easiest method to restrict that pulldown box so that no one can type in any information. Currently it is an unbound control getting it's data from a table that I created that simply ahve the values 1 through 6 in each record.

Thanks for all the references. Most of my recent areas have been with Visual Studio, especially with Visual Studio Web Developer. My eventual plan is to get all the access databases moved into a wen environment and into SQL.

Thanks again

Robert


Quote:
quote:Originally posted by Bob Bedell
 Hi Robert,

Try:

DoCmd.OpenReport "rptSummarySewn", acPreview, , "[STOP] = " & Me.cmbRouteNum

As far as references go, these are the books I seemed to return to the most when learning VBA/VB6:

Access and VBA:

By Ken Getz et. al.:
VBA Developer's Handbook, 2nd Edition
Access 2000 Developer's Handbook Volume 1: Desktop Edition
Access 2000 Developer's Handbook, Volume 2: Enterprise Edition

In my opinion the books listed above are the indispensible Access/VBA books, whether beginner or expert (though Mr. Getz saw the writting on the wall when .NET came out and writes pretty much exclusively on .NET topics now. Access hasn't added a significant programmability feature since version 2K, so these are still "current".)

Access and VB6

By John Connell (Wrox):
Beginning Visual Basic 6 Database Programming
(Pretty good, but a lot of wizard, widget, and pre-fab data access control coverage, mostly DAO though some ADO. Not great on unbound apps.)

VB6

By Harvey M. Deitel, Paul J. Deitel, Tem R. Nieto
Visual Basic 6 How to Program
(Decent general reference of VB6 language capabilities)

SQL Server and VB6

By Thearon Willis (Wrox):
Beginning SQL Server 2000 for Visual Basic Developers
(A favorite book! Super coverage of basic N-tier development and user-defined data access classes. I've adapted a lot of his techniques for Access/VB6/ADO development too.)

Visual Basic 6.0 Business Objects (Wrox)
by Rockford Lhotka
(Advanced, but really cool. Introduced Rocky's CSLA Framework, which has quite a following now - see http://www.lhotka.net/. He's gone on to write VB.NET and C# versions of the CSLA Framework (for Appress) and is currently working on an edition for .NET 2K5)

ADO

Programming ADO
by David Sceppa
(Really takes you inside ADO, especially some of its "black box" aspects, like the ADO Cursor Engine).

Just keep in mind that these are all COM technologies, and Microsoft has stopped active development on all of them (some of them, like VBA and DAO, years ago). If I was learning Windows app development at this point in time, I'd be concentrating a lot on Visual Studio .Net too.

HTH,

Bob





Similar Threads
Thread Thread Starter Forum Replies Last Post
Report Builder Filter Error gunjan.sh SQL Server 2005 2 April 28th, 2008 03:38 PM
Filter vs WhereCondition in Report Access powerz Access 4 May 23rd, 2006 02:57 PM
How to filter Crytal report ? abdusalam VB How-To 0 June 24th, 2004 01:44 AM
Filter Report TonyG Access VBA 6 February 11th, 2004 11:56 AM
Filter Report using value Box DBoulos Access VBA 6 December 17th, 2003 08:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.