Passing the value of Query Input to Form

[dath]

Hi,

Searched the forum and found a lot on passing form input to aquery but not the other way around. Here is the situation:

I have a timesheet form based on a subform query that asks the user for the Month and their last name. The fields in the table being updated include: FY, Month, Employee ID, Task Number, hours. The query then displays the results in data sheet view on the form. I have been asked by users to display total they input for the month which I finally was able to do. However, one admin person asked that a control total be added for allowable hours per month so the employee could compare what they entered for a month to the control total. So, I created a control total table with 2 fields: Month and Control Total.

On the form I would like it to display the control total for the month the user input in activating the form. While the form displays the month requested, I cannot seem to get the form to display the value/month input by the user.

I tried creating a control in the form and tried to set the control source to the query that the form was based on. However, I had no luck, I either get a blank or name reference error. I also tried a lookup without sucess. Seems that once the query generates the form the month that was prompted for is no longer avialable. I am fairly new to this so patience and any suggestions are appreciated.

[Starasoris]

Hi dath,
I imagine that from your info, you have a criteria in a query called [Month] that will pop up a message for them to enter a month. If I have this right then what you need to do is in the query, create a new column with the field set to SelMonth: [Month] and make sure Month is spelt the same. Tick on the Show check box. your query will now have a field called SelMonth which you can put on a form or use to join in a nother table with the value.

Option 1.
In the controlsource of a text box that you want to show the control total, put =DLookup("controlvalue","controltable","themonth=" & SelMonth)

Option 2
In the record source of the form, use this field to join between your query with SelMonth and the table with the control totals in.

Hope this helps.

Hi,

Searched the forum and found a lot on passing form input to aquery but not the other way around. Here is the situation:

I have a timesheet form based on a subform query that asks the user for the Month and their last name. The fields in the table being updated include: FY, Month, Employee ID, Task Number, hours. The query then displays the results in data sheet view on the form. I have been asked by users to display total they input for the month which I finally was able to do. However, one admin person asked that a control total be added for allowable hours per month so the employee could compare what they entered for a month to the control total. So, I created a control total table with 2 fields: Month and Control Total.

On the form I would like it to display the control total for the month the user input in activating the form. While the form displays the month requested, I cannot seem to get the form to display the value/month input by the user.

I tried creating a control in the form and tried to set the control source to the query that the form was based on. However, I had no luck, I either get a blank or name reference error. I also tried a lookup without sucess. Seems that once the query generates the form the month that was prompted for is no longer avialable. I am fairly new to this so patience and any suggestions are appreciated.

[dath]

Thanks it worked. It was such a simple solution. I guess I was making it more complicated than I needed to.

Dave