Programatically adding data from next field

Need to get data from future entry

Page 1 of 1

2 Replies - 1024 Views - Last Post: 23 October 2007 - 11:33 AM Rate Topic: -----

#1 sawman  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 03-February 07

Programatically adding data from next field

Post icon  Posted 22 October 2007 - 08:56 PM

Okay:

I have been trying to figure this one out but can't even find any topics on it to read. I have an Access database that tracks the time a saw blade is put on and the time it is removed. I have a field for DateTimeOn and one for DateTimeOff. I have no problem calculating the hours and minutes the blade was on the saw. Here is the problem:

Is there a way to programatically populate the Time off field with the next records time on field? Basically, the date and time when the new blade goes on is the date and time when the old blade comes off. Convincing good ol' boy operators to take the time to look for this information and enter it is out of the question. So, can I make it automatic? Is there a way to use the FindNext method to find the next records time on field and put that data in the time off field of the previous record.

Any idea's on how I could make this happen?

Is This A Good Question/Topic? 0
  • +

Replies To: Programatically adding data from next field

#2 ajwsurfer  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 373
  • Joined: 24-October 06

Re: Programatically adding data from next field

Posted 23 October 2007 - 09:10 AM

I am going to assume that since you are using Access that you are using a "Form" item to enter data. You do have date time functions and masks available from the "Form" item.

What you can do with the Form is to highlight the dateTime field so that text entry box has the 8 black handles around it (little black boxes). The right click and select "Properties" and then "Data". The two fields you are concerned with here are "Input Mask" and "Default Value".
* First click in the blank field next to "Input Mask" and then the "..." there will be a range of time and date formats available. Chose one and move on.
* Next click on "Default Value" the same way and then double click the "+" sign next to "Functions" then "Built-In-Functions | Date/Time | Now | OK". If all that doesn't work just type "=Now()" in the box next to "Default Value".

If you are not using forms the "Now()" function should still be available. In which case you can just use a query statements like:
UPDATE SawBlade SET TimeOff = Now() WHERE IDN = 3;
UPDATE SawBlade SET TimeOn = Now() WHERE IDN = 4;
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5642
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: Programatically adding data from next field

Posted 23 October 2007 - 11:33 AM

I think you might be looking for something like this.

UPDATE SawBlade a
   SET DateTimeOff = (
	  select min(DateTimeOn) 
		 from SawBlade b
		 where b.DateTimeOn > a.DateTimeOn
   )
where DateTimeOff is Null


I believe that's the SQL style that will work in access. You may need to play with it.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1