Search results

@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...

In the database / info tab, off to the right is a link labeled View and edit database properties. A neat little tool for what it is. Click it and then click Custom In the db I am working in a ton of custom properties are displayed, none of which are populated. But you can click one of them...

We need the table name optional so that if it isn't there we just use the db properties. But with a little work we should be able to pass in something that says what kind of option it is. Or set up a new function to do things other than db properties. A function just for tables, another just...