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

MS access- disabeling a user instead of deleting

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+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Question is being moved to the Access forum.
Mar 20 '07 #2

Post your reply

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