Drill Down on a Form

I using the code below to drill down from one form to another. It works if the match is one word however when the lookup is more than one word it does not find the value. For example if the lookup is "ISSO" the value is found...If the lookup is "System Owner" the value is not found. Any thoughts.

Dim strList As String
 strList = "('" & Replace(Me.Currently_With, " ", "','") & "')"
 If DCount("*", "UFMS Weakness Remediation Summary", "[Currently With] In " & strList) = 0 Then
      MsgBox "No related record found", vbExclamation, "UFMS"
      Exit Sub
  Else
 End If
 DoCmd.OpenForm "frm_Remediation Summary", , , "[Currently With] In " & strList
shieldscoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
post the values displayed in "Me.Currently_With"

<.If the lookup is "System Owner" the value is not found>
in your replace statement, the space " " is being replaced, so  "System Owner"  becomes  "SystemOwner"
0
shieldscoAuthor Commented:
OFSP
O&M
System Owner
OpDiv
OCIO
0
shieldscoAuthor Commented:
I would prefer not to hard code the values
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
why are you using this line in your code

strList = "('" & Replace(Me.Currently_With, " ", "','") & "')"

is the control Me.Currently_With, list  multiple items?
what is the control Source of the cotrol Me.Currently_With?
0
shieldscoAuthor Commented:
Me.Currently_With is multiple items
and the control source is a query
0
Rey Obrero (Capricorn1)Commented:
post the query here.
0
shieldscoAuthor Commented:
SELECT [UFMS Weakness Remediation Summary].[Weakness Remediation Status] AS Status, [UFMS Weakness Remediation Summary].[Currently With], Count([UFMS Weakness Remediation Summary].ID) AS [Number]
FROM [UFMS Weakness Remediation Summary]
GROUP BY [UFMS Weakness Remediation Summary].[Weakness Remediation Status], [UFMS Weakness Remediation Summary].[Currently With];
0
shieldscoAuthor Commented:
Can the spaced be trimed in the query?
0
Rey Obrero (Capricorn1)Commented:
<Can the spaced be trimed in the query? >  YES, but i don't think it resolve your problem.

how are data entered in the field [Currently With] ?
0
PatHartmanCommented:
Me.Currently_With is multiple items
When you build this string, why not build it correctly with commas and quotes rather than using spaces that you later have to replace with commas?  

Usually when building an IN() string people concatenate the value with the delimiters and then remove the final comma.

strIN = strIN & """" & Me.someitem & ""","

then at the end,

If strIN & "" <> "" Then
    strin = Left(StrIN, Len(StrIN) -1)
End If
0
shieldscoAuthor Commented:
it a list box
0
shieldscoAuthor Commented:
Dim strList As String


 'strList = "('" & Replace(Me.Currently_With, " ", "','") & "')"
 StrIN = StrIN & """" & Me.Currently_With & ""","

Pat - No related record found

 'If DCount("*", "UFMS Weakness Remediation Summary", "[Currently With] In " & strList) = 0 Then

 If StrIN & "" <> "" Then
     StrIN = Left(StrIN, Len(StrIN) - 1)
      MsgBox "No related record found", vbExclamation, "UFMS"
      Exit Sub
  Else
 End If
 DoCmd.OpenForm "frm_Remediation Summary", , , "[Currently With] In " & strList
0
shieldscoAuthor Commented:
Pat - No related record found

Dim strList As String


  'strList = "('" & Replace(Me.Currently_With, " ", "','") & "')"
  StrIN = StrIN & """" & Me.Currently_With & ""","

  'If DCount("*", "UFMS Weakness Remediation Summary", "[Currently With] In " & strList) = 0 Then

  If StrIN & "" <> "" Then
      StrIN = Left(StrIN, Len(StrIN) - 1)
       MsgBox "No related record found", vbExclamation, "UFMS"
       Exit Sub
   Else
  End If
  DoCmd.OpenForm "frm_Remediation Summary", , , "[Currently With] In " & strList
0
PatHartmanCommented:
I'm confused.  Are you building a string of values or are you selecting a single value?  If you are selecting a single value then

DoCmd.OpenForm "frm_Remediation Summary", , , "[Currently With] = '" & Me.Currently_With  & "'"

is the simplest syntax.

You would only use IN() if you were actually building something that looked like:

'apple', 'orange', 'pear'

DoCmd.OpenForm "frm_Remediation Summary", , , "[Currently With] = 'orange'"
OR
DoCmd.OpenForm "frm_Remediation Summary", , , "[Currently With] In('apple', 'orange', 'pear')"
0
Rey Obrero (Capricorn1)Commented:
to get the values selected from the list box use this codes

Dim j, strList As String
With Me.[Currently_With]
    If .ItemsSelected.Count > 0 Then
        For j = 0 To .ItemsSelected.Count - 1
            strList = strList & "," & Chr(39) & .ItemData(j) & Chr(39)
        Next
    End If
End With
strList = "(" & Mid(strList, 2) & ")"

DoCmd.OpenForm "frm_Remediation Summary", , , "[Currently With] In " & strList





.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shieldscoAuthor Commented:
Rey - Compile error - method or data member not found

.ItemsSelected.Count
0
Rey Obrero (Capricorn1)Commented:
what is the name of the listbox, check if it has underscore or no
0
shieldscoAuthor Commented:
Currently With... no underscore
0
Rey Obrero (Capricorn1)Commented:
change this line

With Me.[Currently_With]

with


With Me.[Currently With]
0
shieldscoAuthor Commented:
Change to Currently With and received the following error:

method or data member not found

 .ItemsSelected.Count
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.