Form won't show entered records

[FMAlanbrooke]

Hi, I have a Units table which has 280 records entered - just the autonumber ID and the name of the units. I have added many more fields to it and want to fill them in as well using a form. One of the fields is an Owner field, which comes from the Owner table as a Foreign Key. I created the form automatically and everything looked fine. The only problem was that it was only showing the ID number for the Owner. So I put in the Owner's name field from the Owners Table (which has 60 records entered). Suddenly the form will only show record 1 of 1 or all fields empty.



I deleted the Owners name field and it made no difference. I deleted the form and did it all again, this time using the form wizard and only using a few fields. Same thing happened - as soon as anything other than OwnerID from the Owner's form was added, the form only showed record 1 of 1 or New record. The 280 records in the Unit Table are still there. A combo-box listing of all the owners in that form works fine. If I add a record to the Unit table using this form, it shows on the form as record 1 but on the table as record 281. I need to edit records 1-280 and be sure I am allocating the right Owner to the right Unit.

It is OK to put fields from different tables on the same form isn't it? What's going on? Sorry if this is a stupid question, I'm trying to learn how to use Access. I checked the form properties, they all seem to be OK (Additions, Deletions, Edits = Yes; Data Entry=No).

[orange]

The secret is to describe, in plain English, what the proposed database is about. Look at the description and identify the nouns --these are typically the entities or things involved. You really need to get the tables and relationships designed to meet your business requirements before moving to forms etc. Here is a tutorial that leads you from a business description/requirement to a well structured database design. The answer is included, but the tutorial requires that you work through it.

Good luck.

[FMAlanbrooke]

Thanks for that. I've done the tutorial (it's an interesting method) and re-done my database designDatabase Narrative.zip I didn't find any reason in that tutorial why previously entered data should suddenly stop showing in a form when I added a field from a different table (and why it should stay that way when the offending field is removed). It did get me second-guessing everything. I previously spent weeks designing the database, even creating a little Powerpoint presentationDatabase diagram.zip showing the desired output as well as the database design (which now seems much too complex). The only thing I can see is that perhaps I should have a one to one relationship where I have a one to many relationship, between OWNERS and UNITS (as each can have only one of the other) and that I have got several of the one to many relationships the wrong way round (though if I reverse them it doesn't make sense). I tried changing the properties of the relationship between OWNERS and UNITS but none of the changes made any difference - the form still wouldn't show any of the previously entered records.

It's obvious when you have many customers and many orders but each order can have only one customer and each customer can have many orders that it is a one to many relationship between customers and orders. If there are many managers, many departments, and each manager can have only one department- but has many departments to choose from, and each department can only have one manager, that's when I get confused - is that a one to one relationship or a one to many, and if the latter, which way round is the one to many relationship? It seems to be a one to many between manager and department but because the manager can only have one, mutually exclusive, department, I'm not so sure. Sorry to be such an idiot, I thought this was a simple project that could be made available on-line free for other researchers when finished.

[orange]

Did you actually work through the author's tutorial? Follow his logic/approach to determine tables and relationships...

[FMAlanbrooke]

Yes, I did what he suggested to my database and posted the results in Database Narrative.zip. The tutorial says nothing about forms or entering data. I'm not sure that the relationships are responsible for my problem as I changed the relationship from one- to- many to one -to- one and it made no difference even to several new, simple forms that I tried creating - I still only get record 1 of 1 instead of record 1 of 280.