Can not create form from 2 tables?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 24 Aug 2005 22:42 GMT

I am trying to create a basic form in an Access 2000 .adp file. My two
tables are Products and ProdPacks. Products contains the product name,
description, etc and ProdPacks contains a list of SKUs for the various
packaging options for each product in the Products table.

Using the Diagrams tool, I created a relationship with PK being RecordID in
Products, FK is ProductID in ProdPacks. RecordID is an integer field.

When I try to create the form in the wizard, it allows me to add the fields
from both tables, but when I try to go to the next step, it returns the
error "You have chosen fields from these tables: dbo.Products,
dbo.ProdPacks; One or more of the tables isn't related to the others. Click
OK to close the wizard so you can open a database diagram and create the
necessary relationships. ..."

The diagram window shows the 2 tables DO share a relationship, so I can't
figure out why the form wizard says they do not. The diagram has been saved,
and the database closed and re-opened a few times, but nothing changes.

Can you give me some ideas and suggestions?

Thanks.
Paul

Reply to this Message

Paul - 24 Aug 2005 23:00 GMT

Additional oddity just found:

I tried to delete a record from Products and an error popped up saying it
would cause a conflict due to the relationship (which the form wizard says
does not exist) and wouldn't let me delete the Products record. I deleted
the related records in ProdPacks, then it allowed me to delete the record
from Products. I thought one-to-many relationships would automatically
delete the many when the one was deleted?

Reply to this Message

Sylvain Lafontaine - 24 Aug 2005 23:28 GMT

No, the Cascade Delete Related Records property is not set to ON by default.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> Additional oddity just found:
>
[quoted text clipped - 4 lines]
> from Products. I thought one-to-many relationships would automatically
> delete the many when the one was deleted?

Reply to this Message

Sylvain Lafontaine - 24 Aug 2005 23:27 GMT

There are lots of problems with ADP-2000. Try the refresh function (F5 or
View --> Refresh) for both the Tables and the Queries windows instead of
closing/reopening the database.

For best results, you will have to set the UniqueTable property and a
ResyncCommand. Often, you will have problem setting the UniqueTable
property with the GUI. In these cases, do it in the OnOpen event or
temporary change the record source to give you the opportunity to set this
property.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

>I am trying to create a basic form in an Access 2000 .adp file. My two
>tables are Products and ProdPacks. Products contains the product name,
[quoted text clipped - 20 lines]
> Thanks.
> Paul

Reply to this Message