Listboxes to open and filter form and subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with two listboxes. The first list box opens the form frmParts
and finds the record based on the selection. I am having trouble getting the
second listbox to open the frmParts with the subform qryProcedure and find
the record with the selection on both listboxes. frmParts and qryProcedure
are on the same form. Here is the code that works for the first one:

Private Sub List2_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmParts"

stLinkCriteria = "[Part_ID]=" & Me![List2]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Please let me know the correct code for the second listbox (List4)
 
Hi Matt
What is the problem?
==========================================
The code doesn't react at all?
It could be that you copy and paste the first code, but you didn't declare
it in the Double click event of the second list box.
Open the properties of the list box 4 and see if any thing is declared in
this list box
==========================================
You get an error message? then please share this error
==========================================
The form open, but doesn't filter?
In that case does the fields you filter on are the same fileds as the first
list box?
If they are not the same, and you are tryng to filter on a text field try this

stLinkCriteria = "[Part_ID]='" & Me![Enter here List4 Name] & "'"


==========================================
Are you trying to filter the records using the first column in the list box,
or it is another column
For example if it is the second column, try this

stLinkCriteria = "[Part_ID]='" & Me![Enter here List4 Name].column(1)

The column number start from 0
===========================================
 
The first listbox works fine, it opens the form and filters the form. It is
the second listbox that is troubling. I need it to open the form (which has a
subform). It must that the first column of information (number) from the
first listbox and use that to filter the form, then it must take the info
(number) form the first column of the second list box to filter the subform
after the form is filtered.

Ofer said:
Hi Matt
What is the problem?
==========================================
The code doesn't react at all?
It could be that you copy and paste the first code, but you didn't declare
it in the Double click event of the second list box.
Open the properties of the list box 4 and see if any thing is declared in
this list box
==========================================
You get an error message? then please share this error
==========================================
The form open, but doesn't filter?
In that case does the fields you filter on are the same fileds as the first
list box?
If they are not the same, and you are tryng to filter on a text field try this

stLinkCriteria = "[Part_ID]='" & Me![Enter here List4 Name] & "'"


==========================================
Are you trying to filter the records using the first column in the list box,
or it is another column
For example if it is the second column, try this

stLinkCriteria = "[Part_ID]='" & Me![Enter here List4 Name].column(1)

The column number start from 0
===========================================
Matt Muzila said:
I have a form with two listboxes. The first list box opens the form frmParts
and finds the record based on the selection. I am having trouble getting the
second listbox to open the frmParts with the subform qryProcedure and find
the record with the selection on both listboxes. frmParts and qryProcedure
are on the same form. Here is the code that works for the first one:

Private Sub List2_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmParts"

stLinkCriteria = "[Part_ID]=" & Me![List2]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Please let me know the correct code for the second listbox (List4)
Click to expand...
Click to expand...
 
Pass the value of list4 using the OpenArgs in the open form command line

Private Sub List2_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmParts"

stLinkCriteria = "[Part_ID]=" & Me![List2]
DoCmd.OpenForm stDocName, , , stLinkCriteria,,,Me![List4]
End Sub

On the OnLoad event of the frmParts form, write code that assign a new
record source to the subform using the filter from the OpenArgs

Me.[SubFormName].Form.RecordSource = "Select * From TableName Where
FieldName = " & Me.OpenArgs

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Matt Muzila said:
The first listbox works fine, it opens the form and filters the form. It is
the second listbox that is troubling. I need it to open the form (which has a
subform). It must that the first column of information (number) from the
first listbox and use that to filter the form, then it must take the info
(number) form the first column of the second list box to filter the subform
after the form is filtered.

Ofer said:
Hi Matt
What is the problem?
==========================================
The code doesn't react at all?
It could be that you copy and paste the first code, but you didn't declare
it in the Double click event of the second list box.
Open the properties of the list box 4 and see if any thing is declared in
this list box
==========================================
You get an error message? then please share this error
==========================================
The form open, but doesn't filter?
In that case does the fields you filter on are the same fileds as the first
list box?
If they are not the same, and you are tryng to filter on a text field try this

stLinkCriteria = "[Part_ID]='" & Me![Enter here List4 Name] & "'"


==========================================
Are you trying to filter the records using the first column in the list box,
or it is another column
For example if it is the second column, try this

stLinkCriteria = "[Part_ID]='" & Me![Enter here List4 Name].column(1)

The column number start from 0
===========================================
Matt Muzila said:
I have a form with two listboxes. The first list box opens the form frmParts
and finds the record based on the selection. I am having trouble getting the
second listbox to open the frmParts with the subform qryProcedure and find
the record with the selection on both listboxes. frmParts and qryProcedure
are on the same form. Here is the code that works for the first one:

Private Sub List2_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmParts"

stLinkCriteria = "[Part_ID]=" & Me![List2]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Please let me know the correct code for the second listbox (List4)
Click to expand...
Click to expand...
Click to expand...
 
The problem with this is that i still need to open the form regularly in edit
mode from my switchboard and be able to goto previous and next records. These
listboxes are just another way to open the form, but i need to keep the
original way available.
 
In that case add a criteria

If not IsNull(Me.OpenArgs) and Me.OpenArgs <> "" Then
Me.[SubFormName].Form.RecordSource = "Select * From TableName Where
FieldName = " & Me.OpenArgs
End if
 
Not sure if that helped, I am now getting a Run-Time error '3145': Syntax
error in WHERE clause.
 
Back
Top