Code modification to select a unique record from a list box

[Guest]

Hi,
I have a list box that shows "company names" and their "work type"
(plumbing, electrical, etc.). My problem is that I have some companies that
are listed under more than one work type and when I select a company name I
get all of the work types for that company name. How do I specifically select
a company name with only that specific work type? I copied my code below.
Thanks,
Jim

Private Sub BtnJobs_Click()
On Error GoTo Err_BtnJobs_Click
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

Exit_BtnJobs_Click:
Exit Sub

Err_BtnJobs_Click:
MsgBox Err.Description
Resume Exit_BtnJobs_Click

End Sub

 

[Guest]

Hi Jim,

You will need to do two operations. Since a company can be associated with
more than one work type, then it is necessary to go through two steps. You
can either select the company name and have that selection populate a list
box with the relevant work types for that company, then select the work type
you are wanting, or alternatively select the work type wanted and have that
selection populate the companies that offer that work type. Somehow the
computer must be told exactly what you are looking for. It cannot second
guess the combination you want.

With a combination of queries and code, it is possible to have a drop down
list of say companies. The selection is then used to run a query, using the
company name as the criteria, to find the work types carried out by that
company. This result is then used to populate a list box. Selecting the
relevant record from the list box can then be used to place both thecompany
name and work type into a new record (if that is what you are trying to
achieve).

I have done simillar things using the one form tabbed in the right sequence
such that selecting a record in a drop down box, then populates a list box,
and selecting a record from that list box. I then had other fields relevant
to that particular combination of records to be filled in, and on ending the
input have all the data including the company name and work type selected,
create a new reocrd.

Hope this gives you some direction to work towards.


regards

DavidC