Open form trying to use where parameter, somewhat lengthy.

[cominapart]

When opening a second form using a command button on the first form i have found that it is necessary to use the where parameter to properly do so from the other posts I have searched and read through. However when the form opens it does not appear to do so. Probably does but i do not know how to initialze or pass the data to this field correctly.

Here is the scenario. I have a main tbl named tblContact with ContactID as the primary key. This tbl is related to tblContract
with a foreign key named ContactID also, ( not using this as primary key ). Within tblContract is a primary key of ControlNumber with text as its data type. Their relationship is a one-to-many since any one individual may have multiple contracts.

Two more tables are involved. These tables are for brevity sakes tbl1 and tbl2. These two tbls 1 & 2 are both related to tblContract in a one-to-one relationship using ControlNumber data type text as their primary keys. ( to many fields to place them all in the same table - information different ControlNumber Unique)

I use an inserted subform on frmContact to enter the
ControlNumber for frmContract which i open from within the subform on frmContact, this works and populates field ControlNumber . I have a command button to open form x from frmContract and use the code below however the ControlNumber field does not populate with any information. I am trying to pass or have entered automatically the ControlNumber to this third form and have it populated by the value in frmContract field
= ControlNumber.

Private Sub Command320_Click()
On Error GoTo Err_Command320_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmX"

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

Exit_Command320_Click:
Exit Sub

Err_Command320_Click:
MsgBox Err.Description
Resume Exit_Command320_Click

End Sub

Hope this wasn't to confusing or to long, thanks for any help.

 

[WayneRyan]

cominapart,

Since you are not using the Master-Child relationships that
subforms allow, Access does not "know" that they are
related.

You can make the default value for [ControlNumber]:

=Forms![YourMainForm]![ControlNumber]

That way when you enter a new record, it will have the
proper ID.

hth,
Wayne

 

[cominapart]

Thanks Wayne. I'll go to the Forms Forum, do some research and correct my mistake. I am a newbie just trying to make sense of all of this.