Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2010-09-09
5
Medium Priority
?
860 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

578 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