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
Offline
Last seen: 5 years 7 months ago
Joined: 06/05/2018 - 12:16
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
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