Records not auto-filling

Hi

I´m really stuck - and can´t see what i´m doing wrong.

I have 3 tables - Customer Details (Customer ID is the PK) containing
address etc. Unit Details (Unit ID is the PK) - which includes Description,
Price and Unit Code. I´ve then created another table that is called
Reservations and I want to be able to pick a UNIT and for it to then draw
down the description, and price. I dragged Customer ID into this table, and
also Unit ID and created one-to-many relationships with enforced referential
integrity. I´ve tried making Unit Code field a lookup, but it doesnt
autofill the rest of the row.

This is where I now get stuck. I´ve tried to create a subform in my
customer form, that uses the Reservations table, but the rows just won´t
autofill. I´ve tried creating various queries, but these arne´t working.
Really frustrated, I can´t see what i´ve done wrong, and i´ve been comparing
to the Northwind sample and have tried everything!!

Did you set the Master/Child links of the form/subform?

"Banjo" wrote:

Hi

I´m really stuck - and can´t see what i´m doing wrong.

I have 3 tables - Customer Details (Customer ID is the PK) containing
address etc. Unit Details (Unit ID is the PK) - which includes Description,
Price and Unit Code. I´ve then created another table that is called
Reservations and I want to be able to pick a UNIT and for it to then draw
down the description, and price. I dragged Customer ID into this table, and
also Unit ID and created one-to-many relationships with enforced referential
integrity. I´ve tried making Unit Code field a lookup, but it doesnt
autofill the rest of the row.

This is where I now get stuck. I´ve tried to create a subform in my
customer form, that uses the Reservations table, but the rows just won´t
autofill. I´ve tried creating various queries, but these arne´t working.
Really frustrated, I can´t see what i´ve done wrong, and i´ve been comparing
to the Northwind sample and have tried everything!!

The subform wizard automatically made the master field Customer ID and the
Child field the same, Customer ID.

Is that correct?

"KARL DEWEY" wrote:

Did you set the Master/Child links of the form/subform?

"Banjo" wrote:

Hi

I´m really stuck - and can´t see what i´m doing wrong.

I have 3 tables - Customer Details (Customer ID is the PK) containing
address etc. Unit Details (Unit ID is the PK) - which includes Description,
Price and Unit Code. I´ve then created another table that is called
Reservations and I want to be able to pick a UNIT and for it to then draw
down the description, and price. I dragged Customer ID into this table, and
also Unit ID and created one-to-many relationships with enforced referential
integrity. I´ve tried making Unit Code field a lookup, but it doesnt
autofill the rest of the row.

This is where I now get stuck. I´ve tried to create a subform in my
customer form, that uses the Reservations table, but the rows just won´t
autofill. I´ve tried creating various queries, but these arne´t working.
Really frustrated, I can´t see what i´ve done wrong, and i´ve been comparing
to the Northwind sample and have tried everything!!

On Fri, 5 Jun 2009 06:41:01 -0700, Banjo
wrote:

I have 3 tables - Customer Details (Customer ID is the PK) containing
address etc. Unit Details (Unit ID is the PK) - which includes Description,
Price and Unit Code. I´ve then created another table that is called
Reservations and I want to be able to pick a UNIT and for it to then draw
down the description, and price.

It would seem that you're misunderstanding how relationships work!

The Reservations table should contain the CustomerID and the UnitID... *AND
NOTHING ELSE* from either table. That's how relational databases work - you
store the data in its own table, and then use queries and other tools to
display it. It is *not* necessary or appropriate to redundantly store the
description in two different tables; they'll get out of synch if either one
gets edited.

One exception would be the price: the price in the Unit Details table should
record today's current price, but the Reservations table should store the
price as of when the reservation was made. This will not happen automagically,
you will need some code to copy the price value.
--

John W. Vinson [MVP]

In message , Banjo
writes
Hi

I´m really stuck - and can´t see what i´m doing wrong.

I have 3 tables - Customer Details (Customer ID is the PK) containing
address etc. Unit Details (Unit ID is the PK) - which includes Description,
Price and Unit Code. I´ve then created another table that is called
Reservations and I want to be able to pick a UNIT and for it to then draw
down the description, and price. I dragged Customer ID into this table, and
also Unit ID and created one-to-many relationships with enforced referential
integrity. I´ve tried making Unit Code field a lookup, but it doesnt
autofill the rest of the row.

What you need is a multi-column List Box. Make the [Unit ID] the first
column and bind that to the field in the Reservations table. Display the
description and price as columns in the list box.

The reservations table needs at least three fields; [Unit ID], [Customer
ID] and [Reservation Price]. When you create the reservations record you
will need some code to copy the current value of the price from the Unit
table. This means that you can alter prices without affecting historical
data.

I suggest that the Reservations table should also include a field to
capture the precise time and data the record is created and the user ID
of the person that created it. You will want these for audit purposes.
You can also add a field that logs the time of any changes and the user
that made them.



This is where I now get stuck. I´ve tried to create a subform in my
customer form, that uses the Reservations table, but the rows just won´t
autofill. I´ve tried creating various queries, but these arne´t working.
Really frustrated, I can´t see what i´ve done wrong, and i´ve been
comparing
to the Northwind sample and have tried everything!!

--
Bernard Peek

Hi - Thanks for your answers. I´ve been having problems getting back on to
the site, hence the delay in replying. I´m afraid neither answer seems to
help me get to the bottom of this. I´ve tried various things based on your
answers, and I know it´s the basics that I seem to be missing... the logic
just isn´t sinking in.

Are there any sample basic databases I can look at or manuals? The
Northwind one is way too much information!! I´ve found one sample that shows
me the relationship area and I think i´ve sussed that bit out, but then i´m
doing something wrong in the table and form design bit.

Thanks again.

"John W. Vinson" wrote:

On Fri, 5 Jun 2009 06:41:01 -0700, Banjo
wrote:

I have 3 tables - Customer Details (Customer ID is the PK) containing
address etc. Unit Details (Unit ID is the PK) - which includes Description,
Price and Unit Code. I´ve then created another table that is called
Reservations and I want to be able to pick a UNIT and for it to then draw
down the description, and price.

It would seem that you're misunderstanding how relationships work!

The Reservations table should contain the CustomerID and the UnitID... *AND
NOTHING ELSE* from either table. That's how relational databases work - you
store the data in its own table, and then use queries and other tools to
display it. It is *not* necessary or appropriate to redundantly store the
description in two different tables; they'll get out of synch if either one
gets edited.

One exception would be the price: the price in the Unit Details table should
record today's current price, but the Reservations table should store the
price as of when the reservation was made. This will not happen automagically,
you will need some code to copy the price value.
--

John W. Vinson [MVP]

On Wed, 10 Jun 2009 03:55:01 -0700, Banjo
wrote:

Are there any sample basic databases I can look at or manuals? The
Northwind one is way too much information!! I´ve found one sample that shows
me the relationship area and I think i´ve sussed that bit out, but then i´m
doing something wrong in the table and form design bit.

Take a look here, especially Crystal's video and tutorials:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]