-
DOES ANYONE HAVE ANY SUGGESTIONS?? I'M DESPERATE!!
I have a database like the one below (only with a lot more records). For each unique contact, I need to be able to pull the unique funds and unique programs which that person has. For example, I need to be able to pull the data and paste it to a worksheet column such that Jim Smith will show Funds 110, 250, and 900 and Programs 101 and 169. Same for the other "Contacts." I figure an array is the best way to do this but I am not that familiar with arrays. This also needs to be dynamic...the names in the Contact column could change, and the number of contacts could change as well. Any thoughts? Am using VBA (Excel).
Grant # Fund Program Contact
102365 110 169 Jim Smith
123645 250 169 Jim Smith
123697 110 183 Kate Johnson
265971 600 179 Bill Brown
265985 250 183 Kate Johnson
316495 250 160 Bill Brown
369855 110 236 Bill Brown
412365 250 236 Bill Brown
659784 900 101 Jim Smith
698531 110 119 Kate Johnson
965231 900 102 Kate Johnson
-
U can use the Type statement to define your own data structure:
Code:
Option Explicit
Private Type FUNDTYPE
Grant As Long
Fund As Integer
Program As Integer
Contact As String
End Type
Dim DynamicTable() As FUNDTYPE
Private Sub Form_Load()
'0 TO 100 -> This could be any value
ReDim DynamicTable(0 To 100) As FUNDTYPE
'Fill first record in table...
DynamicTable(0).Grant = 102365
DynamicTable(0).Fund = 110
DynamicTable(0).Program = 169
DynamicTable(0).Contact = "Jim Smith"
'Perform some actions...
End Sub
Private Sub Form_Unload(Cancel As Integer)
Erase DynamicTable
End Sub
-
Re: FUNDTYPE
Thanks for responding...With the code you showed me, does this just fill the first (0) index? Is there a way to automatically fill the indexes with the data I already have? There is probably about 1000 records that I want to do calculations, etc on. Can I assign each record automatically to an index? (I'm new with arrays!)
-
Yes, the sample only fills the first record, record 0.
The ReDim statement tells VB to reserve 101 records, numbered from 0,1,2,...,98,99,100 in memory.
You can also write your own Form that could contain the max number of records allowed, and some Textboxes to fill in each record.
Just modify the index and assign the values like:
Code:
'Ix is some integer variable...
DynamicTable(Ix).Fund = 104
Arrays are very fast, but consider that you don't have any possibility to search thru the table.
There are well-known algorithms to sort tables (quick-sort, bubble-sort, binary sort), even as there are algorithms to search (binary search in sorted tables).
The most effective use of arrays is the implementation of a binary tree, or some B+ tree. But this is VERY difficult to explain.
Try always to use DAO, to use MS Access RDBMS coded databases. This allow you to easily add, change, delete and lookup records in any table...
