Listbox double click event

[Guest]

Greetings all. I found many posts with suggestions to my need, but for some
reason none of them work for me. I have a form with five unbound text and
combo boxes that populate a list box when a button is hit,from which I would
like the user to be able to double click a record and open frmJobInfo to the
record selected in the list box.

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO, tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion contains no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" & Me.cboRegion & "*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*' AND"
End If

If Not IsNull(Me.txtRte) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRte & "*' AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2 & "*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" & Me.txtEWO & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub.

I copied the code from an Access tips website, and just changed the names.
It works great in acting as a filter to populate the list box, but the code
for the double click event of the list box does not work. Does anyone know
how to make this work for me? Thanks in advance.

 

[Brian Bastl]

It's as simple as docmd.openform "YourForm",,, "[YourFieldIsText]='" &
Me.MyList & "'" or
docmd.openform "YourForm",,, "[YourFieldIsNumber]=" & Me.MyList

Or did I misunderstand?

Brian

 

[Guest]

Thanks for the reply. I have tried both of those lines, and neither of them
work for me. When I try the text option my form opens up blank. When I try
the number option I get prompted for a parameter entry, and then my form
opens up blank. Does it matter that I am running SQL 2K as a back end? I
have found that many of the tricks I have learned here no longer work after
upsizing.

It's as simple as docmd.openform "YourForm",,, "[YourFieldIsText]='" &
Me.MyList & "'" or
docmd.openform "YourForm",,, "[YourFieldIsNumber]=" & Me.MyList

Or did I misunderstand?

Brian

Greetings all. I found many posts with suggestions to my need, but for some
reason none of them work for me. I have a form with five unbound text and
combo boxes that populate a list box when a button is hit,from which I would
like the user to be able to double click a record and open frmJobInfo to the
record selected in the list box.

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO, tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion contains no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" & Me.cboRegion & "*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*' AND"
End If

If Not IsNull(Me.txtRte) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRte & "*' AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2 & "*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" & Me.txtEWO & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub.

I copied the code from an Access tips website, and just changed the names.
It works great in acting as a filter to populate the list box, but the code
for the double click event of the list box does not work. Does anyone know
how to make this work for me? Thanks in advance.

 

[Brian Bastl]

Does it matter that I am running SQL 2K as a back end? I

have found that many of the tricks I have learned here no longer work after
upsizing.

I don't have any experience with SQL 2K, but I wouldn't think so.

Which column from your listbox are you using in your Where clause?
Do you know the datatype of the field in your Where clause?

If it is text, perhaps there are records with punctuation or other special
characters in them. In that case, you'd need extra quotes

"[YourFieldIsText]=""" & Me.MyList & """"

Brian.

Thanks for the reply. I have tried both of those lines, and neither of them
work for me. When I try the text option my form opens up blank. When I try
the number option I get prompted for a parameter entry, and then my form
opens up blank. Does it matter that I am running SQL 2K as a back end? I
have found that many of the tricks I have learned here no longer work after
upsizing.

It's as simple as docmd.openform "YourForm",,, "[YourFieldIsText]='" &
Me.MyList & "'" or
docmd.openform "YourForm",,, "[YourFieldIsNumber]=" & Me.MyList

Or did I misunderstand?

Brian

Greetings all. I found many posts with suggestions to my need, but

for
some

reason none of them work for me. I have a form with five unbound text and
combo boxes that populate a list box when a button is hit,from which I would
like the user to be able to double click a record and open frmJobInfo

to
the

record selected in the list box.

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO, tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion contains no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" & Me.cboRegion & "*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*' AND"
End If

If Not IsNull(Me.txtRte) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRte & "*' AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2 & "*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" & Me.txtEWO & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub.

I copied the code from an Access tips website, and just changed the names.
It works great in acting as a filter to populate the list box, but the code
for the double click event of the list box does not work. Does anyone know
how to make this work for me? Thanks in advance.

 

[Brian Bastl]

Also, you may need to specify the listbox's column index

=""" & Me.MyList.Column(1) & """"

Brian

Does it matter that I am running SQL 2K as a back end? I
have found that many of the tricks I have learned here no longer work after
upsizing.

I don't have any experience with SQL 2K, but I wouldn't think so.

Which column from your listbox are you using in your Where clause?
Do you know the datatype of the field in your Where clause?

If it is text, perhaps there are records with punctuation or other special
characters in them. In that case, you'd need extra quotes

"[YourFieldIsText]=""" & Me.MyList & """"

Brian.

Thanks for the reply. I have tried both of those lines, and neither of them
work for me. When I try the text option my form opens up blank. When I try
the number option I get prompted for a parameter entry, and then my form
opens up blank. Does it matter that I am running SQL 2K as a back end? I
have found that many of the tricks I have learned here no longer work after
upsizing.

It's as simple as docmd.openform "YourForm",,, "[YourFieldIsText]='" &
Me.MyList & "'" or
docmd.openform "YourForm",,, "[YourFieldIsNumber]=" & Me.MyList

Or did I misunderstand?

Brian


Greetings all. I found many posts with suggestions to my need, but for
some
reason none of them work for me. I have a form with five unbound

text

and

frmJobInfo

to

the
record selected in the list box.

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO, tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion

contains

no

&

"*'

AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*' AND"
End If

If Not IsNull(Me.txtRte) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRte & "*' AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2

&

"*'

 

[Guest]

Brian, it has to be something so simple. So far the best I can do is get the
form to open up blank with no error messages. Could there be something wrong
with the code I copied? The field for the where clause, EWO, is text, but it
has numbers in it. Every job number will have '8A0' first, followed by four
numbers like this; '8A01234'. I am completely stumped.

Also, you may need to specify the listbox's column index

=""" & Me.MyList.Column(1) & """"

Brian

Does it matter that I am running SQL 2K as a back end? I
have found that many of the tricks I have learned here no longer work after
upsizing.

I don't have any experience with SQL 2K, but I wouldn't think so.

Which column from your listbox are you using in your Where clause?
Do you know the datatype of the field in your Where clause?

If it is text, perhaps there are records with punctuation or other special
characters in them. In that case, you'd need extra quotes

"[YourFieldIsText]=""" & Me.MyList & """"

Brian.

Thanks for the reply. I have tried both of those lines, and neither of them
work for me. When I try the text option my form opens up blank. When I try
the number option I get prompted for a parameter entry, and then my form
opens up blank. Does it matter that I am running SQL 2K as a back end? I
have found that many of the tricks I have learned here no longer work after
upsizing.

:

It's as simple as docmd.openform "YourForm",,, "[YourFieldIsText]='" &
Me.MyList & "'" or
docmd.openform "YourForm",,, "[YourFieldIsNumber]=" & Me.MyList

Or did I misunderstand?

Brian


Greetings all. I found many posts with suggestions to my need, but for
some
reason none of them work for me. I have a form with five unbound

text

and
combo boxes that populate a list box when a button is hit,from which I
would
like the user to be able to double click a record and open

frmJobInfo

to
the
record selected in the list box.

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO, tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion

contains

no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" & Me.cboRegion

&

"*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*' AND"
End If

If Not IsNull(Me.txtRte) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRte & "*' AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2

&

"*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" & Me.txtEWO & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub.

I copied the code from an Access tips website, and just changed the names.
It works great in acting as a filter to populate the list box, but the
code
for the double click event of the list box does not work. Does anyone
know
how to make this work for me? Thanks in advance.

 

[Brian Bastl]

Greg,

the EWO value is the fifth column in you rowsource, therefore to retrieve
its value you need to use:

Me.lstJobInfo.Column(4)

And therefore, since there appears to be no special characters in the field,
your docmd statement should look like the following:

DoCmd.OpenForm "MyForm",,,"[EWO]='" & Me.lstJobInfo.Column(4) & "'"

HTH,
Brian

Brian, it has to be something so simple. So far the best I can do is get the
form to open up blank with no error messages. Could there be something wrong
with the code I copied? The field for the where clause, EWO, is text, but it
has numbers in it. Every job number will have '8A0' first, followed by four
numbers like this; '8A01234'. I am completely stumped.

Also, you may need to specify the listbox's column index

=""" & Me.MyList.Column(1) & """"

Brian

Does it matter that I am running SQL 2K as a back end? I
have found that many of the tricks I have learned here no longer work
after
upsizing.

I don't have any experience with SQL 2K, but I wouldn't think so.

Which column from your listbox are you using in your Where clause?
Do you know the datatype of the field in your Where clause?

If it is text, perhaps there are records with punctuation or other special
characters in them. In that case, you'd need extra quotes

"[YourFieldIsText]=""" & Me.MyList & """"

Brian.



Thanks for the reply. I have tried both of those lines, and neither of
them
work for me. When I try the text option my form opens up blank. When I
try
the number option I get prompted for a parameter entry, and then my form
opens up blank. Does it matter that I am running SQL 2K as a back

end?
I

have found that many of the tricks I have learned here no longer work
after
upsizing.

:

It's as simple as docmd.openform "YourForm",,, "[YourFieldIsText]='" &
Me.MyList & "'" or
docmd.openform "YourForm",,, "[YourFieldIsNumber]=" & Me.MyList

Or did I misunderstand?

Brian


Greetings all. I found many posts with suggestions to my need, but
for
some
reason none of them work for me. I have a form with five

unbound
text

and
combo boxes that populate a list box when a button is hit,from

which
I

would
like the user to be able to double click a record and open frmJobInfo
to
the
record selected in the list box.

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO, tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has
been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion contains
no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" &

Me.cboRegion
&

"*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*'
AND"
End If

If Not IsNull(Me.txtRte) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRte & "*'
AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" &

Me.cboF1F2
&

"*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" & Me.txtEWO & "*'
AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub.

I copied the code from an Access tips website, and just changed the
names.
It works great in acting as a filter to populate the list box,

but
the

code
for the double click event of the list box does not work. Does anyone
know
how to make this work for me? Thanks in advance.

 

[Guest]

Thank you so much, that was it. I was putting in 5 rather thatn 4. It now
works like a charm.

Greg,

the EWO value is the fifth column in you rowsource, therefore to retrieve
its value you need to use:

Me.lstJobInfo.Column(4)

And therefore, since there appears to be no special characters in the field,
your docmd statement should look like the following:

DoCmd.OpenForm "MyForm",,,"[EWO]='" & Me.lstJobInfo.Column(4) & "'"

HTH,
Brian

Brian, it has to be something so simple. So far the best I can do is get the
form to open up blank with no error messages. Could there be something wrong
with the code I copied? The field for the where clause, EWO, is text, but it
has numbers in it. Every job number will have '8A0' first, followed by four
numbers like this; '8A01234'. I am completely stumped.

Also, you may need to specify the listbox's column index

=""" & Me.MyList.Column(1) & """"

Brian


Does it matter that I am running SQL 2K as a back end? I
have found that many of the tricks I have learned here no longer work
after
upsizing.

I don't have any experience with SQL 2K, but I wouldn't think so.

Which column from your listbox are you using in your Where clause?
Do you know the datatype of the field in your Where clause?

If it is text, perhaps there are records with punctuation or other special
characters in them. In that case, you'd need extra quotes

"[YourFieldIsText]=""" & Me.MyList & """"

Brian.



Thanks for the reply. I have tried both of those lines, and neither of
them
work for me. When I try the text option my form opens up blank. When I
try
the number option I get prompted for a parameter entry, and then my form
opens up blank. Does it matter that I am running SQL 2K as a back end?
I
have found that many of the tricks I have learned here no longer work
after
upsizing.

:

It's as simple as docmd.openform "YourForm",,, "[YourFieldIsText]='" &
Me.MyList & "'" or
docmd.openform "YourForm",,, "[YourFieldIsNumber]=" & Me.MyList

Or did I misunderstand?

Brian


Greetings all. I found many posts with suggestions to my need, but
for
some
reason none of them work for me. I have a form with five unbound
text
and
combo boxes that populate a list box when a button is hit,from which
I
would
like the user to be able to double click a record and open
frmJobInfo
to
the
record selected in the list box.

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO,
tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has
been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion
contains
no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" & Me.cboRegion
&
"*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*'
AND"
End If

If Not IsNull(Me.txtRte) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRte & "*'
AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2
&
"*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" & Me.txtEWO & "*'
AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub.

I copied the code from an Access tips website, and just changed the
names.
It works great in acting as a filter to populate the list box, but
the
code
for the double click event of the list box does not work. Does
anyone
know
how to make this work for me? Thanks in advance.

 

[Brian Bastl]

Glad you got it figured out.

Brian

Thank you so much, that was it. I was putting in 5 rather thatn 4. It now
works like a charm.

Greg,

the EWO value is the fifth column in you rowsource, therefore to retrieve
its value you need to use:

Me.lstJobInfo.Column(4)

And therefore, since there appears to be no special characters in the field,
your docmd statement should look like the following:

DoCmd.OpenForm "MyForm",,,"[EWO]='" & Me.lstJobInfo.Column(4) & "'"

HTH,
Brian

Brian, it has to be something so simple. So far the best I can do is

get
the

form to open up blank with no error messages. Could there be

something
wrong

with the code I copied? The field for the where clause, EWO, is text,

but
it

has numbers in it. Every job number will have '8A0' first, followed

by
four

numbers like this; '8A01234'. I am completely stumped.

:

Also, you may need to specify the listbox's column index

=""" & Me.MyList.Column(1) & """"

Brian


Does it matter that I am running SQL 2K as a back end? I
have found that many of the tricks I have learned here no longer work
after
upsizing.

I don't have any experience with SQL 2K, but I wouldn't think so.

Which column from your listbox are you using in your Where clause?
Do you know the datatype of the field in your Where clause?

If it is text, perhaps there are records with punctuation or other special
characters in them. In that case, you'd need extra quotes

"[YourFieldIsText]=""" & Me.MyList & """"

Brian.



Thanks for the reply. I have tried both of those lines, and

neither
of

them
work for me. When I try the text option my form opens up blank. When I
try
the number option I get prompted for a parameter entry, and then

my
form

opens up blank. Does it matter that I am running SQL 2K as a

back
end?

I
have found that many of the tricks I have learned here no longer work
after
upsizing.

:

It's as simple as docmd.openform "YourForm",,, "[YourFieldIsText]='" &
Me.MyList & "'" or
docmd.openform "YourForm",,, "[YourFieldIsNumber]=" & Me.MyList

Or did I misunderstand?

Brian


Greetings all. I found many posts with suggestions to my

need,
but

for
some
reason none of them work for me. I have a form with five unbound
text
and
combo boxes that populate a list box when a button is

hit,from
which

I
would
like the user to be able to double click a record and open
frmJobInfo
to
the
record selected in the list box.

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO,
tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if

information
has

been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion
contains
no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" & Me.cboRegion
&
"*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO

&
"*'

AND"
End If

If Not IsNull(Me.txtRte) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" &

Me.txtRte &
"*'

AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2
&
"*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" &

Me.txtEWO &
"*'

AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub.

I copied the code from an Access tips website, and just

changed
the

names.
It works great in acting as a filter to populate the list

box,
but

the
code
for the double click event of the list box does not work. Does
anyone
know
how to make this work for me? Thanks in advance.