By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

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

P: 56
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.
Mar 20 '07 #1
Share this Question
Share on Google+
14 Replies


P: 56
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.
Mar 21 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
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.
Mar 21 '07 #3

P: 56
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.
Mar 21 '07 #4

NeoPa
Expert Mod 15k+
P: 31,489
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.
Mar 21 '07 #5

P: 56
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.
Mar 21 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 21 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
OK now to answer your question.

When the value of the status field changes to Y. Then add this code to the delete button.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3.    strSQL = "INSERT INTO [Deleted Dispensers] (Field1, Field2, Field3) " & _
  4.        "SELECT Field1, Field2, Field3 FROM Dispensers WHERE Status='Y';"
  5.    DoCmd.RunSQL strSQL
  6.  
  7.    strSQL = "DELETE FROM Dispensers WHERE Status='Y';"
  8.    DoCmd.RunSQL
  9.  

Mary
Mar 21 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 22 '07 #9

P: 56
OK now to answer your question.

When the value of the status field changes to Y. Then add this code to the delete button.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3.    strSQL = "INSERT INTO [Deleted Dispensers] (Field1, Field2, Field3) " & _
  4.        "SELECT Field1, Field2, Field3 FROM Dispensers WHERE Status='Y';"
  5.    DoCmd.RunSQL strSQL
  6.  
  7.    strSQL = "DELETE FROM Dispensers WHERE Status='Y';"
  8.    DoCmd.RunSQL
  9.  

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??
Mar 22 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Should be ...
Expand|Select|Wrap|Line Numbers
  1. Private Sub pb_Delete_Click()
  2. On Error GoTo Err_pb_Delete_Click
  3. Dim strSQL As String
  4.  
  5.    strSQL = "INSERT INTO [Deleted Dispensers] (FORNAME, SURNAME, ADDR1, ADDR2, ADDR3, ADDR4, PostCode, Home_No, Mobile_No, StartDate, FullTime, EndDate, Disabled) " & _
  6.        "SELECT FORNAME, SURNAME, ADDR1, ADDR2, ADDR3, ADDR4, PostCode, Home_No, Mobile_No, StartDate, FullTime, EndDate, Disabled FROM Dispensers WHERE Status='Y';"
  7.    DoCmd.RunSQL strSQL
  8.  
  9.    strSQL = "DELETE FROM Dispensers WHERE Status='Y';"
  10.    DoCmd.RunSQL strSQL
  11.  
  12.     Exit_pb_Delete_Click:
  13.     Exit Sub
  14.  
  15. Err_pb_Delete_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_pb_Delete_Click
  18.  
  19. End Sub
  20.  
Mary
Mar 22 '07 #11

P: 56
Should be ...
Expand|Select|Wrap|Line Numbers
  1. Private Sub pb_Delete_Click()
  2. On Error GoTo Err_pb_Delete_Click
  3. Dim strSQL As String
  4.  
  5.    strSQL = "INSERT INTO [Deleted Dispensers] (FORNAME, SURNAME, ADDR1, ADDR2, ADDR3, ADDR4, PostCode, Home_No, Mobile_No, StartDate, FullTime, EndDate, Disabled) " & _
  6.        "SELECT FORNAME, SURNAME, ADDR1, ADDR2, ADDR3, ADDR4, PostCode, Home_No, Mobile_No, StartDate, FullTime, EndDate, Disabled FROM Dispensers WHERE Status='Y';"
  7.    DoCmd.RunSQL strSQL
  8.  
  9.    strSQL = "DELETE FROM Dispensers WHERE Status='Y';"
  10.    DoCmd.RunSQL strSQL
  11.  
  12.     Exit_pb_Delete_Click:
  13.     Exit Sub
  14.  
  15. Err_pb_Delete_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_pb_Delete_Click
  18.  
  19. End Sub
  20.  
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
Mar 22 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 23 '07 #13

P: 56
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??
Mar 23 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Expand|Select|Wrap|Line Numbers
  1. strSQL = "DELETE * FROM Dispensers WHERE Status='Y';"
  2. DoCmd.RunSQL strSQL
  3.  
Mary
Mar 23 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.