Help needed

[lead 27]

I made a "last checked box" and a "require next check box" (which are dates)
they work fine however I put the last checked box in the main table and used a calculation field for the next check date.

But what I didnt think of was that I would need to update each record in the table for it to show on each form as they will all have the same date.

does anyone have any sugesstions how to change this?

 

[boblarson]

If you are calculating the next check date, you don't need to change anything in the table. You just have to display it on the form or create an unbound field in any queries or reports that uses the same calculation.

 

[lead 27]

Thanks for replying. Everything works fine but the problem is that the date is the same for every record in the table so I just wanted to type it once and it shows in each record so when I scroll through the forms its there.

 

[boblarson]

All you need to do is have the control source of an unbound text box be

=DateAdd("d",HoweverManyDaysToAdd,[YourDateFieldHere])

and it will have it for you regardless of which record you are on.

substitute "m" for months, "yyyy" for years, etc. for what to add.

 

[lead 27]

Is that for the next check Date? As that is fine and is already unbound its the last check box which is the problem. This one is a field in the table so it means that I have to write the last date in each record even though there all the same

 

[boblarson]

I'll say it again - you really should NOT be storing the next check date in the table, if you can calculate it with a formula from the last check date. That being said, I answered how you can update it in the table in your other post.

 

[lead 27]

The NEXT CHECK BOX is a unbound calculated field which ISNOT stored in the table. The problem is that the LAST CHECK BOX (which could be todays date) IS stored in a table. My problem is that every item in my table will be checked atthe same time and so will all have the SAME LAST CHECK DATE however at the moment I would have to input the same date into each record on the table (over 200 items)

I want to be able to input one date and it updates every field in the LAST CHECK BOX field.

I hope that this makes more sense.

 

[boblarson]

Yes, that helps. My suggestion of an update query would be the way to go. So something like:

UPDATE YourTableNameHere SET YourTableNameHere.YourCheckDateFieldHere = Date()

 

[lead 27]

Thanks for your response

I am now trying to make a button that on click it update the Last Check field with todays date and then updates the next check box. i have this sorted however it obviously still only updates one at a time. So would an update query be best for this

Thank you for your help

 

[boblarson]

So would an update query be best for this

Yes. It is much more efficient.

 

[lead 27]

How would I go about the update query as I am trying but not getting very far

 

[boblarson]

1. Go to the database window and select Queries
2. Select New Query
3. When the table select comes up, double-click on the applicable table to add it to the query.
4. drag the field you want updated into the grid
5. Go to the menu bar and select QUERY > UPDATE QUERY to change it from a select to an update query.
6. Find the part in the grid that says UPDATE TO: (on the left of the screen and in that area just below the table name type Date()
7. You can then run it by selecting QUERY > RUN on the menu or by clicking on the red exclamation point on the toolbar.

 

[lead 27]

Yeah I had done that but nothing happens when I click run. I will keep playing around with it

 

[pbaldy]

With your attempt in design view, click on View/SQL View and copy/paste the SQL here.