Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Interface Design _ Search Vba Code Not Recognizing Multi-value Text Boxes

Posted by: sarinadipity Apr 17 2020, 01:36 PM

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

Posted by: theDBguy Apr 17 2020, 05:27 PM

Hi. Welcome to UtterAccess! welcome2UA.gif

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

Posted by: sarinadipity Apr 20 2020, 01:15 PM

Is there a way to do that without all the company only data on it?

Posted by: theDBguy Apr 20 2020, 01:22 PM

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

Posted by: sarinadipity Apr 20 2020, 01:39 PM

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.

Posted by: sarinadipity Apr 20 2020, 02:02 PM

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.

 UtterAccess_BrokenSearchVBA_Demo.zip ( 1.01MB ): 24
 

Posted by: theDBguy Apr 20 2020, 02:10 PM

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?

Posted by: sarinadipity Apr 20 2020, 02:40 PM

Search the Audit or Reference Documents in the search bar those are the multi valued fields

Posted by: isladogs Apr 20 2020, 03:04 PM

Please read this article: http://www.mendipdatasystems.co.UK/multivalued-fields/4594468763

Posted by: sarinadipity Apr 20 2020, 03:25 PM

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.

Posted by: isladogs Apr 20 2020, 03:37 PM

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

Posted by: sarinadipity Apr 20 2020, 04:02 PM

Sorry if these seems like a dumb question but are multi value boxes different from multi select list boxes?

Posted by: isladogs Apr 20 2020, 05:27 PM

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.

Posted by: sarinadipity Apr 21 2020, 01:51 PM

Okay so I have them as the list box but the search code still isnt working

Posted by: sarinadipity Apr 21 2020, 03:38 PM

Were you able to see the issue?

Posted by: FrankRuperto Apr 21 2020, 05:02 PM

I have yet to use multi-select list boxes. How does Access store multiple selections? Can multi-select be disabled in a list box?

Posted by: projecttoday Apr 21 2020, 05:09 PM

Can you post your form's query?

Why didn't you post in forms?

Posted by: FrankRuperto Apr 21 2020, 06:21 PM

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?





 

Posted by: projecttoday Apr 21 2020, 06:35 PM

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.

Posted by: theDBguy Apr 21 2020, 08:04 PM

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

Posted by: FrankRuperto Apr 21 2020, 09:03 PM

So if more than one value is selected in a list box and they are stored in a MVF of a hidden table, how do you query the stored values?

Posted by: theDBguy Apr 21 2020, 09:20 PM

You can use the In() clause. For example:

SQL
SELECT * FROM TableName WHERE MVF In(1,3,5,7)

Posted by: FrankRuperto Apr 21 2020, 09:33 PM

Ok so in a report if you have to print some or all elements of the collection, how does that work?

Posted by: theDBguy Apr 21 2020, 09:45 PM

Hi Frank. I'm not sure I follow. A record with a multivalue field would, of course, include all the values in the MVF. For example, if record ID=1 has a MVF of 1,3,5,7. Then record ID=1 will be included in the report if you select to filter the MVF with 1 or 3 or 5 or 7.

So, my confusion is, which collection were you referring to? If you display record ID=1 on a report with the MVF, then all the values in the MVF will also be included.

Posted by: projecttoday Apr 21 2020, 09:58 PM

This link has a loop that retrieves listbox selections: http://access.mvps.org/access/forms/frm0007.htm

Posted by: FrankRuperto Apr 21 2020, 10:02 PM

What I mean is if the MVF has 1,3,5,7 but I only want to display some of the values like the 3 and the 5, or the first two values in the MVF, is there a way to reference elements within the array and enumerate them like, e.g. MVF[1,2]?

Posted by: projecttoday Apr 21 2020, 10:11 PM

I don't think so.

Posted by: theDBguy Apr 21 2020, 10:25 PM

Oh, I see. Yes, you display individual values in a MVF by using the Value subfield. For example.

select mvf.value from tablename where mvf.value=1

Posted by: MadPiet Apr 21 2020, 10:29 PM

At the risk of hijacking the thread (I hope not) how do you remove the MVFs in a table like that? I remember doing this in FileMaker a million years ago... Not fun. Create a child table and then append to that?

Posted by: theDBguy Apr 21 2020, 10:32 PM

Hi Pieter. Yes, pretty much.

Posted by: isladogs Apr 22 2020, 03:05 AM

Frank/Pieter
My article linked in https://www.UtterAccess.com/forum/index.php?s=&showtopic=2057657&view=findpost&p=2749835 answers most of the questions you have asked about MVFs.

For info, I have retrieved MVFs for clients by first converting the deep hidden system table (where the MVF values are stored as normalised records) to a standard table.
However, the MVF table is normally completely inaccessible to users - it cannot be retrieved from the navigation pane or using a query.
Similarly, it is possible to retrieve the memo field column history which is also stored in a deep hidden system table though its not always straightforward.

Posted by: sarinadipity Apr 22 2020, 11:58 AM

Sorry everyone I am still very new to this. I posted in the interface design because I thought that was talking about coding, the form functions fine its just the specific section in the VBA code no longer working when I changed from a table to a query as the source. The query should be on the sample database.

Posted by: projecttoday Apr 22 2020, 01:45 PM

So do you have a table with an MVF defined in it?

Posted by: FrankRuperto Apr 22 2020, 02:01 PM

Hi Colin,

Thanks for the thorough explanation about MVF's. I have never been a fan of using MVF's because they can be quite complicated to manage. This is confirmed by several topics I have read on the subject matter which conclude to avoid using MVF's and store multiple values as separate records in child tables.

Posted by: sarinadipity Apr 22 2020, 02:37 PM

After reading everything in this thread and the articles recommended let me see if I can clarify. I have a table with the Reference Documents and Audits as List Boxes in the table. I have a Data Entry form which has the Reference Documents and Audits as list box selections.

The form I am having issues with is another form that I am purely using as a search form where records can be viewed but nothing can be added or edited on that form. The Search Form's source is a query. Using the reference documents field as an example.(Reference Document Values are pulled from a table and that reference table is fed by a append query) When running the Document Search source query I have the option to do either [Reference Documents].values or [Reference Documents]. I chose the [Reference Documents] so that all the values from a single records Reference Documents show up in the same field box instead of [Reference Documents].values which would split all the current records into multiples creating repetitive records with the only difference being a singular value in the reference documents.

What I think I need to do to fix the code on the Search Form is change the Reference Documents and Audits field in the table to be long textbox fields. Then on the Data Entry Form have the reference documents list box choose from the reference table and have any values selected for a record combined in the main table's reference document field as one entry instead of multiple selections. However, I am not entirely sure how to do that without messing up what I have established in other parts of the database.

I should note is when I started this project months ago I was a beginner that knew nothing about MS Access and I've been learning as I go along. While I have come a long way and am very happy with how the database is turning out what I am noticing is some aspects I established early on need to be modified.Basically some parts are the coding and design are jerry-rigged so while they work they are far from the best way and will eventually cause issues if not fixed. I am stuck in a bind where I am trying to change out bricks of a foundation without causing the whole house to come crashing down. The Search Form is something I established early on and it worked and looked exactly the way I wanted it but then other things needed to be changed and it malfunctioned so I am trying to get it to be almost exactly how it was before except with more sound structure and consistent function.

Posted by: theDBguy Apr 22 2020, 03:11 PM

Hi. When I open the file you posted and simply try to manually filter the Audits column, I am getting an error. You may have to fix that first before we can move on to fix your code.

Posted by: sarinadipity Apr 22 2020, 03:50 PM

What is the error you are getting? On mine it doesnt error but doesnt sort

Posted by: theDBguy Apr 22 2020, 04:15 PM

Not sort but try to filter it. (The Audits column, that is.)

Posted by: sarinadipity Apr 23 2020, 02:01 PM

Yeah it wont let me filter it either. Thats part of the issue. It acts like that column doesnt exist or acts like all the records are blank even though you can see it. I am not sure why though. It only started happening with the query.

Posted by: theDBguy Apr 23 2020, 02:20 PM

Hi. You need to trace the problem with that issue first and then fix it. Then, we can fix the code. That's why the code is not working, something is wrong with the form/query setup. Try creating a new form with a new query and only a MVF dropdown - to isolate everything else. If you can make that work, then adding in the rest should be easy.

Posted by: sarinadipity Apr 23 2020, 03:36 PM

QUOTE
What I think I need to do to fix the code on the Search Form is change the Reference Documents and Audits field in the table to be long textbox fields. Then on the Data Entry Form have the reference documents list box choose from the reference table and have any values selected for a record combined in the main table's reference document field as one entry instead of multiple selections. However, I am not entirely sure how to do that without messing up what I have established in other parts of the database.


How could I do this? The fields are list boxes which I have been told arent multivalue fields but they still arent working?

Posted by: theDBguy Apr 23 2020, 05:44 PM

Hi. I'm not sure you need to do any of that. The code worked fine for me, just not on your form because there's something wrong with the query. Look at the error message you got. I tried to trace where it's (the extra bracket in the syntax error) coming from; but since I am not familiar with your database, I couldn't find it. That's why I said you need to fix the query first because you created it, so you should be able to understand the error message and know where to fix it.

Posted by: sarinadipity Apr 27 2020, 12:48 PM

I tried to fix the coding by recombining front end and back end and now none of the codes will work. Ugh I am at a loss on this right now

Posted by: theDBguy Apr 27 2020, 12:52 PM

Hi. I think the code works fine. I told you, the query was what was broken for me. If you fix the query, then the code should just work. So, to save your sanity, I'd say start over without using any code. Create your query and try to manually apply a filter in the Audits column. Once you get that working, you can post the new db, and we can try to add the "code" for the search form part (one at a time).

Posted by: theDBguy Apr 27 2020, 12:59 PM

Hi. Just to clarify what I am trying to say. When I open the query "tblQualityQMSDocuments_SiteSpecific" and then click the filter dropdown arrow on the Audits column and uncheck the Blanks checkbox, I get the following error message.



Notice the double square brackets? I can't tell where that's coming from (without digging really deep into your query and tables). I am hoping you would be able to quickly fix it.

Posted by: theDBguy Apr 27 2020, 01:16 PM

Hi. Just as a follow up and troubleshooting, I bound the form to the table instead of the query, and I was able to make the code work.


Posted by: Griditem Apr 28 2020, 01:21 PM

Ok so in a report if you have to print some or all elements of the collection, how does that work?

Posted by: sarinadipity Apr 28 2020, 01:22 PM

Yeah I know the table works but I need to somehow get it to work with the query which seems to not be possible

Posted by: theDBguy Apr 28 2020, 01:23 PM

QUOTE (Griditem)
Ok so in a report if you have to print some or all elements of the collection, how does that work?
Hi. Welcome to UtterAccess! Please explain, collection of what exactly?

Posted by: sarinadipity Apr 28 2020, 01:25 PM

I found part of the issue. I had to change the names on some of the fields and it keeps going back and forth between recognizing the old name and the new name. How do I scrub the database so it no longer is reading it as the old name?

Posted by: theDBguy Apr 28 2020, 01:30 PM

QUOTE (sarinadipity)
Yeah I know the table works but I need to somehow get it to work with the query which seems to not be possible

I don't agree. This is an image of a table with a MVF.

This is an image of a query based on that same table with a MVF.

This is the result of applying a filter to the MVF column (f7).

As you can see, it's very possible to filter a query with a MVF.

Posted by: theDBguy Apr 28 2020, 01:32 PM

QUOTE (Sarinadipity)
I found part of the issue. I had to change the names on some of the fields and it keeps going back and forth between recognizing the old name and the new name. How do I scrub the database so it no longer is reading it as the old name?
You will have to explain that a bit. I am not familiar with your database enough to understand what that means. What names are you referring to?

Posted by: sarinadipity Apr 28 2020, 03:01 PM

So as I stated before I was learning as I went along. There are a few things wrong with the field names. For one, they have spaces in them which I know I shouldn't have done and two, a few of the field names were decided they needed to be called different things. For example, the IMS field in the search is called QMS in the table. I tried to change the names over but the autochange feature will not work on spilt databases and it wasn't functioning correctly when I did it manually.

So what I tried to do the past few days was unspilt the database, change the names and work from there. That was a disaster because then none of the codes or features wanted to work. Now I went back to the backup I made before I unsplit it and I am back to where we were originally. I am not sure why some things function at some points then start to malfunction at the smallest changes. When the document search is hooked to a table it is fine, when I filter the document search it is fine but it will not recognized either multi value field even though I know in other cases like you pointed out it can.

Posted by: theDBguy Apr 28 2020, 03:08 PM

Hi. Like I said earlier, don't worry about the code for now. Create a new database, put your tables in it, and then create queries to see if you can filter the MVFs. Once you get that to work, we can go back to working with the code and fix it.

Posted by: sarinadipity Apr 29 2020, 01:04 PM

For the sort its saying there is a syntax error on it even though there isnt one. Im very stuck on how to fix it.

Posted by: theDBguy Apr 29 2020, 01:13 PM

Hi. Did you try what I suggested above? Start over! Create a new database file and import your one table with MVF into it. See if you can filter the table. If you can, create a query and see if you can filter it. Let us know how that goes. There could be something wrong with your existing file, and trying to keep using it may not be very productive right now. Just my 2 cents...

Posted by: sarinadipity May 5 2020, 02:31 PM

I have one MVF filled by a value list of 3 different values and I have the other filled by a table which is updated by a query. What is your recommended way to fill a MVF field with?

Posted by: theDBguy May 5 2020, 03:05 PM

Hi. I'm not sure I understand your question; but if you're asking how to populate the MVF field with multiple data at once, I think you'll need to use VBA code (specifically Recordsets) to do it. I haven't looked at it in a while, but maybe check out my demo on MVFs from my website. Let me know if that doesn't help.

Posted by: sarinadipity May 7 2020, 02:27 PM

I think I found a way around my issue but not entirely sure. In the search code I have in the sample database how would I reference the subform so that the reference value search box searches that? Having the reference value MVF on the main form is causing a lot of repetition so Im thinking if I put the subform to use for the code then I can eliminate the MVF in the query which fixes the sort issue.

Posted by: FrankRuperto May 7 2020, 06:32 PM

Are we having fun yet with MVF's?... I encourage you to not use them as they're a nightmare to work with.

Posted by: theDBguy May 7 2020, 06:51 PM

QUOTE (sarinadipity)
I think I found a way around my issue but not entirely sure. In the search code I have in the sample database how would I reference the subform so that the reference value search box searches that? Having the reference value MVF on the main form is causing a lot of repetition so Im thinking if I put the subform to use for the code then I can eliminate the MVF in the query which fixes the sort issue.

I'm not sure I understand your question. Can you elaborate further or post some images to help explain it?

Posted by: sarinadipity May 13 2020, 02:22 PM

I actually managed to make it work! I had do a select SQL in the record source instead of going off a query directly. Thank you for all your help the DBguy!!

I regret using MVF but they seemed like such a useful thing when I was learning and first building this and now I am too far into it to try and use an alternative method. That's the issue with learning while you go, you gotta try to untangle the junk later on. It worked at the time but was not the best way to do some of this stuff.

Posted by: theDBguy May 13 2020, 03:53 PM

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

Posted by: FrankRuperto May 13 2020, 06:49 PM

Hi Sarinadipity,

Check out Commandment #2 (Lookup Fields) in "The Ten Commandments of Access": http://access.mvps.org/access/tencommandments.htm

Good Luck!