Deleting records

[royalrodger]

I am having trouble with my autonumbers when i have to delete a record.

Can anyone help me out with auto numbers?

 

[pbaldy]

Help with what? It should not matter whether a table has an autonumber; you should be able to delete records.

 

[royalrodger]

Yes, I can delete records, its just I dont want to lose the number sequencing, and I want a new record assigned to the old autonumber of the deleted record. thanks for your help from Australia

 

[pbaldy]

Won't happen. If the number sequence is important, you should manually assign the numbers rather than use autonumber.

 

[royalrodger]

How do i manually assign numbers, they are really important as they relate to a card given to people in the program.

 

[pbaldy]

The normal method to get the next number is using DMax() to find the current largest value in the table and adding 1 to it. Yours will be trickier if you want to check for missing numbers and use them over again. If your numbers are static and predictable, you could have an extra table that contained all of them and check against that. Odds are that's not practical though. The methods I've seen to find gaps involve opening a recordset and stepping through the table. If you search on "sequence" and "gap" or something along those lines, you should turn up the code. It wouldn't be too tricky.

By the way, Australia is often referred to as "down under" around here. I'm curious if you use "up over" for us, or something along those lines?

 

[royalrodger]

Thanks for your help mate. That kind of makes sense. Yes we are known as coming from Down under, it just depends where your from, but generally no, we are the only place around who is under anything, as we are at the bottom of the earth next to nowhere. The only place I can think of that would be up over is like Alaska.

 

[pbaldy]

Well, I've heard great things from friends and relatives who have traveled there. I hope to visit someday.

G'day!

 

[royalrodger]

Still looking for help

Is there a place online where I can see example expressions?

I am still struggling with the fact when I delete a record because a customer has requested to be removed from the db, i lose their entire record and their unique number. All I want is for the record to be replaced with a new record.

Where do i write these details in?

 

[pbaldy]

Well, generally speaking, I wouldn't actually delete them. I'd have a field to mark them inactive or something. I'm not clear on what you want, but one alternative to deleting the record would be to empty all the fields but the unique number. Or replace that person's data with another's. But like I said, I'd probably leave them in there for historical purposes. If you simply mark them inactive, it's easy with a query to only view active people.