Automating Access Reports from VB 1

[MLK]

I'm trying to solve a problem involving automating an Access
mailing label report. When I send a "WHERE" clause to Access
in the OpenReport method I keep getting the little parameter dialog box popping up asking me to fill in the parameter I just sent. I'm not having this problem with any of the other reports that I'm automating and I'm wondering if this problem is associated with the "Label Wizard."


P.S. I've verified my query numerous times.

Any help would be greatly appreciated.
Thanks, MLK

 

[mailzero28]

I encountered the very same problem one time. In this case, the first field I used was called Name : and for some reason the report automatically assumed that I was referring to a Form object, or a reference on the report, I can't remember which.

I deleted the field in question and then re-inserted it using the field list and it worked.

However, failing this - have you tried deleting the report an recreating it? With the same report, when I moved to some elses machine, it would work - I could view a third of the document - I had no choice but to delete and recreate it and then it worked.

bye

Martin.

 

[MLK]

Thanks Martin for responding to my question. I tried your suggestions and the problem remains. I'm wondering if it is "MailingLabel Wizard" specific, since I'm not having this problem with 4 other Reports whose queries are all written the same way. Thanks, MLK

 

[bnath001]

MLK, could you kindly post the piece of code which invokes Access Report from VB. My access report is based on a access query. Does that make it difficult to invoke the report from VB?

Your help is greatly appreciated.

thank you
nath

 

[MLK]

Hi nath, This is some rough code to automate Access Reports from VB.

Option Explicit

Private Enum DocumentOption
doInvoice = 0
doTimeWorksheet = 1
doMaterialsWorksheet = 2
doLabels = 3
doLargeEnvelope = 4
doStandardEnvelope = 5
End Enum

Private m_accApp As Access.Application
Private m_strReportName As String
Private m_strWhereCriteria As String
Private m_lngSelectedView As Long
Private m_lngProjectValue As Long

Private Sub chkPreview_Click()

If chkPreview.Value = vbChecked Then
m_lngSelectedView = acViewPreview ' preview
cmdPrintInvoice.Caption = "&Preview"
Else
m_lngSelectedView = acViewNormal ' print
End If
End Sub

Private Sub cmdClose_Click()

Static blnClosed As Boolean

If blnClosed = False Then
m_accApp.CloseCurrentDatabase
Set m_accApp = Nothing
blnClosed = True
End If
Unload Me

End Sub



Private Sub cmdPrintReport_Click()

Dim m_accApp As Access.Application
Dim strDb As String

Set m_accApp = New Access.Application

strDb = "D:\Documents and Settings\Mark Keller\My Documents\MKPData\MKPData.mdb"

m_accApp.OpenCurrentDatabase (strDb)
m_accApp.DoCmd.OpenReport m_strReportName, m_lngSelectedView, , m_strWhereCriteria & Text1.Text

m_accApp.Visible = True

End Sub

Private Sub Form_Activate()
Text1.Text = frmMKPData.CurrentProjectID
Text1.SetFocus

End Sub


Private Sub optReport_Click(Index As Integer)

Select Case Index
Case doInvoice
m_strReportName = "Invoice Report"
m_strWhereCriteria = "Projects.ProjectID ="
Case doTimeWorksheet
m_strReportName = "Time Worksheet"
m_strWhereCriteria = "TimeItems.ProjectID ="
Case doMaterialsWorksheet
m_strReportName = "Materials Worksheet"
m_strWhereCriteria = "MaterialItems.ProjectID ="
Case doLabels
m_strReportName = "Labels Report"
m_strWhereCriteria = ""
Text1.Text = ""
' Case doLargeEnvelope
' m_strReportName
' m_strWhereCriteria
' Case doStandardEnvelope
' m_strReportName
' m_strWhereCriteria
End Select

End Sub

finished code would be tighter and include error handling.

Set a reference to your version of Access in the Projects-References menu in VB and the Access object model will be available i ypur Object browser. Good Luck MLK