Search Form Code Help

[potts]

I have the following code:

If Not IsNothing (Me!Keycodes) Then
gstrArticle = "[ArticleID] IN " & " (SELECT ArticleID FROM " & " _
qryArticleKeyword" & " Keyword IN (" & Me!KeyCodes & ")"
Else
gstrArticle = gstrArticle & " AND [ArticleID] IN " & " (SELECT _ ArticleID FROM " & " qryArticleKeyword" & " Keyword IN _
(" & Me!KeyCodes & ")"
End If


The code is attached to the On Click event of a command button on an unbound search form.

KeyCodes is an unbound text box that holds the Keyword

qryArticleKeyword is based on the link table in a many - many relationship, which has fields ArticleID and Keyword as its primary key.

DoCmd.OpenForm "frmArticles", Wherecondition:= gstrArticle



I have used the same code (minus the SELECT SQL) for the other unbound controls on the search form with no problems. However, this time I keep getting a message box requesting the entry of the parameter value and the Keyword(s) selected (there is one message box for each keyword).

It is possibly of note that this is the only control so far to contain multiple selections.

Can anyone see where I might be going wrong?

 

[WayneRyan]

potts,

I've seen this before, but I can't find any documentation.

gstrArticle = gstrArticle & " AND [ArticleID] IN " & " (SELECT _ ArticleID FROM " & " qryArticleKeyword" & " Keyword IN _
(" & Me!KeyCodes & ")"


What you are trying to build is:

AND [ArticleID] IN
(SELECT _ ArticleID
FROM qryArticleKeyword
where Keyword IN ('kw1', 'kw2', 'kw3')

Unless your value for Me!KeyCodes is = 'kw1', 'kw2', 'kw3'
(with the quotes) you will have a problem.

Run your code with the debugger and either use the
Object Browser or the Immediate window to view your
completed SQL string. I don't think it will appear as above.

let me know what you see,
Wayne

 

[potts]

Wayne,

I actually got it to work by placing & Chr$(34) either side of the & Me!KeyCodes &.

However, the form I copied this from had the control working as an OR search rather than an AND, which I seem to be stuck with. Any ideas here?

 

[WayneRyan]

potts,

The In works like an OR by definition.

To make an AND I think that you have to make 3 (or n)
controls and express your WHERE like:

sql = "...Where ([field] = '" & Control1 & "' or " & _
Isnull(Control1) & ") And " & _
"([field] = '" & Control2 & "' or " & _
Isnull(Control2) & ") And " & _
"([field] = '" & Control3 & "' or " & _
Isnull(Control3) & ");"

This allows for the user to enter up to 3 criteria.

In typing this out I realized that you can't want an AND
because the table's field can't possible equal all three values
in your control.

let me know,
Wayne

 

[potts]

Nah, it's not working.

Maybe I should have been clearer, but the Or search will only be conducted within the results contained in one control.

The first code works fine from this respect, if only it would stop asking for the parameter again.

 

[potts]

Just found that the code does work provided your control supplies numeric and not text data.

Any help with getting it to work with text would be greatly appreciated.

 

[WayneRyan]

Hi potts,

The reason that it works with numerics is that the SQL
you are building needs no punctuation, other than the
commas.


What you are trying to build is:

AND [ArticleID] IN
(SELECT _ ArticleID
FROM qryArticleKeyword
where Keyword IN ('kw1', 'kw2', 'kw3')

Unless your value for Me!KeyCodes is = 'kw1', 'kw2', 'kw3'
(with the quotes) you will have a problem.

If (visually) the value of your control is --> kw1, kw2, kw3
you need to programmatically tranform to --> 'kw1', 'kw2', 'kw3'

If they were dates --> #kw1#, #kw2#, #kw3#

Try typing --> 'kw1', 'kw2', 'kw3' into your control.

let me know,
Wayne

 

[potts]

Thanks