Not delete record, hide/deactivate?

[behedwin]

Hi

I have a set of forms connected to my tables.

Currently i let users remove records.
But id like to stop that and instead find a way to hide or deactivate the records instead of running the delete command.

Does access support something like this?
Or do i have to build something?

If i have to build something... im thinking of having a archive table for every table i create and move records to them instead of deleting... but this will be quite messy i believe....

any ideas on how to do this?

another idea is to create a column that gets an value "INACTIVE" and then i need to filter everything i do so those records are not shown... but i have no clue if this is doable or how i should approach it.

 

[behedwin]

if i open up a new query
go to sql view
paste this

Update Profile_Table SET [ProfileArchive]= Date() WHERE Profile_ID = 6

press run
i get a window that asks me for a value for profilearchive
screenshot: http://prntscr.com/hay3hx

if i enter a value like "2017-12-12"
i get this error
http://prntscr.com/hay3sx

also tried this
http://prntscr.com/hay4mg

or should i do something else?

 

[xenou]

Well, just for kicks I actually run your code and the error I get is the same as one where a table, field, or form control name is not spelled correctly (or doesn't exist). Do you get the same error running the raw SQL as described in the post just above?

Never mind - you did it.
So is ProfileArchive a field in the table?! What kind of table is that?

 

[xenou]

Yes, I looked at your screenshot of the table:
https://prnt.sc/hauqyw

The field is not called ProfileArchive
It is called ProfileArchived (ends with a "d")

So this should work (preferring Micron's usage):

Dim strSQL As String
strSQL = "UPDATE Profile_Table SET [ProfileArchived]= Date() WHERE Profile_ID = " & Forms!SelectProfile_Form!ListPicker
CurrentDb.Execute strSQL

 

[behedwin]

i found the problem.

it was so simple as a misspelling.
the column name is ProfileArchived
but the code had ProfileArchive
without "d" at the end.

so all this just for this little thing.
i must have read the code over and over and over and never noticing this.

thanks for all your help.
and im sorry for not noticing this before.

 

[xenou]

Rest assured this has happened to many people who have also spent hours trying to find such a small mistake.

 

[Micron]

Interesting that the prompt is about too few parameters when using the .Execute method, but if you paste the sql output as xenou so wisely suggested back around post 18 that you'd get a parameter prompt. I guess that's one of the differences between error handling in Access vs Jet. I'm glad I didn't go off on a tangent about DAO parameters, which is often the cause of the posted error. I feel dumb for not suggesting a field might have been mis-named, but I didn't realize 2 different messages existed for what is basically the same problem.

So behedwin, if this ever happens again, you'll likely output the query into the immediate window of the vb editor as xenou suggested, paste that sql into a new query in sql view (you just close the dialog box that wants you to pick a table for the new query and go to sql view paste there) and run it. If you get a parameter prompt for one or more fields and haven't explicitly created parameters in your sql, you know what to look for. BUT if the query runs but you still get the parameters expected message, time to look into DAO parameters.
FYI: when using the .Execute method, it's highly advisable to include the dbFailOnError keyword. In absence of some major problem like you had, that method will give no clue that the sql failed to perform the action you intended if you don't use CurrentDb.Execute ..., dbFailOnError
Error handling is often used in conjunction with this, but if you don't create your own handler, Access will use the default.

 

[behedwin]

Interesting that the prompt is about too few parameters when using the .Execute method, but if you paste the sql output as xenou so wisely suggested back around post 18 that you'd get a parameter prompt. I guess that's one of the differences between error handling in Access vs Jet. I'm glad I didn't go off on a tangent about DAO parameters, which is often the cause of the posted error. I feel dumb for not suggesting a field might have been mis-named, but I didn't realize 2 different messages existed for what is basically the same problem.

So behedwin, if this ever happens again, you'll likely output the query into the immediate window of the vb editor as xenou suggested, paste that sql into a new query in sql view (you just close the dialog box that wants you to pick a table for the new query and go to sql view paste there) and run it. If you get a parameter prompt for one or more fields and haven't explicitly created parameters in your sql, you know what to look for. BUT if the query runs but you still get the parameters expected message, time to look into DAO parameters.
FYI: when using the .Execute method, it's highly advisable to include the dbFailOnError keyword. In absence of some major problem like you had, that method will give no clue that the sql failed to perform the action you intended if you don't use CurrentDb.Execute ..., dbFailOnError
Error handling is often used in conjunction with this, but if you don't create your own handler, Access will use the default.

Thank you.
How would i add the dbfailonerror method?

is something like this enough?

Dim strSQL As String
strSQL = "UPDATE Profile_Table SET [ProfileArchived]= Date() WHERE Profile_ID = " & Forms!SelectProfile_Form!ListPicker
MsgBox "Profilen tas bort. Detta går att ångra om du kontaktar en administratör."
CurrentDb.Execute strSQL, dbFailOnError
Form_SelectProfile_Form.ListPicker.Requery

 

[Micron]

Looks correct. It's enough to raise an error if it fails.
Good luck with your project!

 

[xenou]

I would prefer to catch the error and handle it. You might end up falling into debug mode or crashing (not sure).

(either that, or don't use the error option, but since you are asking, I don't think it is enough to just add the extra argument - the reason for using dbFailOnError is to be able to do something about the error, not to simply fail, especially if you are not the only user (what is the typical user supposed to do when confronted with a SQL error they have never heard of?).

 

[Micron]

I wouldn't run an action query without it. I guess I wasn't clear enough in my last paragraph of the post where I wrote that this is usually done in conjunction with error handling. I made the last brief response using my phone, which tends to make me somewhat brief.