problem with form...

[Guest]

Hi!

I'm making a database on fire hydrants of a city. I have 2 main tables: one
with the characteristics of the hydrant (ID, emplacement, model,etc.) and one
with interventions made by employees on this hydrant. This table contains the
hydrant id, a date field, many yes/no fields (about 20) that describe the
problems and interventions (problem to the valve, problem to the bolts,... ,
interior washed, drained,... and a functional field).

I want to make a user friendly form that will ask the user what kind of
report he wants to print. For example, he will choose between the list of
yes/no fields, like problem to the valve, and this will print a report with
the hydrant numbers and their emplacement based on a query that finds the
hydrants with a valve problem at the latest intervention. I already designed
the code for that kind of query, but I have over 20 fields.

Right now, the only solution that I have in mind is to create about 20
queries that will return me the hydrants with certain problems. But I will
have to do 20 reports too. The code for the queries is the same except that
the yes/no fields are different. The code of the report is the same except
for the title, which is the name of the intervention/problem of the hydrant.

I'd like to known if there is a way to make only one query and one report,
that will automatically change the code of the query and the report depending
of the option chosen or do I have to create those 20 queries ?.?.?

Before the printing, I want another form that will pop up and count the
total number of hydrant with these problems, (if there are none, the user
won' have to print a blank page...) will I have to create 20 forms too or can
I do this automatically ?

Thanks a lot

Louis Pat

 

[Nikos Yannacopoulos]

If i get this right, the fields are always the same, and what changes is
the filters? If yes, you only need one report, and one query, or no
(saved) query at all. Set the report's recordsource to your table or
single saved query, and in the code behinf the command button on the
form which opens the report, construct a Where statement, based on the
user's selections on the form; then use this string in the
WhereCondition argument in the DoCmd.OpenReport.

On your other question, Also, put this code in the report's On No Data
event:

msg = "No data exists for the chosen criteria."
typ = vbExclamation
ttl = "Report cannot be generated"
MsgBox msg, typ, ttl
Cancel = True

This will produce a message for the user and cancel opening the report,
if there is no data.

HTH,
Nikos

 

[Guest]

Thanks... this should work.
But I have a problem getting my code right in the DoCmd.OpenReport in the
WhereCondition

in the queries that I did, the criteria was I used
WHERE ((([intervention hydrant].[Date interv])=(SELECT MAX (tmp.[Date
interv]) FROM [intervention hydrant] as tmp WHERE tmp.[hydrant id] =
[intervention hydrant].[hydrant id])) AND (([intervention
hydrant].[Functional])=True));

for the case where I'd like to get the functional hydrant at the latest date
of intervention

how do I write this in the DoCmd.OpenReport?

Thanks again!

 

[Nikos Yannacopoulos]

Louis,

I unerstand you are trying to filter records for all [hydrant id]'s,
getting the one record for each with the most recent date? Then you
don't need a filter a with a subwuery, you just need a query with a
Totals function, selecting Group By function on all other fields, and
Max on the date field. Make the select query, and copy the SQL statement
from the query's SQL view.


HTH,
Nikos

 

[Guest]

Hi!

My queries didn't work the way I wanted when I did what you tell me. (It was
giving me both functional and non functional most recent dates for an
hydrant). Anyway, I got it to work by making 2 queries and by inspiring from
your first message... Thanks a lot!

But I have another question... When my report appears, I want the title of
the report to automatically change depending upon the criteria... How can I
do that?

(ex. when my filter is filtering by functionality, I want the title to be:
Functional hydrant (it is the name of the field Which I'm filtering)

THANK you

LouisPat

 

[Nikos Yannacopoulos]

My queries didn't work the way I wanted when I did what you tell me. (It was
giving me both functional and non functional most recent dates for an
hydrant).

That's because you included a field in the Totals query whose values
were not unique for all records with the same hydrant id. You could
either skip this field, or, if you needed it, either pick it up with a
DLookup or (my preferred solution, in the interest of performace) do it
in two steps: in the first query include only the fields that would
produce unique records by grouping, then use that query and the original
record in a second query, join on hydrant id and date (I assume those
two would identify a record uniquely) and get all the fields from the
first query, and the extra ones like functionla/non-functional from the
table.

Anyway, I got it to work by making 2 queries and by inspiring from
your first message... Thanks a lot!

Is what you did similar to what I described above?

But I have another question... When my report appears, I want the title of
the report to automatically change depending upon the criteria... How can I
do that? (ex. when my filter is filtering by functionality, I want the title to be:
Functional hydrant (it is the name of the field Which I'm filtering)

Suppose you have a label named Label1 in the report header; put some
simple code in the report header's Format event to look at the
selections on the form, and set the label caption accordingly, something
like:

If Forms![MyFormName]!chkFunctionality = True Then
Me.Label1.Caption = "Functional hydrant"
Else
Me.Label1.Caption = "Non-Functional hydrant"
End If

This is probably simpler that what you really need, but I hope it gives
you the idea.

Nikos

 

[Guest]

Hi!

I based my queries in a similar way to the one you described... I created
one query named [last visit] that finds the latest visit. My second query
[AAA] based on this one and on the original table looks for the different
checked fields [functional], [valve problem],[leak],[slit] ... <- all check
buttons
I have more than 20 check fields (more than 20 kinds of search),

I want my form to look this way: the user chooses from the list of check
buttons an can either preview the report or print the report. This is the
code I designed for my form:

Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
Select Case Me!ReportToPrint
Case 1
CHANGE THE TITLE OF THE REPORT
DoCmd.OpenReport "REPORT1", PrintMode,, [AAA].[functional]=true
Case 2
CHANGE THE TITLE OF THE REPORT
DoCmd.OpenReport "REPORT1", PrintMode,,[AAA].[valve problem]=true
........etc.
End Select
DoCmd.Close acForm, "form1"
Exit_Preview_Click:
Exit Sub
Err_Preview_Click:
Resume Exit_Preview_Click
End Sub
//////////////////
Case 1 is the case where the user checked functional...
Case 2 :valve problem...etc.

Question1 : Is it possible to change the label name of the title in my
report directly from the form? If yes, what's the code? I have tried
Report1.Title.Caption = "functional hydrants"
but it's not working...

Question2: how come my condition in DoCmd does not work?... my report is
based on the [AAA] query and that's my filtering condition.

Thanks for helping me Nikos!

Louis Pat

 

[Nikos Yannacopoulos]

Question1 : Is it possible to change the label name of the title in my
report directly from the form? If yes, what's the code? I have tried
Report1.Title.Caption = "functional hydrants"
but it's not working...

Yes, you can do it, but only after the report has been opened, not before!

Question2: how come my condition in DoCmd does not work?... my report is
based on the [AAA] query and that's my filtering condition.

If you look up the OpenReport method in help, you will see that the
WhereCondition argument is a string; you need to enclose it in quotes.

The above comments would result in your code in each case looking
something like:

DoCmd.OpenReport "REPORT1", PrintMode,, "[functional]=true"
Reports!Report1.Title.Caption = "functional hydrants"

(I suppose Title is the name of the label in your report header?)

Alternatively, you could use two local variables, one for the
WhereCondition string and one for the report title, set those in the
Select Case structure, and use a single DoCmd.OpenReport after it:

Sub PrintReports(PrintMode As Integer)
Dim sWhere As String
Dim sTitle As String
On Error GoTo Err_Preview_Click
Select Case Me!ReportToPrint
Case 1
sWhere = "[functional]=true"
sTitle = "functional hydrants"
Case 2
sWhere = "[valve problem]=true"
sTitle = "hydrants with a valve problem"
........etc.
End Select
DoCmd.OpenReport "REPORT1", PrintMode,, sWhere
Reports!Report1.Title.Caption = sTitle
DoCmd.Close acForm, "form1"
Exit_Preview_Click:
Exit Sub
Err_Preview_Click:
Resume Exit_Preview_Click
End Sub

HTH,
Nikos

 

[Nikos Yannacopoulos]

Hey, I wasn't thinking!!! By the time the code execution gets to try to
set the title the report is already open (preview or rpint), and it's
too late to do anything with the label caption. The way to do it is to
use the OpenArgs argument of OpenReport to apss on the required title,
and set the label caption in the report's Open event by reading it. So,
the code for the PrintReports sub should be:

Sub PrintReports(PrintMode As Integer)
Dim sWhere As String
Dim sTitle As String
On Error GoTo Err_Preview_Click
Select Case Me!ReportToPrint
Case 1
sWhere = "[functional]=true"
sTitle = "functional hydrants"
Case 2
sWhere = "[valve problem]=true"
sTitle = "hydrants with a valve problem"
........etc.
End Select
DoCmd.OpenReport "REPORT1", PrintMode,, sWhere,,sTitle
DoCmd.Close acForm, "form1"
Exit_Preview_Click:
Exit Sub
Err_Preview_Click:
Resume Exit_Preview_Click
End Sub

And the code in the report's Open event should be:

Private Sub REPORT1_Open(Cancel As Integer)
Me.Title.Caption = OpenArgs
End Sub

Sorry about the confusion!

HTH,
Nikos

 

[Guest]

Hi Nikos!

Thanks, that should work.... The only problem is that the openreport method
have no openargs arguments... The syntax is
DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]
while the syntax for the openform method is
DoCmd.OpenForm formname[, view][, filtername][, wherecondition][,
datamode][, windowmode][, openargs]

(I'm using access 2000)
Should I create a form and printing it or there is another solution?

Thanks, You are the best!
Louis Pat

 

[Nikos Yannacopoulos]

Louis,

You're absolutely right, no OpenArgs in OpenReport in A2K (I have A2K3).
I suppose the best way around it is to remove the sTitle value
assignment from the PrintReports sub, open the report without it, and
use a Select Case for it in the report's Open event, referencing the
ReportToPrint contol on the form... so the On Open event would look
something like:

Private Sub REPORT1_Open(Cancel As Integer)
Dim sTitle As String
Select Case Forms![TheFormNameHere]!ReportToPrint
Case 1
sTitle = "functional hydrants"
Case 2
sTitle = "hydrants with a valve problem"
........etc.
End Select
Me.Title.Caption = sTitle
End Sub

Alternatively you could declare sTitle as a public variable in a general
module and still set its value in the PrintReports sub, then in the
report Open event simply Me.Title = sTitle.
Generally speaking, it's recommnded to avoid the use of public
variables, as they have this nasty habit to reset if an untrapped error
occurs in the code, but this case is so simple, and the variable value
used only once, immediately after it is assigned, that I would not
consider this a major issue.

Hey, come to think of it, ReportToPrint returns a numerical value, but
it doesn't display that, I suppose, it's most likely a combo or listbox
showing its second column with a description to the user, right? If
that's the case, then you could change the Title label to a textbox in
the report, and set its controlsource property to:

Forms![TheFormNameHere]!ReportToPrint.Column(1)

so it reads the description right off the combo or list box, no code
required!

Nikos