Link criteria on Open Form Command Button

[Cathleen]

I am trying to use a command button on a subform (SecchiEntryParam_sub) of
the form named Secchi_Entry to open a second form (WeatherCond), which
contains the subform WeatherCond_sub and go to the corresponding record. This
seems overly complicated, but I cannot find a way around it. Each
form/subform’s record source is a different table. The purpose is to allow a
user who is entering lake clarity data to pull up the form/subform to enter
corresponding data on weather conditions.

I need to link on two criteria, but one is in the main form and one is in
the subform:
First, I need to link the field [event_id] on the SecchiEntryParam_sub
subform to [WeatherCondEvent_event_id] on the WeatherCond form (these are
numeric fields). This step appears to be working o.k.
However, I also need to link the field [sample_type_secchi] on the
SecchiEntryParam_sub subform to [sample_type_weathercond] on the
WeatherCond_sub subform (these are text fields). I’ve made many attempts at
this but always get some sort of error message.

In the On Click Event of the command button, I have:
Private Sub OpenWeatherCond_Click()
On Error GoTo Err_OpenWeatherCond_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WeatherCond"

stLinkCriteria = "[WeatherCondEvent_event_id]= " & Me![event_id] & "And"
& "Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='" &
Me![sample_type_secchi] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenWeatherCond_Click:
Exit Sub

Err_OpenWeatherCond_Click:
MsgBox Err.Description
Resume Exit_OpenWeatherCond_Click

End Sub


I haven’t been able to find any other examples of someone linking on one
field in the main form and a second in a subform, so I’m hoping this can be
accomplished somehow. Could someone please check out my code and see what I
am doing wrong?

Thank you!

 

[Tom Wickerath]

Hi Cathleen,

I recommend inserting a Debug.Print statement immediately after the
assignment of the stLinkCriteria variable, as indicated below. After running
the code, inspect the results by displaying the Immediate Window (<Ctrl><G>).

One thing that stands out to me is that the word "And" at the end of the
first line shown below needs to be padded with spaces, so that it reads:

....& " And " & ....

Otherwise, the word "And" will be jammed up against the [event_id] value and
the[sample_type_weathercond] value from the subform. Also, the form you have
used indicates that [event_id] is numeric and [sample_type_weathercond] is
text. Is this correct?


stLinkCriteria = "[WeatherCondEvent_event_id]= " & Me![event_id] & "And"
& "Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='" &
Me![sample_type_secchi] & "'"

Debug.Print stLinkCriteria '<---Insert this line *******

DoCmd.OpenForm stDocName, , , stLinkCriteria


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

I am trying to use a command button on a subform (SecchiEntryParam_sub) of
the form named Secchi_Entry to open a second form (WeatherCond), which
contains the subform WeatherCond_sub and go to the corresponding record. This
seems overly complicated, but I cannot find a way around it. Each
form/subform’s record source is a different table. The purpose is to allow a
user who is entering lake clarity data to pull up the form/subform to enter
corresponding data on weather conditions.

I need to link on two criteria, but one is in the main form and one is in
the subform:
First, I need to link the field [event_id] on the SecchiEntryParam_sub
subform to [WeatherCondEvent_event_id] on the WeatherCond form (these are
numeric fields). This step appears to be working o.k.
However, I also need to link the field [sample_type_secchi] on the
SecchiEntryParam_sub subform to [sample_type_weathercond] on the
WeatherCond_sub subform (these are text fields). I’ve made many attempts at
this but always get some sort of error message.

In the On Click Event of the command button, I have:
Private Sub OpenWeatherCond_Click()
On Error GoTo Err_OpenWeatherCond_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WeatherCond"

stLinkCriteria = "[WeatherCondEvent_event_id]= " & Me![event_id] & "And"
& "Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='" &
Me![sample_type_secchi] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenWeatherCond_Click:
Exit Sub

Err_OpenWeatherCond_Click:
MsgBox Err.Description
Resume Exit_OpenWeatherCond_Click

End Sub


I haven’t been able to find any other examples of someone linking on one
field in the main form and a second in a subform, so I’m hoping this can be
accomplished somehow. Could someone please check out my code and see what I
am doing wrong?

Thank you!

 

[Cathleen]

Thanks, Tom - I tried your suggestions, but still have a problem I'm hoping
you could help with. So, I added the spaces before and after the "And" . I
no longer got error messages but the WeatherCond form opened on a new record.
However, when I inserted the Debug.Print statement, I got this line:

[WeatherCondEvent_event_id]= 122 And
Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='FP'

Those are the values of event_id and sample_type for the record displayed in
my Secchi_Entry form, so why does the WeatherCond form open on a new record?

And, yes, event_id is numeric and the sample_type fields are text.

Any insight would be greatly appreciated.

Thanks!

Hi Cathleen,

I recommend inserting a Debug.Print statement immediately after the
assignment of the stLinkCriteria variable, as indicated below. After running
the code, inspect the results by displaying the Immediate Window (<Ctrl><G>).

One thing that stands out to me is that the word "And" at the end of the
first line shown below needs to be padded with spaces, so that it reads:

....& " And " & ....

Otherwise, the word "And" will be jammed up against the [event_id] value and
the[sample_type_weathercond] value from the subform. Also, the form you have
used indicates that [event_id] is numeric and [sample_type_weathercond] is
text. Is this correct?


stLinkCriteria = "[WeatherCondEvent_event_id]= " & Me![event_id] & "And"
& "Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='" &
Me![sample_type_secchi] & "'"

Debug.Print stLinkCriteria '<---Insert this line *******

DoCmd.OpenForm stDocName, , , stLinkCriteria


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

I am trying to use a command button on a subform (SecchiEntryParam_sub) of
the form named Secchi_Entry to open a second form (WeatherCond), which
contains the subform WeatherCond_sub and go to the corresponding record. This
seems overly complicated, but I cannot find a way around it. Each
form/subform’s record source is a different table. The purpose is to allow a
user who is entering lake clarity data to pull up the form/subform to enter
corresponding data on weather conditions.

I need to link on two criteria, but one is in the main form and one is in
the subform:
First, I need to link the field [event_id] on the SecchiEntryParam_sub
subform to [WeatherCondEvent_event_id] on the WeatherCond form (these are
numeric fields). This step appears to be working o.k.
However, I also need to link the field [sample_type_secchi] on the
SecchiEntryParam_sub subform to [sample_type_weathercond] on the
WeatherCond_sub subform (these are text fields). I’ve made many attempts at
this but always get some sort of error message.

In the On Click Event of the command button, I have:
Private Sub OpenWeatherCond_Click()
On Error GoTo Err_OpenWeatherCond_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WeatherCond"

stLinkCriteria = "[WeatherCondEvent_event_id]= " & Me![event_id] & "And"
& "Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='" &
Me![sample_type_secchi] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenWeatherCond_Click:
Exit Sub

Err_OpenWeatherCond_Click:
MsgBox Err.Description
Resume Exit_OpenWeatherCond_Click

End Sub


I haven’t been able to find any other examples of someone linking on one
field in the main form and a second in a subform, so I’m hoping this can be
accomplished somehow. Could someone please check out my code and see what I
am doing wrong?

Thank you!

 

[Tom Wickerath]

Hi Cathleen,

Reviewing what you wrote earlier:

However, I also need to link the field [sample_type_secchi] on the
SecchiEntryParam_sub subform to [sample_type_weathercond] on the
WeatherCond_sub subform (these are text fields).

I don't think you can do that. The fields specified in the optional
WhereCondition parameter of the DoCmd.Openform method (stLinkCriteria) must
be present in the recordset of the form being opened, ie. WeatherCond.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Thanks, Tom - I tried your suggestions, but still have a problem I'm hoping
you could help with. So, I added the spaces before and after the "And" . I
no longer got error messages but the WeatherCond form opened on a new record.
However, when I inserted the Debug.Print statement, I got this line:

[WeatherCondEvent_event_id]= 122 And
Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='FP'

Those are the values of event_id and sample_type for the record displayed in
my Secchi_Entry form, so why does the WeatherCond form open on a new record?

And, yes, event_id is numeric and the sample_type fields are text.

Any insight would be greatly appreciated.

Thanks!

Hi Cathleen,

I recommend inserting a Debug.Print statement immediately after the
assignment of the stLinkCriteria variable, as indicated below. After running
the code, inspect the results by displaying the Immediate Window (<Ctrl><G>).

One thing that stands out to me is that the word "And" at the end of the
first line shown below needs to be padded with spaces, so that it reads:

....& " And " & ....

Otherwise, the word "And" will be jammed up against the [event_id] value and
the[sample_type_weathercond] value from the subform. Also, the form you have
used indicates that [event_id] is numeric and [sample_type_weathercond] is
text. Is this correct?


stLinkCriteria = "[WeatherCondEvent_event_id]= " & Me![event_id] & "And"
& "Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='" &
Me![sample_type_secchi] & "'"

Debug.Print stLinkCriteria '<---Insert this line *******

DoCmd.OpenForm stDocName, , , stLinkCriteria


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

I am trying to use a command button on a subform (SecchiEntryParam_sub) of
the form named Secchi_Entry to open a second form (WeatherCond), which
contains the subform WeatherCond_sub and go to the corresponding record. This
seems overly complicated, but I cannot find a way around it. Each
form/subform’s record source is a different table. The purpose is to allow a
user who is entering lake clarity data to pull up the form/subform to enter
corresponding data on weather conditions.

I need to link on two criteria, but one is in the main form and one is in
the subform:
First, I need to link the field [event_id] on the SecchiEntryParam_sub
subform to [WeatherCondEvent_event_id] on the WeatherCond form (these are
numeric fields). This step appears to be working o.k.
However, I also need to link the field [sample_type_secchi] on the
SecchiEntryParam_sub subform to [sample_type_weathercond] on the
WeatherCond_sub subform (these are text fields). I’ve made many attempts at
this but always get some sort of error message.

In the On Click Event of the command button, I have:
Private Sub OpenWeatherCond_Click()
On Error GoTo Err_OpenWeatherCond_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WeatherCond"

stLinkCriteria = "[WeatherCondEvent_event_id]= " & Me![event_id] & "And"
& "Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='" &
Me![sample_type_secchi] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenWeatherCond_Click:
Exit Sub

Err_OpenWeatherCond_Click:
MsgBox Err.Description
Resume Exit_OpenWeatherCond_Click

End Sub


I haven’t been able to find any other examples of someone linking on one
field in the main form and a second in a subform, so I’m hoping this can be
accomplished somehow. Could someone please check out my code and see what I
am doing wrong?

Thank you!

 

[Tom Wickerath]

By the way, is your goal to open the WeatherCond form to a specific record,
and then set focus to a specific record in the WeatherCond_Sub form?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

Hi Cathleen,

Reviewing what you wrote earlier:

However, I also need to link the field [sample_type_secchi] on the
SecchiEntryParam_sub subform to [sample_type_weathercond] on the
WeatherCond_sub subform (these are text fields).

I don't think you can do that. The fields specified in the optional
WhereCondition parameter of the DoCmd.Openform method (stLinkCriteria) must
be present in the recordset of the form being opened, ie. WeatherCond.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

Thanks, Tom - I tried your suggestions, but still have a problem I'm hoping
you could help with. So, I added the spaces before and after the "And" . I
no longer got error messages but the WeatherCond form opened on a new record.
However, when I inserted the Debug.Print statement, I got this line:

[WeatherCondEvent_event_id]= 122 And
Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='FP'

Those are the values of event_id and sample_type for the record displayed in
my Secchi_Entry form, so why does the WeatherCond form open on a new record?

And, yes, event_id is numeric and the sample_type fields are text.

Any insight would be greatly appreciated.

Thanks!
__________________________________________

:

Hi Cathleen,

I recommend inserting a Debug.Print statement immediately after the
assignment of the stLinkCriteria variable, as indicated below. After running
the code, inspect the results by displaying the Immediate Window (<Ctrl><G>).

One thing that stands out to me is that the word "And" at the end of the
first line shown below needs to be padded with spaces, so that it reads:

....& " And " & ....

Otherwise, the word "And" will be jammed up against the [event_id] value and
the[sample_type_weathercond] value from the subform. Also, the form you have
used indicates that [event_id] is numeric and [sample_type_weathercond] is
text. Is this correct?

stLinkCriteria = "[WeatherCondEvent_event_id]= " & Me![event_id] & "And"
& "Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='" &
Me![sample_type_secchi] & "'"

Debug.Print stLinkCriteria '<---Insert this line *******

DoCmd.OpenForm stDocName, , , stLinkCriteria


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

I am trying to use a command button on a subform (SecchiEntryParam_sub) of
the form named Secchi_Entry to open a second form (WeatherCond), which
contains the subform WeatherCond_sub and go to the corresponding record. This
seems overly complicated, but I cannot find a way around it. Each
form/subform’s record source is a different table. The purpose is to allow a
user who is entering lake clarity data to pull up the form/subform to enter
corresponding data on weather conditions.

I need to link on two criteria, but one is in the main form and one is in
the subform:
First, I need to link the field [event_id] on the SecchiEntryParam_sub
subform to [WeatherCondEvent_event_id] on the WeatherCond form (these are
numeric fields). This step appears to be working o.k.
However, I also need to link the field [sample_type_secchi] on the
SecchiEntryParam_sub subform to [sample_type_weathercond] on the
WeatherCond_sub subform (these are text fields). I’ve made many attempts at
this but always get some sort of error message.

In the On Click Event of the command button, I have:
Private Sub OpenWeatherCond_Click()
On Error GoTo Err_OpenWeatherCond_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WeatherCond"

stLinkCriteria = "[WeatherCondEvent_event_id]= " & Me![event_id] & "And"
& "Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='" &
Me![sample_type_secchi] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenWeatherCond_Click:
Exit Sub

Err_OpenWeatherCond_Click:
MsgBox Err.Description
Resume Exit_OpenWeatherCond_Click

End Sub


I haven’t been able to find any other examples of someone linking on one
field in the main form and a second in a subform, so I’m hoping this can be
accomplished somehow. Could someone please check out my code and see what I
am doing wrong?

Thank you!

 

[Cathleen]

Yes, I'd like to open the WeatherCond form with the subform set to a specific
record (that matches the sample type in the Secchi_Entry form and subform).

I thought I found a way around the problem by changing the recordsource of
the WeatherCond form to a query based on the two underlying tables,
WeatherCondEvent and WeatherCond (between which there is a one to many
relationship).

In this case, the command button on the SecchiEventParam_sub subform will
open the WeatherCond form to the desired record. However, there's a problem
when entering new records in the WeatherCond_sub subform for records with an
existing [event_id] in the WeatherCondEvent table. As Access is treating the
whole thing as a new record, I am trying to add a duplicate record to the
WeatherCondEvent table when I just need to add another record to the table
(WeatherCond) feeding the subform.

I think I may have some confusing form names, but maybe that makes sense to
you! Unless you know of some fix for this problem, it looks like I may need
to take another route for data entry into these tables.

I appreciate all of your help!

By the way, is your goal to open the WeatherCond form to a specific record,
and then set focus to a specific record in the WeatherCond_Sub form?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

Hi Cathleen,

Reviewing what you wrote earlier:

However, I also need to link the field [sample_type_secchi] on the
SecchiEntryParam_sub subform to [sample_type_weathercond] on the
WeatherCond_sub subform (these are text fields).

I don't think you can do that. The fields specified in the optional
WhereCondition parameter of the DoCmd.Openform method (stLinkCriteria) must
be present in the recordset of the form being opened, ie. WeatherCond.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

Thanks, Tom - I tried your suggestions, but still have a problem I'm hoping
you could help with. So, I added the spaces before and after the "And" . I
no longer got error messages but the WeatherCond form opened on a new record.
However, when I inserted the Debug.Print statement, I got this line:

[WeatherCondEvent_event_id]= 122 And
Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='FP'

Those are the values of event_id and sample_type for the record displayed in
my Secchi_Entry form, so why does the WeatherCond form open on a new record?

And, yes, event_id is numeric and the sample_type fields are text.

Any insight would be greatly appreciated.

Thanks!
__________________________________________

:

Hi Cathleen,

I recommend inserting a Debug.Print statement immediately after the
assignment of the stLinkCriteria variable, as indicated below. After running
the code, inspect the results by displaying the Immediate Window (<Ctrl><G>).

One thing that stands out to me is that the word "And" at the end of the
first line shown below needs to be padded with spaces, so that it reads:

....& " And " & ....

Otherwise, the word "And" will be jammed up against the [event_id] value and
the[sample_type_weathercond] value from the subform. Also, the form you have
used indicates that [event_id] is numeric and [sample_type_weathercond] is
text. Is this correct?

stLinkCriteria = "[WeatherCondEvent_event_id]= " & Me![event_id] & "And"
& "Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='" &
Me![sample_type_secchi] & "'"

Debug.Print stLinkCriteria '<---Insert this line *******

DoCmd.OpenForm stDocName, , , stLinkCriteria


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

I am trying to use a command button on a subform (SecchiEntryParam_sub) of
the form named Secchi_Entry to open a second form (WeatherCond), which
contains the subform WeatherCond_sub and go to the corresponding record. This
seems overly complicated, but I cannot find a way around it. Each
form/subform’s record source is a different table. The purpose is to allow a
user who is entering lake clarity data to pull up the form/subform to enter
corresponding data on weather conditions.

I need to link on two criteria, but one is in the main form and one is in
the subform:
First, I need to link the field [event_id] on the SecchiEntryParam_sub
subform to [WeatherCondEvent_event_id] on the WeatherCond form (these are
numeric fields). This step appears to be working o.k.
However, I also need to link the field [sample_type_secchi] on the
SecchiEntryParam_sub subform to [sample_type_weathercond] on the
WeatherCond_sub subform (these are text fields). I’ve made many attempts at
this but always get some sort of error message.

In the On Click Event of the command button, I have:
Private Sub OpenWeatherCond_Click()
On Error GoTo Err_OpenWeatherCond_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WeatherCond"

stLinkCriteria = "[WeatherCondEvent_event_id]= " & Me![event_id] & "And"
& "Forms![WeatherCond]![WeatherCond_sub].Form![sample_type_weathercond]='" &
Me![sample_type_secchi] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenWeatherCond_Click:
Exit Sub

Err_OpenWeatherCond_Click:
MsgBox Err.Description
Resume Exit_OpenWeatherCond_Click

End Sub


I haven’t been able to find any other examples of someone linking on one
field in the main form and a second in a subform, so I’m hoping this can be
accomplished somehow. Could someone please check out my code and see what I
am doing wrong?

Thank you!