Help creating a Report-I created an unbound form with a date range & 1 other criteria

[molnerr]

Help creating a Report-I created an unbound form with a date range & 1 other criteria

Hello, I am trying to create a report that will filter through a table and tell me whose applications are going to expire within a 3 months time frame (or quarterly), for different divisions.

I used the help feature in Access and followed the directions for "use a form to enter report criteria." I created an unbound form to accept the division name and a beginning date and an ending date.

I want the report to show the division and all of the companies that are assigned to that division whose applications are going to expire. I also need to have a few other fields on the report from the table.

In design view I created an unbound form and created macros for the form in a macro group. (OK, Cancel, OpenForm, CloseForm).

I used the IsLoaded function and defined the function in the database.

I created a query using the table fields I needed and under the critera for Division I put:
[Forms]![ExperationDialog]![Division]

and under the criteria for the Expiration Date I put:
Between [Forms]![ExperationDialog]![BeginninDate] And [Forms]![Ending Date]

Here is where I need HELP!

I created a report using the report wizard and selected the fields from the query I created. When I run the report the form pops up, you enter the criteria and hit OK, and nothing happens : (. The report does not generate. I think I need to do something else to link the form to the report?

I did not understand the directions for what to do if it is an Access project. I am not sure if I am using an Access project or not? The database information in Visual Basics shows up as if it is project, but that is in VB. I used the VB script to define the function for IsLoaded. How do I tell if it is an Access project .adp?

I don’t know what to do from here. I need help creating a report or linking the report? I have this nice form that pops up when I open the report, but after I enter the criteria the pop up closes and no report is generated.

 

[thisisntwally]

im not totally clear what you have done, but do you have a button on your form which opens your report and passes the values from the form to the report?

 

[molnerr]

Hum, well when I click on the report the form I created pops up. There is not a button on my form though that opens a report. I only have an OK command button.

I think I might need something that passes the values from the form to the report.

The macros for the OK only hides the form.

the Argument Setting for OK are
Item [Visible]
Expression No

I created a query using query wizard. I choose the main table fields I needed and under the criteria for Division I put:
[Forms]![ExperationDialog]![Division]

and under the criteria for the Expiration Date I put:
Between [Forms]![ExperationDialog]![BeginninDate] And [Forms]![Ending Date]

Do I need to link this in a different way?

 

[thisisntwally]

Between [Forms]![ExperationDialog]![BeginninDate] And [Forms]![ExperationDialog]![Ending Date]

 

[thisisntwally]

i just saw in your post that the report is opening before the form. this is bass-ackwards. Once the report is open, it is a dead object, if you want to control what results are displayed you must do this before opening your report.

 

[molnerr]

oh my, that is what I thought I had. Ok I changed it, it says

Between [Forms]![ExperationDialog]![BeginningDate] And [Forms]![ExperationDialog]![EndingDate]

The text box properties names are BeginningDate and EndingDate so that should be right now, but it still doesn't work.

 

[molnerr]

I am getting closer. When I click on the report and enter the criteria and hit ok the form closes and it looks like nothing happened. When I go back and click on the query the results show up in a table view.

How to I get this information in a nice form that has a title, shows each field name as a heading, and shows the results in columns below?

 

[thisisntwally]

well you need to set the reports record source to the query. right click your report, select properties. in the window that opens select report (its probably on details). then set the control source to your query.

Remember. if the report is already open when your query is updated from your form, I do not think it will reflect the new query results. you need to have your report open AFTER the form has updated your query

 

[molnerr]

The record source was already set to the query. I used the query wizard instead of creating it in design view, would that make a difference?

A report is not opening before the unbound form pops up. In the database view when I click on reports the first thing that happens is the unbound form pops up. I can then enter the criteria. After I click OK I want the report to show up but it doesn't. By chance I figured out that if I click on the query after that I find the information, but it is not in report form.

Is the form not supposed to be set up to open first when the report is clicked? I would think it would need to be?

 

[thisisntwally]

heres a little sample. see if its like what you need. be sure you have both dates in there, or it has issues.

 

[molnerr]

I am unfortunately using Access 2000 and it will not recognize the file you sent because you have a newer version of Access.

 

[thisisntwally]

hereyahgo.

put in a date like 1/10/1950 in the first box and 1/10/2012 in the second.
push the button.

i think this is basically what you're looking for.


as you can see, the report is opening from the form, not viceversa. this lets access pass the form values into your query, and from the query to the report.

 

[molnerr]

ahhhh, I tried it three times each time I click on the command button Access closes down and it says it is closing because of an error.

 

[thisisntwally]

hrmm. weird. it still works for me. try months other than january if you want.

no matter really. if you have a working query, and a report all you need to do is put a button on your form that opens a report preview. specifically your report. disable whatever you have that is opening the form when you open the report.

edit (you can do this with the button wizard)

 

[molnerr]

I see what you are saying that the form needs to open then the report, but my report form never opens. How do I set it up so that the form opens and then the report?

How I have it set up is that when I click reports the first that that happens is the form opens up like it is supposed to. The report just never shows up. I don't know how to explain this to you any better. I just don't know what to add to have the report show after I hit OK?

 

[thisisntwally]

you could throw a pared down version of your database up and i'd be glad to take a look. I think most of the people around here are pretty visual (ie bad at communication / worse at listening )

 

[molnerr]

If the form shouldn’t be opened when I click on “Reports” how do I open the Form? I used the IsLoaded function to open the form when I click on Reports. I used the Access help directions for “Use a form to enter report criteria”

What specifically do I need to do to open a report preview from a form?

 

[molnerr]

hum I don't know if I can figure out how to make the database small enough to send you. I will see what I can do. Thanks so much for trying to figure out my problem. If you figure out what I am saying and have a solution please let me know.

 

[molnerr]

ok, I hope you are still there. I deleted almost everything and made up some table info. But the darn file will not attach I keep getting an upload failed error. The file is small enough? Any suggestions?

 

[molnerr]

I just keep getting Upload of file failed. Is there another way to sent this to you? It is 2.73 MB.