I would do this another way. I would create a Long Integer field in the
table and a control on the form. You can make the control invisible if you
don't want the user to see it. Or if you want them to view but not changeit,
set the Locked property to Yes and the Enabled property to No. You can
emulate autnumbering using the formula below:
Me.txtJrnlLine = Nz(DMax("[JRNL_LINE]", "JournalTable"), 0) + 1
To set it, use the Current event of the form:
If Not Me.NewRecord Then
Me.txtJrnlLine = Nz(DMax("[JRNL_LINE]", "JournalTable"), 0) + 1
End If
If you need to filter the number on some other conditions, just add the
criteria to the formula above.
Me.txtJrnlLine = Nz(DMax("[JRNL_LINE]", "JournalTable", "[SomeField] = "
& SomeCondition), 0) + 1
I hope you choose to adopt this scheme. Autonumbers should really never be
used for any meaningful data, but then, you are starting to understand that
:
The data has to be sent in a format specified by our accounting
department...they require that journal voucher line items be sequential
always starting from 1, so the easiest way to do this is to re-seed the
autonumber back to 1...I think you can do this also by deleting all the data
from the table and compacting the database, but I'm having a nightmare
compacting the replicated database...The worst part is that I never saved a
copy of the original and now I find that I cannot UN-replicate a design
master.
:
I have never had the opportunity to use replication, so I don't know how that
will affect autonumbering, but my guess is changing the autonumber in a
replicated database could be a nightmare.
Just out of curiosity, what are you using the autonumber field for and why
do you need to change it?
:
Thanks, I did make another post regarding the Changeseed function that
Microsoft suggests...it was working fine, but since I replicated my database,
it no longer works.
:
I know there is a way to reset the numbering for an Autonumber field, but
since I don't, as a matter of practice, do that, I can't remember it. I
would suggest you search the Access groups for Restarting or Resetting
Autonumber. I'm sure you will find numerous posts on that.
The reason I never bother to reset an autonumber is I never use them for
anything other than parent child relational purposes, so the value is of no
concern to me. And, if you have child records to the table you want to
restart the numbering, you could have a referential integrity problem.
:
Thanks Klatuu, I updated the Microsoft Data Access Components on my computer
and my references are now OK. The problem is that I am using Microsoft's
suggested code to change the seed in an autonumber field. I am using it in a
database that I recently replicated...and now it doesn't work.
:
There reference will not be on the network. It will be on your C drive.
Moving the database to the network has no effect. The code runs on the
computer that opens it. If you are experiencing this problem on a different
computer, it is possible it does not have the DLL.
What version of Access are you running? I have 2003 and the reference I
show is for 2.7. I don't know if this will help you find it, but the path
should be:
C:\Program Files\Common Files\system\ado\msadox.dll
:
When I compile my database coding, I am getting a missing reference. It is
Microsoft ADO Extension 2.8 for DDL & Security. I know I had it when I
created the coding on my local computer. I think my mistake was copying the
database onto a network drive. Now I get that error and I don't find the
reference - it's missing? How do I get that extension?
