Listboxes to open and filter form and subform

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)
 
G

Guest

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
===========================================
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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.
 
G

Guest

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
 
G

Guest

Not sure if that helped, I am now getting a Run-Time error '3145': Syntax
error in WHERE clause.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find record on form and Me.subform based on two listboxes on separate form 3
filter on combo box possible? 1
Open different form based on combo box selection 2
Multiple criteria on StLinkCriteria 1
A2000 form ceases working under A2002 1
Filter Frustration 6
Same text fields in two different forms 4
From one form, open another form, find record (no filter) 5

Top