creating a report thru the click of a button on a form

[mays0515]

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
.MoveNext
Loop
.Close
End With

db.Close
wrkJet.Close
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?


Thanks,
Mays

 

[WayneRyan]

Mays,

Unless I'm reading that wrong, you're making this way
too difficult.

First, make a query that displays your desired info.

Then, use the wizard to make a report based on the
query.

Then all you have to do on your button is:

DoCmd.OpenReport "YourReport",,,"[YourKeyField] = " & Me.YourKeyField

That will print a report for only the record currently
displayed.

Wayne