subform/query???

[twychopen22]

I am making a form that will be used to enter info, then spit out an employee's pay for the time period. On the form are some goals or qualifications (I am assuming I will have to make queries for those but thats another story) that, if reached, bonus' will be added to final pay. The form will look up records for a specific date or time period for an individual and determine the amount of the bonus. I was wondering how to get a form to show the records in a subform like view. In other words, I want the user to be able to select say, phone calls made on the 10th of last month, then on the right hand side that record would show up just so he could see. (this is more beneficial when there is a date range but for ease sake I am going with one date right now) I am thinking it is a subform/query but am not sure and would like some help b4 I start wasting time on the wrong thing.

 

[twychopen22]

Ok, well I know that it is something with a subform/subreport, but how do I get it to show the one I selected. The more I think about it the more I am thinking, create the query then viewthe subreport on the form.??? Any particulars or details would be great tho. I want to be able to show the record by going through a process like this:

Employee>Date/s>Metric to be Measured>Show records

 

[GailFialho]

Don't assign the record source to the subform until a selection has been made then have your code write the query that will be assigned based on the selection criteria.

 

[twychopen22]

Thanks, Sounds good. After the query I just base the subform on the query correct? Or do I make 2 different queries one for the form, one for the subform. What I have is one dropdownbox that fills in 4 other text boxes, then the user selects (from a different table) a Metric (just a name for a "task") then a start date and end date. After all is selected, I want the answer to show up in a subreport. I am pretty sure that I know what to do about the queries but do I need a button after the selections or an afterselection event to show the subreport? Thanks

Now that I have begun to work on the query, I will have a date picker that I will use to fill in the two dates (start/end) but I am not sure how to have the query relate to those. My fields in the query are:
EmployeeName, MetricName, MetricAchieved, Date (this is the date that they did the work). I need two more that will be the values of the date time pickers but don't know how to do that. I tried making parameters (start/end) but that doesn't work on the form. I want it to look at the 2 text boxes, not ask 2 questions.

I also thought of something else I would like to be able to do but don't know if it is possible. I want to (after all the previous has been finished) select a record from the subreport and have it fill in text boxes later on in the form?

 

[GailFialho]

It's probably a good idea to have a button that says "Show Data".
Until this button is clicked, the subform does not have a recordsource (recordSource in properties sheet is blank).
OnClick event should write the SQL statement that will be assigned to the subforms recordSource. Use variables to capture the data from the 4 fields and insert these variables into the SQL statement so it is querying the exact information you want.
Next assign this SQL statement to the subform's rowsource and it will "magically appear".

 

[twychopen22]

Ok, on click how would I run a sql statement? The only options are code, macro, or expression? Just run a DoCmd. RunQuery?

Also, I want to put a date range in there to chose what metrics to look up. How can Ido this in the query without making it parameter? on the form I have 2 datepickers to select the dates?

 

[twychopen22]

I have attatched what the form looks like right now, maybe this will help with ideas and show what i want to accomplish. Please help, this thing is due very quickly Explanation on top right of form

I have the Employee Information (top left of form) based on a query, then I have based my subform (middle left) off of that query with a few other fields from a child table. I want the top query to be the filter for the bottom query (does this make sense?) I want the employee to be selected then the date then press show data and only the selected employee's info during the specified dates appears. How do I do this?