Open Form With Filter Not Working

[Gwen H]

I used the command button wizard on a form, "MainMenu", to create a button
that opens another form, "Enter_View_Requests". MainMenu has a list box
"empList" with 3 columns. The third column is empID. This is the column I
want to use to filter Enter_View_Requests. Below is the code generated by the
command button wizard. However, when I click the new button,
Enter_View_Requests opens with no records displayed. (Yes I have a record
selected in empList, and there are associated records that should be
displayed on Enter_View_Requests).

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 table underlying MainMenu is numeric. The empList
field on MainMenu is a list box, so you can't set the format. The empID field
on Enter_View_Requests does not have its format set.

I really, really need some help in figuring out why this isn't working.

Many thanks,
GwenH

 

[fredg]

I used the command button wizard on a form, "MainMenu", to create a button
that opens another form, "Enter_View_Requests". MainMenu has a list box
"empList" with 3 columns. The third column is empID. This is the column I
want to use to filter Enter_View_Requests. Below is the code generated by the
command button wizard. However, when I click the new button,
Enter_View_Requests opens with no records displayed. (Yes I have a record
selected in empList, and there are associated records that should be
displayed on Enter_View_Requests).

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 table underlying MainMenu is numeric. The empList
field on MainMenu is a list box, so you can't set the format. The empID field
on Enter_View_Requests does not have its format set.

I really, really need some help in figuring out why this isn't working.

Many thanks,
GwenH

Which column of the list box is the bound column?
If it is not column 3, then:
stLinkCriteria = "[empID]=" & Me![empList].Column(2)

Note: List Boxes are Zero based, so column(2) is the 3rd column.

 

[DrGUI]

Try the following. Since you have the ID inc column 3, try the 2 like I have
it (i believe the columns are zero-based).

stLinkCriteria = "[empID]=" & Me![empList].column(2)

 

[Gwen H]

Sorry, I tried both column(2) and column(3), and it still does not work. I
tried removing the filter from the button on MainMenu that opens the
Enter_View_Requests form, so the button opens the form without filtering.
Then the form works. However, if I close the form, then select a different
employee name MainMenu and click the button to open Enter_View_Requests, it
still displays records for the first employee selected, not the second
employee selected.

Weird! Any ideas?

Many thanks,

Try the following. Since you have the ID inc column 3, try the 2 like I have
it (i believe the columns are zero-based).

stLinkCriteria = "[empID]=" & Me![empList].column(2)

I used the command button wizard on a form, "MainMenu", to create a button
that opens another form, "Enter_View_Requests". MainMenu has a list box
"empList" with 3 columns. The third column is empID. This is the column I
want to use to filter Enter_View_Requests. Below is the code generated by the
command button wizard. However, when I click the new button,
Enter_View_Requests opens with no records displayed. (Yes I have a record
selected in empList, and there are associated records that should be
displayed on Enter_View_Requests).

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 table underlying MainMenu is numeric. The empList
field on MainMenu is a list box, so you can't set the format. The empID field
on Enter_View_Requests does not have its format set.

I really, really need some help in figuring out why this isn't working.

Many thanks,
GwenH

 

[Anne]

I think the reason it is not working, is that you are leaving one form and
opening another without saving the empID some place.
The empID should be a primary key.
It would be much easier, if you made Enter_View_Requests a subform so you
can link the parent and child field.

Sorry, I tried both column(2) and column(3), and it still does not work. I
tried removing the filter from the button on MainMenu that opens the
Enter_View_Requests form, so the button opens the form without filtering.
Then the form works. However, if I close the form, then select a different
employee name MainMenu and click the button to open Enter_View_Requests, it
still displays records for the first employee selected, not the second
employee selected.

Weird! Any ideas?

Many thanks,

Try the following. Since you have the ID inc column 3, try the 2 like I have
it (i believe the columns are zero-based).

stLinkCriteria = "[empID]=" & Me![empList].column(2)

I used the command button wizard on a form, "MainMenu", to create a button
that opens another form, "Enter_View_Requests". MainMenu has a list box
"empList" with 3 columns. The third column is empID. This is the column I
want to use to filter Enter_View_Requests. Below is the code generated by the
command button wizard. However, when I click the new button,
Enter_View_Requests opens with no records displayed. (Yes I have a record
selected in empList, and there are associated records that should be
displayed on Enter_View_Requests).

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 table underlying MainMenu is numeric. The empList
field on MainMenu is a list box, so you can't set the format. The empID field
on Enter_View_Requests does not have its format set.

I really, really need some help in figuring out why this isn't working.

Many thanks,
GwenH

 

[Gwen H]

I solved the problem by restoring the "Enter_View_Requests" form from a
backup copy of the database from last month. I had the form working perfectly
at that point, but subsequent changes had rendered it nonfunctional. I had to
get the backup file from our network administrator, who has been out of town
for two weeks. I had hoped to solve the problem long before his return, but
wasn't successful.

Thanks for your suggestion!

I think the reason it is not working, is that you are leaving one form and
opening another without saving the empID some place.
The empID should be a primary key.
It would be much easier, if you made Enter_View_Requests a subform so you
can link the parent and child field.

Sorry, I tried both column(2) and column(3), and it still does not work. I
tried removing the filter from the button on MainMenu that opens the
Enter_View_Requests form, so the button opens the form without filtering.
Then the form works. However, if I close the form, then select a different
employee name MainMenu and click the button to open Enter_View_Requests, it
still displays records for the first employee selected, not the second
employee selected.

Weird! Any ideas?

Many thanks,

Try the following. Since you have the ID inc column 3, try the 2 like I have
it (i believe the columns are zero-based).

stLinkCriteria = "[empID]=" & Me![empList].column(2)

:

I used the command button wizard on a form, "MainMenu", to create a button
that opens another form, "Enter_View_Requests". MainMenu has a list box
"empList" with 3 columns. The third column is empID. This is the column I
want to use to filter Enter_View_Requests. Below is the code generated by the
command button wizard. However, when I click the new button,
Enter_View_Requests opens with no records displayed. (Yes I have a record
selected in empList, and there are associated records that should be
displayed on Enter_View_Requests).

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 table underlying MainMenu is numeric. The empList
field on MainMenu is a list box, so you can't set the format. The empID field
on Enter_View_Requests does not have its format set.

I really, really need some help in figuring out why this isn't working.

Many thanks,
GwenH