MS access- disabeling a user instead of deleting

[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.disa bled="no"

or

select * dispensers
where dispensers.disa bled="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.DISAB LED, 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_C lick()
On Error GoTo Err_PreviewRepo rt_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.cbD ispenser) Then
MsgBox ("Please pick a Dispenser.")
GoTo Exit_PreviewRep ort_Click
End If

stDispId = Form.cbDispense r
stWhere = ""

If IsNull(Form.cbM onthYear) Then
MsgBox ("Please pick a Month")
GoTo Exit_PreviewRep ort_Click
Else
stMonthYear = Form.cbMonthYea r
stYear = Format(CDate("1 " & Form.cbMonthYea r), "yyyy")
End If


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






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

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

Else
stQuery = "Individual Weekly Sales"
stExtra = "DISPENSERS.Dis abled = ""no"" "
stWhere = "SALES.DISPENSE R_ID = " + stDispId + " and format(START_DT ,""MMMM YYYY"") = """ + stMonthYear + """ and "DISPENSERS.Dis abled = ""no"" """""""
End If
ElseIf stRepName = "Company Weekly Sales" Then
stQuery = "Company Weekly Sales"
stExtra = " AND DISPENSERS.Disa bled = ""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.WorkingW eek=-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.WorkingWe ek =-1"
ElseIf stRepName = "Individual Monthly Sales" Then
If dtEndDate <> cLowDate And _
Format(dtEndDat e, "YYYY") < Format(CDate(cb MonthYear), "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 = "DISPENSERI D=" & stDispId & " and SALES.WorkingWe ek = -1"
End If

DoCmd.OpenRepor t stRepName, acPreview, stQuery, stWhere

Exit_PreviewRep ort_Click:
Exit Sub

Err_PreviewRepo rt_Click:
MsgBox Err.Description
Resume Exit_PreviewRep ort_Click

End Sub


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

[MMcCarthy]

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.disa bled="no"

or

select * dispensers
where dispensers.disa bled="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.DISAB LED, 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_C lick()
On Error GoTo Err_PreviewRepo rt_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.cbD ispenser) Then
MsgBox ("Please pick a Dispenser.")
GoTo Exit_PreviewRep ort_Click
End If

stDispId = Form.cbDispense r
stWhere = ""

If IsNull(Form.cbM onthYear) Then
MsgBox ("Please pick a Month")
GoTo Exit_PreviewRep ort_Click
Else
stMonthYear = Form.cbMonthYea r
stYear = Format(CDate("1 " & Form.cbMonthYea r), "yyyy")
End If


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






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

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

Else
stQuery = "Individual Weekly Sales"
stExtra = "DISPENSERS.Dis abled = ""no"" "
stWhere = "SALES.DISPENSE R_ID = " + stDispId + " and format(START_DT ,""MMMM YYYY"") = """ + stMonthYear + """ and "DISPENSERS.Dis abled = ""no"" """""""
End If
ElseIf stRepName = "Company Weekly Sales" Then
stQuery = "Company Weekly Sales"
stExtra = " AND DISPENSERS.Disa bled = ""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.WorkingW eek=-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.WorkingWe ek =-1"
ElseIf stRepName = "Individual Monthly Sales" Then
If dtEndDate <> cLowDate And _
Format(dtEndDat e, "YYYY") < Format(CDate(cb MonthYear), "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 = "DISPENSERI D=" & stDispId & " and SALES.WorkingWe ek = -1"
End If

DoCmd.OpenRepor t stRepName, acPreview, stQuery, stWhere

Exit_PreviewRep ort_Click:
Exit Sub

Err_PreviewRepo rt_Click:
MsgBox Err.Description
Resume Exit_PreviewRep ort_Click

End Sub


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

Question is being moved to the Access forum.