A combo box that looks up results of a qry based on date entered

[djmurphy58]

I have a form for entering daily costs on a construction site. The user enters the date that work was performed, the quantity of work performed, the applicable cost code for which wwork was performed, and finally the cost incurred on that date to get that amount of work done.

Each type of work has an applicable cost code. Take for example "Pour Concrete". The budget for this item might be $10.00/CY (cubic yard). However the user is able to change the budget for this item on a monthly basis. For example, if we're making money on this item, we may want to move budget out of this item and into another item that is losing money. So to make a long story short, I allow the user to change the budgets on a monthly basis. Keep that in mind.

Now, when the user inputs the date 2/27/04 into the daily cost sheet, the user then selects from a drop down box the costs codes for which work was performed that day. Right now, I have a query setup with that combo box. That query will look up the cost codes and their corresponding budgets for the month of February only (since the work was perfomed on 2/27/04). If, for example the user enters 3/10/04, the query would look up cost code budgets that apply for the period from 3/1/04 through 3/31/04.

So, everything works fine when I enter a brand new daily cost sheet. The query will look up the correct results based on the date entered.

However the problem comes in when the user is scrolling through the historical records of the daily costs sheets. Say I want to go back and look at the daily cost form from 12/18/03. The query in the combo box should look up the budgets for the period 12/1/03 through 12/31/03......but it doesn't!!!

Does anyone know what I am doing wrong.

Thank you

 

[KenReay]

Hi

You do not show the query on which the combo box is based, so I have to guess, based on the symptom you describe

By gues is that the criteria on the Combo box query is todays date, you need to make it the Date of Work Done (or whatever you have called it in your system. I assume you know how to reference a control in an open form in the query eg the syntax Forms!MyForm!MyControl wher MyForm is the name of your form, MyControl is the name of the date field control, you will need to refresh the combo box each time the record changaes (in case the date has changed), do this by putting code in the OnCurrentEven of the code to requery the combo box

MyCombo.Requery

similarly if the user changes the date, so in the after update event of the Date work done control, put MyCombo.Requery

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK