Command Button Increase Value by 1

Question

Hello and thanks for the help.

I have a form where I enter records.  Each row has 8 fields.  1 of the fields is time (ObsTime) and one of the fields is a photo name (ImageName)  that includes letters and an increasing numeric field (IMAG0001, IMAG0002, IMAG0003, etc.).

I want to create a command button that copies the last row entered, increases the value of the ObsTime by 1 minute (13:15 becomes 13:16) and increases the image name by 1 (i.e., IMAG0001 becomes IMAG0002). and then paste the updated record as a new record.

Any ideas, pretty new to access.

Thanks

Sean

Answer 1

It's not clear whether your ObsTime values include a date, and are formatted as hh:nn, or whether they are the 'time value' only.  If the former I'd suggest the following amendment to Scott's code:

strSQL = "INSERT INTO tablename (ObsTime, ImageNumber, field3, etc.) " & _

               "VALUES(#" & Format(DateAdd("n",1,Me.ObsTime),"yyyy-mm-dd hh:nn") &_

               "#, " & Me.ImageNumber+1 & ", " & _

               Me.control3, etc. & ");"

As written the code would fail in the UK and elsewhere not using the US short date format.  Formatting the value to the ISO standard for date/time notation internationalizes the code.

If the latter, then you should understand that there is in reality no such thing in Access as a 'time value' only a date/time value; it is really the time on 30 December 1899, which is 'day zero' in Access's implementation of the date/time data type.  So the code would then be:

strSQL = "INSERT INTO tablename (ObsTime, ImageNumber, field3, etc.) " & _

               "VALUES(#" & Format(DateAdd("n",1,Me.ObsTime),"hh:nn") &_

               "#, " & Me.ImageNumber+1 & ", " & _

               Me.control3, etc. & ");"

I have one question, however.  What happens when the time reaches 23:59?  In scenario 1 above, where the values include a date, then the value would increment to midnight, 00:00, at the start of the following day; in scenario 2, where the values are 'time values' they would revert to 00:00 at the start of 1899-12-30.

Answer 2

While this can be done, I would change your structure. Since the image name is always prefixed by IMAG, why store it? Make your image number a Long Integer datatype. Then it will be easy to increment. 

Dim strSQL As String

strSQL = "INSERT INTO tablename (ObsTime, ImageNumber, field3, etc.) " & _

               "VALUES(#" & DateAdd("n",1,Me.ObsTime) & "#, " & Me.ImageNumber+1 & ", " & _

               Me.control3, etc. & ");"

CurrentDB.Execute strSQL, dbFailOnError

The above code will add a new record with an incremented ObsTime and ImageNumber. The rest of the fields will be copied from the current controls. 

To display the number with the prefixe use the expression:

"IMAG" & Format(ImageNumber, "0000")