Help with table design Q

[Guest]

Hi. Part one - my problem may be my design. I have a db with tables:

tblSystem (*SytemID, CustomerID, lots of detail fields)
tblCustomer (*CustomerID, name and address info...)
tblDatesAndServices (*ServiceNumber, SystemID, InspectionNumber, CustomerID
(no direct link), ServDate, etc, other service details...)
tblSubsurfaceInspections (*InspectionNumber, details of the inspection)

The strange part is getting the DatesAndServices and SubsurfaceInspections
to work the way I want. Often, but not always, a ServiceNumber is an
inspection, and then I want to link the two (in forms). This should be a
1-to-1 link, yes? but it won't let me. Do I just accept that it's 1-to-many?
or something else? (The same is really true of my Customers and Systems - I
only have one cust at a time for any system, we don't really care who they
are except for mailing, our business is the system itself.)

Once I figure this out, I hope I can get my forms to sync. Thanks for your
help.

Cat

 

[strive4peace]

Hi Cat,

try this:

mainform
Recordset --> tblCustomer

make sure you have the following controls:

Name --> CustomerID
ControlSource --> CustomerID

Name --> InspectionNumber
ControlSource --> =IIF(tblDatesAndServices.form.NewRecord, 0,
nz(tblDatesAndServices.form.InspectionNumber))



subform on mainform:
Name --> tblDatesAndServices
Recordset --> tblDatesAndServices
LinkMasterFields --> CustomerID
LinkChildFields --> CustomerID

make sure you have these controls:

Name --> CustomerID
ControlSource --> CustomerID

Name --> InspectionNumber
ControlSource --> InspectionNumber



subform on mainform:
Name --> SubsurfaceInspections
Recordset --> SubsurfaceInspections
LinkMasterFields --> InspectionNumber
LinkChildFields --> InspectionNumber

Name --> InspectionNumber
ControlSource --> InspectionNumber

if the second subform, SubsurfaceInspections doesn't switch
automatically, you may need to requery InspectionNumber on the mainform
on the current event of SubsurfaceInspections and the AfterUpdate event
of InspectionNumber

you also may need to force the record to save in tblDatesAndServices
after you update InspectionNumber


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*