From Form>Query>report (1 Viewer)

[phatus]

ive been looking in google for the answer to this question....

here is the situation:

i have a form the source is my maintable... from the main table i created a query for the search button in my form like this...

Search by Municipality: "enter the desired municipality to view"
Search by District: "enter the desired district to view"

Click to Clear click to Search

after clicking the search for example you put 3rd district in Search by District.. the query will gather all the 3rd district "ONLY".. my problem is i cant put it in report... in my report the four district is shown...

any ideas how to do this?

my codes in clear button:

Private Sub Clear_Click()

Me.qmunicipality = ""
Me.qdistrict = ""
Me.Requery

End Sub

and in my search button:

Private Sub Search_Click()

DoCmd.OpenQuery "Masterlist Query", acViewNormal

End Sub

if any one knows if i can add like this code in search button:

Docmd.OpenReport "rptmasterlist", acviewReport

or any suggestion?

ok i just test this code in my searchbutton, i put 3rd district in search by istrict and when i hit the search button the report appear BUT all the district was there... i only search the 3rd district..

here is the code:

Private Sub Command34_Click()

DoCmd.OpenQuery "Masterlist Query", acViewNormal
DoCmd.OpenReport "Masterlist", acViewReport

End Sub

 

[MStef]

Send a short example of your MDB, (access 2000, 2002-2003).

 

[phatus]

attached here is my sample... when you hit search button the query will appear...

what i want is a report will appear not the query depending on what district you search...

hope you get my point sir

 

[MStef]

I said "Access 2000 or 2002-2003".
I can't to read your "Sample.mdb".

 

[phatus]

ok here i save it in access 2000

 

[MStef]

Hello USER!
Here it is, (attachment, zip).
I think it is what you want.
Look at Form1, (VBA), RptSample, QRYsample.
Open Form1 and try.

 

[phatus]

yes thats what im looking thank you very much sir...

 

[phatus]

sir im having a problem in report button... when i click report it shows all the information here is the final code in my form

the RED FONT it means its working FINE...

Option Compare Database

[COLOR=Red]Private Sub Command4_Click()
Me.District = ""
Me.Municipality = ""
Me.District.SetFocus
End Sub

Private Sub Command5_Click()[/COLOR] [COLOR=Red]
DoCmd.OpenQuery "qryreport", acViewNormal
End Sub[/COLOR]

Private Sub Command6_Click()
On Error GoTo Err_Command12_Click
    DoCmd.OpenReport "rptreport", acPreview
Exit_Command12_Click:
    Exit Sub
Err_Command12_Click:
   If Err <> 2501 Then
    MsgBox Err.Description
   Else
    Resume Exit_Command12_Click
   End If
End Sub

[COLOR=Red]Private Sub District_AfterUpdate()
Me.District.SetFocus
Me.District.Dropdown
End Sub

Private Sub District_GotFocus()[/COLOR] [COLOR=Red]
Me.District.Dropdown
End Sub

Private Sub Municipality_GotFocus()[/COLOR] [COLOR=Red]
Me.Municipality.Dropdown
End Sub[/COLOR]

in the qryreport button its working fine.....

i cant figure it out whats the problem in the preview report button...
in the report design view i put this code in the On No Data event

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No Data for this criteria !!"
Cancel = True
End Sub

 

[MStef]

Check RECORD SOURCE properties (in report).
It must be linked on the query, not on the table.

 

[phatus]

yes thanks i changed the link to my query it working fine now...

 

[MStef]

My congratulations.