Userform with dropdown listboxes

Hi Dennismes,

I will put something together for you.  It will probably take a few days as I am currently not at home.

Thanks

Brian

Hi Dennis,

Sorry for taking so long to get back to you.  Here is an example script where you select the file, then you select the field and then you select a value from within that field.  Hopefully this will help get you started.  I have the script below plus I have attached it as a file.

Dim listbox1$() AS string
Dim listbox2$() AS string

Begin Dialog NewDialog 49,48,362,204,"NewDialog", .DisplayIt
  Text 5,5,40,14, "Select File:", .Text1
  Text 53,5,252,19, "Text", .Text2
  GroupBox 51,1,257,27, .GroupBox1
  PushButton 313,8,10,10, "...", .PushButton1
  Text 6,35,40,10, "Select Field:", .Text3
  DropListBox 6,50,299,11, listbox1$(), .DropListBox1
  Text 5,68,77,10, "Select Item from Field:", .Text4
  DropListBox 7,85,297,11, listbox2$(), .DropListBox2
  OKButton 19,109,40,14, "OK", .OKButton1
  CancelButton 76,108,40,14, "Cancel", .CancelButton1
  Text 16,135,298,43, "Text"
End Dialog

Option Explicit
Dim sFilename As String
Dim sFieldname As String
Dim sFieldItem As String
Dim bExitScript As Boolean

Sub Main
	Dim message As String
	Call mainMenu()
	message = "You have selected the following items:" & Chr(13) & Chr(10)
	message = message & "File: " & sFilename & Chr(13) & Chr(10)
	message = message & "Field: " & sFieldname & Chr(13) & Chr(10)
	message = message & "Item: " & sFieldItem & Chr(13) & Chr(10)
	MsgBox message
End Sub

Function mainMenu()
	Dim dlg As NewDialog
	Dim button As Integer
	On Error Resume Next
	'check to see if a file is already open and if so use it for the file
	sFilename = Client.CurrentDatabase.Name
	button = Dialog(dlg)
	If button = 0 Then bExitScript = True 'selected the x in the window to close
End Function

Function DisplayIt(ControlID$, Action%, SuppValue%)
	Dim bExitFunction As Boolean
	
	Select Case Action%
		Case 1 
			If sFilename <> "" Then
				'get the list of fields
				Call getFields()
				'attached the field list to the first drop down box
				DlgListBoxArray "DropListBox1", Listbox1$()
			End If
		Case 2
			Select Case ControlID$
				Case "PushButton1"
					'select the file
					Call getFile()
					If sFilename <> "" Then 'if the filename is not blank then get the list of fields and attach it.
						Call getFields()
						DlgListBoxArray "DropListBox1", Listbox1$()
					End If
				Case "DropListBox1" 'called if the user selects or changes a field
					sFieldname = listbox1$(SuppValue%) 'get the field name
					Call getValues(sFieldname) 'populate the second drop list with the list of value
					DlgListBoxArray "DropListBox2", Listbox2$()
				Case "CancelButton1"
					bExitScript = True
					bExitFunction = True
					
				Case "OKButton1"
					'make sure everything has been selected before exiting the dialog
					sFieldname = Listbox1$(NewDialog.DropListBox1)
					sFieldItem = Listbox2$(NewDialog.DropListBox2 + 1)
					If sFilename = "" Then
						MsgBox "Please select a file"
					ElseIf sFieldname = "Select Field" Then
						MsgBox "Please select a field"
					ElseIf sFieldItem = "" Then
						MsgBox "Please select a value"
					Else
						bExitFunction = True
					End If
			End Select
	End Select
	
	If bExitFunction Then
		DisplayIt = 0
	Else
		DisplayIt = 1
	End If
	
	If sFilename = "" Then
		DlgText "Text2", "Please select a file"
	Else
		DlgText "Text2", iSplit(sFilename, "", "\", 1, 1)
	End If
	
	'DlgText "Text5", "Action: " & Action% & " ControlID: " & ControlID$ & " SuppValue: " & SuppValue%
End Function

'get the list of fields and populate the listbox1 with the list of fields
Function getFields()
	Dim db As database
	Dim table As table
	Dim field As field
	Dim i As Integer
	
	Set db = client.OpenDatabase(sFilename)
		Set table = db.TableDef
			ReDim listbox1$(table.count)
			For i = 1 To table.count
				Set field = table.GetFieldAt(i)
					listbox1$(i) = field.name
			Next i
			Set field = Nothing
		Set table = Nothing
		db.close
	Set db = Nothing
	Call sortArray(listbox1)
End Function

'select the file from the file explorer
Function getFile()
	Dim obj As Object
	Set obj = client.CommonDialogs
		sFilename = obj.FileExplorer()
	Set obj = Nothing
End Function

'sort the field array
Private Function sortArray(MyArray() As String)
	Dim lLoop, lLoop2 As Integer
	Dim str1, str2 As String
	For lLoop = 0 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 Field"
End Function

'get the list of values in the field
Function getValues(sField As String)
	Dim db As database
	Dim task As task
	Dim dbName As String
	Dim table As table
	Dim field As field
	Dim rs As RecordSet
	Dim rec As Record
	Dim sFieldType As String
	Dim i As Long
	'first perform a summarization on the field
	Set db = Client.OpenDatabase(sFilename)
		Set task = db.Summarization
			task.AddFieldToSummarize sField 
			dbName = client.uniqueFilename("Sum on " & sField)
			task.OutputDBName = dbName
			task.CreatePercentField = FALSE
			task.PerformTask
		Set task = Nothing
		db.close
	Set db = Nothing
	'get the field type, this is necessary to get the information out of the field
	Set db = Client.OpenDatabase(dbName)
		Set table = db.TableDef
			Set field = table.GetField(sField)
				If field.IsCharacter Then
					sFieldType = "Char"
				ElseIf field.IsDate Then
					sFieldType = "Date"
				ElseIf field.IsNumeric Then
					sFieldType = "Num"
				ElseIf field.IsTime Then
					sFieldType = "Time"
				Else
					sFieldType = "unkown"
				End If
			Set field = Nothing
		Set table = Nothing
	Set db = Nothing
	'get the values from the summary field
	Set db = Client.OpenDatabase(dbName)
		ReDim listbox2$(db.count)
		Set rs = db.RecordSet
			rs.AddKey sField, "A"
			rs.ToFirst
			For i = 1 To db.count
				rs.next
				Set rec = rs.ActiveRecord
				If sFieldType = "Char" Then
					listbox2$(i) = rec.GetCharValue(sField)
				ElseIf sFieldType = "Num" Then
					listbox2$(i) = rec.GetNumValue(sField)
				ElseIf sFieldType = "Date" Then
					listbox2$(i) = rec.GetDateValue(sField)	
				ElseIf sFieldType = "Time" Then
					listbox2$(i) = rec.GetTimeValue(sField)	
				End If
			Next i
			Set rec = Nothing
		Set rs = Nothing
	Set db = Nothing
End Function

Great! Thank you so much for your trouble! I'll get to work on it 

Hello Brian,
I've been consulting my Mastering IDEAScript book, which is woefully lacking in information about customized dialog boxes, and this thread seems to be the most appropriate for my needs. I have written a script that allows users to establish materiality thresholds for a variable they select. The script then exports those results to a new table. Right now I'm using two simple Input Boxes but I'd like to insert a more user-proof and nicer-looking Dialog with a Drop Down Combo Box and Edit Box instead.
My idea is that once the dialog launches, it will populate the drop down with only those table variables that are numeric so the users don't have to wade through a large list of selections. Then they can type the materiality threshold into an Edit Box. Attached is my current code, and a template of what I'm going for with the dialog. Any suggestions for adding in a custom dialog would be much appreciated.
Thank you,
Marie
 
 

Hi Marie,

It sounds like you are on the right track.  For the drop-down I don't quite understand where the variables are coming from, are you talking about the numeric fields?  

You might want to watch this video as it should help you out https://www.youtube.com/watch?v=KpaJCx72OO8

 
well, I would like to know what could be causing me this syntax error, Thank you

A couple of things for you to check.  The previous line looks like there is a quote at the beginning of it, that would make it a comment and cause an error but it should be in green, so I am not sure what is going on in that line.

In your menu do you have a Drop List Box with the ID DropListBox1?

The array listBox1$(), where is that defined?  Is it defined as part of the Drop List Box or was it defined outside of the dialog?

Might be one of the above that is causing the error.