How can I increment a Dmax number when I import data?

[Tony Williams]

I have field that holds a reference number. The control source of the
reference number if a record is added manually is based on a Dmax formula and
works just fine. However, when I import records the reference number isn't
allocated. How can I get imported records to be allocated the next number in
the sequence?
Thanks
Tony

 

[Jeff Boyce]

?DMax() + 1?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Williams]

Hi Jeff, yes that's what I use in the control source on the imput form but
when I import records they go straight into the table and so the refernce
number isn't updated. I need to find a way where the number increases by 1
for both manually added records and imported records.
Hope I've explained that?
Cheers
Tony

 

[Jeff Boyce]

Tony

One way to approach this (not particularly 'elegant', so stay open to other
options) would be to write a procedure that steps through the input records,
one-by-one, inserting them along with their DMax() + 1 sequence number.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Williams]

Mm? That sounds tricky, can you point me in the direction of some reference
material that illustrate how to do that? Friday night her in the UK so
signing off till tomorrow. Will pick up any response then
Thanks Jeff
Tony

 

[Jeff Boyce]

Tony

If you aren't familiar with creating procedures or using VBA, this will
probably be ... a learning experience.

The basic concept is a routine that:

1. loads all the records in the raw input data as a recordset
2. goes to the top of the list of records
3. grabs the next (first one the first time through) record
4. calculates the DMax() + 1
5. writes the record and the sequence number to your permanent table
6. goes back to #3 and cycles through until no records are left in the
raw input data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Williams]

Thanks Jeff I'll have a go. Do you know of a good reference book that might
help me with this?
Thanks again
Tony

 

[Jeff Boyce]

Tony

Every person's learning style is different. Consider stopping by your local
bookstore and pulling "VBA"-related texts off the shelf and perusing them
for topic, style and organization. If it doesn't 'ring', pick up another...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP