Multiple Tables/Forms/Relationships But No Data Link

[Shawn Sheridan]

I have created a customer database with a simple table containing contact
information and then about 15 tables containing diverse information (e.g.,
Activity Logs, Equipment Details, Work Orders, etc.)

The main form contains the Customer table and each subform contains its own
table linked to the main form with command buttons. Each table contains the
CustomerID Foreign Key in one-to-many relationships with the CustomerID
Primary Key in the main table. Each table has its own PK (AutoNumber) with
the FKs set to Long Integer Number. The relationship is PK main table to FK
in minor table.

However, when I open a subform, from the main form, the data entered does
not link to the current record viewed on the main form. I have spent HOURS
searching, asking, looking around, reading Microsoft Help and have not been
able to discover why it is not linking. Can you help me??????????

I would be glad to email the database shell (1.5MB) to someone for review.

 

[Duane Hookom]

Use the Link Master/Child properties to "synch" records between the main
form and subform.

 

[John Vinson]

On Wed, 13 Oct 2004 16:07:02 -0700, "Shawn Sheridan" <Shawn

However, when I open a subform, from the main form, the data entered does
not link to the current record viewed on the main form.

Is this actually a *SUBFORM*, always present in a Subform control on
the body of the mainform? Or are you *opening* a separate form in VBA
code?

A Subform automatically provides a link. A form that you open yourself
doesn't - you'll need VBA code to pass the linking field, set the
second form's Filter, set the default value of the linking field, etc.
- all this is unnecessary if you use a Subform control and set the
Master Link Field and Child Link Field properties of the Subform
control to the linking fields.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Shawn Sheridan]

When I created the command button, that opened a popup form from the main
form, it allowed me to link the Customer ID PK with the CustomerID FK on the
"subform". Is that what you're referring to? If not, where can I find the
Link Master/Child Properties? I have researched this in what I thought was an
extensive way and still have not been able to figure out why it's not
working. Everything looks correct throughout.

 

[Shawn Sheridan]

Thanks for helping me. The main form has command buttons that open the popup
minor forms. When creating these buttons, I linked the CustomerID PK with the
minor forms' CustomerID FK. Everything looks right. In a previous database I
correctly, I never had this problem. However, the only difference between
this database and the one before is that on this new design every single form
has its own table. Other than that, linking with command buttons was done the
same way previously with success.

 

[John Vinson]

Thanks for helping me. The main form has command buttons that open the popup
minor forms. When creating these buttons, I linked the CustomerID PK with the
minor forms' CustomerID FK. Everything looks right. In a previous database I
correctly, I never had this problem. However, the only difference between
this database and the one before is that on this new design every single form
has its own table. Other than that, linking with command buttons was done the
same way previously with success.

Then please don't call them subforms. They're not; a Subform is a
control in its own right.

Please post the code that opens the popup form. Are you passing the
link field in the OpenArgs property? Does the popped-up form then use
that value?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Shawn Sheridan]

----------------------------------------------------------------
Private Sub OpenAmplifier_Click()
On Error GoTo Err_OpenAmplifier_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEquipAmplifier"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenAmplifier_Click:
Exit Sub

Err_OpenAmplifier_Click:
MsgBox Err.Description
Resume Exit_OpenAmplifier_Click

End Sub

 

[John Vinson]

----------------------------------------------------------------
Private Sub OpenAmplifier_Click()
On Error GoTo Err_OpenAmplifier_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEquipAmplifier"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenAmplifier_Click:
Exit Sub

Err_OpenAmplifier_Click:
MsgBox Err.Description
Resume Exit_OpenAmplifier_Click

End Sub

This code will filter the table so that existing records with the
current CustomerID will be displayed. That's ALL it will do. There is
nothing in this code to ensure that newly entered records have that
ID!

To accomplish this, you'll need to pass the CustomerID in the OpenArgs
property of OpenForm, and use the form's Open event to set it as the
default:

DoCmd.OpenForm stDocName, WhereCondition:=stLinkCriteria, _
OpenArgs:=Me![CustomerID]

and in frmEquipAmplifier's Open event:

Private Sub Form_Open(Cancel as Integer)
If Me.OpenArgs & "" <> "" Then
Me!txtCustomerID.DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
End Sub


I'm curious why you seem to be so reluctant to use the builtin Subform
functionality!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps