List/Combo Box problem

Hi,
I have a form that has a List Box that displays Company Name, Phone #,
Contact, Work Type, etc. The Row Source for the List Box is as follows ("Subs
by Company" is a query):
SELECT [Subs by Company].Company, [Subs by Company].[Work Type], [Subs by
Company].Phone, [Subs by Company].Fax, [Subs by Company].[Contact #1], [Subs
by Company].[Labor Type], [Subs by Company].WorkID FROM [Subs by Company]
WHERE ((([Subs by Company].[Preferred Contractor])3));

I use the Double Click event procedure to open another form that displays
the Company information in greater detail. If there is a company with more
than one Work Type the List Box displays the same Company Name for each Work
Type. I'm having trouble when I click on a Company Name that has more than
one Work Type. The form displays the first (alphabetically) Work Type for
that particular Company when I double click the Company Name showing the
other Work Type. I tried playing around with the bound column numbers and
can't seem to be able to get it to work.
This is the code used for the List Box form:
---------------------------------------------------------------------------------------------
Private Sub LstJobNo_DblClick(Cancel As Integer)
On Error Resume Next
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sub List (Alpha by Company Name)1"

If Not IsNull(Me.LstJobNo) Then
stLinkCriteria = "[Company]=""" & Me.LstJobNo & """"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.LstJobNo
Else
DoCmd.OpenForm stDocName
End If

End Sub

------------------------------------------------------------------
Private Sub Form_Load()
On Error Resume Next

'set base SQL strings for search
strSelect = "SELECT [Subs by Company].[WorkID], [Subs by Company].[Work
Type], " & _
"[Subs by Company].[Phone], " & _
"[Subs by Company].[Fax], " & _
"[Subs by Company].[Contact #1], " & _
"[Subs by Company].[Labor Type], " & _
"[Subs by Company].[Company], " & _
"[Subs by Company].[Sub ID]" & _
"FROM [Subs by Company]"

strWhereCurrent = "WHERE [Subs by Company].[Preferred Contractor]3"

End Sub
----------------------------------------------------------------------------------------
Bound Column is 1
Any ideas??
Thanks,
Jim

I am not sure why you checked for Null since in the DoubleClick Event,
LstJobNo cannot be Null.

Try chaging your code to (untested):

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

stDocName = "Sub List (Alpha by Company Name)1"

' Comment out 1 of the 2 following statements
' If [Work Type] is of numeric data type
stLinkCriteria = "[Company]=""" & Me.LstJobNo & _
""" And [Work Type] = " & Me.LstJobNo.Column(1)

' If [Work Type] is of text data type
stLinkCriteria = "[Company]=""" & Me.LstJobNo & _
""" And [Work Type] = """ & Me.LstJobNo.Column(1) & """"

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub


--
HTH
Van T. Dinh
MVP (Access)



"JimAA" wrote in message
...
Hi,
I have a form that has a List Box that displays Company Name, Phone #,
Contact, Work Type, etc. The Row Source for the List Box is as follows
("Subs
by Company" is a query):
SELECT [Subs by Company].Company, [Subs by Company].[Work Type], [Subs by
Company].Phone, [Subs by Company].Fax, [Subs by Company].[Contact #1],
[Subs
by Company].[Labor Type], [Subs by Company].WorkID FROM [Subs by Company]
WHERE ((([Subs by Company].[Preferred Contractor])3));

I use the Double Click event procedure to open another form that displays
the Company information in greater detail. If there is a company with
more
than one Work Type the List Box displays the same Company Name for each
Work
Type. I'm having trouble when I click on a Company Name that has more
than
one Work Type. The form displays the first (alphabetically) Work Type for
that particular Company when I double click the Company Name showing the
other Work Type. I tried playing around with the bound column numbers and
can't seem to be able to get it to work.
This is the code used for the List Box form:
---------------------------------------------------------------------------------------------
Private Sub LstJobNo_DblClick(Cancel As Integer)
On Error Resume Next
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sub List (Alpha by Company Name)1"

If Not IsNull(Me.LstJobNo) Then
stLinkCriteria = "[Company]=""" & Me.LstJobNo & """"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.LstJobNo
Else
DoCmd.OpenForm stDocName
End If

End Sub

------------------------------------------------------------------
Private Sub Form_Load()
On Error Resume Next

'set base SQL strings for search
strSelect = "SELECT [Subs by Company].[WorkID], [Subs by Company].[Work
Type], " & _
"[Subs by Company].[Phone], " & _
"[Subs by Company].[Fax], " & _
"[Subs by Company].[Contact #1], " & _
"[Subs by Company].[Labor Type], " & _
"[Subs by Company].[Company], " & _
"[Subs by Company].[Sub ID]" & _
"FROM [Subs by Company]"

strWhereCurrent = "WHERE [Subs by Company].[Preferred Contractor]3"

End Sub
----------------------------------------------------------------------------------------
Bound Column is 1
Any ideas??
Thanks,
Jim

That didn't work. Started over from scratch with new tables and forms and
still got the same results. What I now have is this:
A "SUBLIST" table with "Sub ID" (primary key) (autonumber), "Company" (text
data type), "Work Type" (text data type), and "WorkID" (number data type).
A "frmMain" form with a list box called "ListCompany" with it's row source
as "SELECT DISTINCTROW SUBLIST.Company, SUBLIST[Sub ID], SUBLIST.[Work Type],
SUBLIST.[WorkID] FROM SUBLIST ORDER BY SUBLIST.Company, SUBLIST.[Sub ID]
Column Count = 4 Bound Column = 1

The form has a Double Click event that opens another form called "frmPopUp"
with the following code:
Private Sub ListCompany_DblClick(Cancel As Integer)
On Error GoTo Err_ListCompany_DblClick

DoCmd.OpenForm "frmPopUp", , , "[Company]=""" & Me.ListCompany &
"""", , , Me.ListCompany

Exit_ListCompany_DblClick:
Exit Sub

Err_ListCompany_DblClick:
MsgBox Err.Description
Resume Exit_ListCompany_DblClick

End Sub

I still can't figure out how to have "frmPopUp" display the record selected
from the list box. Again, there may be Company A that has a record as a
Carpentry Company and another record as a Drywall Company. When I double
click on Company A as a Drywall Company the "frmPopUp" displays Company A as
a Carpentry Company.

Thanks.



"Van T. Dinh" wrote:

I am not sure why you checked for Null since in the DoubleClick Event,
LstJobNo cannot be Null.

Try chaging your code to (untested):

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

stDocName = "Sub List (Alpha by Company Name)1"

' Comment out 1 of the 2 following statements
' If [Work Type] is of numeric data type
stLinkCriteria = "[Company]=""" & Me.LstJobNo & _
""" And [Work Type] = " & Me.LstJobNo.Column(1)

' If [Work Type] is of text data type
stLinkCriteria = "[Company]=""" & Me.LstJobNo & _
""" And [Work Type] = """ & Me.LstJobNo.Column(1) & """"

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub


--
HTH
Van T. Dinh
MVP (Access)



"JimAA" wrote in message
...
Hi,
I have a form that has a List Box that displays Company Name, Phone #,
Contact, Work Type, etc. The Row Source for the List Box is as follows
("Subs
by Company" is a query):
SELECT [Subs by Company].Company, [Subs by Company].[Work Type], [Subs by
Company].Phone, [Subs by Company].Fax, [Subs by Company].[Contact #1],
[Subs
by Company].[Labor Type], [Subs by Company].WorkID FROM [Subs by Company]
WHERE ((([Subs by Company].[Preferred Contractor])3));

I use the Double Click event procedure to open another form that displays
the Company information in greater detail. If there is a company with
more
than one Work Type the List Box displays the same Company Name for each
Work
Type. I'm having trouble when I click on a Company Name that has more
than
one Work Type. The form displays the first (alphabetically) Work Type for
that particular Company when I double click the Company Name showing the
other Work Type. I tried playing around with the bound column numbers and
can't seem to be able to get it to work.
This is the code used for the List Box form:
---------------------------------------------------------------------------------------------
Private Sub LstJobNo_DblClick(Cancel As Integer)
On Error Resume Next
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sub List (Alpha by Company Name)1"

If Not IsNull(Me.LstJobNo) Then
stLinkCriteria = "[Company]=""" & Me.LstJobNo & """"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.LstJobNo
Else
DoCmd.OpenForm stDocName
End If

End Sub

------------------------------------------------------------------
Private Sub Form_Load()
On Error Resume Next

'set base SQL strings for search
strSelect = "SELECT [Subs by Company].[WorkID], [Subs by Company].[Work
Type], " & _
"[Subs by Company].[Phone], " & _
"[Subs by Company].[Fax], " & _
"[Subs by Company].[Contact #1], " & _
"[Subs by Company].[Labor Type], " & _
"[Subs by Company].[Company], " & _
"[Subs by Company].[Sub ID]" & _
"FROM [Subs by Company]"

strWhereCurrent = "WHERE [Subs by Company].[Preferred Contractor]3"

End Sub
----------------------------------------------------------------------------------------
Bound Column is 1
Any ideas??
Thanks,
Jim