Look in table to number New Record

[mcgregor123]

Hello. I hope I am posting this in the right place. I have a form that my people use to enter new records. I am in the process of changing how this form numbers the records. Currently it uses autonumbering, but that has caused some problems when we try to import records that have been archived off for a previous year. (The table is archived and emptied each year). We end up with two records with the same number, and that's a problem.

So, what I propose to do is something along the lines of have a tiny table with the highest number of last year. Then when they add a new record, if there are no records in the table, look at this tiny table, and add one, and use that for the record_ID. If there ARE records in the table, look at the max record_ID and add one.

My tables are RecordNumber to hold the last year's highest number, with one field HighNumber.txt.

My main table is TblMain and the field I want the number to go in is Record_ID.

I understand the basic flow, but don't know how to write the VBA. I know it needs to be an IF statement, but I'm not sure where it should occur. This form is called from another form, and right now, when it opens to a new record, I notice that the Autonumbered RecordID doesn't fill in until they start completing the form. I'd like my new record_ID to look like that as well.

If the record count in TblMain = 0
then
RecordID = tbl.RecordNumber.Highnumber.txt, Max, +1
else
RecordID = tbl.TblMain.RecordID, Max, +1
end if


I know this is completely wrong, but I just wanted to take a stab at it so you could see what I'm trying to do.
Right now, in my RecordID field, I have
=DMax("ASRID","Tbl_ASR_Main")+1

Which works fine, except it will start the numbering over every year. And I don't want that, for the reason I mentioned earlier.

So can anyone tell me how to make this work? Thank you for your help.

 

[lameid]

Rather than using code you could simply delete the old records form the table and compact and repair the database. It should remember the autonumber value.

Another thought would be to append a record with the last year's highest id to the table. Access should continue with the next highest number. You may even be able to delete this record and everything still work.

Lastly if you are really pursuing your original thought, I would set the value of the id field using DMAX and adding 1 to the result. See help for information on DMAX (FYI there is also a Dlookup and DSum function that take similar parameters).

 

[mcgregor123]

Hi thank you for replying.

We were deleting the old records before, and it did not remember the value and began at 1 again. This is how it was done every year before now. It creates a problem if for any reason we have to go to the prior years and import a record. Which we usually do at least once. That creates a numbering conflict and corrupts the table. And it happens almost every year.

Additionally, when we have tried starting with an empty table, and importing one from the prior year, Access still restarts the numbering at 1.

I already had set the numbering using DMax, which worked fine. But, that still doesn't account for the first record of the year, which needs to be numbered as one more than the highest number of the prior year.

That is why I'm trying to do this with code. Does this mean that what I want to do isn't possible?

 

[ck1999]

I have a database I use (inherited did not make it) that has a table to store recordcounts

Each time a record is added it looks at this table and adds 1 to it and replaces the value.

This is similar to what you are discussing however, I would make it so it always is changed when a new record added so then you would look to this table for your record number for this years information and then when you delete the old data then next year will continue to look at this field. And then if for any reason you want to restart your number system you could go to this table and reset the number to zero.

I hope I have explained this well.

ck1999

 

[mcgregor123]

I like that ch1999. How did you do it? Would there be any problem if I needed to reimport a record from a prior year? It would just use the record number that record had originally, right? That would be brilliant! Can you explain how to make this work? Thank you very much for your assistance.

 

[lameid]

I have never had an autonumber lose the next record without changing the datatype, saving the table and then changing it back.

Anyways, you could use a Recordset to open the other table. Something like the below...

Dim RS as DAO.Recordset

If Isnull (Me!ID)  Then
    Set RS = Currentdb.openrecordset ("RecordCounter")
    RS.edit 
    RS!Field = RS!Field + 1
    RS.Update
    Me!ID = RS!Field
    Rs.close
    Set RS = Nothing
End if

 

[MoLaker]

I don't know if this might work for you or not, but if you created your record number by starting with the year (2007, 2008, etc) and append a sequence number (20070001, 20080002, etc.) it shouldn't make any difference what the last number was for the previous year and would prevent duplicate numbers when bringing archives back in.

"Retired Programmer". So, please be patient.

 

[mcgregor123]

Hi MoLaker - I like that idea a lot. I'll see what i can do to implement that. Thank you all for your help! LameID, yeah, I dont know why the table did that. Perhaps it was the way the last person "emptied" it out for the new year. I have a feeling she actually just recreated a new table and copied the field set up of the old one, rather than archiving out the records and keeping the original table. But with MoLaker's idea, i think that would eliminate that issue entirely. Thanks y'all.

 

[ck1999]

I would think LameID would work well. I inherited the database and it uses a VB front end so I cannot tell the code that was used. However, you could use a max(recordnumber)+1 statement when the form is opened and then if the record was saved use lameid's code.

This would not affect importing old data since it would be imported directly to the table and not through the form.

ck1999