Evaluating result of a variable from multiple checkboxes

[Airtech]

I have a routine that searches a directory structure for files. Right now the file pattern is simply *.* and when it is finished, I have a cleanup routine that strips out the file extensions that I don't want (*.m3u, and files which the routine will flag as 'unknown')

What I would like to do is to populate a variable with the acceptable file extensions based on the users choice.

For example, I setup four checkboxes, each a different type of file (i.e., audio, video, etc.)

Each type has a checkbox. How can I populate a variable (ScanExt) based on the slection of checkboxes. So if the user chose only audio, the variable might read *.mp3, *.wma, and if they chose audio and video (then it would read *.mp3, *.wma, *.wmv, *.avi.

Does anyone have any suggestions on how I might acheive this?

Thanks,
CJ

[DonRayner]

Put this at the top of your forms module

public myvar as string
 

put this in the on change event for each checkbox

Call VarString
 

Add this to the bottom of the forms module

Public Sub VarString ()
If cb1 then
    myvar = "*.mp3"
Else
    myvar = ""
End if
 
if cb2 then
    if myvar <> "" then
        myvar = myvar & ", *.wma"
    else
        myvar = "*.wma"
    end if
 
if cb3 then
    if myvar <> "" then
        myvar = myvar & ", *.wmv"
    else
        myvar = "*.wmv"
    end if
 
if cb4 then
    if myvar <> "" then
        myvar = myvar & ", *.avi"
    else
        myvar = "*.avi"
    end if
End If
End Sub
 

Replace the cb1, cb2, cb3, cb4 with the actual names of your checkboxes.

[Airtech]

I see what this is trying to achieve and it is working but only partially.
I get lots of errors on the fourth check box about being null, and it doesn't quite work if the audio option gets turned off.

I have the code below:

Public Sub MediaString()
 
If chkAudio Then
    If ScanExt <> "" Then
        ScanExt = ScanExt & "*.mp3, *.wma"
    Else
        ScanExt = ""
    End If
End If
 
If chkVideo Then
    If ScanExt <> "" Then
        ScanExt = ScanExt & ", *.avi"
    Else
        ScanExt = "*.mp3,*.wma"
    End If
End If
 
If chkImages Then
    If ScanExt <> "" Then
        ScanExt = ScanExt & ", *.jpg, *.gif"
    Else
        ScanExt = "*.avi"
    End If
End If
 
If chkKaraoke Then
    If ScanExt <> "" Then
        ScanExt = ScanExt & ", *.CDG, *.KAR"
    Else
        ScanExt = ", *.jpg, *.gif"
    End If
End If
 
If chkAudio And chkVideo And chkImages And chkKaraoke Then
    ScanExt = "*.*"
End If
 
End Sub

I included the last piece so that if they are all left on at the default, then it would simply include all files.
I am still struggling with this and trying to polish it.

Any combo of the four checkboxes could be on or off, and being a file filter the format is as your code suggests, *.mp3, *.wav, *.avi, etc.

The only thing I have not tried, and perhaps I missed this in your code, was having a seperate checkbox for each extension, but that would many more checkboxes than I have now, about 9 off them if I split this out into a seperate checkbox for each extension possible. Then adding extensions would require additional checkboxes.

What do you think?

[Airtech]

I had to tweak the first one, to try and get the formatting right, but that doesn't seem to to the trick either.

[DonRayner]

Try it like this

Public Sub MediaString()
If chkAudio Then
 ScanExt = "*.mp3, *.wma"
Else
 ScanExt = ""
End If
 
If chkVideo Then
 If ScanExt <> "" Then
  ScanExt = ScanExt & ", *.avi"
 Else
  ScanExt = "*.avi"
 End If
End If
If chkImages Then
 If ScanExt <> "" Then
  ScanExt = ScanExt & ", *.jpg, *.gif"
 Else
  ScanExt = "*.jpg, *.gif"
 End If
End If
If chkKaraoke Then
 If ScanExt <> "" Then
  ScanExt = ScanExt & ", *.CDG, *.KAR"
 Else
  ScanExt = "*.CDG, *.KAR"
 End If
End If
If chkAudio And chkVideo And chkImages And chkKaraoke Then
ScanExt = "*.*"
End If
End Sub
 

[DonRayner]

oops, add the following between lines one and two of my previous post.

scanext = ""
 

you can then delete lines 4,5 or leave as is.

[Airtech]

Thank you for sending this, I see where I got the intention of your original suggestion built with a couple mistakes. I still get the error on chkKaraoke, but I think I can put in some handling.

This looks like it will work great.

THANKS.!!!!! I appreciate your help.

CJ

[Airtech]

I seem to have found a pretty effective workaround. What seems to be happening is that the check on chkKaraoke isn't taking, and the logic was having trouble with the fact that it was Null, so I added the following code for this and seem to have a workaround, that stops generating errors, you just don't get the check mark,.

If IsNull(chkKaraoke) Then
GoTo MediaString_Exit
End If

While not ideal, if this is the worst thing that my code has to deal with for this function, then it works for me.

Thanks again for your help. Basic, to the point, and it gets the job done.
Now I will take that variable and can use it in the file scan module for the project.

PROGRESS!!!!!! I Love it!

[DonRayner]

That's great, good luck on the rest of your project. You can fix the error you're getting by setting the default value of your fields to false.

[Airtech]

I will actually have to use the other one, because the default is to scan all files.
But you are right, and either way should be perfectly functional.

[Airtech]

Don,

OK, I am adding to this one, because what this solution resolved, lead to another issue. I suspect you will probably have a fast answer.
I am using the AllenBrowne code of "filldirlisttotable"

The code works great. Only one issue, I cannot find anywhere on the net any notes about using the filespec option.
By default it uses *.*, and I can make it work by specifying only one extension, such as *.jpg, but it becomes useless when I specify multiple extensions such as *.jpg, *.gif, *.png.

Obviously that must be the wrong way to provide the filespec.
Have you used this function? I am providing part of it below.
There may even be a better way of doing this, but I have written substantial code around this.

I am kind of thinking that unless this code simply canot handle the multiple extensions regardless of how they are provided, then I may have to re-write this section of code. This is what the previous issue is feeding into to allow the user to search only certain types of files.

Call FillDirToTable(colDirList, strpath, strFileSpec, bIncludeSubfolders)

This goes to:

Private Function FillDirToTable(colDirList As Collection _
    , ByVal strFolder As String _
    , strFileSpec As String _
    , bIncludeSubfolders As Boolean)
 
    'Build up a list of files, and then add add to this list, any additional folders
    On Error GoTo Err_Handler
 
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
    Dim strSQL As String
 
    'Add the files to the folder.
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
         gCount = gCount + 1
         SysCmd acSysCmdSetStatus, gCount
         strSQL = "INSERT INTO zzLibScan " _
          & " (MediaFileName, MediaPath) " _
          & " SELECT """ & strTemp & """" _
          & ", """ & strFolder & """;"
         CurrentDb.Execute strSQL
        colDirList.Add strFolder & strTemp
        strTemp = Dir
    Loop
 
    If bIncludeSubfolders Then
        'Build collection of additional subfolders.
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop
        'Call function recursively for each subfolder.
        For Each vFolderName In colFolders
            Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
        Next vFolderName
    End If
 
Exit_Handler:
 
    Exit Function
 
Err_Handler:
    strSQL = "INSERT INTO zzLibScan " _
    & " (MediaFileName, MediaPath) " _
    & " SELECT ""  ~~~ ERROR ~~~""" _
    & ", """ & strFolder & """;"
    CurrentDb.Execute strSQL
 
    Resume Exit_Handler
End Function
 
Public Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0& Then
        If Right(varIn, 1&) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

What do you think?

[DonRayner]

Airtech, sorry I'm so slow getting back to you, I was away over the weekend. I'll take a look at this later today and see what's up with.

[DonRayner]

Airtech, can you post the actual line of code that you are using to call this function.