Passing VB parameter to Access Report 1

[XP2000]

How can I pass the value in a text box in a VB form to an Access report.
I have managed to open Access report directly from a VB6 command button, but I don't know how to link a a parameter for a label on the report.

Many thanks,
Neil

 

[flaniganmj]

Open the form, in the OnClick event procedure hide the form, i.e. Me.Visible = False.

Go to the report, in the OnOpen and OnClose event procedure enter the following:

______________
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Form Name", , , , , acDialog, ""
If Not IsLoaded("Form Name&quot Then
Cancel = True
End If
End Sub

Private Sub Report_Close()
DoCmd.Close acForm, "Form Name"
End Sub
______________

Create a new module and enter the following:

______________

Function IsLoaded(ByVal strForm As String) As Integer

Const X = 0
Const Y = 0

If SysCmd(acSysCmdGetObjectState, acForm, strForm) <> X Then

If Forms(strForm).CurrentView <> Y Then
IsLoaded = True
End If

End If

End Function
___________________

Then in your query builder and text boxes reference the forms textbox like you would any other, [FORMS]![FORM_NAME]![TEXTBOX_NAME].

So really all you are doing is opening a form prior to a report for input, make sure Modal and Popup properties are set to &quot;Yes&quot;. Then hiding the form while the report is being viewed, and eventually closed when the report is closed. The Function is there simply to verify that that the form is loaded for use in the report. Hope some this helped.

 

[XP2000]

Thanks for the quick response.
My problem maybe needs further clarification in that the data and text boxes originate in a separate Visual Basic program that calls Access purely for its reporting feature.
I need to pass values that exist within textbox controls in the VB application to the Access report.

The report gets called in the VB application using the following code:

====================================================
Set objAccess = New Access.Application
With objAccess
.OpenCurrentDatabase &quot;Mydatabase&quot;
.DoCmd.OpenReport &quot;MyReport&quot;, intView, , strFilter

Do While Len(.CurrentDb.Name) > 0
DoEvents
Loop
End With
=====================================================

How can I pass a textbox value in the VB application to the Access report?

Thanks,
Neil

 

[StarPassing]

If you must pass a variable to an Access application, I use something like the following:

Your VB function:
Function DoReport()
Dim objAccess As Application
Set objAccess = New Access.Application
With objAccess
' open the Access Application
.OpenCurrentDatabase &quot;C:\Access 2000\Projects\Test\Northwind.mdb&quot;
' run a function in the application named MyCallSql
' passing in as a parameter what is in the textbox, i.e.,'London'
.Run &quot;MyCallSql&quot;, &quot;London&quot;
.DoCmd.OpenReport &quot;Customers&quot;, acViewNormal
End With
objAccess.CloseCurrentDatabase
Set objAccess = Nothing
End Function

A function in the called Access application:
Function myCallSql(Optional InComing)
Static mySql As String
If Not IsMissing(InComing) Then
mySql = InComing
Exit Function
End If
myCallSql = mySql
End Function

Sample code in the report :
Private Sub Report_Open(Cancel As Integer)
Dim mySql As String
‘ call function to get parameter
mySql = myCallSql
If Len(mySql) > 0 Then
‘ place the variable in the label
Me.label.caption=mysql
End If
End Sub

There are better ways, but hope you get the point from this example.

Tom

 

[XP2000]

Excellent, it's easy when you know how.

Neil