First Time Array User. Young, blonde, GSOH.....

[Petemush]

I have a procedure which whips through a recordset and selects the email addresses of employees according to certain conditions, blah blah blah, that is all fine and dandy.

At the moment, if the email address isn't stored then the procedure just doesn't add it. What I would like to do is make a list of the records that don't get their email address added due to the fact they're missing, and then display this to the user somehow.

I thought I could use an array to store the names of the employees although never having used arrays in VBA I 've used them in other languages and assumed it'd be similar.

I tried setting the dimension of the array to the .recordcount but got an error message saying constant expression required so I gave up on that approach.

Then I thought I could set the dimensions to be just

(1 To 1,1 To 2)

and then increase the dimensions everytime something is added in the manner of

(1 To 2,1 To 2)
(1 To 3,1 To 2)
(1 To 4,1 To 2)

etc etc using the Redim statement.

but now I get a subscript out of range error(number 9 error no less!Anyone know what number 1 is?) on the Redim statement line and I can't see why.

I'm now beginning to wonder whether I need an Array at all and haven't yet even thought how I'm going to display all this data to the user.

Sooooooooooo, anyone know why this is happening? The code's below:

Dim notSet(),i as integer

Set db = CurrentDb
Set qdf = db.QueryDefs("qryMassEmail&quot
i = 1

'Set the three parameters in the query.
qdf![Forms!frmMassEmail!CCode] = Forms![frmMassEmail]![CCode]
qdf![Forms!frmMassEmail!CoDate] = Nz(Forms![frmMassEmail]![CoDate], "*&quot
qdf![Forms!frmMassEmail!Status] = Nz(Forms![frmMassEmail]![Status], "*&quot

Set rs = qdf.OpenRecordset()

With rs
Do While Not rs.EOF

'If this is the first email address...
If Len(sText) = 0 Then

'...If an Email Address does exist and it's not equal to "Not Set"...
If Len(![EMail Address]) And Not (![EMail Address] = "Not Set&quot Then

'...add the first address to the string.
sText = ![EMail Address]
Else
ReDim Preserve notSet(1 to i,1 to 2)
notSet(i, 1) = ![Forename]
notSet(i, 2) = ![Surname]
i = i + 1
End If

'Otherwise this is not the first email address to be added.
Else

If Len(![EMail Address]) And Not (![EMail Address] = "Not Set&quot Then

'Add the new address on to the end of the sText.
sText = sText & "; " & ![EMail Address]
Else
ReDim Preserve notSet(1 to i,1 to 2)
notSet(i, 1) = ![Forename]
notSet(i, 2) = ![Surname]
i = i + 1
End If
End If

'Move to the next record in the recordset.
.MoveNext
Loop
End With

Cheers for any help,

Pete

 

[jebry]

Hi Pete!

The problem is that VBA (and VB) allow you to leave only one dimension dynamic. Therefore you need to declare the array like this:

Dim NotIn(,1 to 2)

Now you should be able to dynamically control the size of the first dimension.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[dpimental]

A Simpler approach might be the better solution, but would require a greater degree of detail.

What are you adding email addresses from and what are you adding them to.

How is the procedure run and when?

My suggestion would be to test whether the email address exists and / or whether it is set.

Then do what you want based on results.

I would need more information to help further.

Do users open up a "User" profile.
IF so, then you could use the Email Address exists or set to write a message to the screen to ask them to supply an email address.

Can you give me some more information I'm Your Huckleberry!

 

[bkclaw113]

In my opinion you are heading down the wrong track trying to use an array here. I have not found an easy way to display information store in an array, it normally take 1 to to intermediate processing steps before you get suitable output.
My suggestion would be to have a table to house your email address and create a second recordset object to update the email info to the new table. This way you can use a listbox or form to display the addresses after they have been stored.

The additional code would look something like this:

dim rs2 as recordset
Set rs2 = db.OpenRecordset("Email_Add_Tbl_Name&quot
...
rs2.AddNew
rs2.[fld1] = ![Forename]
rs2.[fld2] = ![Surname]
rs2.Update
...
rs2.close
set rs2.nothing

If you use this where you are trying to store the infomation into the array you will end up with everything in your new table, and then you will have a much easier time outputing the information as well.

 

[Petemush]

Cheers Jeff,

Fraid it doesn't work though, just get a Expected Expression as soon as I type it in. Tried it without the comma but then got Array already dimensioned error at the first redim statement. Any ideas?

dpimental,

This is all part of a mass emailing procedure.

A user wants to email a number of employees.

All these employees are related to a certain training course.

The employees may have completed the course,be booked on it or just need it.

The user selects the training course and if they want to,a status(completed/booked on/needed) and a course date.

My code then bases a recordset on a query which returns all these related employees names and email addresses.

It then cycles through the recordset and picks out the email addresses into a string that is sent to outlook.

If an email address doesn't exist or is not set then the code skips that employee and carries on.

I want to be able to record the employees that are skipped and warn the user that they did not get emailed since they have no email address stored.

I thought an array could store this information. Don't know if there's a better way, as you can see from the code above, every time an employee is found who doesn't have an email address, they are added to the array.

Still can't get this to work tho,even with the help of jeff, appreciate anything anyone comes up with!

Cheers,

Pete

 

[Petemush]

Sorted the problem

if you use the Preserve keyword you can only change the last dimension of the array. Just swapped storage around and all is well.

Just got to figure out how to get the data to the user now!

Cheers,

Pete

 

[jebry]

Hi Pete!

Another possibility is to store the same information in an invisible text box, separated by semicolons. After the process is complete you can pop up a form with a list box in it and the row source type set to Value List. In the open event of the form, you can set the row source to the string in the invisible text box. This will cause the list box to display the list of users without email. If a printed list is required you may need to try to work something out on a report.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[dpimental]

I don't know if you need an array to accomplish this.

I'm not sure how the data is sent to OutLook; but what you could do is, instead of just skipping those that don't have an email address, you add them to a temp variable.

You can do this a number of ways.
Here is one way.

Dim temp as String
temp = ""

If IsNull(EmailAddress) Then
temp = temp & EmployeeName & vbCrLf
End If

At the end of the procedure you can open a pop up form with a memo text box, setting the textbox recordsource to this variable.

You could put a print button on the popup form to allow the user to print the names.

When the form is closed the variable is set to null.

I'm Your Huckleberry!

 

[Petemush]

Thanks for everyones help,

Seems like I don't need an array after all.

I think I'm gonna with dpimental's suggestion, similar to jeff's suggestion about popping up a form at the end.

Thanks again

Cheers,

Pete

 

[scking]

The short code below is a start to using arrays. Whether it is the correct design or not could be discussed. You could put as many elements as you want in the type statement and the newly dimensioned mNewArray will store the elements as you desire. You can also embed other arrays as elements in the array so it could show 1 -> many type relationships in the data. Create a GetArrayData function to return the information as you wish. You can also use an array to populate ListBoxes if you decide that is they best design to use.

Type NewArray
Val1 As String
Val2 As Integer
End Type
Dim mNewArray As NewArray

Sub AddArrayElement(Val1 As String, Val2 As Integer)

Dim intUBound As Integer

On Error GoTo HandleErr

intUBound = UBound(mNewArray) + 1
ReDim Preserve mNewArray(intUBound)
mNewArray.Val1 = Val1
mNewArray.Val2 = Val2

Exit_Proc:
Exit Sub

HandleErr:
Select Case Err.Number
Case 9
intUBound = 0
Resume Next
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
End Select
Resume Exit_Proc
End Sub
----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------