strLinkCriteria

[Gwen H]

I have two forms, MainMenu and Enter_View_Requests. MainMenu has a button
that opens Enter_View_Requests and finds a specific record. I used the button
wizard to create the code for this, so I'm pretty sure it's correct, but here
it is:

'Button to view requests for a specific employee
Private Sub viewRequests_Click()
On Error GoTo Err_viewRequests_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Enter_View_Requests"

stLinkCriteria = "[empID]=" & Me![empList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_viewRequests_Click:
Exit Sub

Err_viewRequests_Click:
MsgBox Err.description
Resume Exit_viewRequests_Click

End Sub

When I click this button, it opens Enter_View_Requests, but it displays no
records, even if there are records that should be displayed. Here's the code
that runs when the form opens:

'Maximize the form when it is opened, display message box if no records found
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Forms.Item("MainMenu").Visible = False
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No requests for this employee since January 1, 2006.", , "No
Records Found"
Me.FilterOn = True
End If
End Sub

Any idea why Enter_View_Requests displays no records, when it should display
at least one?

Many thanks!
GwenH

 

[Marshall Barton]

I have two forms, MainMenu and Enter_View_Requests. MainMenu has a button
that opens Enter_View_Requests and finds a specific record. I used the button
wizard to create the code for this, so I'm pretty sure it's correct, but here
it is:

'Button to view requests for a specific employee
Private Sub viewRequests_Click()
On Error GoTo Err_viewRequests_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Enter_View_Requests"

stLinkCriteria = "[empID]=" & Me![empList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_viewRequests_Click:
Exit Sub

Err_viewRequests_Click:
MsgBox Err.description
Resume Exit_viewRequests_Click

End Sub

When I click this button, it opens Enter_View_Requests, but it displays no
records, even if there are records that should be displayed. Here's the code
that runs when the form opens:

'Maximize the form when it is opened, display message box if no records found
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Forms.Item("MainMenu").Visible = False
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No requests for this employee since January 1, 2006.", , "No
Records Found"
Me.FilterOn = True
End If
End Sub

Your syntax for the criteria assumes the empID is a numeric
type field. If the field in the table is type Text then it
should be:
stLinkCriteria = "[empID]=""" & Me![empList] & """"

There should be no need to set the form's FilerOn property.

 

[John Vinson]

Any idea why Enter_View_Requests displays no records, when it should display
at least one?

Possibly emplist isn't returning what you think it is - might the
control's Bound Column be incorrect?

Step through the code in the debugger and check that the
strLinkCriteria in fact contains a valid search string. If the ID is
of Text type you'll need quotemarks in the criterion:

stLinkCriteria = "[empID]='" & Me![empList] & "'"


John W. Vinson[MVP]

 

[Gwen H]

The empID field in the table is indeed numeric. What is the "form's FilerOn
property"?

Thanks!

I have two forms, MainMenu and Enter_View_Requests. MainMenu has a button
that opens Enter_View_Requests and finds a specific record. I used the button
wizard to create the code for this, so I'm pretty sure it's correct, but here
it is:

'Button to view requests for a specific employee
Private Sub viewRequests_Click()
On Error GoTo Err_viewRequests_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Enter_View_Requests"

stLinkCriteria = "[empID]=" & Me![empList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_viewRequests_Click:
Exit Sub

Err_viewRequests_Click:
MsgBox Err.description
Resume Exit_viewRequests_Click

End Sub

When I click this button, it opens Enter_View_Requests, but it displays no
records, even if there are records that should be displayed. Here's the code
that runs when the form opens:

'Maximize the form when it is opened, display message box if no records found
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Forms.Item("MainMenu").Visible = False
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No requests for this employee since January 1, 2006.", , "No
Records Found"
Me.FilterOn = True
End If
End Sub

Your syntax for the criteria assumes the empID is a numeric
type field. If the field in the table is type Text then it
should be:
stLinkCriteria = "[empID]=""" & Me![empList] & """"

There should be no need to set the form's FilerOn property.

 

[Gwen H]

The control on MainMenu has its bound property set to 2. The control has 3
columns, with the empID being the last column.

The empID is numeric in the source table and everywhere else.

I have tried stepping through the code in the debugger, but I always find
that to be of no use to me. All it does is highlight the line of code that is
wrong and give you a cryptic error message.

Thanks!

Any idea why Enter_View_Requests displays no records, when it should display
at least one?

Possibly emplist isn't returning what you think it is - might the
control's Bound Column be incorrect?

Step through the code in the debugger and check that the
strLinkCriteria in fact contains a valid search string. If the ID is
of Text type you'll need quotemarks in the criterion:

stLinkCriteria = "[empID]='" & Me![empList] & "'"


John W. Vinson[MVP]

 

[John Vinson]

The control on MainMenu has its bound property set to 2. The control has 3
columns, with the empID being the last column.

Unlike the Columns() property which is zero based, the BoundColumn
property is 1 based - I think you want the Bound Column to be 3.

The empID is numeric in the source table and everywhere else.

I have tried stepping through the code in the debugger, but I always find
that to be of no use to me. All it does is highlight the line of code that is
wrong and give you a cryptic error message.

You can "hover" the mouse above the name of a variable and see its
value, or you can use the Immediate window and type

?StrLinkCriteria

to see the current value of the variable; or you can set a Watch to
automatically display the values of one or more variables.

John W. Vinson[MVP]

 

[Gwen H]

What I did is setup a message box on MainMenu to display the value of
strLinkCriteria. It dislpayed the correct value, the employee ID (empID),
before opening the second form. So, I commented out the If statement in the
the following code from the second form (Enter_View_Requests), and the form
works. It would seem that Me.RecordSetClone.RecordCount finds the number of
records to be zero, even when there *are* records. Any ideas how to correct
this?

'Maximize the form when it is opened, display message box if no records found
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Forms.Item("MainMenu").Visible = False
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No requests for this employee since January 1, 2006.", , "No
Records Found"
Me.FilterOn = True
End If
End Sub

 

[Gwen H]

Update: I deleted the button on MainMenu and its associated VBA code. I also
deleted the whole Private Sub Form_Open code from the VBA code for
Enter_View_Requests. I closed the database, closed Access, then reopened it.
I went back into the VBA editor to ensure the code for the button and the
Form_Open were still gone. Then I added the button back to MainMenu that
opens Enter_View_Requests and used the wizard to write code that will apply a
filter. Enter_View_Requests opens with no records. So, that means the code
associated with the button on MainMenu isn't working:

Private Sub viewRequests_Click()
On Error GoTo Err_viewRequests_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Enter_View_Requests"

stLinkCriteria = "[empID]=" & Me![empList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_viewRequests_Click:
Exit Sub

Err_viewRequests_Click:
MsgBox Err.description
Resume Exit_viewRequests_Click

End Sub

The empID field in the underlying table of MainMenu is a number. On
MainMenu, it's a list box, which doesn't allow you set the format. The format
of the empID field on Enter_View_Requests is not set.

 

[Marshall Barton]

If you don't know what it is, why are you using it?
Regardless, it enables/disables the form's Filter property.
Since you are filtering through the OpenForm's
WhereCondition argument you are not trying to control the
Filter property so that line is superfluous.

In addition to John's outstanding deduction about the bound
column, I have to lock in on the record count being 0. To
me, this implies that the form has a problem in its record
source (perhaps there is some criteria in the form's record
source query?). What happens if you open the form directly
from the database window?
--
Marsh
MVP [MS Access]

The empID field in the table is indeed numeric. What is the "form's FilerOn
property"?

I have two forms, MainMenu and Enter_View_Requests. MainMenu has a button
that opens Enter_View_Requests and finds a specific record. I used the button
wizard to create the code for this, so I'm pretty sure it's correct, but here
it is:

'Button to view requests for a specific employee
Private Sub viewRequests_Click()
On Error GoTo Err_viewRequests_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Enter_View_Requests"

stLinkCriteria = "[empID]=" & Me![empList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_viewRequests_Click:
Exit Sub

Err_viewRequests_Click:
MsgBox Err.description
Resume Exit_viewRequests_Click

End Sub

When I click this button, it opens Enter_View_Requests, but it displays no
records, even if there are records that should be displayed. Here's the code
that runs when the form opens:

'Maximize the form when it is opened, display message box if no records found
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Forms.Item("MainMenu").Visible = False
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No requests for this employee since January 1, 2006.", , "No
Records Found"
Me.FilterOn = True
End If
End Sub

Your syntax for the criteria assumes the empID is a numeric
type field. If the field in the table is type Text then it
should be:
stLinkCriteria = "[empID]=""" & Me![empList] & """"

There should be no need to set the form's FilerOn property.

 

[Gwen H]

I am not using the form's "filer on" property. The bound column number is
correct. The record count on MainMenu is 1, while the record count on
Enter_View_Requests is 0. The form is based on a query that is not using any
criteria, other than to pull all the records from an underlying table, and
the query works fine when you run it alone.

If you don't know what it is, why are you using it?
Regardless, it enables/disables the form's Filter property.
Since you are filtering through the OpenForm's
WhereCondition argument you are not trying to control the
Filter property so that line is superfluous.

In addition to John's outstanding deduction about the bound
column, I have to lock in on the record count being 0. To
me, this implies that the form has a problem in its record
source (perhaps there is some criteria in the form's record
source query?). What happens if you open the form directly
from the database window?
--
Marsh
MVP [MS Access]

The empID field in the table is indeed numeric. What is the "form's FilerOn
property"?

Gwen H wrote:

I have two forms, MainMenu and Enter_View_Requests. MainMenu has a button
that opens Enter_View_Requests and finds a specific record. I used the button
wizard to create the code for this, so I'm pretty sure it's correct, but here
it is:

'Button to view requests for a specific employee
Private Sub viewRequests_Click()
On Error GoTo Err_viewRequests_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Enter_View_Requests"

stLinkCriteria = "[empID]=" & Me![empList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_viewRequests_Click:
Exit Sub

Err_viewRequests_Click:
MsgBox Err.description
Resume Exit_viewRequests_Click

End Sub

When I click this button, it opens Enter_View_Requests, but it displays no
records, even if there are records that should be displayed. Here's the code
that runs when the form opens:

'Maximize the form when it is opened, display message box if no records found
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Forms.Item("MainMenu").Visible = False
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No requests for this employee since January 1, 2006.", , "No
Records Found"
Me.FilterOn = True
End If
End Sub


Your syntax for the criteria assumes the empID is a numeric
type field. If the field in the table is type Text then it
should be:
stLinkCriteria = "[empID]=""" & Me![empList] & """"

There should be no need to set the form's FilerOn property.

 

[Marshall Barton]

What happens when you open the Enter_View_Requests form
directly from the database window? This simple test removes
the mainmenu form from the problem and bypasses all the
filtering code etc. From everthing I think you've said, it
sounds like it will have no records here too. If so, there
is no way applying a filter to a 0 record dataset is going
to include records that the form never displayed in its most
basic situation.

The FilterOn issue is not all that important, but your code
sets it in Enter_View_Requests Open event. If that's not
clear, let's just forget it for now.
--
Marsh
MVP [MS Access]

I am not using the form's "filer on" property. The bound column number is
correct. The record count on MainMenu is 1, while the record count on
Enter_View_Requests is 0. The form is based on a query that is not using any
criteria, other than to pull all the records from an underlying table, and
the query works fine when you run it alone.

If you don't know what it is, why are you using it?
Regardless, it enables/disables the form's Filter property.
Since you are filtering through the OpenForm's
WhereCondition argument you are not trying to control the
Filter property so that line is superfluous.

In addition to John's outstanding deduction about the bound
column, I have to lock in on the record count being 0. To
me, this implies that the form has a problem in its record
source (perhaps there is some criteria in the form's record
source query?). What happens if you open the form directly
from the database window?

The empID field in the table is indeed numeric. What is the "form's FilerOn
property"?

:

Gwen H wrote:

I have two forms, MainMenu and Enter_View_Requests. MainMenu has a button
that opens Enter_View_Requests and finds a specific record. I used the button
wizard to create the code for this, so I'm pretty sure it's correct, but here
it is:

'Button to view requests for a specific employee
Private Sub viewRequests_Click()
On Error GoTo Err_viewRequests_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Enter_View_Requests"

stLinkCriteria = "[empID]=" & Me![empList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_viewRequests_Click:
Exit Sub

Err_viewRequests_Click:
MsgBox Err.description
Resume Exit_viewRequests_Click

End Sub

When I click this button, it opens Enter_View_Requests, but it displays no
records, even if there are records that should be displayed. Here's the code
that runs when the form opens:

'Maximize the form when it is opened, display message box if no records found
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Forms.Item("MainMenu").Visible = False
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No requests for this employee since January 1, 2006.", , "No
Records Found"
Me.FilterOn = True
End If
End Sub


Your syntax for the criteria assumes the empID is a numeric
type field. If the field in the table is type Text then it
should be:
stLinkCriteria = "[empID]=""" & Me![empList] & """"

There should be no need to set the form's FilerOn property.

 

[Gwen H]

Thanks for your time ... I solved the problem by restoring the form
"Enter_View_Requests" from a backup file. I had to get the backup file from
our network administrator, who has been out of town for 2 weeks. I had hoped
to solve the problem before his return, but I didn't.

I was not using the form's "filer on" property, but I was using the form's
"filter on" property. I was not sure whether you were talking about a
different property, or it was just a typo in your original post.

What happens when you open the Enter_View_Requests form
directly from the database window? This simple test removes
the mainmenu form from the problem and bypasses all the
filtering code etc. From everthing I think you've said, it
sounds like it will have no records here too. If so, there
is no way applying a filter to a 0 record dataset is going
to include records that the form never displayed in its most
basic situation.

The FilterOn issue is not all that important, but your code
sets it in Enter_View_Requests Open event. If that's not
clear, let's just forget it for now.
--
Marsh
MVP [MS Access]

I am not using the form's "filer on" property. The bound column number is
correct. The record count on MainMenu is 1, while the record count on
Enter_View_Requests is 0. The form is based on a query that is not using any
criteria, other than to pull all the records from an underlying table, and
the query works fine when you run it alone.

If you don't know what it is, why are you using it?
Regardless, it enables/disables the form's Filter property.
Since you are filtering through the OpenForm's
WhereCondition argument you are not trying to control the
Filter property so that line is superfluous.

In addition to John's outstanding deduction about the bound
column, I have to lock in on the record count being 0. To
me, this implies that the form has a problem in its record
source (perhaps there is some criteria in the form's record
source query?). What happens if you open the form directly
from the database window?

Gwen H wrote:
The empID field in the table is indeed numeric. What is the "form's FilerOn
property"?

:

Gwen H wrote:

I have two forms, MainMenu and Enter_View_Requests. MainMenu has a button
that opens Enter_View_Requests and finds a specific record. I used the button
wizard to create the code for this, so I'm pretty sure it's correct, but here
it is:

'Button to view requests for a specific employee
Private Sub viewRequests_Click()
On Error GoTo Err_viewRequests_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Enter_View_Requests"

stLinkCriteria = "[empID]=" & Me![empList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_viewRequests_Click:
Exit Sub

Err_viewRequests_Click:
MsgBox Err.description
Resume Exit_viewRequests_Click

End Sub

When I click this button, it opens Enter_View_Requests, but it displays no
records, even if there are records that should be displayed. Here's the code
that runs when the form opens:

'Maximize the form when it is opened, display message box if no records found
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Forms.Item("MainMenu").Visible = False
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No requests for this employee since January 1, 2006.", , "No
Records Found"
Me.FilterOn = True
End If
End Sub


Your syntax for the criteria assumes the empID is a numeric
type field. If the field in the table is type Text then it
should be:
stLinkCriteria = "[empID]=""" & Me![empList] & """"

There should be no need to set the form's FilerOn property.

 

[Marshall Barton]

Whoa! That sounds like the form might have been corrupted.
If that's what was going on, then all bets are off and it's
no wonder we couldn't figure it out.

Glad to here you're back up and runnung again.
--
Marsh
MVP [MS Access]

Thanks for your time ... I solved the problem by restoring the form
"Enter_View_Requests" from a backup file. I had to get the backup file from
our network administrator, who has been out of town for 2 weeks. I had hoped
to solve the problem before his return, but I didn't.

I was not using the form's "filer on" property, but I was using the form's
"filter on" property. I was not sure whether you were talking about a
different property, or it was just a typo in your original post.

What happens when you open the Enter_View_Requests form
directly from the database window? This simple test removes
the mainmenu form from the problem and bypasses all the
filtering code etc. From everthing I think you've said, it
sounds like it will have no records here too. If so, there
is no way applying a filter to a 0 record dataset is going
to include records that the form never displayed in its most
basic situation.

The FilterOn issue is not all that important, but your code
sets it in Enter_View_Requests Open event. If that's not
clear, let's just forget it for now.
--
Marsh
MVP [MS Access]

I am not using the form's "filer on" property. The bound column number is
correct. The record count on MainMenu is 1, while the record count on
Enter_View_Requests is 0. The form is based on a query that is not using any
criteria, other than to pull all the records from an underlying table, and
the query works fine when you run it alone.

:

If you don't know what it is, why are you using it?
Regardless, it enables/disables the form's Filter property.
Since you are filtering through the OpenForm's
WhereCondition argument you are not trying to control the
Filter property so that line is superfluous.

In addition to John's outstanding deduction about the bound
column, I have to lock in on the record count being 0. To
me, this implies that the form has a problem in its record
source (perhaps there is some criteria in the form's record
source query?). What happens if you open the form directly
from the database window?

Gwen H wrote:
The empID field in the table is indeed numeric. What is the "form's FilerOn
property"?

:

Gwen H wrote:

I have two forms, MainMenu and Enter_View_Requests. MainMenu has a button
that opens Enter_View_Requests and finds a specific record. I used the button
wizard to create the code for this, so I'm pretty sure it's correct, but here
it is:

'Button to view requests for a specific employee
Private Sub viewRequests_Click()
On Error GoTo Err_viewRequests_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Enter_View_Requests"

stLinkCriteria = "[empID]=" & Me![empList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_viewRequests_Click:
Exit Sub

Err_viewRequests_Click:
MsgBox Err.description
Resume Exit_viewRequests_Click

End Sub

When I click this button, it opens Enter_View_Requests, but it displays no
records, even if there are records that should be displayed. Here's the code
that runs when the form opens:

'Maximize the form when it is opened, display message box if no records found
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Forms.Item("MainMenu").Visible = False
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No requests for this employee since January 1, 2006.", , "No
Records Found"
Me.FilterOn = True
End If
End Sub


Your syntax for the criteria assumes the empID is a numeric
type field. If the field in the table is type Text then it
should be:
stLinkCriteria = "[empID]=""" & Me![empList] & """"

There should be no need to set the form's FilerOn property.