Array Help

[Stephen Lynch]

Instead of adding 15 docmd.runsql statements that use the same query except
one field is changed for each query, I was thinking that I could loop though
the query and add a variable for the field name instead.

I am not sure how to do a loop through the array or write any code for it as
this is the first time I have encountered a need for an array, so I thought
I would try.

For Each

F1 = Array("Deferral","Match","Roth","SafeMatch","PS","MPP","SafePS")

INSERT INTO tblContributions ( Deferral )
SELECT F1 FROM tblContributionsPostAll;

Next

Back to the books for me.Thanks in advance

 

[pietlinden]

Stephen,

what's the structure of the table you are appending to? Could you
explain why you need to do this? It seems like a really roundabout
way of doing something that should be simple. It could well be that I
am misunderstanding your need, but could you explain further?

At any rate, yes, you could append all this to your table or fields in
code... If all the values go into separate records, you would do
something like this:

Are you saying that you have a column for each value in your array?
Wouldn't a different structure be better? Something like:

tblContributions(
ContributionID LONG PRIMARY KEY,
PersonID LONG IS NOT NULL,
ContributionType VARCHAR(15) IS NOT NULL)

Then you can add as many contribution types as you want?

Anyway, to answer your question...

What value are you inserting in your append query? Just all the
values from "F1"? Where does "F1" come from? If it's from a query,
just turn that into an append query and Access will do the append for
you.

Otherwise, you could add the records manually.

If you wanted to do this to learn something, you could do something
like...

Option Compare Database
Option Explicit

Private Sub AddContributionTypes_Click()

Dim rs As DAO.Recordset
Dim astrTypes(1 To 7) As String

astrTypes(1) = "Deferral"
astrTypes(2) = "Match"
astrTypes(3) = "Roth"
astrTypes(4) = "SafeMatch"
astrTypes(5) = "PS"
astrTypes(6) = "MPP"
astrTypes(7) = "SafePS"

'tblContributionTypes is a table I assume to be in your database
with a field called "ContributionType" which is a text field.

Set rs = DBEngine(0)(0).OpenRecordset("tblContributionTypes",
dbOpenTable, dbAppendOnly)
For intCounter = 1 To 7
rs.AddNew
rs.Fields("ContributionType") = astrTypes(intCounter)
rs.Update
Next intCounter

rs.Close
Set rs = Nothing

End Sub

If you wanted all the values to go into separate fields in the same
record, you would do something like this:

rs.AddNew

For intCounter = 1 To 7

rs.Fields(i) = astrTypes(intCounter)

Next intCounter

rs.update

HTH,
Pieter

 

[Steve Sanford]

Stephen,

I am with Pieter in that I don't understand what you are trying to do. But I
have a little different take on what you posted. Maybe this will help:

(watch for line wrap) !!!!! mostly untested code !!!!

'-----beg code-------------------------------------
Option Compare Database
Option Explicit

Private Sub AddContributionTypes_Click()

On Error GoTo Err_Handler

Dim arrFields(1 To 7) As String
Dim i As Integer
Dim strSQL As String

'Array("Deferral", "Match", "Roth", "SafeMatch", "PS", "MPP", "SafePS")
arrFields(1) = "Deferral"
arrFields(2) = "Match"
arrFields(3) = "Roth"
arrFields(4) = "SafeMatch"
arrFields(5) = "PS"
arrFields(6) = "MPP"
arrFields(7) = "SafePS"

'loop thru array
For i = 1 To UBound(arrFields)

'For multiple records gathered from other tables or external sources
'For multiple records, the Access "INSERT INTO" SQL query is:
'
'Insert Into TableName (FieldName1, FieldName2) _
'Select FieldName1, FieldName2 From TableName;

strSQL = "INSERT INTO tblContributions (" & arrFields(i) & ")"
strSQL = strSQL & " SELECT " & arrFields(i)
strSQL = strSQL & " FROM tblContributionsPostAll;"

'MsgBox strSQL
CurrentDb.Execute strSQL, dbFailOnError
Next i


Err_Exit:
Exit Sub

Err_Handler:
MsgBox "ERROR: " & Err.Number & " - " & Err.Description
Resume Err_Exit

End Sub

'------end code------------------------------------
HTH

 

[Stephen Lynch]

Guys;

Thanks for the replies. I am executing the journaling of funds to a
participants retirement account and I need to show seperate journals instead
of adding them all together as a seperate sum. My original Table looks like
similar to this:

EmployeeID 12345
PayDate 07/15/08
Deferral 50.00
Match 12.65
PS 0
SafeMatch 0
SafePS 0


What I am trying to do is send them to a temporary table that looks like
this:

EmployeeID JournalAmount
12345 50.00
12345 12.65



Where there can be multiple amounts for each employeeID. I then export it as
a text file and upload it. I am completely rebuilding my db in 07 now and
the way that I did it in the past was a series of append queries for each
contribution source. Hope this helps.

TIA

Steve

 

[Stephen Lynch]

Steve & Piet:

I used your code and it worked. Thanks as I attempt to learn about arrays.

If you know of a better way let me know.