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.
Quote:
Originally Posted by 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.
