create report based on value in a field

[bobsyeruncle]

Hi all,

I am pretty new to access but have managed to scramble together a table and form for the information i use.

My table has a field for 'next visit due' where the entries would be 2009/2010 .. 2010/2011 etc.

I can create a form with command button but would like some help with generating a report based on all visits due 2009/2010 etc.

can you help?

 

[mikerea90]

Have you considered making a query? In the criteria for your date field, put "2009/2010"

Or you could put a Textbox in the form and change the query criteria to = [FormName]![Textbox]

Then create a report off of your new query.

 

[bobsyeruncle]

Ok so i have created a query .... and a report based on that query.

When i click on the Report file or the Query file i get the little box asking for the correct parameters ... i then manually type 2009/2010 or 2010/2011 etc and the query or report comes up with the required data.

However, when i select 2009/2010 etc from the dropdown list i have created in the form and click my preview report button the report always comes up blank.

Any ideas folks

 

[Dezirous]

Hi Dear,
Check you data in query that, does it match with your data that you select from dropdown menu. For example, in query a field name "next visit due" and it contains data like "2009-2010" or "2010-2011" and your dropdown menu contains data like "2009/2010" or "2010/2011", and if you click on preview button, this will retrive no data or blank report just because of difference in information you provided i.e. "2009-2010" and "2009/2010". So it must be same in dropdown menu as in query and same format as well.

 

[bobsyeruncle]

Thanks for that .. yeah the data is the same with a / rather than - (that rhymes)

When creating the drop down menu, i manually typed the values i wanted to appear rather than lookup the values in a table ... if i did the latter then it created a huge menu with lots of spaces as not all the records have this field filled in yet.

 

[Dezirous]

Hmm.......OK.....
Just compare your dropdown data to your queries........ if they are same then do one thing......... In criteria field of field'next visit due' from your query, type this;
=[forms]![formname]![comboname]
[forms] refers to forms category (type as it is)
[formname] refers to the name of your form i.e. form1, myform, mainform etc...
[combo] refers to the name of your combo i.e. cboDate, cmboVisitDue etc...
Follow these [], it is necessary then run the query, it will ask the parameter of 'next visit due', enter it manually to check it works or not, Then open your form, select value from combo and click preview, if it doesn't show any thing then check recordsource/datasource of your report and select the query in which you defined criteria....

 

[bobsyeruncle]

Went through all the steps in your post ... everything looks as it should be.

Reports are fine when manually entered into the parameter box but still blank report when using the report button.

 

[Dezirous]

OK......... lets see........
Kindly post you PREVIEW button codes.

 

[bobsyeruncle]

Option Compare Database
Private Sub Prvreportbutton_Click()
On Error GoTo Err_Prvreportbutton_Click
Dim stDocName As String
stDocName = "rpt2009/2010"
DoCmd.OpenReport stDocName, acPreview
Exit_Prvreportbutton_Click:
Exit Sub
Err_Prvreportbutton_Click:
MsgBox Err.Description
Resume Exit_Prvreportbutton_Click

End Sub
Private Sub previewreportbutton_Click()
On Error GoTo Err_previewreportbutton_Click
Dim stDocName As String
stDocName = "rpt2009/2010"
DoCmd.OpenReport stDocName, acPreview
Exit_previewreportbutton_Click:
Exit Sub
Err_previewreportbutton_Click:
MsgBox Err.Description
Resume Exit_previewreportbutton_Click

End Sub

 

[Dezirous]

OK....... Now we are going to solve this proble step by step........ Do these steps to getrid of this situation.............
1. Set the name of your table properly like "tblMain", "tblData" etc, try to avoid spacing and special character between table name.
2. Goto query wizard and generate query of that table and set its name followed by table name rules mentioned above.
3. Open query in design mode and in criteria field of "nextvisitdue" column type this statement;
=[forms]![formname]![comboname]
4. Goto Report wizard and create report using that query in which criteria was set.
5. Now goto Forms section, and create form using wizard and use the table that we have renamed.
6. Insert a combo box using wizard mode and enter data by selecting table/query or by entering manually.......
7. Create a button using wizard and select Report Preview command from available list.
The code of that button would be similay to this code;

Private Sub PREVIEW_Click()
On Error GoTo Err_PREVIEW_Click
Dim stDocName As String
Dim Link As String

stDocName = "REPORTNAME"

DoCmd.OpenReport stDocName, acPreview

Exit_PREVIEW_Click:
Exit Sub
Err_PREVIEW_Click:
MsgBox Err.Description
Resume Exit_PREVIEW_Click

End Sub

8. Now check it by pressing this button. If you have already done some of these steps then just review it.

If any problem or error raise then I am here...

 

[bobsyeruncle]

Once again ... went through your post step by step.

Deleted everything except my main table and other forms.

Created all new query, report and form,

renamed the criteria in the query to that of form name combo name etc.

And still when i type the parameters manuall i get the required info but not when i click the button.

Am goin for Lunch!

Thanks for your help

 

[bobsyeruncle]

Anybody else help with this one? It's driving me nuts!

 

[bobsyeruncle]

Sorted!!

It was the combo box name that was wrong. So the query didn't recognise it.

Although i labeled the combo box as cboVISITDATES the name of the box itself was still 'combobox26'.

renamed and now works fine.

Thanks for all the help

 

[Dezirous]

You are welcome.

 

[Dezirous]

If someone helped you to solve the proble and the problem solved then add his post to his reputation.