form option button and date fields to pass parameter (1 Viewer)

[810311]

Good day guys,

I have a basic knowledge of MS Access. I am stuck with the form issue.

I have a form with option group (two option buttons) and date fields (to select a date range). The form should pull/pass parameters from the query. There's a form button that generates a report based on the query.

Issue: I can't figure it out how to link option buttons and date range to the query so when the button is clicked it generates the report with chosen criteria. The form is for the user to enter parameters.

Appreciate any advice.

 

[namliam]

See if this can help you
http://www.access-programmers.co.uk/forums/showthread.php?p=1387091#post1387091

 

[810311]

Thanks for the thread, namliam. I think I am doing something wrong coz after I fill out the form (select the option and date range) and press Open Report button, the query pop up still asking for dates (this is after I submitted dates through the form)

Here's the code from query Design View

Field: item_type

Table: chargebacks

Criteria: IIf([Forms]![search_by_date_range]![type_retrieval]=1,True,IIf([Forms]![search_by_date_range]![type_chargeback]=2,False,Null))

Field: item_date

Table: chargebacks

Between [Forms]![search_by_date_range]![start_date] And [Forms]![search_by_date_range]![end_date]

 

[themurph2000]

It's hard to tell with what you've written because the code we'd need to see is tied to the button that opens the report. The code Namliam gave you in the other link should be tied to that and you just have to fill in the names with your query and text box names. Can you post the OnClick procedure that is executed for the button that opens the report?

 

[810311]

Thanks for the quick response,themurph2000. I just checked: the button opens the report using Open Report macro which is embedded in the OnClick Event

 

[themurph2000]

Run the query by itself with dates filled in on the form. If it still asks for parameters, the error is there. If the query runs correctly, then the values in the parameter are not getting passed through to the report.

 

[810311]

I just did. Now the query returned empty. Not sure why...

 

[themurph2000]

I just did. Now the query returned empty. Not sure why...

That is a bit odd. I just made a mock-up of a form with two text boxes and a button that previews a report, and it worked perfectly well. Perhaps there's something here that will help.

 

[810311]

Thanks. I downloaded it and opened. Yes, it's working in your DB file.

I did a little experiment: I removed criteria from field: item_type (which should be linked to option buttons) and left only date fields with the the criteria that follows syntax in your query field: Date of Registration

Here's my criteria in the field: item_date

Between [Forms]![search_by_date_range]![start_date] And [Forms]![search_by_date_range]![end_date]

I saved my query, opened the form and filled out the date range (skipping option buttons, however, I forgot to mention: I am using option group: where option button 1 gets selected by default if the other one is not clicked)

After doing all this, I clicked the button on the form, and it gives me a pop up "Enter Parameter Value "(prompting me to enter the dates again after I did it through the form...)

 

[themurph2000]

Are you able to post your db like I did in my example? There has to be a small error someplace that's stopping the report from reading the dates off of the form.

 

[810311]

please, find file attached.

 

[themurph2000]

It looks like you named your query the same as the report. More than likely, Access was confused by the two. I recommend always naming queries with the prefix qry, forms with the prefix frm, and reports with the prefix rpt. For tables, place the name in all caps so the names of tables stick out from the rest of your database objects.

 

[810311]

Yes, it's working in your example. I used the same code in my query but it's giving me again same "Enter Parameter Value" prompt for date range....
Also, I changed the names of objects - they are all different names.

query: search_by_date_range
form: search_by_type_and_date_range
report: disputes_by_type_and_date_range

I've attached the full version of DB. If you can take a look, I would appreciate that.

What I am trying to achieve is
1. Open the form "search_by_type_and_date_range"
2. Choose option "retrieval" or "chargeback" and date range (if you can give me the tip on how to code this part...)
3. Click the form button and open the report based on that criteria

 

[themurph2000]

The parameters it is asking for aren't from the form. They come from the text boxes in the header of your report. In those boxes, you entered the phrase "=[enter the start date: mm/dd/yyyy]" and "=[enter the end date: mm/dd/yyyy]"

Change those to match the criteria from the query behind the report, and it should work fine. Also, you didn't have the option group for retrieval/chargeback tied in to the query behind the report. That has to be on the same criteria line as the date range.

I don't know if I fixed everything on here, but I got the two big ones.

 

[810311]

Thanks. Especially for

IIf([Forms]![search_by_type_and_date_range]![Frame12]=1,"retrieval","chargeback")

coz I did not know how to tie that part....

There's couple of things that I would like to ask:

1. After using the form that you fixed for me,search_by_type_and_date_range, and generating the report, when I switch to Print View it's showing "#Name? #Name?" instead of dates in the header section of the report. Also, when I switch back to Report View, it's showing same "#Name? #Name?" in the header..I did not try to print the report yet...

2. I want to hide all the DB objects and have one form with subforms and buttons for a user that opens when the user clicks on DB file. Can you refer me to any good source on the web on how to set it up?

 

[themurph2000]

1. After using the form that you fixed for me,search_by_type_and_date_range, and generating the report, when I switch to Print View it's showing "#Name? #Name?" instead of dates in the header section of the report. Also, when I switch back to Report View, it's showing same "#Name? #Name?" in the header..I did not try to print the report yet...

That depends. Is the form still open with dates in it when you change to Report View? If not, it may not be able to reference the names of the text boxes that contain the dates.

2. I want to hide all the DB objects and have one form with subforms and buttons for a user that opens when the user clicks on DB file. Can you refer me to any good source on the web on how to set it up?

You can create a blank form that contains buttons to open whatever form or table you need. Then, under the Options for Current Database, it'll give you the option of the object to be opened when the database is.

 

[810311]

That depends. Is the form still open with dates in it when you change to Report View? If not, it may not be able to reference the names of the text boxes that contain the dates.

Yes, you are right - when the form is open, all dates showing correctly in both views: Report View and Print View. It's not a big deal, but ideally I would prefer the form automatically close after the report opens and still show correct date range in the header. Again,if it's not possible, that's fine.

You can create a blank form that contains buttons to open whatever form or table you need. Then, under the Options for Current Database, it'll give you the option of the object to be opened when the database is.

Thanks for that. I will try to follow this route.

 

[themurph2000]

Typically you need that form open so the report can refer to the correct text boxes when it is generated. If you close the form, you lose the dates in it and the report doesn't know what to refer to. Unless you want to have Access open an invisible form from which it can read text boxes, but that seems like a waste of time.

But if someone out there knows better, please feel free to correct me.

 

[810311]

Typically you need that form open so the report can refer to the correct text boxes when it is generated. If you close the form, you lose the dates in it and the report doesn't know what to refer to. Unless you want to have Access open an invisible form from which it can read text boxes, but that seems like a waste of time.

But if someone out there knows better, please feel free to correct me.

No, that's fine. Thanks a lot for all your help.

 

[pbaldy]

I un-deleted this thread. We leave answered threads as an archive that others may find while searching and perhaps find an answer for their own problem.