Search results

  1. C

    Auto-incrementing id number, that resets to 1 each year, with a year prefix

    In order to reset the incrementing number yearly, here's what I ended up doing, with the help of plog: In a table I created the number fields (Long Integer) [Inc] and [fldYear]. In a query based on said table I created a calculated field ID: [FldYear] & "/" & Format([INC];"0000") , that will...
  2. C

    Auto-incrementing id number, that resets to 1 each year, with a year prefix

    OK. I pasted the records to the table with the 2 separate fields (Inc and Year) and the form was updated fine. I think that's all. Thanks again for all your help and patience.
  3. C

    Auto-incrementing id number, that resets to 1 each year, with a year prefix

    I'm pasting from an excel that has the 2015 records up to now. There are 196, so manual copying is pretty much ruled out. I'm attaching the empty database that I used in order to figure this out. I have only put in a name field, so that I can simulate the whole situation.
  4. C

    Auto-incrementing id number, that resets to 1 each year, with a year prefix

    One more issue, that I don't know if it can be solved: When I try to paste more than 1 records on the form, they all take the id 1015/0001, because the function BeforeInsert runs once for all of them. Any ideas?
  5. C

    Auto-incrementing id number, that resets to 1 each year, with a year prefix

    OK I (you) did it! Correct code looks like this: Private Sub Form_BeforeInsert(Cancel As Integer) Dim vLast As Variant Dim iNext As Integer Me![RIMYear] = Right(Year(Date), 2) vLast = DMax("[RIMINC]", "[RoadIM]", "[RIMYEAR]=" & Right(Year(Date), 2)) If IsNull(vLast) Then iNext = 1 Else iNext =...
  6. C

    Auto-incrementing id number, that resets to 1 each year, with a year prefix

    Here's where I am: In the table RoadIM, I created the number field [RIMInc] with the format 0000. I can't create the RIMYear field in the table because the table can't take the variable Date() function. In the query qryRoadIM, I created the field RIMYear: Right(Year(Date());2) Then I created...
  7. C

    Auto-incrementing id number, that resets to 1 each year, with a year prefix

    Thanks! I will try it.
  8. C

    Auto-incrementing id number, that resets to 1 each year, with a year prefix

    Firstly, thank you for your reply. I need to format the id like that because the database will be used in another program that stores the records in that manner. Your 2-field solution seems much more logical that the 1-field clusterfuck. Most of the code I posted was copied from a thread from...
  9. C

    Auto-incrementing id number, that resets to 1 each year, with a year prefix

    What I want to do is create an incrementing id that resets to 1 every year with a prefix. The prefix is "10yy/", where 10 refers to the table and "yy" is the current year. For 2015 I want it to go: 1015/0001 1015/0002 etc. In 2016 I want it to go: 1016/0001 1016/0002 etc. The table is "RoadIM"...
Top Bottom