want to find to to new form and link to two different criterias

[angie]

I have a subform which when i click on a record takes me
to a new form which shows me the record for that
particular id reference. in this subform there are more
than one record for that id referenc and the new form
gives me all of them at the moment as i only seem to be
able to link by one field. I would like to link by two
fields so that when i go to that form i get to the
specific record i want rather than using the datacontrol
to find it. Can I do this?

 

[Ed Jobe]

There are a couple of different ways to do this.

Although you call the second form a "subform" it sounds as if you just open
when the record is clicked on. This is not a true subform. Change your main
form's display to "Single form" and then add a subform control for the
second form. The wizard will prompt you for the form to use an how to link
the data. By default, it only gives you the option of one field to link on.
Just accept that and then go to the properties for the subform's Data tab.
Put your cursor in the "Link Child Field" box or "Link Master Field" box
and notice that a button with an elipsis (...) appears. Click on it to open
the Linker wizard and you can link on more than one field.

The other way utilizes the filter argument when opening the form. Just add a
'where' clause for the filter when you open the form.
DoCmd.OpenForm "myform",acNormal,,"(([field1]='value1') AND
([field2]='value2'))"

 

[angie]

Thank you I am trying with the second option but I think i
have the syntax wrong. the first value is an integer and
the second is a string

Dim stCase As Integer
Dim stStage As String
stCase = Case_ID
stStage = Stage_Number
Dim stDocName As String
stDocName = "frmStagePractice"
DoCmd.OpenForm stDocName, acNormal, , "(([Case_id]=
stCase) AND ([Stage_number]= stStage))"

Any help appreciated

-----Original Message-----
There are a couple of different ways to do this.

Although you call the second form a "subform" it sounds as if you just open
when the record is clicked on. This is not a true subform. Change your main
form's display to "Single form" and then add a subform control for the
second form. The wizard will prompt you for the form to use an how to link
the data. By default, it only gives you the option of one field to link on.
Just accept that and then go to the properties for the subform's Data tab.
Put your cursor in the "Link Child Field" box or "Link Master Field" box
and notice that a button with an elipsis (...) appears. Click on it to open
the Linker wizard and you can link on more than one field.

The other way utilizes the filter argument when opening the form. Just add a
'where' clause for the filter when you open the form.
DoCmd.OpenForm "myform",acNormal,,"(([field1]='value1') AND
([field2]='value2'))"

--
Ed
--

I have a subform which when i click on a record takes me
to a new form which shows me the record for that
particular id reference. in this subform there are more
than one record for that id referenc and the new form
gives me all of them at the moment as i only seem to be
able to link by one field. I would like to link by two
fields so that when i go to that form i get to the
specific record i want rather than using the datacontrol
to find it. Can I do this?

.

 

[Ed Jobe]

Your where clause is incorrect. The syntax is fieldname=value. String values
must be in quotes. This whole clause must be a single string. Therefore you
have to build it when using variables by concatenating the clause text with
the variable values, not the variable names.

Dim stWhere As String

stWhere = "(([Case_id]= > " & stCase & ") AND ([Stage_number]='" & stStage &
"'))"

--
Ed
--

Thank you I am trying with the second option but I think i
have the syntax wrong. the first value is an integer and
the second is a string

Dim stCase As Integer
Dim stStage As String
stCase = Case_ID
stStage = Stage_Number
Dim stDocName As String
stDocName = "frmStagePractice"
DoCmd.OpenForm stDocName, acNormal, , "(([Case_id]=
stCase) AND ([Stage_number]= stStage))"

Any help appreciated

-----Original Message-----
There are a couple of different ways to do this.

Although you call the second form a "subform" it sounds as if you just open
when the record is clicked on. This is not a true subform. Change your main
form's display to "Single form" and then add a subform control for the
second form. The wizard will prompt you for the form to use an how to link
the data. By default, it only gives you the option of one field to link on.
Just accept that and then go to the properties for the subform's Data tab.
Put your cursor in the "Link Child Field" box or "Link Master Field" box
and notice that a button with an elipsis (...) appears. Click on it to open
the Linker wizard and you can link on more than one field.

The other way utilizes the filter argument when opening the form. Just add a
'where' clause for the filter when you open the form.
DoCmd.OpenForm "myform",acNormal,,"(([field1]='value1') AND
([field2]='value2'))"

--
Ed
--

I have a subform which when i click on a record takes me
to a new form which shows me the record for that
particular id reference. in this subform there are more
than one record for that id referenc and the new form
gives me all of them at the moment as i only seem to be
able to link by one field. I would like to link by two
fields so that when i go to that form i get to the
specific record i want rather than using the datacontrol
to find it. Can I do this?

.