DropList issue
Forums
Hello to all,
New user here, and thoroughly enjoy this site! It has helped me learn this software better than anywhere else I have found.
I am currently having an issue with a script that I am developing. I have added a DropListBox and at the moment, it is for fiscal quarters, so even though I am selecting 'Q4' from the array, the box is still displaying 'Q1'. Any help would be greatly appreciated! Here is a slimmed down version of my current script:
Option Explicit
Dim filename1 As String
Dim filename2 As String
Dim FYQTR As String
Dim VER As String
Dim working_directory As String
Dim exitScript As Boolean
Sub Main
working_directory = Client.WorkingDirectory
Call QTRtesting()
If Not exitScript Then
Call Testing
End If
client.refreshFileExplorer
End Sub
Function QTRtesting()
Dim dlg As dlgQuarterly
Dim button As Integer
Dim filebar1 As Object
Dim filebar2 As Object
Dim exitDialog As Boolean
Dim source As Object
Dim table As Object
Dim fields As Integer
Dim i, j As Integer
Dim field As Object
Dim FYQuarters(4) As String
Dim VER(2) As String
Do
button = Dialog(dlg)
Select Case button
Case -1 'ok button
If dlg.DropListBox1 > -1 Then
VER = ListBox1$(VER())
Else
VER = ""
End If
If dlg.DropListBox2 > -1 Then
FYQTR = ListBox2$(FYQuarters())
Else
FYQTR = ""
End If
If validateQTRtesting() Then exitDialog = TRUE
Case 0 ' cancel button
exitDialog = TRUE
exitScript = TRUE
Case 1 'filename1 select button
Set filebar1 = CreateObject("ideaex.fileexplorer")
filebar1.displaydialog
filename1 = filebar1.selectedfile
Case 2 'filename2 select button
Set filebar2 = CreateObject("ideaex.fileexplorer")
filebar2.displaydialog
filename2 = filebar2.selectedfile
End Select
Loop While exitDialog = FALSE
Set source = Nothing
Set table = Nothing
Set field = Nothing
End Function
Function DisplayIt(ControlID$, Action%, SuppValue%)
Dim dlg As dlgQuarterly
Dim VER(2) As String
VER(1) = "V.1"
VER(2) = "V.2"
Dim FYQuarters(4) As String
FYQuarters(1) = "Qtr 1"
FYQuarters(2) = "Qtr 2"
FYQuarters(3) = "Qtr 3"
FYQuarters(4) = "Qtr 4"
DlgListBoxArray "DropListBox1", VER()
DlgListBoxArray "DropListBox2", FYQuarters()
If filename1 = "" Then
DlgText "txtFilename1", "No file selected"
Else
DlgText "txtFilename1", "File: " & getFileName(filename1, 0)
End If
If filename2 = "" Then
DlgText "txtFilename2", "No file selected"
Else
DlgText "txtFilename2", "File: " & getFileName(filename2, 0)
End If
End Function
Thanks Brian! I have made the
Thanks Brian! I have made the suggested changes, and everything runs fine, but I am having the same issue with the droplistbox not displaying the proper selection. Also, when I run it, file naming that should include sVer and sFYQTR show blank " ". Updated version:
Option Explicit
Dim filename1 As String
Dim filename2 As String
Dim sFYQTR As String
Dim sVer As String
Dim FYQuarters(4) As String
Dim VER(2) As String
Dim working_directory As String
Dim exitScript As Boolean
Sub Main
working_directory = Client.WorkingDirectory
Call QTRtesting()
If Not exitScript Then
Call Testing
End If
client.refreshFileExplorer
End Sub
Function QTRtesting()
Dim dlg As dlgQuarterly
Dim button As Integer
Dim filebar1 As Object
Dim filebar2 As Object
Dim exitDialog As Boolean
Dim source As Object
Dim table As Object
Dim fields As Integer
Dim i, j As Integer
Dim field As Object
Do
button = Dialog(dlg)
Select Case button
Case -1 'ok button
If dlg.DropListBox1 > -1 Then
sVer = VER(dlg.DropListBox1)
Else
sVer = ""
End If
If dlg.DropListBox2 > -1 Then
sFYQTR = FYQuarters(dlg.DropListBox2)
Else
sFYQTR = ""
End If
If validateQTRtesting() Then exitDialog = TRUE
Case 0 ' cancel button
exitDialog = TRUE
exitScript = TRUE
Case 1 'filename1 select button
Set filebar1 = CreateObject("ideaex.fileexplorer")
filebar1.displaydialog
filename1 = filebar1.selectedfile
Case 2 'filename2 select button
Set filebar2 = CreateObject("ideaex.fileexplorer")
filebar2.displaydialog
filename2 = filebar2.selectedfile
End Select
Loop While exitDialog = FALSE
Set source = Nothing
Set table = Nothing
Set field = Nothing
End Function
Function validateQTRtesting() As Boolean
validateQTRtesting = TRUE
If filename1 = "" Then
MsgBox "Please select a file", MB_ICONEXCLAMATION, "Error"
validateQTRtesting = FALSE
End If
If filename2 = "" Then
MsgBox "Please select a file", MB_ICONEXCLAMATION, "Error"
validateQTRtesting = FALSE
End If
End Function
Function DisplayIt(ControlID$, Action%, SuppValue%)
Dim dlg As dlgQuarterly
Dim VER(2) As String
VER(1) = "V.1"
VER(2) = "V.2"
Dim FYQuarters(4) As String
FYQuarters(1) = "Qtr 1"
FYQuarters(2) = "Qtr 2"
FYQuarters(3) = "Qtr 3"
FYQuarters(4) = "Qtr 4"
DlgListBoxArray "DropListBox1", VER()
DlgListBoxArray "DropListBox2", FYQuarters()
If filename1 = "" Then
DlgText "txtFilename1", "No file selected"
Else
DlgText "txtFilename1", "File: " & getFileName(filename1, 0)
End If
If filename2 = "" Then
DlgText "txtFilename2", "No file selected"
Else
DlgText "txtFilename2", "File: " & getFileName(filename2, 0)
End If
End Function
I also tried changing:
If dlg.DropListBox2 > -1 Then
sFYQTR = FYQuarters(dlg.DropListBox2)
Else
sFYQTR = ""
End If
to...
sFYQTR = FYQuarters(dlg.DropListBox2)
but no luck there either. Thanks again for all your help.
Kyle
Hi Kyle, any chance you can
Hi Kyle, any chance you can send this to my email address as it will be easier to check out. You can email it to brian.element@ideascripting.com
Thanks
Brian
Hi Brian, I am in the process
Hi Brian, I am in the process of updating a few scripts and was looking to add a dropdown, that i want populated from the document i have selected in my script. I have attempted this, but have had not success to date. I was hoping that you would be able to point me in the right direction? Thanks for all the great help.
Kyle
Hi Kyle, here is an example
Hi Kyle, here is an example of a script with one drop down. The script will see if you have a file already open, if so it will populate the drop-down with the fields from that file. You can also select another file.
Let me know if you have any questions or need some clarrification on what I was doing. I have also attached the file.
Thanks
Brian
Dim listbox1$() AS STRING
Begin Dialog NewDialog 50,49,270,150,"Drop Down Demo", .DisplayIt
Text 9,6,15,14, "File:", .Text1
Text 34,5,179,15, "Text", .Text2
PushButton 225,5,10,10, "...", .PushButton1
GroupBox 30,1,188,23, .GroupBox1
OKButton 20,67,40,14, "OK", .OKButton1
CancelButton 77,67,40,14, "Cancel", .CancelButton1
DropListBox 13,32,202,10, listbox1$(), .DropListBox1
End Dialog
Option Explicit
Dim sFilename As String 'to hold the filename
Dim sFields() As String 'to hold the field names
Dim working_directory As String
Dim bExitScript As Boolean 'flag in case user hits cancel, the script will be exited
Sub Main
working_directory = Client.WorkingDirectory()
Call menu()
End Sub
Function menu()
Dim button As Integer
Dim db As database
Dim dlg As NewDialog
On Error Resume Next
Set db = Client.CurrentDatabase()
If err.number = 0 Then
sFilename = db.name
Else
sFilename = ""
End If
Set db = Nothing
button = Dialog(dlg)
End Function
Function displayIt(ControlID$, Action%, SuppValue%)
Dim bExitFun As Boolean
Select Case Action%
Case 1
If sFilename <> "" Then 'if the file is open then get the fields
Call getFields() 'get the fields
DlgListBoxArray "DropListBox1", sFields() 'add the fields to the drop down
End If
Case 2
Select Case ControlId$
Case "PushButton1"
sFilename = getFile()
If sFilename <> "" Then 'if the file is open then get the fields
Call getFields() 'get the fields
DlgListBoxArray "DropListBox1", sFields() 'add the fields to the drop down
End If
Case "OKButton1"
bExitFun = True
Case "CancelButton1"
bExitFun = True
End Select
End Select
If sFilename = "" Then
DlgText "Text2", "Please select a file"
Else
DlgText "Text2", getFileName(sFilename, 0)
End If
If bExitFun Then
displayIt = 0
Else
displayIt = 1
End If
End Function
'gets the fields from the selected database
Function getFields()
Dim db As database
Dim table As table
Dim field As field
Dim i As Integer
Dim count As Integer
Set db = Client.OpenDatabase(sFilename)
Set table = db.TableDef
count = table.Count
ReDim sFields(count)
For i = 1 To count
Set field = table.GetFieldAt(i)
sFields(i) = field.name
Set field = Nothing
Next i
Set table = Nothing
Set db = Nothing
Call sortArray(sFields())
End Function
'****************************************************************************************************
' Name: sortArray
' Description: Routine to sort an array
' Last Update:
' Accepts: A one dimensional array
' Returns: Same array sorted
'****************************************************************************************************
Private Function sortArray(MyArray() As String)
Dim lLoop, lLoop2 As Integer
Dim str1, str2 As String
For lLoop = 1 To UBound(MyArray)
For lLoop2 = lLoop To UBound(MyArray)
If UCase(MyArray(lLoop2)) < UCase(MyArray(lLoop)) Then
str1 = MyArray(lLoop)
str2 = MyArray(lLoop2)
MyArray(lLoop) = str2
MyArray(lLoop2) = str1
End If
Next lLoop2
Next lLoop
MyArray(0) = "Select one"
End Function
Function getFileName(temp_filename As String, temp_type As Boolean) '1 if get the name with any folder info, 0 if only the name
Dim temp_length As Integer
Dim temp_len_wd As Integer
Dim temp_difference As Integer
Dim temp_char As String
Dim tempfilename As String
If temp_type Then
temp_len_wd = Len(working_directory ) + 1'get the lenght of the working directory
temp_length = Len(temp_filename) 'get the lenght of the file along with the working directory
temp_difference = temp_length - temp_len_wd + 1'get the lenght of just the filename
getFileName = Mid(temp_filename, temp_len_wd, temp_difference)
Else
temp_length = Len(temp_filename )
Do
temp_char = Mid(temp_filename, temp_length , 1)
temp_length = temp_length - 1
If temp_char <> "\" Then
tempfilename = temp_char & tempfilename
End If
Loop Until temp_char = "\" Or temp_length = 0
getFileName = tempfilename
End If
End Function
Function getFile()
Dim filebar As Object
Set filebar =CreateObject ("ideaex.FileExplorer")
' Display the File Explorer
filebar.DisplayDialog
' Set Variable to store Select File Name
getFile = filebar.SelectedFile
End Function
Hi Brian, this was a ton of
Hi Brian, this was a ton of help! I have successfully gotten my dropdowns to work properly within my menu window, I believe, but I am not sure, as my script is not yet fully functioning properly. I believe I have an issue with either: 1) Attaching each dropdown to a constant, or 2) my use of the constant values within the equations in my calculation area of the script. I have attached the .iss file. Any help with my errors would be greatly appreciated, I've given it a number of attempts but I am still learning. Thanks again!
Hi Kyle,
Hi Kyle,
Glad that what I posted was helpful. I looked at your script and made a few changes / additions to it. I added some code that it now picks-up if a file is already open. I also fixed your problem of items not being returned when you select the drop-down, basically you needed to be using the sFields() variable. I commented all the lines that I changed / added so you can see what I did.
Good luck with your project.
Brian
Hi Kyle and welcome to the
Hi Kyle and welcome to the site, glad you are enjoying it and learning stuff.
You will need to make a few changes to your script. Right now you have your arrays that hold the drop down information named the same as your variables that hold your selection. So I would change them after the Option Explicit:
Dim FYQTR as String to Dim sFYQTR as string
Dim VER as String to Dim sVer as String
Just to keep the variables separate.
I would also move your arrays from the functions to a global array, the reason being is that you need them in both the QTRTesting and the DisplayIt functions.
So under Option Explicit add:
Dim FYQuarters(4) As String
Dim VER(2) As String
And remove them from the functions.
The next change is obtaining the contents of the dialog as you are trying to get the info from the ListBox1$ and ListBox2$ but you are not using those arrays as you have created your own to hold the information. So change the following
VER = ListBox1$(VER()) to sVER = VER(dlg.DropListBox1) and
FYQTR = ListBox2$(FYQuarters()) to sFYQTR = FYQuarters(dlg.DropListBox2)
I think with those changes that will get the script working.
Let me know how it goes.
Brian