Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 May 12th, 2004, 08:21 PM
Authorized User
Join Date: Apr 2004
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default creating reports thru a button click on a form

Hi All,

I am trying to generate a report from the click of a button on a form and I am stuck. Here is what I have:

Function GetZipCodes(RepName As String)

    Dim db As Database
    Dim wrkJet As Workspace
    Dim SQL As String
    Dim qdfTemp As QueryDef
    Dim rstZipCodes As Recordset
    Dim X As Variant
    Dim Rpt As Report

    Set wrkJet = CreateWorkspace("NewJetWorkspace", _
           "admin", "", dbUseJet)
    Set db = wrkJet.OpenDatabase("Youth Conference.mdb")
    Set Rpt = CreateReport

    SQL = "SELECT zip_code FROM ZipCodes where rep_name = '" & RepName & "'"
    Set qdfTemp = db.CreateQueryDef("", SQL)

    Set rstZipCodes = qdfTemp.OpenRecordset()
    With rstZipCodes
      Do While Not .EOF
        X = !zip_code
        MsgBox X
        Rpt.RecordSource = X
    End With

End Function

Instead of X being printed in a message box, I want X (and the corresponding rep_name) to be printed on a form----how do i go about doing this? (rep_name is duplicated in the table and a rep_name can be associated with many zip_codes. I am allowing the user to select the rep_name from a drop down list. When this is selectd, I want to find all zip_codes the rep_name is associated with. I only want the rep_name selected to appear on the report once, followed by all the zip_codes under him. Can anyone help me with this?


Old May 13th, 2004, 10:52 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

Your code is strange to me. You seem to be GETTING data FROM a report in your SELECT statement. You PUT data INTO a report, you don't get it OUT of one. To open a report based on data in your form, you simply use

DoCmd.OpenReport "Report Name Here", acViewPreview, , "Criteria statement here"

assuming the report's recordsource is the same as the report's.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Old May 13th, 2004, 08:01 PM
Authorized User
Join Date: Apr 2004
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts

Hi Greg,

rep_name is not report name, it is a table in my database (short for representative name) so I think that is why you were confused? I am using the select statement to get info from a table. I tried the DoCmd.OpenReprot statement but that didnt work--I open the report, but only the headings show up on the report---with no data. any other suggestions?

Thanks again!

Old May 13th, 2004, 09:34 PM
Authorized User
Join Date: Apr 2004
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts

never mindd---it is workign now!

Similar Threads
Thread Thread Starter Forum Replies Last Post
on the click of a button how to load another form? amit_mande@yahoo.com VB.NET 2 September 12th, 2006 06:53 AM
Button Click ~Bean~ ASP.NET 1.0 and 1.1 Basics 2 September 27th, 2005 09:32 AM
Print on Button Click mahulda ASP.NET 1.0 and 1.1 Basics 2 August 5th, 2004 02:50 PM
Creating Reports from a Query by Form jBranch Access VBA 0 March 3rd, 2004 09:04 AM

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