Result from a query being passed to a text box on a form (1 Viewer)

[david.paton]

Hi,

You will have to go easy on me and give me step by step instructions, as while I know a fair bit about access, i don't know any SQL.

I am making a database for my work place where there is telephone counsellors and they need to complete a certain number of supervision hours and a few other categories of hours required for training and several other things. The manager wants to be able to see the total supervision hours and the other categories for a worker when the worker is selected and the date range for the queried time entered.

I have a form that has quite a few items on it. I have two text boxes that allow me to enter a start date and an end date, I have a combo box that allows me to select a worker and I have 3 text boxes that I want to populate with the sum of 3 separate columns in another table when the date is entered and the worker selected so the manager can see, at a glance, how many hours each worker has done on the separate items. Maybe I would need an update button at the bottom that, when clicked, would perform the required calculations.
I have 3 queries that return the required information but I need to get one of the columns from the query results put into each of the 3 text boxes.
Basically, when I click on the buttons the right query appears, I just want column 3 (which is a sum column) to get put in the corresponding text box and I want all the boxes to display the sum of their corresponding queries when a date and person is selected.


The form is called frmSearch, the 3 queries are called qry_sumisshours, qry_sumisshours and qry_sumtcshours. The 3 text boxes I need to get populated from the queries are called txt_results_sv_hours, txt_results_TCS_hours and txt_results_iss_hours.

I think that is all the required information. Let me know if I have missed anything.

Thanks,
Dave

 

[JHB]

Use the DLookup function, to get an idea how to use it, look in the MS-Access Help file.

 

[david.paton]

I put this into the control source of the text box I want to display the figure:

=DLookUp("No_hours","qry_Sumsvhours")

No_hours is the field name I want summed and qry_Sumsvhours is the query that sums the field name for the selected person and during the required dates.

When I do this and select form view on the form, the word #Error flashes constantly in the text box. A start and an end date have been entered as well as the counselor.

Not sure what I have to do to get it to work properly.

 

[JHB]

Can you post a stripped version of you database with some sample data, (zip it) + the name of the form in which you have the problem?

 

[david.paton]

I don't know what has happened. I went home last night then came to work this morning and it worked. I will keep you posted if I run into more issues.

 

[david.paton]

I am having another issue. I am trying to get all types of hours, supervision, tcs and iss hours for the various locations.

I have attached the database and I am having a problem with frmSearch, it is unbound. Underneath the Total hours by location, there is a drop down menu with various locations.
At the moment, I am just trying to get a query working, when I do, I will make the other queries and will attach it to the form. The query that I am working on is qry_svhoursbytown. I open frmSearch and enter the date range of 01/07/2011 to 04/07/2011. I select the location of Bathurst in the drop down list then I try to run qry_svhoursbytown. I thought that it would tally all the supervision hours for Bathurst but It doesn’t return anything.
Have a look and see where I have gone wrong please?

Thanks,
Dave

 

[JHB]

Your town combobox is the problem.
The first column (which you hide) is a number/Id but you search criteria need to be town name.
Change the rowsource for the combobox to the below, remember to set it up for only one column and make it visible, (List Width).

SELECT Crisis_support_workers.Town
FROM Crisis_support_workers;

 

[david.paton]

I put this in the row source of ComboTown, the town combo box:

SELECT [Crisis_support_workers].[Town] FROM Crisis_support_workers;

and when I try to view the form, there is nothing in the combo box.

 

[JHB]

...
and when I try to view the form, there is nothing in the combo box.

As I wrote: ", remember to set it up for only one column and make it visible, (List Width)."
Look at the "Column Count", "Column Widths" and the "List Width".

 

[david.paton]

Thanks, that is great.

I am having 2 more issues at the moment.

First issue is when I open frmSearch and enter a date range and select a worker, then his recalculate results, the hours don't appear under Total hours by worker in the test boxes until I click on the text boxes. How do I get the values to appear when I click on Recalculate Results?

Second issue is that when I enter a date into the start date and finish date text boxes on the same form, in the format, dd/mm/yyyy, it converts the format to mm/dd/yyyy. I can't seem to find anywhere on the internet how to get the format to stay in the dd/mm/yyyy. Any suggestions?

Thanks,
Dave

 

[david.paton]

I found another issue. I have a query that is called qry_svhoursbytown and it is meant to calculate the supervision hours by town. but when I try to run frmSearch using the dates 01/07/2011 to 20/07/2011 and select Bathurst for the location, it gives me 66 hours but bob is the only one in Bathurst and he has only worked 11 hours.

Not sure why this is happening. In the qry_svhoursbytown the fields are No_hours from Supervision_hours table, total = Sum; Town from Crisis_support_workers table, total = Where, criteria = [forms]![frmSearch]![ComboTown] and Town, total = Group By but this was just for diagnostic purposes, to check my queries are working.