PC Review


Reply
Thread Tools Rate Thread

problem with form...

 
 
=?Utf-8?B?TG91aXNQYXQ=?=
Guest
Posts: n/a
 
      29th Jun 2005
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



 
Reply With Quote
 
 
 
 
Nikos Yannacopoulos
Guest
Posts: n/a
 
      29th Jun 2005
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
 
Reply With Quote
 
 
 
 
=?Utf-8?B?TG91aXNQYXQ=?=
Guest
Posts: n/a
 
      29th Jun 2005
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" wrote:

> 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
>

 
Reply With Quote
 
Nikos Yannacopoulos
Guest
Posts: n/a
 
      30th Jun 2005
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
 
Reply With Quote
 
=?Utf-8?B?TG91aXNQYXQ=?=
Guest
Posts: n/a
 
      30th Jun 2005
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" wrote:

> 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
>

 
Reply With Quote
 
Nikos Yannacopoulos
Guest
Posts: n/a
 
      1st Jul 2005

> 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
 
Reply With Quote
 
=?Utf-8?B?TG91aXNQYXQ=?=
Guest
Posts: n/a
 
      4th Jul 2005
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
 
Reply With Quote
 
Nikos Yannacopoulos
Guest
Posts: n/a
 
      5th Jul 2005

> 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
 
Reply With Quote
 
Nikos Yannacopoulos
Guest
Posts: n/a
 
      5th Jul 2005
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
 
Reply With Quote
 
=?Utf-8?B?TG91aXNQYXQ=?=
Guest
Posts: n/a
 
      5th Jul 2005
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" wrote:

> 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
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
problem after problem after problem - winXP wont start! =?Utf-8?B?TWF2aXJpY2s=?= Windows XP Help 2 23rd Apr 2006 02:55 PM
problem after problem after problem =?Utf-8?B?VGhlIEF1c3NpZSBHaXJs?= Windows XP Performance 3 31st Mar 2005 12:23 PM
problem problem problem:( Need Help =?Utf-8?B?TWlrZQ==?= Microsoft Dot NET 0 7th May 2004 10:46 PM
hibernation problem! problem!, Power Option problem! Farzad Hayati Microsoft Windows 2000 Hardware 2 5th Feb 2004 11:22 PM
hibernation problem! problem!, Power Option problem! Farzad Hayati Microsoft Windows 2000 Advanced Server 2 5th Feb 2004 11:22 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:10 AM.