wizard doesn't ask for table relationship

[chuck gregory]

I'm working with the 2002 (10.6771.6839) SP3 Access program. I originally (earn while I learn) developed a 250-field table.

To streamline it, I saved the table under six different names, then deleted from each of the six different sections of fields. This gave me six tables which included all the fields of the original elephant and allowed me to add in a few extra fields where needed.

I then created new queries, using the six new tables. However, unlike previous creation, the wizard did not ask what the relationship was between the tables.

I used two existing forms, and have no trouble entering data, but although the entered data can be found by opening up the tables, when I open the forms, previously entered data is not there.

Am I correct in suspecting that there's a relationship between the lack of the wizard's non-question and the problem? And of course, how can I fix it?

cg

[NeoPa]

Hard to follow exactly what you're describing Chuck, but a few things you should check :

1. Are all the tables sharing the same Primary Key (They should be)?
2. Have you defined the logical connection between all the tables in the Relationship window?
3. In truth, although it's not strictly necessary, I would consider treating one of the sub-tables as the principal table, such that it links to all the others, rather than all linking to all individually.

[Stewart Ross]

...I used two existing forms, and have no trouble entering data, but although the entered data can be found by opening up the tables, when I open the forms, previously entered data is not there. ...

Are the two forms set to Allow Additions but not Allow Edits, by any chance? Check in the form's properties, and if you open the forms from a command button check that they are not being opened in Add mode, i.e.

DoCmd.OpenForm "Name of Form", , , acFormAdd

instead of

DoCmd.OpenForm "Name of Form", , , acFormEdit

If there were problems with relationships I doubt you would be able to add new data at all.

-Stewart

[chuck gregory]

Stewart, the forms allow for both addition and edit.

NeoPa, I have all the tables linked by auto id to just one table. Is there some other relationship that they should have?

cg

[NeoPa]

No. If you mean what I think you mean (One table has an Autonumber field and all the others have numeric fields that are Foreign keys to that value in the principal table. They would also be PKs in their own tables of course, but not Autonumbers) then that's pretty well as I would recommend.

[chuck gregory]

It gets a little stranger, NeoPa--

I generated two new tables, all new titles, no relation to the tables I'm having the problem with, and then used the wizard to create a query (using fields from both of the tables).

Again, the wizard didn't ask if there was any relationship between the two tables.

Again, when I enter data, the data show up in the tables, but not on the forms when I open them.

Any insights?

cg

[chuck gregory]

NeoPa, here's an update--

I've found out that if I make a whole new form and new tables using the wizard, it will ask me about the relationship between tables, but not if I use imported forms or pieces from imported tables.. If you hve any insight as to how this would contribute to the problem of entered data failing to be retrieved when the form is opened up, I'd appreciate it.

Meanwhile, I'm solving the problem by generating all new forms...

cg

[NeoPa]

Not an area of great expertise for me I'm afraid Chuck (Form Wizard - I do them all manually now).

I will have a bit of a dig and see if I can notice something though.

[chuck gregory]

Thanks, NeoPa. It will be nice to know if's not simply paranoia.

[NeoPa]

I have been trying to fit this in but time has been tight recently :(

I'm out this evening too, so won't get much Byting time, but I'll see if I can give it some attention later in the week.

[NeoPa]

Seems like just paranoia I'm afraid :D

I think the automatic adding of the JOINs by the query wizard depends on three things (fundamentally) :

1. The Relationships defined (Relationship Pane).
2. The names of the fields.
3. The compatibility of the fields.

If the relationship has already been defined it should default in the query wizard. Otherwise, if the name is the same and the types are either the same or compatible, then it should also add the link in by default.

Do your tables match these criteria?

[chuck gregory]

they meet all three criteria, NeoPa-- they're all with the same title in the field, they're all AUto-id numbers, and they're all long integer.

I've given up with the imported tables and have been making new tables. I've done three-field tables to keep it simple-- auto-id and two 255 character text fields.

It works fine when I link only two tables. When I generate a third table and am asked for the relationship, neither linking the third table as many-to-one with the first or the second results in the form being useable.

Might as well give up on this one! At least I can use the klutzy old one. Thanks for your time and trouble.

[NeoPa]

No worries Chuck. Thank you for your consideration.

Final parting shot, you say many-to-one in your link. I would expect one-to-many to be updateable, but strangely not a many-to-one. If you have the inclination (you're possibly well fed-up by now) you could try it that way around and see.

Ooops, just noticed something else - if they are all AutoNumbers I would expect Access to realise that AutoNumbers should never be linked to AutoNumbers. I don't think that can make sense. Perhaps you just meant they are all Numbers?

[chuck gregory]

NeoPa, in my klutzy old one, almost all the tables are linked by autonumbers, and at least the whole system works. With this new attempt, I've tried linking two text fields together for just two tables, and even that doesn't work.

cg

[NeoPa]

When I say you can't link AutoNumber fields, I mean when BOTH fields are AutoNumbers.

For Text fields to link they should be of the same length. Again, they must also be One-to-One or, in some circumstances, One-to-Many. Many-to-One is not supported.

Does any of this help?