Form not saving records

[jree3000]

I have not built a MS Access database for about 20 years. I was asked to create one for a client and I am having difficulty getting back into the swing of things. I'm working in Access 2013.

Here is the problem I am having. I have linked several tables together using one to one relationships (linked the primary key ID). I then created a simple query containing fields from these tables that I need to incorporate into a form. The form is to be used by the client to enter new records. The form is bound to the query. All the data fields appear to be working properly. The problem is this: I go to the form, enter a new order, it autonumbers a new record, I save, go onto to a new record, enter more data, then close the form and when I go back there are no records.

Upon investigating I see that the record data is being stored in the tables (not the record source query) and the tables are not linked together the way they should be. For example I want the autonumber ID to be the same across all tables. Right now it is not doing that, the ID numbers do not match. I figured when creating this that all the data would be store in the query, perhaps I am just confused, actually I am quite sure I'm confused.



I want to be able to scroll through all existing records in the form, not just in the tables.

Is there anyone here that can give me a hand and help me figure out what it is I am doing wrong here? Thanks in advance for your assistance.

[June7]

Normally a form can be used to enter/edit data into only one table. A query with INNER JOIN(s) requires records in both/all tables for any records to display.

Why are these 1-to-1 related tables - why not 1 table?

Tables should not be linked on autonumber fields. One table should be primary and can have an autonumber PK but the other tables should save this value as a FK into a number field.

Use form/subform arrangement.

[ItsMe]

Like June mentioned, two tables with a one to one relationship is synonymous with having one table.

Also, queries and forms do not store data. You might be able to argue they do in memory via a recordset or data set. But, tables are the objects that store the data.

Use queries to retrieve data. Use forms as graphical interfaces.

[jree3000]

Many thanks for your time. I tried just making it all one table but many of the fields have a lookup relationship to other tables. I reached the maximum allowed (32) before finishing the fields in the table. So I figured if I broke them down into multiple table I may have more luck with that. Little did I know the can of worms I was opening. Back to the drawing board.

[June7]

That's a lot of lookups!

Recommend not building lookups in table, especially if alias is involved. Review: http://access.mvps.org/access/lookupfields.htm

How many tables and how many fields? I suspect not a normalized structure. If you want to provide db for analysis, follow instructions at bottom of my post.

[jree3000]

I actually saw that page earlier today when I was looking for answers. It does a good job describing the perils of lookups but what are the "healthy" alternatives?

My main table has 29 fields. I have lookups on about a dozen of those fields. The other 12 tables I have joined to the main table each have 6 fields, 3 of which being lookups, one being a yes/no check box. So all in all I am talking about 101 fields. Really i just want the capability to provide drop down choices for these fields within the form. If there is a better way than lookups to accomplish this, I am all ears.

I would provide the db but it contains a lot of confidential data within the structure so I'd rather avoid that if at all possible.

Based on what I've said here, what would be your suggested course of action?

[ItsMe]

Create an actual RDBMS by converting each lookup field into a table.

Use Comboboxes on forms to assign Primary Key values to the foreign key fields of the relative table.

[jree3000]

Excuse my ignorance but how would I go about doing that? I have my table open in design view. I have one of the fields in question selected and the Lookup tab selected. Not sure where to go from there. Is the goal to just return the field to a regular text field and then set up comboboxes for those fields in the form?

[ItsMe]

Kinda.

However, you do not want your field to be type Text. You want it to be type Number. Probably of type Long Integer so it is compatible with the type Autonumber.

This way your field can behave as a Foreign Key field.

Don't go straight to creating combos. You need tables to base the combobox's RowSource on.

Take the value list from your lookup field and use it to enter records in your new table. You could just skip to the combo and base the combo on its own value list. My preference is to use tables.


Tables are easy to find and understand. Value lists are not easy to find and understand. Look Up Fields at the table layer are not easy to locate and understand.

[jree3000]

Okay. I want to thank you both for your advice. I think you've given me enough to go on here. I'm gonna dive in and see if I can get this squared away tonight. Thanks.