Passing parameters to a Report

[Garry in Perth]

Hi All,

This is my first post , so please be gentle with me.

I have two parameters (entered via two unbound text boxes on a form) that define the start and end points in a range of supposedly sequential records. A query is run to check for "missing" records within the pre-defined range. A report then prints the missing records indentifier. How do I get the Start and End parameters to print in the Header of my report.

For example, I enter Record #23 and Record #56 as my Start and Finish parameters. My report tells me that three records - Records #28, #39 and #51 - are missing, but my report doesn't show me that I searched between Records #23 and #56.

I have tried adding Forms![Missing Records form]![Starting Record]
and Forms![Missing Records form]![Ending Record] onto my report but I get an error.

Is it because my Missing Records form is getting closed down before my Report is called. If so, how do I keep it open? Preferably without VBA programming!

TIA

 

[bat17]

Hello and welcome to the board

What you have appears correct. I dont see that your form could be closed before the report runs as the query must be accessing it to get its criteria.

I would double check your spelling to make sure that no errors have crept in there.

Exactly what is the error msg you get as this may give us a clue?

How are you opening the report?

Peter

 

[Garry in Perth]

Hi Peter,

Thanks for your prompt reply.

1. SPELLING
Here are the cut-and-pastes of the two Text Boxes in my Report Header:-
=[Forms]![Bulk Docket Range (Missing Dockets) sub-form]![cmbStart]
=[Forms]![Bulk Docket Range (Missing Dockets) sub-form]![cmbFinish]
Is there anything wrong with these names or their spelling? I have a nagging suspicion about the embedded parentheses inside the form name. Should I try renaming this form without them?

2. ERROR MSG
The error message received on the report is the standard #Name?

3. OPENING OF REPORT
On the form where I enter the Start and Finish parameters (via combo boxes hence the "cmb........") there are three command buttons; Cancel, View and Print. Here are the OnClick Event Procedures for the Print and the View command buttons .
===============================
Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

If CreateMDTable Then
DoCmd.OpenReport "Missing Bulk Dockets Report"
Else
MsgBox "There are no missing dockets in the specified range!", vbInformation + vbOKOnly, "Missing Docket Information"
End If

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub
====================================
Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

If CreateMDTable Then
DoCmd.OpenReport "Missing Bulk Dockets Report", acPreview
Else
MsgBox "There are no missing dockets in the specified range!", vbInformation + vbOKOnly, "Missing Docket Information"
End If

Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

End Sub
=============================

Hope this helps.

TIA

 

[bat17]

I dont think that the brackets are the problem. From the name of your form it would appear to be a sub form from a main form. To link to this you need to go through the mainform. Something like:-
=[Forms]![Main Form]![Sub Fom].[Form]![Control]

If you use the control's builder function to set this up it will get it right. just navigate to the main form, and select the sub-form from there.

HTH

Peter

 

[Garry in Perth]

Peter,

Sorry about the sub-form red herring - No, the form is definitely a form, not a sub-form.

I did rename the form to remove the parentheses, just in case.
I used the control's builder function to set up the two controls, as you suggested.
The Control Source for one of the text boxes now reads
=[Forms]![Missing Dockets form]![cmbStart]
But still no joy

Some more info for you. The query which checks for missing records creates a temporary Table called Missing Dockets. This table is re-set each time the query is run. Only the actual missing docket number are stored in this table.

The Report then uses this Missing Dockets table as its record source.

When this Report is viewed on screen (with the #Name? error messages), I checked to see what database elements are still open. Clicked on Window and there are four elements shown - 1. the Main database, 2. my Main switchboard, 3. a sub-menu from where the Missing Dockets form/query/report is run, and 4. the Missing Dockets report itself.

So the actual Missing Dockets form is not open when the report is run. It is probably getting closed when the query gets run. Hence the Start and Finish values are getting lost (or more correctly not available to the report).

Perhaps I need to store them in the temporary Missing Dockets table as say the first two records.

For example if I search from Records #23 to #56 and find three records (#28, #39, #51) are missing, then I should be storing FIVE values in my Missing Docket table (#23, #56, #28, #39, #51). The report would then need to be re-designed to show the first two records (Start and Finish) in the report header and the remaining records in the report detail section. This seems quite messy.

To my mind, it should be much more simple to keep the Missing Dockets form open, whilst both the query and then the report are being run. And then I should be able to "harvest" their values directly into the report.

Hence my original enquiry about how to keep the form open. I read somewhere in this forum about needing to put either a "Hide" statement or perhaps a “Forms!frm1.Visible = False” statement somewhere into the SQL program in the OnClick section for the View and Print command buttons. How do I do that? I am no expert on SQL programming for Access. And I didn't write the existing SQL programme. And I can't contact the original programmer.

Also I notice that there is already a reference in the View and Print OnClick program to a Public Function entitled CreateMDTable. Here it is, just in case you need to reference to it:-
==========================
Public Function CreateMDTable() As Boolean
On Error GoTo Err_CreateMDTable
Dim I As Long
Dim rst As Recordset
Dim flag As Boolean

flag = False
DoCmd.SetWarnings False
' Truncate the Missing Bulk Dockets table.
DoCmd.RunSQL "DELETE * FROM [Missing Bulk Dockets]"
I = Me.cmbStart.Value
While I <= Me.cmbFinish.Value
' Test to see if docket is in table.
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Bulk Dockets] WHERE [Bulk Docket Number]= " & CStr(I))
If rst.RecordCount = 0 Then
' Docket is not in the table, therefore add it to the missing table.
DoCmd.RunSQL "INSERT INTO [Missing Bulk Dockets] ([Bulk Docket Number]) VALUES ('" & CStr(I) & "')"
flag = True
End If
rst.Close
I = I + 1
Wend
DoCmd.SetWarnings True
DoCmd.Close
CreateMDTable = flag

Exit_CreateMDTable:
Exit Function

Err_CreateMDTable:
MsgBox Err.Description
Resume Exit_CreateMDTable

End Function
========================

Are there any good on-line references to SQL programming that I might be able to research?

I really appreciate your help so far.

TIA

 

[bat17]

It looks as if your function is closing the form behind it.
You could try either just taking out the line
DoCmd.Close
which should leave the form visible and then close it by hand after, but, if the form is opened as a popup form it will stay in front of the report!

or you could replace that line with
Me.Visible = False
This may cause some complications latter on when the form is called again

Life is never easy


Peter

 

[SydneyGeek]

Garry,

just chucking in my 2 cents. Go with bat's idea of hiding the form when the report opens. Then, in the report, place some code in the On Close event to shut the form properly.
That way, you won't have issues with re-running the form.

Denis

 

[Garry in Perth]

Eureka! By God, They've done it!!

Thanks Peter and now also Denis - both suggestions worked a treat.

I am very happy with my first experience on MrExcel.com. All in less than 24 hours.

I owe you both a cold beer when you're next in Perth, Western Australia.

Big thanks.

CYA around in the forum.

Garry

 

[bat17]

Bet Denis can get there before me