Solved Multiple search engine with dropdowns

[zelarra821]

No that cannot be done, with or without any filters. There is no you can bind a multi select listbox to a control. Your locations have to be stored in a junction table unless you use a multivalue field. That is two bad ideas.

I would concatenate the value using a concatenate function in the query
So you display. location1; Location2; Location 4
If you want to add or delete locations than you do that with a pop up form.
You also have the calculated field with only IDs, but not shown
1;2;4

Then you can code a filter. It can be done, it will be complicated.

And understanding what I'm looking for, is there a simple way to do it? You have proposed a complicated

 

[zelarra821]

Because the only thing I can think of is repeating songs, that is, that there are as many records of a song as there are times it appears repeated in several albums

 

[MajP]

Try this in the read files.

Private Sub cmdRead_Click()
  ClearTree
  SpanAndLog
  LoadFileDirectory Me.cmboDefault
  mcTree.ExpandToLevel 1, True
  mcTree.Refresh
  mcTreeSummation.ExpandToLevel 1, True
  mcTreeSummation.Refresh
End Sub

 

[MajP]

And understanding what I'm looking for, is there a simple way to do it? You have proposed a complicated

No there is no easy way to do it. I have never even seen anyone use a listbox in a continuous form because it does not make any sense because you are unlikely to even see the selection unless you have a large listbox. Fore sure I have never seen a multiselect listbox in a continuous form. If I was to do it I would show these values concatenated and have a popup to add or delete. If you think about it this would be a useless presentation. If they had selections 6,7,8 you would not see any selections.

 

[zelarra821]

Try this in the read files.

Private Sub cmdRead_Click()
  ClearTree
  SpanAndLog
  LoadFileDirectory Me.cmboDefault
  mcTree.ExpandToLevel 1, True
  mcTree.Refresh
  mcTreeSummation.ExpandToLevel 1, True
  mcTreeSummation.Refresh
End Sub

Keep changing the value in the table for me.

 

[zelarra821]

No there is no easy way to do it. I have never even seen anyone use a listbox in a continuous form because it does not make any sense because you are unlikely to even see the selection unless you have a large listbox. Fore sure I have never seen a multiselect listbox in a continuous form. If I was to do it I would show these values concatenated and have a popup to add or delete. If you think about it this would be a useless presentation. If they had selections 6,7,8 you would not see any selections.

I agree. Thanks so much for the explanation. In this case, what I will do is add all the songs, even if they are on different discs.

What I told you I had in mind for another database, but I have looked for another alternative, which I think may be simpler. It would have a list box to choose the options you want to print. On this page comes an example (number 84) of how to do it: https://sites.google.com/site/jerrmex/descargas.

 

[MajP]

It would not be hard to concatenate the solution and show in a textbox, but you would not be able to edit them from that location.

You could display

SongA	Disc 1 Dis 2
SongB	Disc 3
SongC	Disc1 Disc 3

Where the location field is a concatenated field, displayed in a textbox. You cannot shrink or grow so all are the same size.

 

[zelarra821]

It would not be hard to concatenate the solution and show in a textbox, but you would not be able to edit them from that location.

You could display

SongA	Disc 1 Dis 2
SongB	Disc 3
SongC	Disc1 Disc 3

Where the location field is a concatenated field, displayed in a textbox. You cannot shrink or grow so all are the same size.

I understand that you say to concatenate the locations, but I don't know how you intend to do it. Also, I also don't understand that I couldn't edit it and they would all be the same size.

 

[zelarra821]

Try this in the read files.

Private Sub cmdRead_Click()
  ClearTree
  SpanAndLog
  LoadFileDirectory Me.cmboDefault
  mcTree.ExpandToLevel 1, True
  mcTree.Refresh
  mcTreeSummation.ExpandToLevel 1, True
  mcTreeSummation.Refresh
End Sub

I've already caught the bug, and it was mine. Above the form you showed the route, but it took only the one of the first route, and what I did was "make it dynamic", using this line. Of course, I did not realize that it is referenced to the table, and hence I changed it.

  Me.TxtDefaultFolderPath = Me.cmboDefault.Column(2)

Thanks a lot.

 

[MajP]

I understand that you say to concatenate the locations, but I don't know how you intend to do it. Also, I also don't understand that I couldn't edit it and they would all be the same size

I will do a demo when I get some time. I am tearing out a bathroom today.

 

[zelarra821]

Ok, when you can. Enjoy!

 

[MajP]

See demo.

 

[zelarra821]

Hi. I like the idea.

We are going to improve it. I have set FAYT to search for a text string using the SearchFor text box, and also to search by location using the cmbLocation dropdown. To get it to work, I had to use Like. For this there is no problem, because it works.

Now, in the form where I enter the locations, I want to replace the form with datasheet view, with a list box. I have managed to add the locations that I mark (whether I mark one, or if I mark several). However, there are three problems that I cannot solve:

1. When you open the form for choosing locations, the locations that are already assigned to the song should be highlighted. To do this, I think, you have to do the reverse step: de-chain. And through a for next enter the selected items.

2. Another thing is that, once those associated with the song appear selected, if I deselect one, I should remove it, both from the FSongs form field and from the j_tblSongsLocations table.

3. Do not add the locations at the end, but replace the existing ones with the selected ones. For example, I have three selected, and I add a fourth. What should appear are four, not 3 + 4 as it happens now.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thank you very much for the demo. It's super good.

 

[MajP]

See demo

 

[zelarra821]

Yes, perfect. Now I put together the three databases into one, I hope not to mess it up.

1. Another thing I've been seeing regarding the tree. There is the possibility to change the design of the tree, by touching these two parameters:

.Indentation = 15 * mcPtPixel 'To 25
.NodeHeight = 20 * mcPtPixel 'To 25

However, I change them in the InitializeTree and InitializeSummationTree procedures, and it only changes the first tree, not the summation. Look:



2. In addition, in the attached database there is the possibility of changing the icon to expand. Instead of a square put a triangle. I am trying to change it but I am not able to. I leave you a video:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[MajP]

There are a lot of features formatting. I have not dug into too much. I would look at the real demo for some ideas.

Excel, Access: An MSForms (all VBA) treeview

jkp-ads.com: Treeview control

jkp-ads.com

 

[zelarra821]

I have already managed to put the symbols I wanted.

You have to put this in the header of the form where the tree is:

Private mfrImages As MSForms.Frame

In the event when loading, you have to load the images:

     Set mfrImages = Me.subTreeView.Form.Controls ("frImages"). Object

And then in both InitializeTree and InitializeSummationTree, under

         .ShowExpanders = True

add this

         Call .ExpanderImage (mfrImages.Controls ("Win7Minus"). Picture, _
                                 mfrImages.Controls ("Win7Plus2"). Picture)

And with that it would change the icons.

Now, what I am not able to do is update the Summation tab. No matter how much I give the code, I don't see where the fault is, so if you do me a favor, you know how you did it, tell me where I would have to touch so that it applies the same changes in that tab. I leave you the database with all the changes.

Thank you.

 

[MajP]

Sorry that was my fault. I have two methods that allow you to format each tree. The problem was in the second method for the summation, I copied from the first method. I did not change mcTree to mcTreeSummation after copying. The second method was just reformatting the first tree.

Private Sub InitializeSummationTree()
'-------------------------------------------------------------------------
' Procedure : Initialize
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (www.jkp-ads.com)
' Created   : 15-01-2013
' Purpose   : Initializes the userform,
'             adds the VBA treeview to the container frame on the userform
'             and populates the treeview.
'-------------------------------------------------------------------------

    On Error GoTo errH

    ' create a new clsTreeview returned with the TreeControl container attached
    ' (a new treeclass cleans out previous properties, though could reuse an old treeclass)
    Set mcTreeSummation = Me.subTreeViewSummations.Form.pTreeview
    
    With mcTreeSummation
        Call .NodesClear ' if reusing an exisiting treeclass
        .AppName = "Video File Directory"
        'Set some characteristics of the root of the tree,
        .CheckBoxes() = False
        .LabelEdit(bAutoSort:=True) = 1  'default 0, can be edited (like LabelEditConstants tvwAutomatic/tvwManual)
        ' new in v022, EnableLabelEdit added as alternative to LabelEdit
        .EnableLabelEdit(bAutoSort:=True) = False
        .FullWidth = True
        'Need to find out what this conversion is the constant is .75
        .Indentation = 25 * mcPtPixel '15
        .NodeHeight = 25 * mcPtPixel '20
        .ShowLines = True
        .ShowExpanders = True
        Call .ExpanderImage(mfrImages.Controls("Win7Minus").Picture, _
                                mfrImages.Controls("Win7Plus2").Picture)
    End With
    Exit Sub
errH:
    #If debugMode = 1 Then
        Debug.Print Err.Source, Err.Description
        Stop
        Resume
    #End If

    Debug.Print Err.Source, Err.Description

    If Not mcTreeSummation Is Nothing Then
        mcTreeSummation.NodesClear
    End If
End Sub

These needed to get change to mcTreeSummation from mcTree
Set mcTreeSummation = Me.subTreeViewSummations.Form.pTreeview
With mcTreeSummation

 

[zelarra821]

I've already seen where the error was:

With mcTreeSummation

I'm going to join the three databases and translate them. Thanks a lot.

 

[zelarra821]

I'm trying to put a default value in the dropdown Estado, and it won't let me. It does not matter if you put it in the properties of the drop-down or activate Me.FilterOn when starting the form.