asked on

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

Rey Obrero (Capricorn1)

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"

shieldsco

ASKER

OFSP
O&M
System Owner
OpDiv
OCIO

shieldsco

ASKER

I would prefer not to hard code the values

Rey Obrero (Capricorn1)

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?

shieldsco

ASKER

Me.Currently_With is multiple items
and the control source is a query

Rey Obrero (Capricorn1)

post the query here.

shieldsco

ASKER

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];

shieldsco

ASKER

Can the spaced be trimed in the query?

Rey Obrero (Capricorn1)

<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] ?

PatHartman

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

shieldsco

ASKER

it a list box

shieldsco

ASKER

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

shieldsco

ASKER

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

PatHartman

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')"

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shieldsco

ASKER

Rey - Compile error - method or data member not found

.ItemsSelected.Count

Rey Obrero (Capricorn1)

what is the name of the listbox, check if it has underscore or no

shieldsco