HELP

[Fie]

Right what I have done is...

set up a form (frmKerbside) with 2 combo boxes
- Route Number (comb20)
- Collection Type (comb21)


OK button:


Private Sub cmdOk_Click()
Me.Visible = False
End Sub


Cancel button:


Private Sub cmdCancel_Click()
DoCmd.Close acForm, Me.Name
End Sub


On from Open;


Private Sub Form_Open(Cancel As Integer)
If Not bInReportOpenEvent Then
' If we're not called from the report
MsgBox "For use from the Kerbside Report only", vbOKOnly
Cancel = True
End If
Form_Open_Exit:
Exit Sub
End Sub


The Report (rptKerbside) has record source qryKerbside


OnOpen Event is:


Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True


' Open Sales By Category Dialog
DoCmd.OpenForm "frmKerbside", , , , , acDialog


' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmKerbside") = False Then Cancel = True


' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub


On Close event:


Private Sub Report_Close()
DoCmd.Close acForm, "Sales By Category Dialog"
End Sub


The module code:


Option Compare Database
Option Explicit


Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet

view.


Const conObjStateClosed = 0
Const conDesignView = 0


If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If


End Function


I have a button on my Main Form (frmMain) that when clicked is set to
bring up the form frmKerbside it comes up with Complie error: variable
not defined


and the line Private Sub Report_Open(Cancel As Integer)
and
bInReportOpenEvent = True
are highlighted...




I need to declare the variable so how do I do this???
HELP

 

[Klatuu]

It would have to be a Public variable defined in a standard module.

You technique is a little unusual. Most developers open the form first,
then have the form run the report once the values are entered that the report
needs. Unless there is a very good reason (please tell me, I am always
willing to learn) to do it this way, I would suggest you change your approach.

If it is really necessary, a better technique would be to use the OpenArgs
argument of the OpenForm method.

 

[Fie]

Hey,

I am just copying an example I found on the microsoft website, this
example is for Access 2000 and XP only prob is I have 97 so I am
guessing I have done this totally wrong! Could you please help me
achieve this as I am well confused and totally lost with everything :-(

Fie

 

[Klatuu]

Although you can get some useful information from Microsoft, their coding
examples are not the best you can find. They don't often follow good
programming practices and they totally ignore good naming conventions. For
example, the names of the Combo Boxes are Combo20 and Combo21. Now, reading
the code and looking at the form in design mode, what do they mean? How do
they relate to the data? Those names are totally useless.
If you use good naming conventions, combo box names start with cbo and
should have some relation to the data. No names in Access should have spaces
or special characters. Use only letters, numbers, and the underscore.
Nothing should be named using an Access reserved word. For example, don't
name anything Date, or Year. So, the combo boxes should be:

cboRouteNumber
cboCollectionType

I assume these combos are being used to filter the data for the report.
So, for example purposes, I will also assume there are fields in your table
named ROUTE_NUMBER and COLLECTION_TYPE (Notice the field names are all upper
case, I do that so it is easy to tell they are fields in a record set and
can't be confused with variable or object names.

Once you have made your selections in the combos, you will need a command
button on the form to run the report. Let's have a command button named
cmdPrint (cmd for Command Button). I usually have two buttons one for Print
and one for Preview - cmdPrint and cmdPreview.

Here is the code for the Print Button:

Private Sub cmdPrint_Click()
Call PrintReport(acViewNormal)
End Sub

Here is the code for the Preview Button:

Private Sub cmdPreView_Click()
Call PrintReport(acViewPreview)
End Sub

Here is the code to do the printing:

Private Sub PrintReport(lngView As Long)
Dim strWhere As String

strWhere = "[ROUTE_NUMBER] = " & Me.cboRouteNumber & " [COLLECTION_TYPE]
= " & Me.cboCollectionType

DoCmd.OpenReport strDocName, lngView, , strWhere

PrintReport_Exit:

On Error Resume Next

Exit Sub

PrintReport_Error:

'Error 2501 is returned if the report has no data, so dont show an error
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
End If
GoTo PrintReport_Exit

End Sub


Let's change it so the form opens first, you make your selections in the
combo boxes, and run the report.

Now, to complete the task, we need to use the No Data event in the Report so
that if an empty data set is returned, we control the error gracelfully and
let the user know what is happening:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo Report_NoData_Error

MsgBox "No Data Matching Selections", vbInformation + vbOKOnly,
"Kerbside Report"
Cancel = True

Report_NoData_Exit:

On Error Resume Next
Exit Sub

Report_NoData_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
GoTo Report_NoData_Exit
End Sub

I am not blasting you at all. Everything I have stated above is meant only
to help. Please post back if you have more questions.

Good Luck

 

[Fie]

Hi,

Right I am being really really stupid..! I am so lost and so conused iv
got bits of code from the microsoft site, bits of code from here and
well as you can guess it aint working..

Youv stated code for the print button, and then code to print shouldnt
they be together or am I just being silly. I totally baffeled. I have
deleted everything and I am going to start again. Can someone give me
idiots guide on how to do this.. and where the code goes etc.
PLEASE

Fie x

 

[Fie]

actually forget i said that..... iv figured it out.. iam not so silly
asfter all.. and its all working....

THANKS LOADS

Fie x

 

[Klatuu]

You are not being stupid or silly. I was only trying to give you some
usefull advice.

As to your question regarding the code to print and the code for the print
button.
Notice that both the Print and Preview buttons use the same code to create
the report. The only difference is one opens in Preview mode and the other
sends the report to the printer. That is controled by the View argument of
the OpenReport method. Any time you have to do the same thing more than once
in your code, it is a good idea to use a sub or a funtion to do it. It makes
your code more compact, takes up less memory, and if you should have to
maintain it, you only have to do it in one place.

If you look back at the code, you will see that the click events of both
buttons call the same sub. Once passes the value that tells the report to
preview and the other tells it to print. The sub then uses the value it
receives from the caller to determine what to do.

I'm glad you figured it out.