list box probs

[dlowry]

I need help here...
I have created a list box (LstCName) from a form named (contactlist) that picks up employee names from a query named (lkpcontacts). The employee names are displayed fine but when i click on a name, i want it to take me to that persons details which are stored in (frmcontacts). At the moment it's not doing it... can you help.




Private Sub LstCName_DblClick(Cancel As Integer)
' Double-clicking a name is the same as choosing the name
' and then clicking the edit button.
cmdSome_Click
End Sub

Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!LstCName.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!LstCName.ItemsSelected
' Grab the ContactID column for each selected item
strWhere = strWhere & Me!LstCName.Column(0, varItem) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the contacts form filtered on the selected contacts
strWhere = "[ContactID] IN (" & strWhere & ") "
DoCmd.OpenForm FormName:="frmContacts", WhereCondition:=strWhere
DoCmd.Close acForm, Me.Name

End Sub

[VALIS]

I need help here...
I have created a list box (LstCName) from a form named (contactlist) that picks up employee names from a query named (lkpcontacts). The employee names are displayed fine but when i click on a name, i want it to take me to that persons details which are stored in (frmcontacts). At the moment it's not doing it... can you help.




Private Sub LstCName_DblClick(Cancel As Integer)
' Double-clicking a name is the same as choosing the name
' and then clicking the edit button.
cmdSome_Click
End Sub

Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!LstCName.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!LstCName.ItemsSelected
' Grab the ContactID column for each selected item
strWhere = strWhere & Me!LstCName.Column(0, varItem) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the contacts form filtered on the selected contacts
strWhere = "[ContactID] IN (" & strWhere & ") "
DoCmd.OpenForm FormName:="frmContacts", WhereCondition:=strWhere
DoCmd.Close acForm, Me.Name

End Sub

Doesn't Me refer to the current object executed?
If so you'd be opening and closing the form almost instantaneously?
What happens if you step through the code?

[NeoPa]

You're missing the quotes around the items in the IN() list in the WHERE clause.
The particular line should read :-

strWhere = strWhere & "'" & Me!LstCName.Column(0, varItem) & "',"

[NeoPa]

Doesn't Me refer to the current object executed?

No, Me refers to the object in which the code has scope.
In this case, the owning form, or the form which is the parent of the object that this code is related to (LstCName).

[dlowry]

Appreciate your help.... from what you've given me it now opens the form fine.....but no data within. Any ideas ?

[NeoPa]

Appreciate your help.... from what you've given me it now opens the form fine.....but no data within. Any ideas ?

Can't help without any idea of what data is being processed; what is in the list box etc.

[dlowry]

i'll keep it simple for now...

ContactID
FirstName
LastName

[MMcCarthy]

I need help here...
I have created a list box (LstCName) from a form named (contactlist) that picks up employee names from a query named (lkpcontacts). The employee names are displayed fine but when i click on a name, i want it to take me to that persons details which are stored in (frmcontacts). At the moment it's not doing it... can you help.

Try this:

 
Private Sub LstCName_DblClick(Cancel As Integer)
' Double-clicking a name is the same as choosing the name
' and then clicking the edit button.
 
cmdSome_Click
 
End Sub
 
Private Sub cmdSome_Click()
Dim strWhere As String
Dim varItem As Variant
 
strWhere = "" ' initialise the variable
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!LstCName.ItemsSelected.Count = 0 Then Exit Sub
 
' Loop through the items selected collection
For Each varItem In Me!LstCName.ItemsSelected
' Grab the ContactID column for each selected item
'assuming ContactID is a number and not a string and ContactID is the bound column
    strWhere = strWhere & "[ContactID]=" & Me!LstCName.ItemData(varItem) & " OR "
Next varItem
 
' Throw away the " OR " in the string
strWhere = Left(strWhere, Len(strWhere) - 4)
 
' Open the contacts form filtered on the selected contacts
 
DoCmd.OpenForm "frmContacts", , , strWhere
DoCmd.Close acForm, Me.Name
 
End Sub
 
 

Just a note...

Has the form "frmContacts" already got a where statement in its record source ?