Search results

In #62 I said that it would do this except that I thought the PK mechanism was smart enough to go find the last PK value and increment that. IOW I thought when I entered I: 5 it would know it was "out of room" and go find the highest PK... 6, increment that, and start with 7. Instead it tried...

I used George's little demo to run his query, inserting the PK number 2. I then entered G and H successfully. Notice that the PK continued to increment the counter from 2 - G: 3, H: 4 However when I tried to enter I : 5, it stopped me from entering that record because the autonumber was trying...

@GPGeorge can you test what happens if you add a single record into a multi record hole? IOW delete 5 records, then fill in the first hole, then add another record manually. IIRC the autonumber picks up after your append and adds one to whatever that PK you just placed in with a query. PK...

I keep my very powerful windows laptop mostly to allow me to play around with Access. It is Windows 10. It will never go to Windows 11. I woul love to run a VM on this laptop where if I caught some nasty I could just start a fresh VM. Sadly I don't have the expertise to move this instance...

I think I see the problem here. Businesses run by rules. It is not a state table it is a business decision. Active is a state. Trash is a state. Each of these are business decisions. A paint color record is Active or Trash. Active means that we sell more than 10000 gallons a month. The...

true true and true. OTOH having an inserted date field, automatically set to now() as any record is inserted.... And really reiterate to everyone involved that an autonumber field is just the PK of the table. Nothing more.

True!!!

>>>it does represent the order in which rows were inserted No it represents the PK of the record. You are interpreting the fact that they are in order to represent order. It might, but it might not. Someone might decide to fill in the holes with new records. Suddenly the autonumber no...

Nothing in my original post indicated in any way that only a single flag can represent two different pieces of information. It can be done obviously but we are database folks. We can also denormalize our data and keep info about two things in a single field or table but we don't. In fact we...

In the case of @The_Doc_Man , he was actually asking HOW I did it. A class with event sinks enforcing rules is the how.

ROTFL, well thanks. I actually used this pattern to prevent deletes by users. In my databases the "delete" key never deleted anything, all it did was set the trash flag and cleared the active flag. That is just me. It is not my job to tell other developers what to do, only what I did and...

What can be done? Access or SQL Server?

I would think it could. Restoring archived records is a valid use for this. That is not an "arbitrary" value, but how is a database engine supposed to know what the administrator intends?

ROTFL. Indeed I do. Active and Trash are related but not identical. And so I have a class which wraps those two controls, and imposes a set of rules on them. 1 If a record is Active, it cannot be Trash. 2 If a record is Trash, it cannot be Active. see #1 above 3) if a record is Inactive...

It can be done. I have done it. It is not particularly useful, and you probably don't want to go doing that. Except in one case... Suppose that you archive records in a table, keeping the PK out in the archive (which you BETTER do!). Then you delete the original records in the original...

> "AutoNumber primary keys should not be used for anything other than identifying records." ^THIS!!!

Is the append query bringing in the autonumber field from wherever the data is coming from? If so then that will "override" the autonumber value. If you are not bringing in the old PK from wherever the data is coming from then... but the bigger question is: The autonumber is "supposed to be"...

It turns out that you can intentionally "fill in the holes" in a table with an autonumbered field (caused by deleted records) by creating a query which appends a single record into that hole. Once you do so autonumber picks up, takes over and starts autonumbering from that number you appended...