Creating and Using an array with VBA

I have create a table that looks like this and I have made a form – the unbound field is where I want to put the department name that is not on the table. I will set up an array to accomplish this. First I click on the form and bring it up in Selection Type: Form and then I click on the On Load event.

Note that I first Dim the DeptName to be able to be accessed with indexes or subscripts or pointers up through 4. Then in the Form Load which was the result of the On Load event associated with the form, I give the first DeptName(1) a value of “Hardware”, the second DeptName(2) a value of Software etc.

What this means is that I have one name DeptName that refers to the names of all departments. I set up 4 of them. If I put the index of one after DeptName I get the first one in the list. This is an array.

Now I am going to go to the button and add a on click event to see the department name.

Note that the Dim of DeptName was done up with the Option Compare Database which mean that this memory variable was defined as global which meant that it can be seen in all of the subroutines. Note that I defined Ptr inside the subroutine called cmdGetDeptName_Click. This means it is local and can only be seen in this subroutine. That works fine for Ptr, but I wanted to refer to DeptName in both subroutines so I had to give it a global definition.

I wanted to make sure that txtDeptNo was numeric so I used Val which assures me it is a number around the name – it is the Val function and the name of the field goes in the parenthesis. The IF statement is checking to see if the txtDeptNo contains a number that is > 0 and < 5 – in other words, a valid number. If it does then I move that number to the Ptr and use the Ptr to tell what DeptName I want to put in txtDeptName. If the number in txtDeptNo is not valid, then I move the Invalid Dept Number message to the box.

Note that when I worked with this I encountered a message that said some things were not enabled and I had to enable to have this work.