UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
4 Pages V  1 2 3 > »   (Go to first unread post)
   Reply to this topicStart new topic
> Search Vba Code Not Recognizing Multi-value Text Boxes, Access 2013    
 
   
sarinadipity
post Apr 17 2020, 01:36 PM
Post#1



Posts: 57
Joined: 20-March 20



Originally when I had the form hooked up to the table the search worked perfectly as it is below. Now that I needed to switch the source from the table to a query the code isnt working on the Multi-Value boxes ([Reference Documents] and [Audits]). I am working with a front end linked to a back end with the table data. What can I do to make this code recognize the multi-valued boxes? The form is a search form only and not used for data entry. The malfunctioning code is in red below.


Private Sub btnSearch_Click()
Dim strSQL As String
Dim filterStr As String

On Error GoTo ProcError

If Not IsNull(Me.txtQMSNumber) Then
filterStr = filterStr & " [Site QMS] Like '*" & Me.txtQMSNumber & "*' AND "
End If
If Not IsNull(Me.txtDocumentclass) Then
filterStr = filterStr & " [Document Class] Like '*" & Me.txtDocumentclass & "*' AND "
End If
If Not IsNull(Me.txtdocumentname) Then
filterStr = filterStr & " [Document Name] Like '*" & Me.txtdocumentname & "*' AND "
End If
If Not IsNull(Me.txtManagementsystem) Then
filterStr = filterStr & " [Management System] Like '*" & Me.txtManagementsystem & "*' AND "
End If
If Not IsNull(Me.txtreferencedocuments) Then
filterStr = filterStr & " tbl_ReferenceDocuments.[Reference Documents].Value Like '*" & Me.txtreferencedocuments & "*' AND "
End If

If Not IsNull(Me.txtdepartment) Then
filterStr = filterStr & " [Department] Like '*" & txtdepartment & "*' AND "
End If
If Not IsNull(Me.txtaudits) Then
filterStr = filterStr & " [Audits] Like '*" & Me.txtaudits & "*' AND "
End If

If Not IsNull(filterStr) Then
filterStr = Left(filterStr, Len(filterStr) - 4)
End If

Me.Filter = filterStr
Me.FilterOn = True

ProcExit:
Exit Sub
ProcError:
Call LogError(Err.Number, Err.Description, "btnSearch_Click()")
Go to the top of the page
 
theDBguy
post Apr 17 2020, 05:27 PM
Post#2


UA Moderator
Posts: 78,474
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

Are you able to share a demo version of your db?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
sarinadipity
post Apr 20 2020, 01:15 PM
Post#3



Posts: 57
Joined: 20-March 20



Is there a way to do that without all the company only data on it?
Go to the top of the page
 
theDBguy
post Apr 20 2020, 01:22 PM
Post#4


UA Moderator
Posts: 78,474
Joined: 19-June 07
From: SunnySandyEggo


Hi. There's probably a better way that I just can't remember at this moment, but try the following steps.

1. Create a copy of your db for sharing here
2. Open the copy
3. Right-click each table and select Copy
4. Right-click the table again and select Paste
5. When the option window shows up, select Structure Only
6. Delete the original table and rename the empty table
7. Add some test/dummy data
8. Perform a C&R
9. Zip the file and post it

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
sarinadipity
post Apr 20 2020, 01:39 PM
Post#5



Posts: 57
Joined: 20-March 20



There is a lot in the database so I will see if that's possible in a way that will hold up to help fix the issue. The issue is even though the form is pulling up the multi value field from the query so that it is viewable the search VBA wont recognize them as fields in the form like it does with the single valued fields.
Go to the top of the page
 
sarinadipity
post Apr 20 2020, 02:02 PM
Post#6



Posts: 57
Joined: 20-March 20



It is giving me a different error message but it is still the same issue. I was getting the Error 2455 : You entered an expression that has an invalid reference to the property |. Hopefully it doesn't mess it up that I just gave you what was causing issues and not the whole thing as blanks.
Attached File(s)
Attached File  UtterAccess_BrokenSearchVBA_Demo.zip ( 1.01MB )Number of downloads: 24
 
Go to the top of the page
 
theDBguy
post Apr 20 2020, 02:10 PM
Post#7


UA Moderator
Posts: 78,474
Joined: 19-June 07
From: SunnySandyEggo


Hi. I opened your search form, and it seems to be working fine. What are the steps I need to do to duplicate the problem you're getting?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
sarinadipity
post Apr 20 2020, 02:40 PM
Post#8



Posts: 57
Joined: 20-March 20



Search the Audit or Reference Documents in the search bar those are the multi valued fields
Go to the top of the page
 
isladogs
post Apr 20 2020, 03:04 PM
Post#9


UtterAccess VIP
Posts: 2,396
Joined: 4-June 18
From: Somerset, UK


Please read this article: Multi-value fields ... and why you shouldn't use them

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
sarinadipity
post Apr 20 2020, 03:25 PM
Post#10



Posts: 57
Joined: 20-March 20



What would be an alternative to multiple value fields?

*Edit* I know multi value fields are not ideal for queries but I am unsure of an alternative I could use that would keep the document search form looking exactly the same. I like when you click the plus button you can see all the reference document records that go with the main record. The issue with this database is I was learning as I went so it does need cleaned up I just dont know how to do that without messing up all the things I like about the set up.
This post has been edited by sarinadipity: Apr 20 2020, 03:59 PM
Go to the top of the page
 
isladogs
post Apr 20 2020, 03:37 PM
Post#11


UtterAccess VIP
Posts: 2,396
Joined: 4-June 18
From: Somerset, UK


That depends on what you were doing with them. A multiselect list box is a good alternative in most cases.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
sarinadipity
post Apr 20 2020, 04:02 PM
Post#12



Posts: 57
Joined: 20-March 20



Sorry if these seems like a dumb question but are multi value boxes different from multi select list boxes?
Go to the top of the page
 
isladogs
post Apr 20 2020, 05:27 PM
Post#13


UtterAccess VIP
Posts: 2,396
Joined: 4-June 18
From: Somerset, UK


Multiselect list boxes are completely different to multivalue fields.
Read my article about MVFs, then research multiselect listboxes

BTW the + sign next to table records is used to open a Subdatasheet which is something else entirely.
Not totally bad but I would avoid them as well as they can be confusing to end users and cause apps to run more slowly.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
sarinadipity
post Apr 21 2020, 01:51 PM
Post#14



Posts: 57
Joined: 20-March 20



Okay so I have them as the list box but the search code still isnt working
Go to the top of the page
 
sarinadipity
post Apr 21 2020, 03:38 PM
Post#15



Posts: 57
Joined: 20-March 20



Were you able to see the issue?
Go to the top of the page
 
FrankRuperto
post Apr 21 2020, 05:02 PM
Post#16



Posts: 1,099
Joined: 21-September 14
From: Tampa, Florida USA


I have yet to use multi-select list boxes. How does Access store multiple selections? Can multi-select be disabled in a list box?
This post has been edited by FrankRuperto: Apr 21 2020, 05:04 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
projecttoday
post Apr 21 2020, 05:09 PM
Post#17


UtterAccess VIP
Posts: 12,385
Joined: 10-February 04
From: South Charleston, WV


Can you post your form's query?

Why didn't you post in forms?

--------------------
Robert Crouser
Go to the top of the page
 
FrankRuperto
post Apr 21 2020, 06:21 PM
Post#18



Posts: 1,099
Joined: 21-September 14
From: Tampa, Florida USA


Hi Robert,

The OP started this thread on 4/17 so I think its too late to move it to Forms forum. How does Access store multi-selected values in a table, as a collection in MVF's or a special table?




Attached File(s)
Attached File  LstMultiSelect.PNG ( 124.07K )Number of downloads: 12
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
projecttoday
post Apr 21 2020, 06:35 PM
Post#19


UtterAccess VIP
Posts: 12,385
Joined: 10-February 04
From: South Charleston, WV


I'm a late-comer to this thread. I had no idea it's been around since the 17th. I posted in the other thread. I suggested he post in Forms.
Multi-values and multi-selected values are two different things. Access stores things in system tables.

--------------------
Robert Crouser
Go to the top of the page
 
theDBguy
post Apr 21 2020, 08:04 PM
Post#20


UA Moderator
Posts: 78,474
Joined: 19-June 07
From: SunnySandyEggo


Hi Frank. MVFs and Attachments are stored in hidden tables.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
4 Pages V  1 2 3 > » 


Custom Search


RSSSearch   Top   Lo-Fi    8th July 2020 - 05:28 PM