New "copied" record doesn't gain autonumber

[Guest]

Sometimes we have virtually identical transactions (customer orders) except
for a small detail or two.
Rather than entering all the data manually each time I have set up a Copy
command button on the main order page and that generates a new record just
fine. We can then edit the odd field or two that needs amending for the new
order.
However, all our new records need to pick up a new unique ref from
Autonumber - this works OK when manually adding a new record, but not if we
create one via the Copy command. And since it is an autonumber we cannot
input the ref manually. Stuck!
Ideas, please??
Many thanks
CW

 

[Douglas J. Steele]

How are you creating the copy? Show us the code...

 

[Guest]

I used the Command Button Wizard > Record Operations > Duplicate Record >
Copy to New Record, which generated the following:

Private Sub Duplicate_Click()
On Error GoTo Err_Duplicate_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Duplicate_Click:
Exit Sub

Err_Duplicate_Click:
MsgBox Err.Description
Resume Exit_Duplicate_Click

End Sub
_______________________
Thanks for your advice
CW

 

[Douglas J. Steele]

No, that won't work.

You're going to have to run an INSERT INTO query that takes the values from
all of the text boxes other than the AutoNumber field and inserts into all
the fields in the table other than the AutoNumber field.

 

[Guest]

Douglas - just realised it's a Duh... issue, sorry!!
It was incrementing the ID all right, but I have hidden that control so did
not see that it was clocking up.
Instead, I have a special ref no in a different format that I was wanting to
notch up every time I created a new record. We use that Ref as our unique
record identifier, rather than the Access ID.
But of course that cannot take place as I can only have one autonumber field
per table, can't I? Or do you know some sneaky way round this?
Thanks again
CW

 

[Van T. Dinh]

Only one AutoNumber Field per Table. There is no point in having more than
1 AutoNumber Field in the Table as it is supposed to provide uniqueness
only.

Not sure what "sneaky" way you are looking for?

 

[Guest]

I see what you're saying...but this is the sort of usage I have in mind: we
use separate numbering sequences for our inquiries, for our quotes, and for
our booked jobs.
We receive (say) 10 inquiries per day, each of which is logged and needs to
have a sequential number. That's used up the autonumber.
We then issue perhaps 13 quotations based on differing options requested in
the inquiries. We need a run of quote-numbers for those. Can't use the
autonumber, that's already been taken.
And then we book a certain number of those quotes as actual jobs to be
carried out. Perhaps 8 of them. So we need another autonumber sequence every
time a job is booked.
I don't know how it would be done (obviously, or I wouldn't be posting
this!) but I thought perhaps somebody somewhere might have some little
home-grown incremental numbering system that I could "borrow". Please!!!!

 

[aaron.kempf]

those should have autonumber fields in related tables; not in the main
table.

 

[Douglas J. Steele]

Realistically, it's wrong to assume that an autonumber will be sequential.
If what you need is a sequential number, "roll your own". See, for example,
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

 

[aaron.kempf]

I dont understand what you're saying; I think that rolling your own is
too complex.

in the database world; performance beats everything else; and I see
this as a performance question.

that's another reason i use Sql Server.. I can set seed and increment
values for a table.. you can't do that in MDB

yes; identity-- in SQL Server have their shortcomings; but when you
start writing 5k records a second I dont think that it would kill you
to use a guid

-Aaron