Showing global variable in a report??

[mju]

How do I get my contact info for a particular company to show up on a report?

This is my Code for the Module declaring contact name as global variable.
I made it global variable because I am transferring information from a form
to a report
The info appears on a list box in a form whenever a user selects the company
name. I made the list box invisible.
I now want to transfer the data from the form to a control on the report.


Declaring variable:

Option Compare Database

Option Explicit

public Contact_name As Variant


Public Function GETContact_name() As Variant

GETContact_name = Forms![frmRetailerPullDownList]![listContactName]

End Function


This is the code for the form that pulls the info ,
I made the listcontactname invisible.

Private Sub RetailerNameComboBox_AfterUpdate()
Dim cnn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim ContactName As String


strConn = " F:\Company-DB_BE.mdb"

Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strConn


'ContactName = GETretailer_name()
ContactName = RetailerNameComboBox

sSQL = "Select DISTINCT
[TBLRetailerDocumentContacts.RdcContactName],[TBLRetailerDocumentContacts.RdcContactTitle],[TBLRetailerDocumentContacts.RdcContactTelephoneNumber],
WHERE [TBLRetailerDocumentContacts.RdcRetailerName]='" & ContactName & "';"
Me. listContactName.RowSource = sSQL

End Sub

This is where my issue is….
I am unable to show these information on the report.
I tried report load event

Private Sub Report_Open(Cancel As Integer)
Dim ContactName As String

Me.Text105.ControlSource = GETContact_name()
'End Sub

I tried putting this code in the control source of a listbox but it did not
work
= GETContact_name()


I also tried this option but I got type mismatch error.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Label107.Caption = Forms![frmRetailerPullDownList]! [listContactName]

End Sub

Please help me! What am I doing wrong?

 

[mju]

Thanks alot.

I am new to access programming. How do i go abt using item selected.

Also, i have the form opened in the background when the report opens up.
So i tried using a text box in the detail section of the report to assign
the value(text108 = Forms!frmRetailerPullDownList!listboxname) but i got this
error message:

You can not assign a value to this object

Please i need some serious help. i am so stuck

I don't see how your "Function GETContact_name" is gonna work with that
listbox "listContactName" which is very likely to be unbound base on the
combobox event you created.

The listbox is unbound and you'll get nothing from it. Look up in Help for
itemselected or selected or itemdata.

How do I get my contact info for a particular company to show up on a report?

This is my Code for the Module declaring contact name as global variable.
I made it global variable because I am transferring information from a form
to a report
The info appears on a list box in a form whenever a user selects the company
name. I made the list box invisible.
I now want to transfer the data from the form to a control on the report.

Declaring variable:

Option Compare Database

Option Explicit

public Contact_name As Variant

Public Function GETContact_name() As Variant

GETContact_name = Forms![frmRetailerPullDownList]![listContactName]

End Function

This is the code for the form that pulls the info ,
I made the listcontactname invisible.

Private Sub RetailerNameComboBox_AfterUpdate()
Dim cnn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim ContactName As String

strConn = " F:\Company-DB_BE.mdb"

Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strConn

'ContactName = GETretailer_name()
ContactName = RetailerNameComboBox

sSQL = "Select DISTINCT
[TBLRetailerDocumentContacts.RdcContactName],[TBLRetailerDocumentContacts.RdcContactTitle],[TBLRetailerDocumentContacts.RdcContactTelephoneNumber],
WHERE [TBLRetailerDocumentContacts.RdcRetailerName]='" & ContactName & "';"
Me. listContactName.RowSource = sSQL

End Sub

This is where my issue is….
I am unable to show these information on the report.
I tried report load event

Private Sub Report_Open(Cancel As Integer)
Dim ContactName As String

Me.Text105.ControlSource = GETContact_name()
'End Sub

I tried putting this code in the control source of a listbox but it did not
work
= GETContact_name()

I also tried this option but I got type mismatch error.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Label107.Caption = Forms![frmRetailerPullDownList]! [listContactName]

End Sub

Please help me! What am I doing wrong?