Virtual Delete

[pinkdatabase]

I am trying to set up a system where by when a user wants to delete a record it is not deleted as such but flagged as deleted by recording -1 in the table's IsDeleted field. I do not want important information to be totally lost to the database

To do this I have IsDeleted Yes/No in every important table in the application.

When a user hits the delete button in any form I want to call a routine that says check out the name of the current form and update the IsDeleted field to -1.

I thought is should look like this

Public Sub DeleteIt()

Dim strCurrentName As String

strCurrentName = Application.CurrentObjectName

Forms!strCurrentName!IsDeleted = -1

End Sub

but Forms!strCurrentName!IsDeleted doesn't work. Access does not recognize strCurrentName as a Form.

I could put this code in all of the forms but I was hoping to do better than that.

QUESTIONS: How would I do what I am trying above?

What other way can I achieve this type of process of virtual deletion?

Thanks in advance

Greg

 

[Galaxiom]

Disable Deletes in the Form properties.
Add either a bound checkbox for the IsDeleted field or a button to set the value.

If you want to let them use the Delete key then add an OnKeyDown event to run the procedure.

 

[pinkdatabase]

I was trying to do this outside the form in a Module and the problem is to reference and update a checkbox on the "current" form

Getting the name of the form no matter what form is open

Forms!srtCurrentName!IsDeleted does not work

Is there some way to do this outside the form

BTW I didn't mean that the code in my post would be used within the form but a code that does the same thing (as you have suggested)

 

[Galaxiom]

Screen.ActiveForm.Name

 

[Galaxiom]

BTW You can't use a variable for the name like this:
Forms!stringvariable!contolname

To refer to the IsDeleted control on the form using the variable:
Forms(stringvariable)!IsDeleted

In fact you don't even need the IsDeleted control on the form. Access will default to a control if present but will refer to the field in the record source if there is no control by that name.

 

[pinkdatabase]

Thank you

Forms(stringvariable)!IsDeleted

Worked a treat

 

[Benny Lim]

Hope this can be useful for you. (Form)
Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
Dim db As Database
Dim tbl As TableDef
Dim qry As QueryDef
Dim i As Integer

Set db = CurrentDb()
ObjectExists = False

If strObjectType = "Table" Then
For Each tbl In db.TableDefs
If tbl.Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next tbl
ElseIf strObjectType = "Query" Then
For Each qry In db.QueryDefs
If qry.Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next qry
ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next i
ElseIf strObjectType = "Macro" Then
For i = 0 To db.Containers("Scripts").Documents.Count - 1
If db.Containers("Scripts").Documents(i).Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next i
Else
MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
End If
End Function

 

[pinkdatabase]

Thanks for this Have been away

Not sure what the code is doing with respect to virtual deleting

Can you clarify?