TimJar
asked on
Create a dynamic array in Access VBA and perform actions on it
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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" ,"ProjectI D=" & "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
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"
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
Sometimes it is useful to write data to a temp table, so it can examined in the interface.
If you are going to use DLookup, I think you will need to save the data to a table.
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...