Specification of multiple link criteria

[RoBo]

I am having a problem. From a selection from, I display a list of records
that meet one link field. From the shown list I would like to select a record
but this record can only be selected by using two link-criteria [ID_CSS] and
[ID-Lid]. How do I specify this correctly in the stLinkCriteria field?

Thanks for helping me out. in the Dutch forum nobody answered me.

Ron

 

[Allen Browne]

The basic idea is to put the 2 parts together with AND between them.
Brackets are optional, but can help.

This example assumes both fields are of type Number (not Text), and you have
text boxes on the form that supply the values:
stLinkCriteria = "([ID_CSS] = " & [ID_CSS] & ") AND ([ID-Lid] = " &
[ID-Lid] & ")"

If it doesn't work correctly, make this the next line:
Debug.Print stLinkCriteria
Then when it fails, open the Immediate Window (press Ctrl+G), and see what
came out. It has to look exactly like the WHERE clause of a query. You can
mock up a query putting any values in the Criteria, and swith the query to
SQL View (View menu in query design) to see the WHERE clause.

If the fields are Text type, you need extra quote marks.

 

[RoBo]

Hi Allen,

This does not seem to work. In the form in which I select a record (the form
is based on a query) the [ID_Lid] field contains the value of the first
record of the query. It should contain the value of the selected record in
that wquery. How do I set the value of the link field to the value [ID_Lid]
of the selected record?

Ron

The basic idea is to put the 2 parts together with AND between them.
Brackets are optional, but can help.

This example assumes both fields are of type Number (not Text), and you have
text boxes on the form that supply the values:
stLinkCriteria = "([ID_CSS] = " & [ID_CSS] & ") AND ([ID-Lid] = " &
[ID-Lid] & ")"

If it doesn't work correctly, make this the next line:
Debug.Print stLinkCriteria
Then when it fails, open the Immediate Window (press Ctrl+G), and see what
came out. It has to look exactly like the WHERE clause of a query. You can
mock up a query putting any values in the Criteria, and swith the query to
SQL View (View menu in query design) to see the WHERE clause.

If the fields are Text type, you need extra quote marks.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am having a problem. From a selection from, I display a list of records
that meet one link field. From the shown list I would like to select a
record
but this record can only be selected by using two link-criteria [ID_CSS]
and
[ID-Lid]. How do I specify this correctly in the stLinkCriteria field?

Thanks for helping me out. in the Dutch forum nobody answered me.

Ron

 

[RoBo]

The value should match the cursor position of the record selected on screen.

Ron

Hi Allen,

This does not seem to work. In the form in which I select a record (the form
is based on a query) the [ID_Lid] field contains the value of the first
record of the query. It should contain the value of the selected record in
that wquery. How do I set the value of the link field to the value [ID_Lid]
of the selected record?

Ron

The basic idea is to put the 2 parts together with AND between them.
Brackets are optional, but can help.

This example assumes both fields are of type Number (not Text), and you have
text boxes on the form that supply the values:
stLinkCriteria = "([ID_CSS] = " & [ID_CSS] & ") AND ([ID-Lid] = " &
[ID-Lid] & ")"

If it doesn't work correctly, make this the next line:
Debug.Print stLinkCriteria
Then when it fails, open the Immediate Window (press Ctrl+G), and see what
came out. It has to look exactly like the WHERE clause of a query. You can
mock up a query putting any values in the Criteria, and swith the query to
SQL View (View menu in query design) to see the WHERE clause.

If the fields are Text type, you need extra quote marks.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am having a problem. From a selection from, I display a list of records
that meet one link field. From the shown list I would like to select a
record
but this record can only be selected by using two link-criteria [ID_CSS]
and
[ID-Lid]. How do I specify this correctly in the stLinkCriteria field?

Thanks for helping me out. in the Dutch forum nobody answered me.

Ron

 

[RoBo]

Extra info: The form consists of a main form and a sub form. Both forms are
based on the same query. Main form "Maintenace CSS" and subform "Selection
CSS".
Field to link: ID_CSS and ID_Lid.
To open the form "Maintenance Lid", I need the ID_CSS from "Maintenace CSS"
and the ID_Lid from the form "Selection CSS".

Ron

The value should match the cursor position of the record selected on screen.

Ron

Hi Allen,

This does not seem to work. In the form in which I select a record (the form
is based on a query) the [ID_Lid] field contains the value of the first
record of the query. It should contain the value of the selected record in
that wquery. How do I set the value of the link field to the value [ID_Lid]
of the selected record?

Ron

The basic idea is to put the 2 parts together with AND between them.
Brackets are optional, but can help.

This example assumes both fields are of type Number (not Text), and you have
text boxes on the form that supply the values:
stLinkCriteria = "([ID_CSS] = " & [ID_CSS] & ") AND ([ID-Lid] = " &
[ID-Lid] & ")"

If it doesn't work correctly, make this the next line:
Debug.Print stLinkCriteria
Then when it fails, open the Immediate Window (press Ctrl+G), and see what
came out. It has to look exactly like the WHERE clause of a query. You can
mock up a query putting any values in the Criteria, and swith the query to
SQL View (View menu in query design) to see the WHERE clause.

If the fields are Text type, you need extra quote marks.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am having a problem. From a selection from, I display a list of records
that meet one link field. From the shown list I would like to select a
record
but this record can only be selected by using two link-criteria [ID_CSS]
and
[ID-Lid]. How do I specify this correctly in the stLinkCriteria field?

Thanks for helping me out. in the Dutch forum nobody answered me.

Ron

 

[Allen Browne]

If the ID_CSS value needs to come from from the main form, you could try
this:
stLinkCriteria = "([ID_CSS] = " & Me.Parent![ID_CSS] & ") AND ([ID-Lid]
= " & Me.[ID-Lid] & ")"

Each form has a current record. The value referred to in the statement will
be from the current record of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Extra info: The form consists of a main form and a sub form. Both forms
are
based on the same query. Main form "Maintenace CSS" and subform "Selection
CSS".
Field to link: ID_CSS and ID_Lid.
To open the form "Maintenance Lid", I need the ID_CSS from "Maintenace
CSS"
and the ID_Lid from the form "Selection CSS".

Ron

The value should match the cursor position of the record selected on
screen.

Ron

Hi Allen,

This does not seem to work. In the form in which I select a record (the
form
is based on a query) the [ID_Lid] field contains the value of the first
record of the query. It should contain the value of the selected record
in
that wquery. How do I set the value of the link field to the value
[ID_Lid]
of the selected record?

Ron

:

The basic idea is to put the 2 parts together with AND between them.
Brackets are optional, but can help.

This example assumes both fields are of type Number (not Text), and
you have
text boxes on the form that supply the values:
stLinkCriteria = "([ID_CSS] = " & [ID_CSS] & ") AND ([ID-Lid] = "
&
[ID-Lid] & ")"

If it doesn't work correctly, make this the next line:
Debug.Print stLinkCriteria
Then when it fails, open the Immediate Window (press Ctrl+G), and see
what
came out. It has to look exactly like the WHERE clause of a query.
You can
mock up a query putting any values in the Criteria, and swith the
query to
SQL View (View menu in query design) to see the WHERE clause.

If the fields are Text type, you need extra quote marks.

I am having a problem. From a selection from, I display a list of
records
that meet one link field. From the shown list I would like to
select a
record
but this record can only be selected by using two link-criteria
[ID_CSS]
and
[ID-Lid]. How do I specify this correctly in the stLinkCriteria
field?

Thanks for helping me out. in the Dutch forum nobody answered me.

 

[RoBo]

I get an error message saying that the expression entered has an invalid
reference to the Parent property.

Ron

If the ID_CSS value needs to come from from the main form, you could try
this:
stLinkCriteria = "([ID_CSS] = " & Me.Parent![ID_CSS] & ") AND ([ID-Lid]
= " & Me.[ID-Lid] & ")"

Each form has a current record. The value referred to in the statement will
be from the current record of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Extra info: The form consists of a main form and a sub form. Both forms
are
based on the same query. Main form "Maintenace CSS" and subform "Selection
CSS".
Field to link: ID_CSS and ID_Lid.
To open the form "Maintenance Lid", I need the ID_CSS from "Maintenace
CSS"
and the ID_Lid from the form "Selection CSS".

Ron

The value should match the cursor position of the record selected on
screen.

Ron

:

Hi Allen,

This does not seem to work. In the form in which I select a record (the
form
is based on a query) the [ID_Lid] field contains the value of the first
record of the query. It should contain the value of the selected record
in
that wquery. How do I set the value of the link field to the value
[ID_Lid]
of the selected record?

Ron

:

The basic idea is to put the 2 parts together with AND between them.
Brackets are optional, but can help.

This example assumes both fields are of type Number (not Text), and
you have
text boxes on the form that supply the values:
stLinkCriteria = "([ID_CSS] = " & [ID_CSS] & ") AND ([ID-Lid] = "
&
[ID-Lid] & ")"

If it doesn't work correctly, make this the next line:
Debug.Print stLinkCriteria
Then when it fails, open the Immediate Window (press Ctrl+G), and see
what
came out. It has to look exactly like the WHERE clause of a query.
You can
mock up a query putting any values in the Criteria, and swith the
query to
SQL View (View menu in query design) to see the WHERE clause.

If the fields are Text type, you need extra quote marks.

I am having a problem. From a selection from, I display a list of
records
that meet one link field. From the shown list I would like to
select a
record
but this record can only be selected by using two link-criteria
[ID_CSS]
and
[ID-Lid]. How do I specify this correctly in the stLinkCriteria
field?

Thanks for helping me out. in the Dutch forum nobody answered me.

 

[RoBo]

Allen,

The [ID_Lid] still refers to the first record of the recordset altough I
clicked in the record that I would like to maintain. The Parent reference is
solved.

Ron

If the ID_CSS value needs to come from from the main form, you could try
this:
stLinkCriteria = "([ID_CSS] = " & Me.Parent![ID_CSS] & ") AND ([ID-Lid]
= " & Me.[ID-Lid] & ")"

Each form has a current record. The value referred to in the statement will
be from the current record of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Extra info: The form consists of a main form and a sub form. Both forms
are
based on the same query. Main form "Maintenace CSS" and subform "Selection
CSS".
Field to link: ID_CSS and ID_Lid.
To open the form "Maintenance Lid", I need the ID_CSS from "Maintenace
CSS"
and the ID_Lid from the form "Selection CSS".

Ron

The value should match the cursor position of the record selected on
screen.

Ron

:

Hi Allen,

This does not seem to work. In the form in which I select a record (the
form
is based on a query) the [ID_Lid] field contains the value of the first
record of the query. It should contain the value of the selected record
in
that wquery. How do I set the value of the link field to the value
[ID_Lid]
of the selected record?

Ron

:

The basic idea is to put the 2 parts together with AND between them.
Brackets are optional, but can help.

This example assumes both fields are of type Number (not Text), and
you have
text boxes on the form that supply the values:
stLinkCriteria = "([ID_CSS] = " & [ID_CSS] & ") AND ([ID-Lid] = "
&
[ID-Lid] & ")"

If it doesn't work correctly, make this the next line:
Debug.Print stLinkCriteria
Then when it fails, open the Immediate Window (press Ctrl+G), and see
what
came out. It has to look exactly like the WHERE clause of a query.
You can
mock up a query putting any values in the Criteria, and swith the
query to
SQL View (View menu in query design) to see the WHERE clause.

If the fields are Text type, you need extra quote marks.

I am having a problem. From a selection from, I display a list of
records
that meet one link field. From the shown list I would like to
select a
record
but this record can only be selected by using two link-criteria
[ID_CSS]
and
[ID-Lid]. How do I specify this correctly in the stLinkCriteria
field?

Thanks for helping me out. in the Dutch forum nobody answered me.

 

[Allen Browne]

The expression should refer to the current row.

To verify this is the row you expect it to be, set the RecordSelectors
property to Yes for the subform. You then see a side-ways triangle in the
Record Selector pointing to the current row.

It could get reset to the first row after a Requery, or if the main form
moves record.

You can also verify which one is the current row by adding this line to the
code:
Debug.Print Me.[ID-Lid]
and checking in the Immediate Window (Ctrl+G) after it runs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

The [ID_Lid] still refers to the first record of the recordset altough I
clicked in the record that I would like to maintain. The Parent reference
is
solved.

Ron

If the ID_CSS value needs to come from from the main form, you could try
this:
stLinkCriteria = "([ID_CSS] = " & Me.Parent![ID_CSS] & ") AND
([ID-Lid]
= " & Me.[ID-Lid] & ")"

Each form has a current record. The value referred to in the statement
will
be from the current record of the form.

Extra info: The form consists of a main form and a sub form. Both forms
are
based on the same query. Main form "Maintenace CSS" and subform
"Selection
CSS".
Field to link: ID_CSS and ID_Lid.
To open the form "Maintenance Lid", I need the ID_CSS from "Maintenace
CSS"
and the ID_Lid from the form "Selection CSS".

Ron

:

The value should match the cursor position of the record selected on
screen.

Ron

:

Hi Allen,

This does not seem to work. In the form in which I select a record
(the
form
is based on a query) the [ID_Lid] field contains the value of the
first
record of the query. It should contain the value of the selected
record
in
that wquery. How do I set the value of the link field to the value
[ID_Lid]
of the selected record?

Ron

:

The basic idea is to put the 2 parts together with AND between
them.
Brackets are optional, but can help.

This example assumes both fields are of type Number (not Text),
and
you have
text boxes on the form that supply the values:
stLinkCriteria = "([ID_CSS] = " & [ID_CSS] & ") AND ([ID-Lid]
= "
&
[ID-Lid] & ")"

If it doesn't work correctly, make this the next line:
Debug.Print stLinkCriteria
Then when it fails, open the Immediate Window (press Ctrl+G), and
see
what
came out. It has to look exactly like the WHERE clause of a query.
You can
mock up a query putting any values in the Criteria, and swith the
query to
SQL View (View menu in query design) to see the WHERE clause.

If the fields are Text type, you need extra quote marks.

I am having a problem. From a selection from, I display a list of
records
that meet one link field. From the shown list I would like to
select a
record
but this record can only be selected by using two link-criteria
[ID_CSS]
and
[ID-Lid]. How do I specify this correctly in the stLinkCriteria
field?

Thanks for helping me out. in the Dutch forum nobody answered
me.