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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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"
shieldscoAuthor Commented:
OFSP
O&M
System Owner
OpDiv
OCIO
shieldscoAuthor Commented:
I would prefer not to hard code the values
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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?
shieldscoAuthor Commented:
Me.Currently_With is multiple items
and the control source is a query
Rey Obrero (Capricorn1)Commented:
post the query here.
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];
shieldscoAuthor Commented:
Can the spaced be trimed in the query?
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] ?
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
shieldscoAuthor Commented:
it a list box
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
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
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')"
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





.

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
Rey Obrero (Capricorn1)Commented:
what is the name of the listbox, check if it has underscore or no
shieldscoAuthor Commented:
Currently With... no underscore
Rey Obrero (Capricorn1)Commented:
change this line

With Me.[Currently_With]

with


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

method or data member not found

 .ItemsSelected.Count
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.