Attempting to create a report that produces batch letters 1

[rakhasa]

Hi all

First post so be gentle

I've currently got a temp job building an Access DB for client to include details of various contacts, site names etc. This will be used to craete batch letters, send out reports and the like.

I'm unfortunately stuck at the batch letters. I've built a query that's accessing various tables and outputting to a formatted report. Letters are accessed by a pop-up form that ask for user input on two combo boxes before previewing or printing.

Problem is that upon selecting both options and clicking either preview or print the form does not disappear and the report hangs in the background, only disappearing when I cancel the form.

When I run this report direct from the query I get two pop up dialogue boxes asking for me to input the exact text from the tables its referencing but, if entered correctly, this way works fine.

The code I'm using behind the report is below. I don't have any behind the form itself, which may be the problem (???)

Hedlp would be appreciated as the temp job is running out and I want to have produced something...


Option Compare Database ' Use database order for string comparisons.
Option Explicit ' Requires variables to be declared before they are used.

' This variable set to True while the Report_Open event is executing.
Public blnOpening As Boolean

Private Sub Report_Open(Cancel As Integer)
' Open Form1 form.
' IsLoaded function (defined in Utility Functions module) determines
' if specified form is open.

Dim strDocName As String

strDocName = "Form1"
' Set public variable to True so Form1 knows that report
' is in its Open event.
blnOpening = True

' Open form.
DoCmd.OpenForm strDocName, , , , , acDialog


' If Form1 form isn't loaded, don't preview or print report.
' (User clicked Cancel button on form.)
If IsLoaded(strDocName) = False Then Cancel = True

'Set public variable to False, signifying that Open event is finished.
blnOpening = False
End Sub



Private Sub Report_Close()
' Close the Form1 form.

Dim strDocName As String

strDocName = "Form1"

DoCmd.Close acForm, strDocName

End Sub

 

[evilmousse]

I'm wildly curious what the blnopening datamember accomplishes..

but if i understand right, the problem is that
the popup form remains in control of the screen
even after the report is opened.

in the onopen event of the report, put
forms!form1.visible = false

and then close the form as you already do
in the onclose event of the report.

this way it SEEMS as if the form is closed
to the user, but you can still reference it
as you need to programaticaly, as it's still
technically open.

-g

 

[rakhasa]

"I'm wildly curious what the blnopening datamember accomplishes.."


Heh, spot the rookie. And by that I mean me.

OK, I'm trying your advice but so far no joy. I'm hgetting unknown macro errors.

Is there a particular point of insertion for forms!form1.visible = false ?

 

[rakhasa]

Is there a problem with section of the code

DoCmd.OpenForm strDocName, , , , , acDialog

which seems to *require* that the form be closed before the code will continue?

 

[danvlas]

The sequence is not OK.

It's the form that should open the report, not the other way round. User won't see the difference, but you will make it a lot easier.

You should open the form from a button on your switchboard or something like that (again...NOT from the report). In this way you can open it in Dialog mode.

The form would have some user input controls and an OK button.

The button would set form's visibility to False and open the report:

Private Sub OKButton_Click()
Me.Visible = False
DoCmd.OpenReport blah blah blah
End Sub

In this way, the report is displayed in the foreground.

The report could close or display the form (which is hidden), depending on what you want to do next in its Close event:

Private Report_Close()
DoCmd.Close acForm, "YourFormName"
'Forms![YourFormName].Visible = True
End Sub

You are right. Opening a form in dialog mode stops the calling code until dialog is closed. That's an excellent thing sometimes.

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[rakhasa]

Thanks Dan. That seems to have done the trick.

Now to sort the problem with my underlying query. THe road goes ever on....

 

[rakhasa]

I'm still having a problem. The report preview is now loading up fine upon clicking the preview button but no data appears to be being passed to the query and thus the fields that should be filled in are blank. Code behind the preview button is, as suggested:

Private Sub PreviewReport_Click()
Me.Visible = False

Dim stDocName As String

stDocName = "rBatchLetter"

DoCmd.OpenReport stDocName, acPreview
End Sub

The form has two combo boxes attached to a table each showing DISTINCTROWs and within my query its asking for input from these boxes by criteria =[Forms]![Form1]![Title]

Still no joy however on getting the data to pass thru.

 

[danvlas]

The report is based on a query that asks for X parameters, right?
The parameters are displayed in the dialog form, right?
The form is open, right?

If all answers are 'Yes', set the criteria in your query as:
Forms![DialogFormName]![AppropriateTextBoxName]

And then run the sequence. You shouldn't be prompted for parameters and the report should have data (of course, if the query returns any data).




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[rakhasa]

Thanks again Dan, but with this bit thats why I'm confused.

Report is based upon query that asks for JobTitle & LetterName, plus includes address details set up as fields in query whcih are also named in report.

Parameters are displayed in form, yes.

Form is open, yes.

Criteria in my query is already set as you suggest and the combo box names are correct.

When I run the query alone and fill in the Forms!Form!JobTitle etc boxes that popup it shows me the results OK>

When I run the form is happily opens the report but doesn't fill in the text boxes with the information gleaned.

Report IS based on the right query and the text boxes DO have the same name as fileds in the query so I don't get it...

 

[rakhasa]

I'm guessing that I need to requery the form....??

 

[rakhasa]

One requery macro later and all is good.

Thanks for all your help.

 

[evilmousse]

>Report IS based on the right query and the text
>boxes DO have the same name as fileds in the query
> so I don't get it...

auuugh nonono, that can cause problems all over
the place. at least call them &quot;txt_<datamembername>&quot;

you needed to requery? i won't argue if it
works, but i wonder why.

you know that in your recordsource setting
command, the form calls and the rest of the
sql command need to be string manip'd..
as in:
bad = &quot;select * from forms!form1!txt_from where...&quot;
good = &quot;select * from &quot; & forms!form1!txt_from & &quot;where..&quot;

 

[rakhasa]

Its 4pm on Friday afternoon.... my Caring Quotient is dropping by the second ;-)

Thanks anyway evilmouse