We help IT Professionals succeed at work.

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

TimJar
TimJar asked
on
916 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
Comment
Watch Question

Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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...
 

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2009

Commented:
Sometimes it is useful to write data to a temp table, so it can examined in the interface.
CERTIFIED EXPERT
Top Expert 2009

Commented:
If you are going to use DLookup, I think you will need to save the data to a table.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.