Need Help with this Form Situation

[brian0721]

Okay, I have a table that stores information on closed Mortgage Loans. Important fields are: Loan Officer and Closed Date. I want to create a form that will let the user pick either a time period with a loan officer, or with all. Also, want them to be able to pick a specific month and loan officer, and specific quarter and total
year. I then want it to open a report that has all the information from the table on it!

Anyone willing to help me out with this? Would be greatly appreciated.

Thanks alot!!!

 

[WayneRyan]

Brian,

The best way to make this happen is to make a nice
dialog box form. You can activate it from the Report
command button on your main form with:

DoCmd.OpenForm "YourDialog"

(1) This new form is a Dialog Box. It has three fields
on it: StartDate, EndDate and Officer. The first
two are date/time fields that define the time window
for the report. The last field specifies which
loan officer (or all) will be on the report.

(2) You can use your new form's OnOpen event to set the
default values for your datefields:

Me.StartDate = DMin("[DateField]", "YourTable")
Me.EndDate = DMax("[DateField]", "YourTable")

Essentially, they'll retrieve all records.

(3) Your Officer is a combo box, have the wizard make it
for you based on the loan officers. Make its default
value "*". This is again all records.

(4) Base your report on a query. For the date field, put
in the criteria section:

Between Forms![YourDialog]![StartDate] And Forms![YourDialog]![EndDate]

Under the officer, in the criteria section put:

Like "*" & Forms![YourDialog]![Officer] & "*"

(5) On the Dialog box, put a button that says "Report".
In it's OnClick event put:

DoCmd.OpenReport "YourReport"

That should get you started. Let me know how you progress.

Wayne

 

[brian0721]

ok...

ok, thanks for the response, heres where i am at. i got the dates working right, however the officer part still doesnt work. i made the combo box and based if off the main table and i put the criteria in the query, but it does not bring up any records when i do this. it works fine with just the date part, but not the loan officer part. any suggestions? also, how can i limit each loan officers name to only appearing one time in the combo box?

thanks alot!

 

[WayneRyan]

Brian,

You can limit the officers by:

Select Distinct(Officer) ...

If you have your form open and your dates and
officer filled in, then just open the query
and run it (with the "!" on the toolbar). If
it doesn't return anything, remove/modify the
criteria until it does.

Wayne