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]

i have not changed datatypes. i just tested to change but im still on "date" type in the column

i dont know how to "view the sql"....

what im trying to do is to add todays date to the selected record in a listbox to the column ProfileArchive.
listbox is named: ListPicker
Form the listbox is located in: SelectProfile_Form

Want to add todays date when clicking a button (on click event).

 

[Micron]

Let's try the simple things first.
- have you compacted the db recently?
- one of those sql statements contains the correct name of the form? And that form is open when you try to do this?
- to dispel any doubt about the form being correctly referenced or being available, try this before CurrentDb.Execute
msgbox Forms!SelectProfile_Form!ListPicker
What do you get?

The solution may involve defining DAO parameters, but let's not go there until we're sure it's the problem. The error is often caused by a reference that can't be resolved. The msgbox function should reveal whether or not there's a problem with your form reference.

FWIW, Date() should work without having to worry about date delimiters, because Date() is a function that returns a date. First get the sql working, then you can prove or disprove that. The sql would then be

"UPDATE Profile_Table SET [ProfileArchive]= Date() WHERE 
Profile_ID = " & Forms!SelectProfile_Form!ListPicker

assuming there's nothing wrong with your current statement
NOTE: you cannot simply write sql in code with line breaks and no line continuation characters, so don't copy sql that I've written that spans lines. Your one line example is OK.

 

[behedwin]

Let's try the simple things first.
- have you compacted the db recently?

Im not sure... i dont even know what this means! I dont think i have done that.

- one of those sql statements contains the correct name of the form? And that form is open when you try to do this?

i have tried to run the function several times. no other tables/forms are open.

- to dispel any doubt about the form being correctly referenced or being available, try this before CurrentDb.Execute
msgbox Forms!SelectProfile_Form!ListPicker
What do you get?

I have modified the code to what you asked.
This:

Private Sub DeleteProfile_Button_Click()


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



End Sub

Running this code gives me a msgbox displaying the value 6.
6 is the Profile_ID of the selected row in the listbox.
If i select another value and run this code i get that rows Profile_ID.

After the msgbox i get the same error message
This: http://prntscr.com/haufw5


Been googling all day at this.
For some reason it looks like this should work... but i cant figure out why it does not.

 

[xenou]

I'd like to see your sql so amend code to show the sql string:

Private Sub DeleteProfile_Button_Click()

Dim strSQL As String
strSQL = "UPDATE Profile_Table SET [ProfileArchive]= #" & Date & "# WHERE Profile_ID = " & Forms!SelectProfile_Form!ListPicker
MsgBox strSQL
CurrentDb.Execute strSQL

End Sub

What does the messagebox display?

also what are the data types of ProfileArchive and Profile_ID in Profile_Table (look again!)
also check all those names for spelling - sql will require exact correctness in names of tables and fields.

 

[behedwin]

I'd like to see your sql so amend code to show the sql string:

Private Sub DeleteProfile_Button_Click()

Dim strSQL As String
strSQL = "UPDATE Profile_Table SET [ProfileArchive]= #" & Date & "# WHERE Profile_ID = " & Forms!SelectProfile_Form!ListPicker
MsgBox strSQL
CurrentDb.Execute strSQL

End Sub

What does the messagebox display?

also what are the data types of ProfileArchive and Profile_ID in Profile_Table (look again!)
also check all those names for spelling - sql will require exact correctness in names of tables and fields.

The messagebox says this
screenshot: http://prntscr.com/haupbc
when running your code.

ProfileArchive is a date column.
Profile_ID is my autonumber for the table, so i assume it is a number column?


screenshot from the table: http://prntscr.com/hauqlt
screenshot2 from table: http://prntscr.com/hauqyw

can not find any spelling errors or errors with capital letters and so on....


uploaded my project file here: https://ufile.io/yj3q2

 

[xenou]

I don't like the format of date (although ISO standard it's not one that Access usually understands).
How do you usually format dates on your computer?
What do the dates look like in this table?

 

[behedwin]

I don't like the format of date (although ISO standard it's not one that Access usually understands).
How do you usually format dates on your computer?
What do the dates look like in this table?

all my dates look like this
YYYY-MM-DD
ex: 2017-11-15

this is how we type them in my company and in general in sweden

 

[xenou]

You can try Micron's usage instead:

Private Sub DeleteProfile_Button_Click()

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

End Sub

Personally I would also try executing the SQL directly (not in your code, just as a SQL query built using the Query builder in SQL view:

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

 

[behedwin]

I get the exact error with Microns code.

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

How do i try to execute this directly as sql query built using the query builder?
How do i from the query tell that "when user press the button, put todays date in selected record"

 

[xenou]

You can test your query by creating a new query (not in code).

1. Open the database
2. On the create tab hit the button for Query Design (to begin creating a new query)
3. Close the dialog box listing tables
4. Right click on the blank query tab and select "SQL view". You can also get to SQL view by choosing SQL view from the view group on the Home tab
5. Paste in your SQL
6. Hit the Run button on the Query Tools Design tab

You are testing your query string as evaluated and run in your code, but not running the code itself, just the same SQL the code would run, as below:

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