Way of getting records from rows into the array for the dialog box

Pawelski_33

Forums

Dear all,
I am working on the code to get the unique values from one of my field into the array but the values are in rows of the database. For getting the field names (column names) I would use table.TableDef and then table.Count to the get number of field and then create the array to get the acutal names but I am a bit confused when i need to get the values from rows rather than columns. I was thinking about recordsset object but I am not sure that is the best approach.
My code looks like below (it runs initially summarize function to get the unique values form the field i am interested in and then follow wiht recordset set up):
'Summarize FS Category field (column) to get the unique list of the FS Category items (Cash,Debtors, Revenue etc.)
                                Set  TargetDb = MasterDb
FieldToSumm = 1
                'Reset the size of the Array
             ReDim Arr_FieldsToSummarize(FieldToSumm)
             Arr_FieldsToSummarize(0) = UserChoice(3)
 
             'No field to total in this summarization
             FieldToTot = 0
 
'Set TargetDb = Client.CurrentDatabase
                Call PerformSummarizeOnly(TargetDb,"FS Category List",                           Arr_FieldsToSummarize(),Arr_FieldsToSummarizeTot(), FieldToSumm ,FieldToTot)                
 
'Set the database and recordset
                                Set TargetDb = Client.CurrentDatabase
                                Set RecSet = TargetDb.Recordset
                                    
                                'Get the number of rows in the summarized FS Category column
                                'RecordsinSourceFile = RecSet.Count +1
                               ' Dim List_of_Records(RecordsinSourceFile)
                                MsgBox RecordsinSourceFile
       'Set Recs_In_FS_Category = MasterDb.RecordSet
                                 'List_of_Records(0) = "N/A"    
                  'For i = 1 To (RecordsinSourceFile-1)
                                
'List_of_Records(i) =tab
                
                ' Next i
 
Does anyone have a sample of similar code that works for data in rows and creates of array of the values to be displayed in the dialog box please?
 
 

Pawelski_33 Tue, 08/07/2018 - 11:38

I found the answer to my question - below is the snippet of my code (just in case anyone is interested):

Global RecordsinSourceFile As String
Global UserChoiceRecords(3) As String
Global MasterDb As Object
Global UserChoice(4) As String
Global dlgSecond As DLG2
Global MasterDb As Object

Sub Second_DialogBox

Dim Arr_FieldsToSummarize() As String
Dim Arr_FieldsToSummarizeTot() As String
Dim FieldToSumm As Integer
Dim FieldToTot As Integer
Dim TargetDb As Object
Dim i,a As Integer
Dim ActiveDatabase As String

'Summarize FS Category field (column) to get the unique list of the FS Category items (Cash,Debtors, Revenue etc.)
Set TargetDb = MasterDb
FieldToSumm = 1
'Reset the size of the Array
ReDim Arr_FieldsToSummarize(FieldToSumm)
Arr_FieldsToSummarize(0) = UserChoice(3)

'No field to total in this summarization
FieldToTot = 0

'Set TargetDb = Client.CurrentDatabase
Call PerformSummarizeOnly(TargetDb,"FS Category List", Arr_FieldsToSummarize(),Arr_FieldsToSummarizeTot(), FieldToSumm ,FieldToTot)

'Set the database and recordset
Set TargetDb = Client.CurrentDatabase
ActiveDatabase = Client.CurrentDatabase.Name
Set rs = TargetDb.RecordSet

'Get the number of rows in the summarized FS Category column
rs.ToFirst
rs.Next
RecordsinSourceFile = rs.Count +1

'On Error GoTo EndofFunction

ReDim List_of_Records(RecordsinSourceFile)

'Define first element of the array of elements in dropdown list in second dialog box
List_of_Records(0) = "N/A"

'Defining remaining elements of the array of elements for the drop down menu in 2nd dialog
For i = 1 To (RecordsinSourceFile-1)
rs.GetAt(i)
Set CurrentRec = rs.ActiveRecord
List_of_Records(i) =CurrentRec.GetCharValue(UserChoice(3))
'MsgBox List_of_Records(i)
Next i

'ACTIVATING SECOND DIALOG BOX
Res = Dialog (dlgSecond)

If Res = 0 Then

'Closing FS Category List database
Client.CloseDatabase ActiveDatabase
MsgBox "FS Category Analysis cancelled"

Exit Sub
Else
'Set up array of elements within dropdown list in second dialog box
UserChoiceRecords(0) = List_of_Records(DLG2.DropListBox1)
UserChoiceRecords(1) = List_of_Records(DLG2.DropListBox2)
UserChoiceRecords(2) = List_of_Records(DLG2.DropListBox3)

End If

'Closing FS Category List database
Client.CloseDatabase ActiveDatabase

Call Main_Function_Caller (MasterDb)

MsgBox "This is the end of this procedure"

End If

End Sub


 
Having said that I am still interested to hear from the users who were able to utilise 
Record.GetCharValueAt 
method as i wasnt able to get it to work properly.
 
Thanks 

Brian Element Tue, 08/07/2018 - 16:33

Hi Pawelski_33,

Thanks for sharing the code.  I was busy today but I would have done something similar, to access records you need to access the RecordSet and Record object which is what you did.

Brian