MS access

[DavidOwens]

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

the above code is for when a preview report button is pushed in the main menu.

basically what i wanted the button to do, ws to show the corosponding reports, but it runs and a parameter box opens and asks for DISPENSER.DISABLED, iv tried defining in the code, but to no joy, sometimes i get the error, variable not defined, sometimes type mismatch, its driving me crazy now.

basically the DISPENSER.DISABLED colum ws added so that when a dispenser is deleted, they are just put as disabled and not deleted off completly,any help would be a plus, sorry if im not clear, its first time on a forum.

many thanks

[Rabbit]

Can you post the metadata of your table?

Table Name
 [Field Name]; Data Type; PK/FK

[DavidOwens]

Can you post the metadata of your table?

Table Name
 [Field Name]; Data Type; PK/FK

metadata?

surename - text
formename - text
ID-auto
addr - text
post code - text
home no - text
start date date/time
full time - tes/no
end date - date time
disabled - text


PERIOD table

start_dt date/time
end_dt date/time
id - aut
working week - yes/no

Sales

ID- AUTO
Dispenser ID- number
perido_id- number
sales- number
sales_units- number
sales_value- number
philips- number
fitted- number
fitted_units- number
fitted_value- number
done- number
booked- number
booked_next - number
workingweek - yes/no

there is the SQL of the dispenser NOT disabled query

SELECT *
FROM DISPENSERS
WHERE Disabled="No"
ORDER BY DISPENSERS.Disabled;

but i just dont know how to stop the PARAMETER BOX appearing, or if it does appear, then for it to show the right info if the right text is entered.

[Rabbit]

I haven't taken a look yet but a parameter box means that it found a variable that does not exist in your table or that you have not created so it brings up the box for the user to define the unknown variable.

[DavidOwens]

I haven't taken a look yet but a parameter box means that it found a variable that does not exist in your table or that you have not created so it brings up the box for the user to define the unknown variable.

well if you could please take a look, yeah i was thinkin that was wt ws up.
but im not quite sure how to define a variable in the visual basic build??

[Rabbit]

metadata?

surename - text
formename - text
ID-auto
addr - text
post code - text
home no - text
start date date/time
full time - tes/no
end date - date time
disabled - text


PERIOD table

start_dt date/time
end_dt date/time
id - aut
working week - yes/no

Sales

ID- AUTO
Dispenser ID- number
perido_id- number
sales- number
sales_units- number
sales_value- number
philips- number
fitted- number
fitted_units- number
fitted_value- number
done- number
booked- number
booked_next - number
workingweek - yes/no

there is the SQL of the dispenser NOT disabled query

SELECT *
FROM DISPENSERS
WHERE Disabled="No"
ORDER BY DISPENSERS.Disabled;

but i just dont know how to stop the PARAMETER BOX appearing, or if it does appear, then for it to show the right info if the right text is entered.

Assuming that first table you posted is called dispensers and that dispensers is a text field can be either "No" or "Yes", why would you order by the disabled field?