If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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!! |
#2
|
|||
|
|||
Records not auto-filling
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!! |
#3
|
|||
|
|||
Records not auto-filling
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!! |
#4
|
|||
|
|||
Records not auto-filling
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] |
#5
|
|||
|
|||
Records not auto-filling
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 |
#6
|
|||
|
|||
Records not auto-filling
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] |
#7
|
|||
|
|||
Records not auto-filling
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] |
Thread Tools | |
Display Modes | |
|
|