linking two forms

[Gen]

I have two tables: Table 1 stores the primary key, Protocol#, along with
details about the protocol. Table 2 stores additional information that will
apply to SOME of hte protocols. It has no primary key, but is linked to table
1 through Protocol#.

Problem: Form 1 is where I enter details into Table 1. I'd like to enter
information into Table 2 using a separate form (not a subform), Form 2, which
I can access from Form 1 using a button. I set the VBA code to open Form 2 as
follows:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FRM_2"
stLinkCriteria = "[Protocol#]=" & "'" & Me![Protocol#] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

This works when there is already information entered about a protocol# in
Table 2, but when I want to add new information using Form 2, when form 2
opens the protocol number is blank - it should be identical to the protocol#
currently displayed on Form 1! Incidentally, when I put Form 2 into Form 1 as
a subform, it works perfectly, but I'd really like them to be separate.

Can anyone help? Thanks very much.

 

[Ken Sheridan]

Pass the value of the Protocol# to the second form as its OpenArgs property.
It would also be advisable to explicitly save the current record in the first
form as otherwise referential integrity could be violated if a new record
with the same protocol# is created in the second form. You might also like
to consider opening the second form in dialogue mode, which forces the user
to close it (strictly speaking to at least hide it) before returning to the
first form:

Me.Dirty = False

DoCmd.OpenForm stDocName, _
WhereCondition:=stLinkCriteria, _
WindowMode:=acDialog, _
OpenArgs:=Me.[Protocol#]

Note the use of names arguments here.

In the second form's Open event procedure set the DefauktValue property of
the Protocol# control to the value, if any, passed o the form:

If Not IsNull(Me.OpenArgs) Then
Me.[Protocol#].DefaultValue = """" & Me.OpenAregs & """"
End If

Setting the DefaultValue property, unlike assigning a value to the control,
does not initiate a new record and 'Dirty' the form, so the user can back out
before adding any data if they wish simply by closing the second form.

BTW, if the relationship between the two tables is, as I think it is,
one-to-one, i.e. Table 2 models a sub type of the type modelled by Table 1,
then Protocol# should also be made the primary key of Table 2 as well as
being a foreign key referencing the primary key of Table 1.

Ken Sheridan
Stafford, England