How can I show data in a form

[Bongo J]

I am doing an HNC Computing course in the UK and we are using access as a
database. My problem is:
I have three tables Customer, Cost, Bookings.

I have created a query to show relevant info and from the query and tables I
have created a form.

If I want to add to an existing form the join date, which is stored in the
customer table. Do I create a text box and put the entry =[Customer]![Join
Date].

I am doing this in expression builder but all I get showing on the form is
#name?.

TIA
Ali

 

[Nick Coe \(UK\)]

You'll need to add the field [Join Date] to your query
underlying the form. I'm assuming that's what you've got at
the moment - a form bound to a query that gets data from the
three tables.

Once it's in the query and shows up when you run the query
in datasheet view then you can add a text control to the
form and bind it to that field.

Is that what you're after?

 

[Bongo J]

The form I created is from the following.

Table - Bookings
Customer ID
Date of Booking
Start Time
Duration
Facility

Query - Customer Query
Name (This is first and surname joined from the Customer table)

Query - Cost
Cost per Hour
Total Cost

The Join Date is a field in the Customer Table. I have tables related as
below

Customer to Booking linked via Customer ID. Booking and Cost linked via
facility (gym, tennis etc..)

I hope this hasn't made things to muddy to understand.

I am relatively new to access (passed my ECDL if that gives an idea where I
am)

Thanks
Ali

You'll need to add the field [Join Date] to your query
underlying the form. I'm assuming that's what you've got at
the moment - a form bound to a query that gets data from the
three tables.

Once it's in the query and shows up when you run the query
in datasheet view then you can add a text control to the
form and bind it to that field.

Is that what you're after?

--
Nick Coe (UK)
www.alphacos.co.uk

---

I am doing an HNC Computing course in the UK and we are using access as a
database. My problem is:
I have three tables Customer, Cost, Bookings.

I have created a query to show relevant info and from the query and tables I
have created a form.

If I want to add to an existing form the join date, which is stored in the
customer table. Do I create a text box and put the entry =[Customer]![Join
Date].

I am doing this in expression builder but all I get showing on the form is
#name?.

TIA
Ali

 

[Nick Coe \(UK\)]

Ali,

I'm not going to do your course work for you.

Having said that here's a few pointers to get you on your
way, I may put too much low level detail in, don't be
offended it's just to make sure I don't skip anything
essential... :

A form can be based on a stored query, a SQL select
statement or a table for it's data. It is said to be
'bound' to that data. If a form has no underlying data
source it is said to be 'unbound'. The same terminology can
be applied to individual controls on the form.

The data source is usually set in the property sheet for
that form. To see the property sheet, open the form in
design view then click the button in the Access button bar
(with an icon of a hand and finger pointing to a sheet of
paper). Alternatively use the menu bar and click
View|Properties. The property sheet should say 'Form' in
it's top caption, if it does not then press Ctrl-R. Click
the 'All' or 'Data' tab on the properties sheet and the
topmost line of the property sheet should be 'Record
Source'. That will contain the source query/table name or
the SQL select statement. If blank then the form is
unbound.

Now to specifics. In the record source for your form you
must have ALL the fields you wish to display on the form.
There are exceptions; calculated controls, controls filled
by code and so on. So you need to make sure [Join Date] is
in the data source to which your form is bound or else
create a new query with all the fields in that you want and
specify that in the form's Record Source.

Either way you can't just point to a field in a control's
'Control Source' property. The control has no way of
knowing which value in a column full of data it should get.
That's where forms come in because in a Continuous form they
effectively repeat the control over and over until all the
records are shown or in Single Form they step through the
records one by one at the users prompt.

Now to completely confuse you, you can use DLookup() to get
a single value in an unbound control thus =DLookup("stuff").
Use Help to get info on DLookup, remember to put stuff in
quotes and use [] around object names that contain spaces.

Good luck... :->>

--
Nick Coe (UK)
www.alphacos.co.uk

---

The form I created is from the following.

Table - Bookings
Customer ID
Date of Booking
Start Time
Duration
Facility

Query - Customer Query
Name (This is first and surname joined from the Customer table)

Query - Cost
Cost per Hour
Total Cost

The Join Date is a field in the Customer Table. I have tables related as
below

Customer to Booking linked via Customer ID. Booking and Cost linked via
facility (gym, tennis etc..)

I hope this hasn't made things to muddy to understand.

I am relatively new to access (passed my ECDL if that gives an idea where I
am)

Thanks
Ali

You'll need to add the field [Join Date] to your query
underlying the form. I'm assuming that's what you've got at
the moment - a form bound to a query that gets data from the
three tables.

Once it's in the query and shows up when you run the query
in datasheet view then you can add a text control to the
form and bind it to that field.

Is that what you're after?

--
Nick Coe (UK)
www.alphacos.co.uk

---

"Bongo J (e-mail address removed)>" <bongo<nospam> wrote in
message news:[email protected]...

I am doing an HNC Computing course in the UK and we are using access as a
database. My problem is:
I have three tables Customer, Cost, Bookings.

I have created a query to show relevant info and from

the
query and tables I

have created a form.

If I want to add to an existing form the join date,

which
is stored in the

customer table. Do I create a text box and put the

entry
=[Customer]![Join

Date].

I am doing this in expression builder but all I get showing on the form is
#name?.

TIA
Ali

 

[Bongo J]

Nick

You have answered my question perfectly. My confusion has been about the
source info for the form. I have been trying to add a new field to the form
after it's creation and you obviously can't do that unless you use the
DLookup(stuff). My lecturer did not know this either.

I thank you for your time and expertise.

Alistair

Ali,

I'm not going to do your course work for you.

Having said that here's a few pointers to get you on your
way, I may put too much low level detail in, don't be
offended it's just to make sure I don't skip anything
essential... :

A form can be based on a stored query, a SQL select
statement or a table for it's data. It is said to be
'bound' to that data. If a form has no underlying data
source it is said to be 'unbound'. The same terminology can
be applied to individual controls on the form.

The data source is usually set in the property sheet for
that form. To see the property sheet, open the form in
design view then click the button in the Access button bar
(with an icon of a hand and finger pointing to a sheet of
paper). Alternatively use the menu bar and click
View|Properties. The property sheet should say 'Form' in
it's top caption, if it does not then press Ctrl-R. Click
the 'All' or 'Data' tab on the properties sheet and the
topmost line of the property sheet should be 'Record
Source'. That will contain the source query/table name or
the SQL select statement. If blank then the form is
unbound.

Now to specifics. In the record source for your form you
must have ALL the fields you wish to display on the form.
There are exceptions; calculated controls, controls filled
by code and so on. So you need to make sure [Join Date] is
in the data source to which your form is bound or else
create a new query with all the fields in that you want and
specify that in the form's Record Source.

Either way you can't just point to a field in a control's
'Control Source' property. The control has no way of
knowing which value in a column full of data it should get.
That's where forms come in because in a Continuous form they
effectively repeat the control over and over until all the
records are shown or in Single Form they step through the
records one by one at the users prompt.

Now to completely confuse you, you can use DLookup() to get
a single value in an unbound control thus =DLookup("stuff").
Use Help to get info on DLookup, remember to put stuff in
quotes and use [] around object names that contain spaces.

Good luck... :->>

--
Nick Coe (UK)
www.alphacos.co.uk

---

The form I created is from the following.

Table - Bookings
Customer ID
Date of Booking
Start Time
Duration
Facility

Query - Customer Query
Name (This is first and surname joined from the Customer table)

Query - Cost
Cost per Hour
Total Cost

The Join Date is a field in the Customer Table. I have tables related as
below

Customer to Booking linked via Customer ID. Booking and Cost linked via
facility (gym, tennis etc..)

I hope this hasn't made things to muddy to understand.

I am relatively new to access (passed my ECDL if that gives an idea where I
am)

Thanks
Ali

You'll need to add the field [Join Date] to your query
underlying the form. I'm assuming that's what you've got at
the moment - a form bound to a query that gets data from the
three tables.

Once it's in the query and shows up when you run the query
in datasheet view then you can add a text control to the
form and bind it to that field.

Is that what you're after?

--
Nick Coe (UK)
www.alphacos.co.uk

---

"Bongo J (e-mail address removed)>" <bongo<nospam> wrote in
message I am doing an HNC Computing course in the UK and we are
using access as a
database. My problem is:
I have three tables Customer, Cost, Bookings.

I have created a query to show relevant info and from the
query and tables I
have created a form.

If I want to add to an existing form the join date, which
is stored in the
customer table. Do I create a text box and put the entry
=[Customer]![Join
Date].

I am doing this in expression builder but all I get
showing on the form is
#name?.

TIA
Ali

 

[Nick Coe \(UK\)]

You _can_ add a field to a form after it's creation, all you
have to do is make sure the field is in the form source
query/table/whatever then add a new control to the form and
bind it to the field.

--
Nick Coe (UK)
www.alphacos.co.uk

---

Nick

You have answered my question perfectly. My confusion has been about the
source info for the form. I have been trying to add a new field to the form
after it's creation and you obviously can't do that unless you use the
DLookup(stuff). My lecturer did not know this either.

I thank you for your time and expertise.

Alistair

Ali,

I'm not going to do your course work for you.

Having said that here's a few pointers to get you on your
way, I may put too much low level detail in, don't be
offended it's just to make sure I don't skip anything
essential... :

A form can be based on a stored query, a SQL select
statement or a table for it's data. It is said to be
'bound' to that data. If a form has no underlying data
source it is said to be 'unbound'. The same terminology can
be applied to individual controls on the form.

The data source is usually set in the property sheet for
that form. To see the property sheet, open the form in
design view then click the button in the Access button bar
(with an icon of a hand and finger pointing to a sheet of
paper). Alternatively use the menu bar and click
View|Properties. The property sheet should say 'Form' in
it's top caption, if it does not then press Ctrl-R. Click
the 'All' or 'Data' tab on the properties sheet and the
topmost line of the property sheet should be 'Record
Source'. That will contain the source query/table name or
the SQL select statement. If blank then the form is
unbound.

Now to specifics. In the record source for your form you
must have ALL the fields you wish to display on the form.
There are exceptions; calculated controls, controls filled
by code and so on. So you need to make sure [Join Date] is
in the data source to which your form is bound or else
create a new query with all the fields in that you want and
specify that in the form's Record Source.

Either way you can't just point to a field in a control's
'Control Source' property. The control has no way of
knowing which value in a column full of data it should get.
That's where forms come in because in a Continuous form they
effectively repeat the control over and over until all the
records are shown or in Single Form they step through the
records one by one at the users prompt.

Now to completely confuse you, you can use DLookup() to get
a single value in an unbound control thus =DLookup("stuff").
Use Help to get info on DLookup, remember to put stuff in
quotes and use [] around object names that contain spaces.

Good luck... :->>

--
Nick Coe (UK)
www.alphacos.co.uk

---

"Bongo J (e-mail address removed)>" <bongo<nospam> wrote in
message news:[email protected]...

The form I created is from the following.

Table - Bookings
Customer ID
Date of Booking
Start Time
Duration
Facility

Query - Customer Query
Name (This is first and surname joined from the

Customer
table)

Query - Cost
Cost per Hour
Total Cost

The Join Date is a field in the Customer Table. I have tables related as
below

Customer to Booking linked via Customer ID. Booking and Cost linked via
facility (gym, tennis etc..)

I hope this hasn't made things to muddy to understand.

I am relatively new to access (passed my ECDL if that gives an idea where I
am)

Thanks
Ali

"Nick Coe (UK)"

wrote in message

You'll need to add the field [Join Date] to your query
underlying the form. I'm assuming that's what you've got at
the moment - a form bound to a query that gets data

from
the

three tables.

Once it's in the query and shows up when you run the query
in datasheet view then you can add a text control to the
form and bind it to that field.

Is that what you're after?

wrote
in

message I am doing an HNC Computing course in the UK and we are
using access as a
database. My problem is:
I have three tables Customer, Cost, Bookings.

I have created a query to show relevant info and

from
the

query and tables I
have created a form.

If I want to add to an existing form the join date, which
is stored in the
customer table. Do I create a text box and put the entry
=[Customer]![Join
Date].

I am doing this in expression builder but all I get
showing on the form is
#name?.

TIA
Ali

 

[Bongo J]

Now it is becoming clearer. Is there a way to add a new source to an
existing form.

I also tried the DLookUp and managed to get it to work, of a fashion. The
figure it gives is correct but it appears to be static. When I cycle the
records the number does not change. I thought it may change with each record
shown. I think the DLookUp is outwith the course at the moment anyway but
it is always nice to learn new things.

Ali

You _can_ add a field to a form after it's creation, all you
have to do is make sure the field is in the form source
query/table/whatever then add a new control to the form and
bind it to the field.

--
Nick Coe (UK)
www.alphacos.co.uk

---

Nick

You have answered my question perfectly. My confusion has been about the
source info for the form. I have been trying to add a new field to the form
after it's creation and you obviously can't do that unless you use the
DLookup(stuff). My lecturer did not know this either.

I thank you for your time and expertise.

Alistair

Ali,

I'm not going to do your course work for you.

Having said that here's a few pointers to get you on your
way, I may put too much low level detail in, don't be
offended it's just to make sure I don't skip anything
essential... :

A form can be based on a stored query, a SQL select
statement or a table for it's data. It is said to be
'bound' to that data. If a form has no underlying data
source it is said to be 'unbound'. The same terminology can
be applied to individual controls on the form.

The data source is usually set in the property sheet for
that form. To see the property sheet, open the form in
design view then click the button in the Access button bar
(with an icon of a hand and finger pointing to a sheet of
paper). Alternatively use the menu bar and click
View|Properties. The property sheet should say 'Form' in
it's top caption, if it does not then press Ctrl-R. Click
the 'All' or 'Data' tab on the properties sheet and the
topmost line of the property sheet should be 'Record
Source'. That will contain the source query/table name or
the SQL select statement. If blank then the form is
unbound.

Now to specifics. In the record source for your form you
must have ALL the fields you wish to display on the form.
There are exceptions; calculated controls, controls filled
by code and so on. So you need to make sure [Join Date] is
in the data source to which your form is bound or else
create a new query with all the fields in that you want and
specify that in the form's Record Source.

Either way you can't just point to a field in a control's
'Control Source' property. The control has no way of
knowing which value in a column full of data it should get.
That's where forms come in because in a Continuous form they
effectively repeat the control over and over until all the
records are shown or in Single Form they step through the
records one by one at the users prompt.

Now to completely confuse you, you can use DLookup() to get
a single value in an unbound control thus =DLookup("stuff").
Use Help to get info on DLookup, remember to put stuff in
quotes and use [] around object names that contain spaces.

Good luck... :->>

--
Nick Coe (UK)
www.alphacos.co.uk

---

"Bongo J (e-mail address removed)>" <bongo<nospam> wrote in
message The form I created is from the following.

Table - Bookings
Customer ID
Date of Booking
Start Time
Duration
Facility

Query - Customer Query
Name (This is first and surname joined from the Customer
table)

Query - Cost
Cost per Hour
Total Cost

The Join Date is a field in the Customer Table. I have
tables related as
below

Customer to Booking linked via Customer ID. Booking and
Cost linked via
facility (gym, tennis etc..)

I hope this hasn't made things to muddy to understand.

I am relatively new to access (passed my ECDL if that
gives an idea where I
am)

Thanks
Ali

"Nick Coe (UK)"

wrote in message
You'll need to add the field [Join Date] to your query
underlying the form. I'm assuming that's what you've
got at
the moment - a form bound to a query that gets data from
the
three tables.

Once it's in the query and shows up when you run the
query
in datasheet view then you can add a text control to the
form and bind it to that field.

Is that what you're after?

--
Nick Coe (UK)
www.alphacos.co.uk

---

"Bongo J (e-mail address removed)>" <bongo<nospam> wrote
in
message I am doing an HNC Computing course in the UK and we are
using access as a
database. My problem is:
I have three tables Customer, Cost, Bookings.

I have created a query to show relevant info and from
the
query and tables I
have created a form.

If I want to add to an existing form the join date,
which
is stored in the
customer table. Do I create a text box and put the
entry
=[Customer]![Join
Date].

I am doing this in expression builder but all I get
showing on the form is
#name?.

TIA
Ali

 

[Nick Coe \(UK\)]

Ali,
Do you mean change the form's source? Yes, in design view
you use the drop down list for Record Source on the form's
Properties Sheet to pick a different one or use the builder
(button with elipses) to invoke the query builder and make a
new one, then alter the controls on your form as required.

DLookup() needs careful construction of it's criteria and it
isn't appropriate if you can get a field value using your
base query. It is very usefull sometimes when you need a
single value that isn't easy to get any other way.

For general tips, tricks and good gen use these links (mvps
access web is a good starting place):


http://members.rogers.com/douglas.j.steele/AccessIndex.html
Doug Steele

http://www.lebans.com
Stephan Lebans

http://www.granite.ab.ca/access/
Tony Toews

http://www.datastrat.com/
Armin Meyer

http://allenbrowne.com
Allen Browne

http://www.attcanada.net/~kallal.msn/
Albert D. Kallal

http://www.mvps.org/access/
MVPS


--
Nick Coe (UK)
www.alphacos.co.uk

---

Now it is becoming clearer. Is there a way to add a new source to an
existing form.

I also tried the DLookUp and managed to get it to work, of a fashion. The
figure it gives is correct but it appears to be static. When I cycle the
records the number does not change. I thought it may change with each record
shown. I think the DLookUp is outwith the course at the moment anyway but
it is always nice to learn new things.

Ali

You _can_ add a field to a form after it's creation, all you
have to do is make sure the field is in the form source
query/table/whatever then add a new control to the form and
bind it to the field.

--
Nick Coe (UK)
www.alphacos.co.uk

---

"Bongo J (e-mail address removed)>" <bongo<nospam> wrote in
message news:[email protected]... has
been about the new
field to the form unless
you use the

wrote in message

Ali,

I'm not going to do your course work for you.

Having said that here's a few pointers to get you on your
way, I may put too much low level detail in, don't be
offended it's just to make sure I don't skip anything
essential... :

A form can be based on a stored query, a SQL select
statement or a table for it's data. It is said to be
'bound' to that data. If a form has no underlying data
source it is said to be 'unbound'. The same

terminology
can

be applied to individual controls on the form.

The data source is usually set in the property sheet for
that form. To see the property sheet, open the form in
design view then click the button in the Access

button
bar

(with an icon of a hand and finger pointing to a

sheet
of

paper). Alternatively use the menu bar and click
View|Properties. The property sheet should say

'Form'
in

it's top caption, if it does not then press Ctrl-R. Click
the 'All' or 'Data' tab on the properties sheet and the
topmost line of the property sheet should be 'Record
Source'. That will contain the source query/table

name
or

the SQL select statement. If blank then the form is
unbound.

Now to specifics. In the record source for your form you
must have ALL the fields you wish to display on the form.
There are exceptions; calculated controls, controls filled
by code and so on. So you need to make sure [Join

Date]
is

in the data source to which your form is bound or else
create a new query with all the fields in that you

want
and

specify that in the form's Record Source.

Either way you can't just point to a field in a control's
'Control Source' property. The control has no way of
knowing which value in a column full of data it

should
get.

That's where forms come in because in a Continuous

form
they

effectively repeat the control over and over until

all
the

records are shown or in Single Form they step through the
records one by one at the users prompt.

Now to completely confuse you, you can use DLookup()

to
get

a single value in an unbound control thus =DLookup("stuff").
Use Help to get info on DLookup, remember to put

stuff
in

quotes and use [] around object names that contain spaces.

Good luck... :->>

wrote
in

message The form I created is from the following.

Table - Bookings
Customer ID
Date of Booking
Start Time
Duration
Facility

Query - Customer Query
Name (This is first and surname joined from the Customer
table)

Query - Cost
Cost per Hour
Total Cost

The Join Date is a field in the Customer Table. I have
tables related as
below

Customer to Booking linked via Customer ID. Booking and
Cost linked via
facility (gym, tennis etc..)

I hope this hasn't made things to muddy to understand.

I am relatively new to access (passed my ECDL if that
gives an idea where I
am)

Thanks
Ali

"Nick Coe (UK)"

wrote in message
You'll need to add the field [Join Date] to your query
underlying the form. I'm assuming that's what you've
got at
the moment - a form bound to a query that gets

data
from

the
three tables.

Once it's in the query and shows up when you run the
query
in datasheet view then you can add a text control

to
the

form and bind it to that field.

Is that what you're after?

we
are

using access as a
database. My problem is:
I have three tables Customer, Cost, Bookings.

I have created a query to show relevant info and from
the
query and tables I
have created a form.

If I want to add to an existing form the join date,
which
is stored in the
customer table. Do I create a text box and put the
entry
=[Customer]![Join
Date].

I am doing this in expression builder but all I get
showing on the form is
#name?.

TIA
Ali