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.

 

[Micron]

You are on the right track. A yes/no field would work, and you archive the record by checking it, assuming this setting is conducive to the name of your new field. Another perhaps better way is to have a field like ArchiveDate and date stamp it. Your queries/form/reports simply include the criteria for where this field is null. If it's not null, it's dated, thus it's archived. The benefit of a date field is that you can know when it was archived. A deleted record is gone for good, so archiving is often a better idea.

Moving the records is not a good idea. Plus, when archived, you can always restore it.

 

[behedwin]

Ok, i like the date idea. Im quite new to access... How should a criteria like that look like? How to tell queries /forms/report to only show those records with null value?

 

[Micron]

In sql
SELECT ..... WHERE criteria1 = something AND ArchiveDate Is Null;
Checking for null in vba is different:
IsNull (some field, variable or control)

 

[behedwin]

I got my query to work.
it only shows field that are not empty in a specific column.

i just typed "Is Not Null" in the criteria field in the query designer.
It seems to work.

Now i need VBA to add date to the current open record.

I use this code in another part of my project.
But i can not get it to work so i add "todays date" instead of "KLAR" that below code is adding.

How to modify this code?

CurrentDb.Execute "UPDATE ProfileLog_Table SET [HRWEBB_CHECK]='KLAR' WHERE Log_ID=" & Form_Profile_Form.ProfileLog_Listbox
Form_Profile_Form.ProfileLog_Listbox.Requery

I know i need to change the table and object names in above code. It should look like this

CurrentDb.Execute "UPDATE Profile_Table SET [ProfileArchive]='TODAYS DATE' WHERE Profile_ID=" & Form_SelectProfile_Form.Listpicker
Form_SelectProfile_Form.Listpicker.Requery

So how do i make TODAYS DATE work?

 

[xenou]

You are simply creating a SQL string.

The string you want to create (assuming this works with US formatted date!) is:

"UPDATE Profile_Table SET [ProfileArchive] = #11/14/2017# WHERE Profile_ID =  1"

So you just build your string and execute. But you want to check it to make sure it's right, at least at first, so print it out when you test the code.

dim strSQL as String
strSQL = "UPDATE Profile_Table SET [ProfileArchive]= #" & Date() & "# WHERE Profile_ID = " & Form_SelectProfile_Form.Listpicker
debug.print strSQL '//Check the SQL
CurrentDb.Execute strSQL

 

[behedwin]

I get an error on this line

CurrentDb.Execute strSQL


To few parameters....
Anything missing?

 

[Micron]

i just typed "Is Not Null"
You seemed to have done it backwards from what you originally asked for, which was to not see the archived records. If the date stamp means it's archived, then that expression will return those with dates, i.e have been archived. Oh well, you say it works so who am I to argue?

Your latest inquiry: the error can have more than one cause. What's the rest of the message (it always tells you how many are missing).
First thing I'd ensure is that all the variable data types fit the way the sql is written. If the types are text, they'll need single quotes. Also, the form reference doesn't look right
Form_SelectProfile_Form.Listpicker
should be Forms!Form_SelectProfile_Form.Listpicker??

 

[behedwin]

Yes sorry, my misstake.
The criteria i use is not "IS NOT NULL" it is "IS NULL"
I was mixing things up.


To the error.
Changed the code to this:

Dim strSQL As String
strSQL = "UPDATE Profile_Table SET [ProfileArchive]= #" & Date & "# WHERE Profile_ID = " & Forms!Form_SelectProfile_Form!ListPicker
Debug.Print strSQL '//Check the SQL
CurrentDb.Execute strSQL

or

Dim strSQL As String
strSQL = "UPDATE Profile_Table SET [ProfileArchive]= #" & Date &  "# WHERE Profile_ID = " & Forms!SelectProfile_Form!ListPicker
Debug.Print strSQL '//Check the SQL
CurrentDb.Execute strSQL

Still same error message
To few parameters, expected 1.
Screenshot: http://prntscr.com/hajhze

The column "PROFILEARCHIVE" is seleted as a date column.

I did try to change it to a "short text" column, still same error.

 

[xenou]

You've got a debug.print statement in there precisely so you can view the sql:

Debug.Print strSQL '//Check the SQL

so .... what is the SQL you are trying to execute here ...

(also if you are changing data types in columns please report the current data types you are now using in these columns!)