auto increment and search with multiple criteria

[hoschi]

Hi there

I got 2 problems:

the first is, that I autonumbered my records, but when I delete a record (perhaps record nr. 17) this number is also deleted (nr 17 doesn't exist any more...).
I want that the numbers of the deleted records are still avaiable!
Or is there another possibility to get a recordnumber which increments itself automatically...

the second problem: I got a lot of colums in my table, so i want to create a form (for all my users) in which they can put in more then one criteria for searching the records they need.
e.g. pers.nr, city, and name --> 02****, Basel (Switzerland), James.
the search must reply with all the records which got in the colum pers.nr, city and name the three criterias (02****, Basel (Switzerland), James) the user entered!

How can I do this, I guess I must use VBA or SQL, but how?. I know C++ a little but nothing about VBA and SQL!

thanx a lot

hoschi

 

[The_Doc_Man]

Your problem is in two parts, so my answer will match...

1. Regarding reclamation of the autonumber associated with a deleted record:

You should NEVER use an autonumber in a way that requires its reclamation. It is NEVER valid as a counter of the number of records in a particular table. (Use a COUNT query or the DCount function.) An autonumber tells you only one thing: How many times an .AddNew method was used on the recordset that the table represents. (It does NOT reflect the number of times that a new record was saved in that recordset.)

If you want the number to be something specific, it CANNOT be an autonumber. But if you "break" the autonumber, then you take on the issues of assigning a new number that (A) does not already exist and (B) fills in the "cracks" left by deletion and (C) goes to a higher number when no lower numbers are available and (D) meets whatever other criteria you assign to the number.

2. On the issue of using multiple search criteria, try searching this forum for articles on the subject of "Cascading Combo Boxes" and "Multiple Search Criteria" - both searchs should return many useful articles.

 

[hoschi]

thanx Doc_Man

but can you explain me, how can I create such number with this criterias (A-C)?

For the second, thanx, I'll search the forum...

thanx again

hoschi

 

[The_Doc_Man]

Hmmm, guess I wasn't explicit enough.

Regarding 're-use' of prime keys like this....

Don't do it. Use count queries or a Dcount or any other method to find out the number of entries in your recordset.

Any program that needs absolutely contiguous prime keys is probably taking a programming shortcut that shouldn't be taken.

And by the way, it also wouldn't work anyway. What would you do if a deletion occurred but no new records were entered, then it became time to run whatever report it is that needed those continuous numbers?

What I am saying is that a design requiring a continuous number sequence should not make those numbers a primary key, precisely because you have to contend with deletions. Make the number anything else. Indexed (no duplicates) is OK too. But you must not depend on the numeric sequence created by an autonumber as a primary key. It is a very bad programming practise.