Not delete record, hide/deactivate?

behedwin

Active Member
Joined
Dec 10, 2014
Messages
399
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
In sql
SELECT ..... WHERE criteria1 = something AND ArchiveDate Is Null;
Checking for null in vba is different:
IsNull (some field, variable or control)
 
Upvote 0
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?
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

Code:
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?
 
Upvote 0
You are simply creating a SQL string.

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

Code:
"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.

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
 
Upvote 0
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??
 
Last edited:
Upvote 0
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:
Code:
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
Code:
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.
 
Last edited:
Upvote 0
You've got a debug.print statement in there precisely so you can view the sql:
Code:
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!)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top