Displaying Variables on Report Forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am very new to Access as the following question will prove.

I have declared some public variables and wish to display them on a Report
Form.

What syntax do I use? I know I need to create a text box - but what do I
put in the Data|Control Source row?

Also the same question re displaying but this time when I want to use more
than one table on a Report Form. I want to disply the first record from a
second table - what syntax do I use?

Please help me - I'm at my wits end and feel so stupid as I know it must be
very simple!!!!!!
 
There are two ways to do that
1. Create a text box on the form that are un bound, and on the on load event
of the form you can enter the code
Me.TextBoxName = VariableName
Me.TextBoxName2 = VariableName2
==============================================
2. You can create a function that return the value of the variable

Function FunctionName()
FunctionName = VariableName
End Function

And in the Control Source of the TextBox you can write
=FunctionName()
================================================
 
1. You cannot read the data from variables directly into a text box. The
workaround is to create a function to return the value of the variable,
e.g.:
Public MyVar As Variant
Public Function GetMyVar() As Variant
GetMyVar = MyVar
End Function
You can then set the Control Source of your text box to:
=GetMyVar()

2. Create a query to use as the RecordSource for your report. The query can
contain more than one table. Use a Totals query (Totals on View menu in
query design) if you don't want all records, or need to summarize the
values.

If your issue is more complex than a simple query can produce, there are 4
possible solutions in this article:
http://www.mvps.org/access/queries/qry0020.htm
 
Hi Ofer

Number 1 does not work - it comes back and says 'Variable not defined'.
Also there does not seem to be an On Load event on reports! I did it in On
Open.

Number 2 - a couple of questions. Where do you create the function? And
what is the syntax for picking up the table and field? I put in what I
thought and it came back requiring a leading parameter.

I told you I was an absolute beginner!!!

I tried Allen's idea of a query and it goes on about joins not being
acceptable.

I think I'll put my head in a bucket!!
 
Create a new module, declare the variable and create the function in this
module.
If you declare the variables under a form or a report, moving to another for
or report will give you the message - 'Variable not defined', you need to
define them under a module.
That way you can use this variables and functions every where in your data
base.
 
The module should look like:
====================================
Option Compare DataBase
Global VariableName as String
Global VariableName2 as Long

Function ReturnVariableName () as string
ReturnVariableName = VariableName
End Function

Function ReturnVariableName2 () as Long
ReturnVariableName2 = VariableName2
End Function
========================================
 
Hi "kayseypops".

kayseypops said:
I am very new to Access as the following question will prove.
I have declared some public variables and wish to display them
on a Report Form.
Click to expand...

btw: reports and forms are different types of objects
What syntax do I use? I know I need to create a text box -
but what do I put in the Data|Control Source row?
Click to expand...

Create a public function that returns the value from the variable.
Use that function as controlsource for a textbox.
Also the same question re displaying but this time when I want to
use more than one table on a Report Form. I want to disply the first
record from a second table - what syntax do I use?
Click to expand...

What table is the first one?
Create a query selecting the record and base a report on that query.
 
Hi ofer

I feel that I am nearly there!

I've created a new module with the following in it:-

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = "Just Testing"
End Function

Now when I create a text box in my report and put the function
=funBranchTitle() in I get the text "Just Testing" displayed when I run the
report.

However when I put the following in as the function I still don't see the
content of the BranchTitle in the report.

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = [Coordinators Details].BranchTitle.Value
End Function

There is obviously still some bit of the syntax wrong as it comes back with
the error 'External Name Not Defined'.

What's missing please?
 
Is the [Coordinators Details] is a form that you want to get a value from?

If it is, then it should be
Function strBranchTitle() As String
strBranchTitle = Forms![Coordinators Details]![BranchTitle]
End Function

Before you said that you want to display a variable value, in that case it
should be
Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = funBranchTitle
End Function

The function will return the value of funBranchTitle.

--
In God We Trust - Everything Else We Test


kayseypops said:
Hi ofer

I feel that I am nearly there!

I've created a new module with the following in it:-

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = "Just Testing"
End Function

Now when I create a text box in my report and put the function
=funBranchTitle() in I get the text "Just Testing" displayed when I run the
report.

However when I put the following in as the function I still don't see the
content of the BranchTitle in the report.

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = [Coordinators Details].BranchTitle.Value
End Function

There is obviously still some bit of the syntax wrong as it comes back with
the error 'External Name Not Defined'.

What's missing please?
--
Peter J Aldersley
BEDWORTH
England


Ofer said:
The module should look like:
====================================
Option Compare DataBase
Global VariableName as String
Global VariableName2 as Long

Function ReturnVariableName () as string
ReturnVariableName = VariableName
End Function

Function ReturnVariableName2 () as Long
ReturnVariableName2 = VariableName2
End Function
========================================
Click to expand...
Click to expand...
 
No it's in a table!
--
Peter J Aldersley
BEDWORTH
England


Ofer said:
Is the [Coordinators Details] is a form that you want to get a value from?

If it is, then it should be
Function strBranchTitle() As String
strBranchTitle = Forms![Coordinators Details]![BranchTitle]
End Function

Before you said that you want to display a variable value, in that case it
should be
Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = funBranchTitle
End Function

The function will return the value of funBranchTitle.

--
In God We Trust - Everything Else We Test


kayseypops said:
Hi ofer

I feel that I am nearly there!

I've created a new module with the following in it:-

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = "Just Testing"
End Function

Now when I create a text box in my report and put the function
=funBranchTitle() in I get the text "Just Testing" displayed when I run the
report.

However when I put the following in as the function I still don't see the
content of the BranchTitle in the report.

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = [Coordinators Details].BranchTitle.Value
End Function

There is obviously still some bit of the syntax wrong as it comes back with
the error 'External Name Not Defined'.

What's missing please?
--
Peter J Aldersley
BEDWORTH
England


Ofer said:
The module should look like:
====================================
Option Compare DataBase
Global VariableName as String
Global VariableName2 as Long

Function ReturnVariableName () as string
ReturnVariableName = VariableName
End Function

Function ReturnVariableName2 () as Long
ReturnVariableName2 = VariableName2
End Function
========================================
:

I am very new to Access as the following question will prove.

I have declared some public variables and wish to display them on a Report
Form.

What syntax do I use? I know I need to create a text box - but what do I
put in the Data|Control Source row?

Also the same question re displaying but this time when I want to use more
than one table on a Report Form. I want to disply the first record from a
second table - what syntax do I use?

Please help me - I'm at my wits end and feel so stupid as I know it must be
very simple!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
What is the value you are trying to get
The name of the table?
The name of the field?
Any filter need to be used on the table, or it had only one record?

--
In God We Trust - Everything Else We Test


kayseypops said:
No it's in a table!
--
Peter J Aldersley
BEDWORTH
England


Ofer said:
Is the [Coordinators Details] is a form that you want to get a value from?

If it is, then it should be
Function strBranchTitle() As String
strBranchTitle = Forms![Coordinators Details]![BranchTitle]
End Function

Before you said that you want to display a variable value, in that case it
should be
Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = funBranchTitle
End Function

The function will return the value of funBranchTitle.

--
In God We Trust - Everything Else We Test


kayseypops said:
Hi ofer

I feel that I am nearly there!

I've created a new module with the following in it:-

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = "Just Testing"
End Function

Now when I create a text box in my report and put the function
=funBranchTitle() in I get the text "Just Testing" displayed when I run the
report.

However when I put the following in as the function I still don't see the
content of the BranchTitle in the report.

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = [Coordinators Details].BranchTitle.Value
End Function

There is obviously still some bit of the syntax wrong as it comes back with
the error 'External Name Not Defined'.

What's missing please?
--
Peter J Aldersley
BEDWORTH
England


:

The module should look like:
====================================
Option Compare DataBase
Global VariableName as String
Global VariableName2 as Long

Function ReturnVariableName () as string
ReturnVariableName = VariableName
End Function

Function ReturnVariableName2 () as Long
ReturnVariableName2 = VariableName2
End Function
========================================
:

I am very new to Access as the following question will prove.

I have declared some public variables and wish to display them on a Report
Form.

What syntax do I use? I know I need to create a text box - but what do I
put in the Data|Control Source row?

Also the same question re displaying but this time when I want to use more
than one table on a Report Form. I want to disply the first record from a
second table - what syntax do I use?

Please help me - I'm at my wits end and feel so stupid as I know it must be
very simple!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
I'm trying to get the value of the first and only record in the table.
The name of the table is 'Coordinators Details'.
The name of the field is 'BranchTitle'.
As stated earlier only one record in the table and thats all there will ever
be.
--
Peter J Aldersley
BEDWORTH
England


Ofer said:
What is the value you are trying to get
The name of the table?
The name of the field?
Any filter need to be used on the table, or it had only one record?

--
In God We Trust - Everything Else We Test


kayseypops said:
No it's in a table!
--
Peter J Aldersley
BEDWORTH
England


Ofer said:
Is the [Coordinators Details] is a form that you want to get a value from?

If it is, then it should be
Function strBranchTitle() As String
strBranchTitle = Forms![Coordinators Details]![BranchTitle]
End Function

Before you said that you want to display a variable value, in that case it
should be
Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = funBranchTitle
End Function

The function will return the value of funBranchTitle.

--
In God We Trust - Everything Else We Test


:

Hi ofer

I feel that I am nearly there!

I've created a new module with the following in it:-

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = "Just Testing"
End Function

Now when I create a text box in my report and put the function
=funBranchTitle() in I get the text "Just Testing" displayed when I run the
report.

However when I put the following in as the function I still don't see the
content of the BranchTitle in the report.

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = [Coordinators Details].BranchTitle.Value
End Function

There is obviously still some bit of the syntax wrong as it comes back with
the error 'External Name Not Defined'.

What's missing please?
--
Peter J Aldersley
BEDWORTH
England


:

The module should look like:
====================================
Option Compare DataBase
Global VariableName as String
Global VariableName2 as Long

Function ReturnVariableName () as string
ReturnVariableName = VariableName
End Function

Function ReturnVariableName2 () as Long
ReturnVariableName2 = VariableName2
End Function
========================================
:

I am very new to Access as the following question will prove.

I have declared some public variables and wish to display them on a Report
Form.

What syntax do I use? I know I need to create a text box - but what do I
put in the Data|Control Source row?

Also the same question re displaying but this time when I want to use more
than one table on a Report Form. I want to disply the first record from a
second table - what syntax do I use?

Please help me - I'm at my wits end and feel so stupid as I know it must be
very simple!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
OK, so try this

Function strBranchTitle() As String
strBranchTitle = Dlookup("[BranchTitle]","[Coordinators Details]")
End Function

But to display this record you have no need for a function or Global
Variable, you just need to write in the Control Source of the TextBox

=Dlookup("[BranchTitle]","[Coordinators Details]")

--
In God We Trust - Everything Else We Test


kayseypops said:
I'm trying to get the value of the first and only record in the table.
The name of the table is 'Coordinators Details'.
The name of the field is 'BranchTitle'.
As stated earlier only one record in the table and thats all there will ever
be.
--
Peter J Aldersley
BEDWORTH
England


Ofer said:
What is the value you are trying to get
The name of the table?
The name of the field?
Any filter need to be used on the table, or it had only one record?

--
In God We Trust - Everything Else We Test


kayseypops said:
No it's in a table!
--
Peter J Aldersley
BEDWORTH
England


:

Is the [Coordinators Details] is a form that you want to get a value from?

If it is, then it should be
Function strBranchTitle() As String
strBranchTitle = Forms![Coordinators Details]![BranchTitle]
End Function

Before you said that you want to display a variable value, in that case it
should be
Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = funBranchTitle
End Function

The function will return the value of funBranchTitle.

--
In God We Trust - Everything Else We Test


:

Hi ofer

I feel that I am nearly there!

I've created a new module with the following in it:-

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = "Just Testing"
End Function

Now when I create a text box in my report and put the function
=funBranchTitle() in I get the text "Just Testing" displayed when I run the
report.

However when I put the following in as the function I still don't see the
content of the BranchTitle in the report.

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = [Coordinators Details].BranchTitle.Value
End Function

There is obviously still some bit of the syntax wrong as it comes back with
the error 'External Name Not Defined'.

What's missing please?
--
Peter J Aldersley
BEDWORTH
England


:

The module should look like:
====================================
Option Compare DataBase
Global VariableName as String
Global VariableName2 as Long

Function ReturnVariableName () as string
ReturnVariableName = VariableName
End Function

Function ReturnVariableName2 () as Long
ReturnVariableName2 = VariableName2
End Function
========================================
:

I am very new to Access as the following question will prove.

I have declared some public variables and wish to display them on a Report
Form.

What syntax do I use? I know I need to create a text box - but what do I
put in the Data|Control Source row?

Also the same question re displaying but this time when I want to use more
than one table on a Report Form. I want to disply the first record from a
second table - what syntax do I use?

Please help me - I'm at my wits end and feel so stupid as I know it must be
very simple!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
That's cracked it!!!

Many thanks for all your help - if you live locally I'll buy you a drink.

Thanks again.
--
Peter J Aldersley
BEDWORTH
England


Ofer said:
OK, so try this

Function strBranchTitle() As String
strBranchTitle = Dlookup("[BranchTitle]","[Coordinators Details]")
End Function

But to display this record you have no need for a function or Global
Variable, you just need to write in the Control Source of the TextBox

=Dlookup("[BranchTitle]","[Coordinators Details]")

--
In God We Trust - Everything Else We Test


kayseypops said:
I'm trying to get the value of the first and only record in the table.
The name of the table is 'Coordinators Details'.
The name of the field is 'BranchTitle'.
As stated earlier only one record in the table and thats all there will ever
be.
--
Peter J Aldersley
BEDWORTH
England


Ofer said:
What is the value you are trying to get
The name of the table?
The name of the field?
Any filter need to be used on the table, or it had only one record?

--
In God We Trust - Everything Else We Test


:

No it's in a table!
--
Peter J Aldersley
BEDWORTH
England


:

Is the [Coordinators Details] is a form that you want to get a value from?

If it is, then it should be
Function strBranchTitle() As String
strBranchTitle = Forms![Coordinators Details]![BranchTitle]
End Function

Before you said that you want to display a variable value, in that case it
should be
Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = funBranchTitle
End Function

The function will return the value of funBranchTitle.

--
In God We Trust - Everything Else We Test


:

Hi ofer

I feel that I am nearly there!

I've created a new module with the following in it:-

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = "Just Testing"
End Function

Now when I create a text box in my report and put the function
=funBranchTitle() in I get the text "Just Testing" displayed when I run the
report.

However when I put the following in as the function I still don't see the
content of the BranchTitle in the report.

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = [Coordinators Details].BranchTitle.Value
End Function

There is obviously still some bit of the syntax wrong as it comes back with
the error 'External Name Not Defined'.

What's missing please?
--
Peter J Aldersley
BEDWORTH
England


:

The module should look like:
====================================
Option Compare DataBase
Global VariableName as String
Global VariableName2 as Long

Function ReturnVariableName () as string
ReturnVariableName = VariableName
End Function

Function ReturnVariableName2 () as Long
ReturnVariableName2 = VariableName2
End Function
========================================
:

I am very new to Access as the following question will prove.

I have declared some public variables and wish to display them on a Report
Form.

What syntax do I use? I know I need to create a text box - but what do I
put in the Data|Control Source row?

Also the same question re displaying but this time when I want to use more
than one table on a Report Form. I want to disply the first record from a
second table - what syntax do I use?

Please help me - I'm at my wits end and feel so stupid as I know it must be
very simple!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
Thanks Wolfgang - as you see from other entries from Ofer my problem is now
solved but thanks for your interest.
 
I'm not, but if I'll ever get there I'll contact you, thank you.
Good luck with your project.


--
In God We Trust - Everything Else We Test


kayseypops said:
That's cracked it!!!

Many thanks for all your help - if you live locally I'll buy you a drink.

Thanks again.
--
Peter J Aldersley
BEDWORTH
England


Ofer said:
OK, so try this

Function strBranchTitle() As String
strBranchTitle = Dlookup("[BranchTitle]","[Coordinators Details]")
End Function

But to display this record you have no need for a function or Global
Variable, you just need to write in the Control Source of the TextBox

=Dlookup("[BranchTitle]","[Coordinators Details]")

--
In God We Trust - Everything Else We Test


kayseypops said:
I'm trying to get the value of the first and only record in the table.
The name of the table is 'Coordinators Details'.
The name of the field is 'BranchTitle'.
As stated earlier only one record in the table and thats all there will ever
be.
--
Peter J Aldersley
BEDWORTH
England


:

What is the value you are trying to get
The name of the table?
The name of the field?
Any filter need to be used on the table, or it had only one record?

--
In God We Trust - Everything Else We Test


:

No it's in a table!
--
Peter J Aldersley
BEDWORTH
England


:

Is the [Coordinators Details] is a form that you want to get a value from?

If it is, then it should be
Function strBranchTitle() As String
strBranchTitle = Forms![Coordinators Details]![BranchTitle]
End Function

Before you said that you want to display a variable value, in that case it
should be
Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = funBranchTitle
End Function

The function will return the value of funBranchTitle.

--
In God We Trust - Everything Else We Test


:

Hi ofer

I feel that I am nearly there!

I've created a new module with the following in it:-

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = "Just Testing"
End Function

Now when I create a text box in my report and put the function
=funBranchTitle() in I get the text "Just Testing" displayed when I run the
report.

However when I put the following in as the function I still don't see the
content of the BranchTitle in the report.

Option Compare Database
Option Explicit
Global funBranchTitle As String
Function strBranchTitle() As String
strBranchTitle = [Coordinators Details].BranchTitle.Value
End Function

There is obviously still some bit of the syntax wrong as it comes back with
the error 'External Name Not Defined'.

What's missing please?
--
Peter J Aldersley
BEDWORTH
England


:

The module should look like:
====================================
Option Compare DataBase
Global VariableName as String
Global VariableName2 as Long

Function ReturnVariableName () as string
ReturnVariableName = VariableName
End Function

Function ReturnVariableName2 () as Long
ReturnVariableName2 = VariableName2
End Function
========================================
:

I am very new to Access as the following question will prove.

I have declared some public variables and wish to display them on a Report
Form.

What syntax do I use? I know I need to create a text box - but what do I
put in the Data|Control Source row?

Also the same question re displaying but this time when I want to use more
than one table on a Report Form. I want to disply the first record from a
second table - what syntax do I use?

Please help me - I'm at my wits end and feel so stupid as I know it must be
very simple!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top