Hi There,

I am looking for a way to do the following if someone could help please...

I have a jobs table with many fields but the three I am concerned with are 'Job Number', 'Date' and 'Locked'.

I have a form with a To and From Date and a button.

When someone enters the dates, for example 01/01/2007 - 20/01/2007, and then presses the button it will automatically run through my table and any job which is in between these two dates has the 'Locked' value changed from FALSE to TRUE.

Can someone tell me how to do this please?

Thanks very much

John.

Hi,
you can use an update query for that.
Create a new query based on the data table. In the query date field's criteria area reference your form controls e.g.:

BETWEEN [Forms]![YourForm]![StartDateControl] AND [Forms]![YourForm]![EndDateControl]

In the criteria of the locked field type:

False

Now change the query to an update query and set the update to area of the locked field to True.
You can execute the query with:

CurrentDb.Execute "YourQueryName", dbFailOnError

From the same form where the user enters the dates, which has to be open at query run.
HTH
Good luck

Thanks Oli,

I'll have to try that tomorrow but many thanks for the reply and I'll let you know how I get on.

Cheers

John

You're welcome.
Good luck.

Hi Oli,

I've just tried that and it worked perfect.

Thanks very much

John

One more thing if you could Oli,

The UPDATE query runs through the selected jobs and sets the locked value to TRUE.

I am actually learning to do this so I can eventually create an invoicing form.

If the locked value of one of the selected jobs is already at TRUE, I would like a message to appear on the users screen saying:

"Job 1234 is currently being edited by user ANDY. Please remove him from this job before continuing".

Is this possible?

Thanks

John

Hi,
currently the query will only run on records which have a false value in that field (hence the criteria).
If you want to have a query returning the ones which already have a true value then create a second one with similar setup...but change the criteria to true.
HTH
Good luck

Yes, that makes sense.

Thanks Oli

John

You're welcome.
Good luck.