Solved

Create a dynamic array in Access VBA and perform actions on it

Posted on 2010-09-09
5
820 Views
Last Modified: 2013-11-27
I would like help performing the following actions in VBA:

1. Create a dynamic array with two columns. Let’s say column A and column B

2. Add a row to the array from the current values of two variables: ProjectID and CStr(frm.Hwnd) to columns A and B respectively.

3. Search the array and retrieve the value of column B based lookup of column A

4. Delete a row in the array that has a specific value in column A

Thank you for any help on this,
Tim
0
Comment
Question by:TimJar
5 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 33636632
A Dynamic Array is one that is re-dimmed as needed:

Dim arr() as String

'/set multiple columns
Redim Preserve arr(ubound(arr)+1, 1)

arr(0, ubound(arr)) = Me.ProjectID
arr(1, ubound(arr)) = frm.Hwnd

To retrieve this:

Msgbox arr(0, <your array index>)

If you need to locate that value, use a Loop:

Dim i As Integer

For i = 0 to ubound(arr,2)
  Msgbox arr(0, i) & " : " & arr(1, i)
Next i

This might help:
http://patorjk.com/programming/tutorials/vbarrays.htm
0
 
LVL 4

Expert Comment

by:r0bertdenir0
ID: 33637709
From your description - you are not just storing but searching & deleting.
A collection might be a better choice for your problem.
Maybe you should tell us a bit more about what you're trying to do...
 
0
 

Author Comment

by:TimJar
ID: 33639663
I am using Allen Browne's method of opening multiple instances of a form (frmProjects). The problem is if I click a link to open an instance of frmProject, and a form with that ProjectID is already open, it will open another instance and I have two forms open referencing the same ProjectID. I would like to keep a log of ProjectID and the corrusponding frm.Hwnd so I can check if a instance is already open and if so set focus to that open form instead of opening a new one.

If I store the data in a table it would have two fields: ProjectID and Hwnd. Then, before I open a new instance, I will dlookup("Hwnd","tableName","ProjectID=" & "ProjectID I'm trying to open") to see if I should go to that form based on the frm.Hwnd or open a new one.

I would have to append to this table when I open a new instance and delete a record when I close an instance.

It seems to me it would be better to use an array for this instead of a table. I haven't thought about how to use a collection
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 33639938
Sometimes it is useful to write data to a temp table, so it can examined in the interface.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 33639945
If you are going to use DLookup, I think you will need to save the data to a table.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question