Search Form

[cbrsix]

Hi, I am trying to add a new field to my current search form. My question is, how can I get it to work properly if I am trying to search a textbox that allows multiple values? It keeps saying that it is not in my recordsource. Let me know if you guys would like to see some code.

[orange]

Watch this video and see if it helps.
http://www.datapigtechnologies.com/f...earchform.html

[Missinglinq]

Just to clarify, by "a textbox that allows multiple values" are you referring to that new Marquis de Sade inspired Datatype introduced in Access 2007? The one where multiple, discrete values can be crammed into a single Field?

I did an extensive search and can find nothing vis-à-vis searching on these type of Fields, per se. I did, however, find something on their use in Queries, which may or may not be of use:

http://office.microsoft.com/en-us/ac...010149297.aspx

The whole idea of allowing multiple, discrete values, to be stored in a single Field, flies in the face of the fundamental rules of relational databases that most experienced developers do not use them. The Access Gnomes are actually using mirrors-and-smoke, with these things, and store each "field's" multiple values in a separate Table, as I understand it, also know as a 'field within a field,' which makes doing even the simplest things complicated! They were introduced as part of the campaign to make Access Databases compatible with Sharepoint, which allows multi-value Fields.

As the video from the above link appears to be done using a 2003 or earlier GUI, I doubt that it will address the issue, vis-à-vis these new Datatypes. It did, by the way, appear to be a well put together presentation on searching in general!

Linq ;0)>

[cbrsix]

@Missinglinq

I believe that's correct. I attached a picture of the option to allow multiple values. If this selection is what you're talking about, then we are on the same page.

[cbrsix]

@orange - Thanks for the video, but that is not what I was looking for.

[orange]

Thanks for getting back. I wasn't sure if it was Search or the multivalued fields that was the heart of the issue.

As 'linq has said, most people do not use them. They are "a feature from M$oft" that will cause more complexity than they're worth. I worked with simiar concepts in Adabas -- multivalued fields and periodic groups - just no equivalent in SQL constructs.

Good luck with your project. If you do find something for searching these multivalued fields you should post your findings -
whether good or bad.

[cbrsix]

I just tried it again and got this error...

Here is the code that I'm using..

Private Sub Search_Click()
'Update the record source
Me.SearchSubform.Form.RecordSource = "Select * From Search " & BuildFilter
'Requery the subform
Me.Form!SearchSubform.Form.Requery
End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
varWhere = Null 'Main Filter
'Check for LIKE Last Name
If Me.LastName > "" Then
    varWhere = varWhere & "[Last Name] Like '*" & Me.LastName & "*' And "
End If
'Check for LIKE First Name
If Me.FirstName > "" Then
    varWhere = varWhere & "[First Name] LIKE '*" & Me.FirstName & "*' And "
End If
'Check for LIKE Company
If Me.Company > "" Then
    varWhere = varWhere & "[Company Name] LIKE '*" & Me.Company & "*' And "
End If
'Check for LIKE Account Number
If Me.AccountNumber > "" Then
    varWhere = varWhere & "[Account Number] LIKE '*" & Me.AccountNumber & "*' And "
End If
'Check for LIKE Social Security Number
If Me.SocialSecurityNumber > "" Then
    varWhere = varWhere & "[Social Security Number] LIKE '*" & Me.SocialSecurityNumber & "*' And "
End If
'Check for LIKE Entity Name
If Me.EntityName > "" Then
    varWhere = varWhere & "[EntityName] LIKE '*" & Me.EntityName & "*' And "
End If
'Check for LIKE Rep
If Me.Rep > "" Then
    varWhere = varWhere & " [Rep Name] LIKE '*" & Me.Rep & "*' And "
End If
'Check for LIKE EIN
If Me.EIN > "" Then
    varWhere = varWhere & "[EIN] LIKE '*" & Me.EIN & "*' And "
End If
'Check if there is a filter to return...
If IsNull(varWhere) Then
    varWhere = ""
Else
    varWhere = "WHERE" & varWhere
    
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
    varWhere = Left(varWhere, Len(varWhere) - 5)
    End If
End If
BuildFilter = varWhere
End Function

[cbrsix]

any suggestions? or could it just not be done? I found this link: http://www.access-programmers.co.uk/...d.php?t=199778 but am not sure how to incorporate it, if at all..

[orange]

You might do some testing using the info from vbaInet from the link you found. See post #10.
I would try with a simple example. Try and find a match in your multivalued field rep name

strFilter = "[Group].Value In ('" & Group.Text & "')"

The name of the posters field was Group and he set up a
strFilter 'string variable and a statement that asks if


I think your statement would be something like

strFilter = "[rep name].Value In ('" & [Rep name].Text & "')"

and he used it to filter a form as I read the post.

Me.Filter = strFilter

[cbrsix]

I just tried your suggestion and it is asking me to enter a parameter value. When I do, it returns all results

[orange]

I don't have acc2010. I have 2003 and no multivalued fields.
I tried to suggest code based on the link you provided.

Can you post the form event code you tried? You did try a simple test to start as suggested right?

[Missinglinq]

The error you show in Post #7, Error 3831, pretty much tells you the problem, doesn't it?

The multi-valued field, [Rep_Name], cannot be used in a WHERE or Having clause...

You've shown that [Rep_Name] is, in fact, a multi-valued field, and you're trying to use it, through concatenation, in a WHERE clause, and the Gnomes apparently won't allow that!

Linq ;0)>

[cbrsix]

@Orange - I entered the code just as you suggested and it came back with the Enter Parameter for [Rep Name].Value...When I did, it returned everything blank...

[cbrsix]

I decided to get rid of the multivalued fields as they have been giving me a ton of trouble.

I do have another question that may be along the same lines... I know it is possible to have a ListBox that is able to select multiple items in that box. My questions, from my code above, is it possible to have my search form search for the multiple things selected from that ListBox? If I have to modify my code for this, what needs to be changed? I can get it search if the ListBox doesn't allow multiple selections.

Thanks.

[Missinglinq]

Depends on how you plan to store these multiple items after you select them from the Listbox.

Linq ;0)>