When a Record deleted, WOULD like it to go into a DELETED TABLE

[DavidOwens]

Hi there everybody

iv designed a database, curently when the delete button is pushed, it deletes the record completly, but i dont want that i want it to just disable the dispenser.

iv crreated, a column in the dispenser table and put the disabled sipensers to NO or Y, i have created a disabled and NOT disabled query.

select * dispensers
where dispensers.disabled="no"

or

select * dispensers
where dispensers.disabled="Y".

so i have also, but this bit of sql in other queries, were i ws to just display the dispensers who are active, but when i go to a report and click privew, a PARAMETER BOX opens and asks for DISPENSER.DISABLED, i ut in Y or NO, but it just does the same thing or errors,

below is the code for when the preview button is pushed

Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click

Dim stRepName As String: Rem Holds the Report name
Dim stDispId As String: Rem Holds the Dispenser ID
Dim stQuery As String: Rem Holds the Query name
Dim stWhere As String: Rem Holds the where clause
Dim stExtra As String:
Dim stMonthYear, stYear, stWeekCount As String
Dim intX As Integer, rst As Recordset
Dim dtEndDate As Date

stRepName = Form.cbReports

If IsNull(Form.cbDispenser) Then
MsgBox ("Please pick a Dispenser.")
GoTo Exit_PreviewReport_Click
End If

stDispId = Form.cbDispenser
stWhere = ""

If IsNull(Form.cbMonthYear) Then
MsgBox ("Please pick a Month")
GoTo Exit_PreviewReport_Click
Else
stMonthYear = Form.cbMonthYear
stYear = Format(CDate("1 " & Form.cbMonthYear), "yyyy")
End If


dtEndDate = Nz(DMax("EndDate", "Dispensers", "[ID] = " & stDispId), cLowDate)






If stRepName = "Weekly Dispenser Sales" Then
stQuery = "Weekly Dispenser Sales"
stWhere = "format(START_DT,""MMMM YYYY"") = """ + stMonthYear + """"
ElseIf stRepName = "Individual Weekly Sales" Then: Rem Sheet 8
If dtEndDate <> cLowDate And _
Format(dtEndDate, "YYYYMMDD") < Format(CDate(cbMonthYear), "YYYYMMDD") Then

MsgBox ("There are no records for this Dispenser in this month")
Exit Sub

Else
stQuery = "Individual Weekly Sales"
stExtra = "DISPENSERS.Disabled = ""no"" "
stWhere = "SALES.DISPENSER_ID = " + stDispId + " and format(START_DT,""MMMM YYYY"") = """ + stMonthYear + """ and "DISPENSERS.Disabled = ""no"" """""""
End If
ElseIf stRepName = "Company Weekly Sales" Then
stQuery = "Company Weekly Sales"
stExtra = " AND DISPENSERS.Disabled = ""no"" "
stWhere = "MonthYear= """ + stMonthYear + """" + stExtra
ElseIf stRepName = "Company Monthly Sales" Then
stQuery = "Company Monthly Sales"
stWhere = "Year= " & stYear
ElseIf stRepName = "Company Year End" Then
stQuery = "Company Year End"
stWhere = "SALES.WorkingWeek=-1"
ElseIf stRepName = "Dispenser Comparison Monthly" Then: Rem Sheet 11
stQuery = "Dispenser Comparison Monthly"
stWhere = "MonthYear= """ + stMonthYear + """"
ElseIf stRepName = "Dispenser Comparison Yearly" Then
stQuery = "Dispenser Comparison Yearly"
stWhere = "Year= " & stYear & " and SALES.WorkingWeek =-1"
ElseIf stRepName = "Individual Monthly Sales" Then
If dtEndDate <> cLowDate And _
Format(dtEndDate, "YYYY") < Format(CDate(cbMonthYear), "YYYY") Then

MsgBox ("There are no records for this Dispenser in this year")
Exit Sub
Else
stQuery = "Individual Monthly Sales"
stWhere = "DISPENSER_ID=" & stDispId & " and Year = " & stYear
End If
ElseIf stRepName = "Individual Year End" Then
stQuery = "Individual Year End"
stWhere = "DISPENSERID=" & stDispId & " and SALES.WorkingWeek = -1"
End If

DoCmd.OpenReport stRepName, acPreview, stQuery, stWhere

Exit_PreviewReport_Click:
Exit Sub

Err_PreviewReport_Click:
MsgBox Err.Description
Resume Exit_PreviewReport_Click

End Sub


HELP ME GUYS...............HEEEELLLLPPPP. i have tried defining DISPENSER.DISABLED as stEXTRA as you can see, but im not sure how to write the syntax or even if im doing it right.

[DavidOwens]

Hey Guys, im having trouble doign something

i have a table of dispensers, also have a main menu, on the menu it has a button were you can amend dispensers, also delete them.

but instead of deleting the member, i would like it to take the member out of the dispenser table and inseart it into a deleted dispenser table, any ideas?

please help, my brain is rattled. cheers guys and girls.

[missinglinq]

Use an Append Query to add the record to the "Delete" table, then delete the record! Depending on your situation, you might consider adding a field to the Delete table for the deletion date. Set the Default Value in the field's definition to Now() or Date() depending on whether you want just the date or date and time.

[DavidOwens]

Use an Append Query to add the record to the "Delete" table, then delete the record! Depending on your situation, you might consider adding a field to the Delete table for the deletion date. Set the Default Value in the field's definition to Now() or Date() depending on whether you want just the date or date and time.

lol thats a bit over my head:(:(, im new to all this, just really want the dispense moved from the dispenser table to dispsenser deleted table when delete is clicked.

[NeoPa]

I started to read this, then saw why there was no reply posted.
Code, when provided, needs to be in tags, readable, no longer than necessary and is an accompaniment to a well explained question (See POSTING GUIDELINES: Please read carefully before posting to a forum). Your explanation skips from one unexplained concept to another, mid-sentence.
I suggest if you want a response, you consider adding another post in here that gives our experts at least a fighting chance of helping you.

MODERATOR.

[DavidOwens]

i have a main form that loads up when my database loads, when you click the delete dispenser button, it changes the value of the dispenser to Y meaning its beeen disabled, but i would like it to go into a table called disabled dispensers and disappear from the dispensers table.

ideas please guys?? if you require to see some code, i havent got any here now, but will post when i get back to work 2morrow.

thanks guys.

[MMcCarthy]

i have a main form that loads up when my database loads, when you click the delete dispenser button, it changes the value of the dispenser to Y meaning its beeen disabled, but i would like it to go into a table called disabled dispensers and disappear from the dispensers table.

ideas please guys?? if you require to see some code, i havent got any here now, but will post when i get back to work 2morrow.

thanks guys.

Hi David

I'm afraid double posting of questions is frowned on so I'm going to merge this with the previous question.

Mary

[MMcCarthy]

OK now to answer your question.

When the value of the status field changes to Y. Then add this code to the delete button.

Dim strSQL As String
 
   strSQL = "INSERT INTO [Deleted Dispensers] (Field1, Field2, Field3) " & _
       "SELECT Field1, Field2, Field3 FROM Dispensers WHERE Status='Y';"
   DoCmd.RunSQL strSQL
 
   strSQL = "DELETE FROM Dispensers WHERE Status='Y';"
   DoCmd.RunSQL
 

Mary

[MMcCarthy]

Not to disagree with the moderator in any way but I'm merging this post as I did the previous one. This is three posts for one question David. Stick to one post in future and if you are not getting attention after 24 hours then post a reply to it to bump it up the list.

ADMIN

[DavidOwens]

OK now to answer your question.

When the value of the status field changes to Y. Then add this code to the delete button.

Dim strSQL As String
 
   strSQL = "INSERT INTO [Deleted Dispensers] (Field1, Field2, Field3) " & _
       "SELECT Field1, Field2, Field3 FROM Dispensers WHERE Status='Y';"
   DoCmd.RunSQL strSQL
 
   strSQL = "DELETE FROM Dispensers WHERE Status='Y';"
   DoCmd.RunSQL
 

Mary

HI Mary

thanks for the help so far, but i have tried that as you can see below

Private Sub pb_Delete_Click()


On Error GoTo Err_pb_Delete_Click

Dim strSQL As String

strSQL = "INSERT INTO [Deleted Dispensers] (FORNAME, SURNAME, ADDR1, ADDR2, ADDR3, ADDR4, PostCode, Home_No, Mobile_No, StartDate, FullTime, EndDate, Disabled) " & _
"SELECT FORNAME, SURNAME, ADDR1, ADDR2, ADDR3, ADDR4, PostCode, Home_No, Mobile_No, StartDate, FullTime, EndDate, Disabled FROM Dispensers WHERE Status='Y';"
DoCmd.RunSQL strSQL

strSQL = "DELETE FROM Dispensers WHERE Status='Y';"
DoCmd.RunSQL


Exit_pb_Delete_Click:
Exit Sub

Err_pb_Delete_Click:
MsgBox Err.Description
Resume Exit_pb_Delete_Click


but i get an error COMPILE ERROR - ARGUMENT NOT OPTIONAL and it points back to this line DoCmd.RunSQL????

any ideas??

[MMcCarthy]

Should be ...

Private Sub pb_Delete_Click()
On Error GoTo Err_pb_Delete_Click
Dim strSQL As String
 
   strSQL = "INSERT INTO [Deleted Dispensers] (FORNAME, SURNAME, ADDR1, ADDR2, ADDR3, ADDR4, PostCode, Home_No, Mobile_No, StartDate, FullTime, EndDate, Disabled) " & _
       "SELECT FORNAME, SURNAME, ADDR1, ADDR2, ADDR3, ADDR4, PostCode, Home_No, Mobile_No, StartDate, FullTime, EndDate, Disabled FROM Dispensers WHERE Status='Y';"
   DoCmd.RunSQL strSQL
 
   strSQL = "DELETE FROM Dispensers WHERE Status='Y';"
   DoCmd.RunSQL strSQL
 
    Exit_pb_Delete_Click:
    Exit Sub
 
Err_pb_Delete_Click:
    MsgBox Err.Description
    Resume Exit_pb_Delete_Click
 
End Sub
 

Mary

[DavidOwens]

Should be ...

Private Sub pb_Delete_Click()
On Error GoTo Err_pb_Delete_Click
Dim strSQL As String
 
   strSQL = "INSERT INTO [Deleted Dispensers] (FORNAME, SURNAME, ADDR1, ADDR2, ADDR3, ADDR4, PostCode, Home_No, Mobile_No, StartDate, FullTime, EndDate, Disabled) " & _
       "SELECT FORNAME, SURNAME, ADDR1, ADDR2, ADDR3, ADDR4, PostCode, Home_No, Mobile_No, StartDate, FullTime, EndDate, Disabled FROM Dispensers WHERE Status='Y';"
   DoCmd.RunSQL strSQL
 
   strSQL = "DELETE FROM Dispensers WHERE Status='Y';"
   DoCmd.RunSQL strSQL
 
    Exit_pb_Delete_Click:
    Exit Sub
 
Err_pb_Delete_Click:
    MsgBox Err.Description
    Resume Exit_pb_Delete_Click
 
End Sub
 

Mary

that amlost works, just had to change status to diabled, but not i want the dispensers table to be updated and the dispensers that have been disbled to be taken out of der

i know the code is something like

UPDATE Dispensers SET and the field names??? but not sure.

but you have been very helpful so far,thank you

[MMcCarthy]

that amlost works, just had to change status to diabled, but not i want the dispensers table to be updated and the dispensers that have been disbled to be taken out of der

i know the code is something like

UPDATE Dispensers SET and the field names??? but not sure.

but you have been very helpful so far,thank you

David

The second DELETE statement should have deleted them from the dispensers table.

Mary

[DavidOwens]

hi, no it doesnt delete, it just copies them into the deleted dispensers table.but leaves them in the dispensers, is that because the UPDATE is not used??

[MMcCarthy]

hi, no it doesnt delete, it just copies them into the deleted dispensers table.but leaves them in the dispensers, is that because the UPDATE is not used??

Try changing it to this

strSQL = "DELETE * FROM Dispensers WHERE Status='Y';"
DoCmd.RunSQL strSQL
 

Mary