You've confused the issue a little here by referring to a subform. From your
description it sounds as though you are in fact referring to a separate form
as you mention 'opening' it. A subform is one which is embedded in the main
form and therefore opens with it. Normally the subform is linked to the
parent form on the keys, so the relevant value(s) from the parent form are
automatically inserted into the foreign key column(s) when a new record is
inserted in the subform. You might well find it better to embed your second
form in the first as a subform, which would avid the need to open the second
form and would automatically insert the key value(s).
How you'd link the parent form and subform would depend on what are the keys.
As Milton has explained you must not duplicate non-key values in the
inspection results table. If the primary key of the inspections table is a
composite one of the two columns PO# and P/N the it is quite correct to have
the same two columns as a composite foreign key in the inspection results
table. In this case the parent form and subform would be linked on the two
columns by making the subform control's LinkMasterFields and LinkChildFields
properties [PO#]/[P/N] in each case. If the key is the one column PO#,
however, then the P/N column should not be present in the inspection results
table, only the PO# as a foreign key, in which case the LinkMasterFields and
LinkChildFields properties would be [PO#] in each case. The alternative is,
as Milton suggests, to use an autonumber column as the surrogate primary key
of the inspections table, InspectionID say, and have a long integer number
column of the same name in the inspection results table. This column would
then be used as the LinkMasterFields and LinkChildFields properties.
Using two separate forms, the first question is do you want them linked, i.e.
do you want the inspection results form to show the results just for the
inspection form's current record, or the results of all inspections? If the
former filter the inspection results form when opening it from the
inspections form like so ( I'll assume the use of surrogate InspectionID keys
for this example)
Dim stCriteria As String
' first ensure current inspection record is saved
Me.Dirty = False
strCriteria = "InspectionID = " & Me.InspectionID
' open form filtered to current inspection
DoCmd.OpenForm "frmInspectionResults", WhereCondition:=strCriteria
' set default value of InspectionID control on inspection
' results form to current inspection record's value
Forms("frmInspectionResults").InspectionID.DefaultValue = """" & _
Me.LastName & """"
Note that the DefaultValue property is a string expression regardless of the
data type of the underlying field, so should be wrapped in quotes characters
as above.
If you want it opened to show all inspection results, don't filter it, so
simply use:
' open form unfiltered
DoCmd.OpenForm "frmInspectionResults"
' set default value of InspectionID control on inspection
' results form to current inspection record's value
Forms("frmInspectionResults").InspectionID.DefaultValue = """" & _
Me.InspectionID & """"
Finally, to keep the two form's in sync; if the second form is openbed
filtered put the following in the first form's Current event procedure:
Const FORM_NOT_OPEN = 2450
On Error Resume Next
Forms("frmInspectionResults").Filter = "InspectionID = " & Me.InspectionID
Forms("frmInspectionResults").InspectionID.DefaultValue = """" & _
Me.InspectionID & """"
Select Case Err.Number
Case 0
' no error
Case FORM_NOT_OPEN
' anicipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description, vbExclamation, "Error"
End Select
If its opened unfiltered put the following:
Const FORM_NOT_OPEN = 2450
On Error Resume Next
Forms("frmInspectionResults").InspectionID.DefaultValue = """" & _
Me.InspectionID & """"
Select Case Err.Number
Case 0
' no error
Case FORM_NOT_OPEN
' anicipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description, vbExclamation, "Error"
End Select
The error handling is needed in each case to cover the situation where the
record pointer in the first form moves while the second form is not open.
Ken Sheridan
Stafford, England
I have a main form for material inspection, and another form for the
inpsection results, and tables for each that store the data. For example, you
have to enter the PO# and P/N on the main form, as well as some other info,
then you click to open up the inspection results form in datasheet view. The
inpsection results form shows the PO# and P/N that you entered on the main
form, but it wont store the info in the table for the inspection results. Can
someone please help me figure out how to get the info to store in the
inspection results table as well as the material inspection table? Thanks for
your help!
--
.
