Subform...dumb question due to Christmas fried brain!

[Colin Foster]

Hi Group,
My brain has fried, I guess, after the excesses of Christmas!
I'm building a database which records a series of projects each with a
unique project ID. Against this, a number of companies can be asked to quote
("Enquirers") and, once one of them is successful, additional companies can
be asked to do other further work ("Contractors"). "Enquirers" can also be
"Contractors".
So, I've set up two subforms within my main project form, both pointing to
the TblCustomers (where I record both "Contractors" and "Enquirers" so that
I don't have to retype information if an "Enquirer" becomes a "Contractor")
called FrmEnquirer and FrmContractor. The problem that I have (and this is,
I guess, where the fried brain syndrome hits) is I can't remember how to set
up a link between the two subforms and the main form containing the project.
Any suggestions would be gratefully received.
Cheers
Colin Foster

 

[MacDermott]

I think I would use a join table, with fields like this:
ProjectID
CompanyID
Relationship (Enquirer, Contractor)

Does that help?
- Turtle

 

[Colin Foster]

Thanks for this...I thought that I'd got around it myself (but was testing
before I reposted here), but its not working...
What I've done is set up a join table as follows...
tblCustomerProject
fldCustomerProjectID
fldProjectID
fldCustomerID

I then set up a form (frmCustomerProject) based on this table which I then
set as a subform within my main Projects form. Then, within this
frmCustomerProject I created another subform based upon my fwmEnquirer.

So far, so good!

However, whenever I try to create a new enquirer, the customerID (which is
set as autonumber) starts at 0 and I then receive an error message stating
that the changes would be unsuccessful as they would create duplicates.

What I need to be able to do is to create new "customers" within the body of
this subform. I've done this before, but can't remember how (very
frustrating!) so any further help would be appreciated.
Regards
Colin

 

[MacDermott]

Have you defined relational integrity with cascade updates?

- Turtle

 

[Colin Foster]

No, why, could this be the problem?

Have you defined relational integrity with cascade updates?

- Turtle

body how

 

[Colin Foster]

I've just had a play within the Customers table and can create new customers
here without a hitch, so I guess that the fault is in teh way that I've got
the subforms working (or not as the case may be!)...however that's thrown up
a second issue (which may help explain the first one)...

I now can see 4 customers of which I want 2 to relate to the current
project. If on my form frmCustomerProject I select one of the customers,
then this appears in the frmEnquirer . If I select a second customer the
details change. However, I still can't create a second customer ffrom within
the subform...it's as though by setting up the relationship to allow
customers to link to projects so that I can have one project with many
enquirers has been fixed to a one to one relationship. I'll recheck the
relationships that I've set in case there is something there.
Colin