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?

 

[AccessVandal via AccessMonster.com]

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?

 

[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?

 

[AccessVandal via AccessMonster.com]

Thanks alot.

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

Have you insert break points to check the value in the VBA editor in your
function? You need to find out if your function works with this form
referencing
"Forms![frmRetailerPullDownList]![listContactName]" and make sure that you
have spelled the correct name.

I don't see the purpose of using the Listbox to select a record as you are
only searching for a single record from the combobox as well as from your
code you gave.

I don't see the reasons of using names to search for single record. What if
you have to records with same name like John Smith of Dept A and John Smith
of Dept B? How will you know which is the one?

It would be correct to use a unique ID to find a record. Like..

WHERE [TBLRetailerDocumentContacts.SomeID]=" & ContactID

The Datatype of the field/column is Number and you should rename "SomeID" and
the textbox "ContactID" as necessary and you don't need quotes for datatype
number.

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

If you have a textbox that is bound to the recordsource of the report, you
can't edit the textbox unless it is unbound.

An alternative is to use DlookUp() function in Access for your report. In
your Textbox "text108" of the properties tab "Data", in the ControlSource,
key something like (in one line, watch for word wrap in your browser)

=DlookUp("ContactName", "TBLRetailerDocumentContacts", "SomeID = Forms!
[frmRetailerPullDownList]! [listContactName]")

I assumed "ContactName" is the field you want, "TBLRetailerDocumentContacts"
is the table and "SomeID" is the new field you created (use the correct field
name of course). Look up Help on DlookUp function.

Another way is to use it in the query of your report RecordSource with the
DlookUp as a Field/Column.

 

[AccessVandal via AccessMonster.com]

Typos!

=DlookUp("ContactName", "TBLRetailerDocumentContacts", "SomeID = Forms!
[frmRetailerPullDownList]! [listContactName]")

You have to change the listbox to pickup the ID or use your combo to pick the
ID like

=DlookUp("ContactName", "TBLRetailerDocumentContacts", "SomeID = Forms!
[frmRetailerPullDownList]! [ContactID]")