RE: Array Help

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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Stephen Lynch" wrote:

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



.

Relevant Pages

  • Re: Need Help With Pivot/Cross-Tab Query Please/ **Dates Are Not Fixed**
    ... I was able to get the pivot-like query to work. ... and loop in the QUERY's SELECT statement that returns the "AS Day1, ... Used another CF array and loop to get the column headers to print the ... variables when outputting the query results to a report, ...
    (microsoft.public.sqlserver.programming)
  • Re: Complicated while loop for a simpleton
    ... That chunk of code makes my head spin. ... connection, and the query result. ... first query in an array, then loop through that array with foreach, and ... make sub-queries inside that loop. ...
    (comp.lang.php)
  • Re: Export multiple txt files from one table based on unique values in one field
    ... Dean, it might be easiest to do this by opening a recordset that gives you the list of products to export, and then exporting a file for each one. ... Replace "Query1" below with the name of your saved query. ... 'Loop through distinct products, exporting each. ... Within loop set criteria of Select Query to array ...
    (microsoft.public.access.modulesdaovba)
  • Re: [PHP] Similar to yesterday
    ... if $id_support returns multiple records as an array and $sql_card is a ... query that looks for records in a table based on $id_support, ... then do a similar loop with $id_traffic. ...
    (php.general)
  • Re: Form fields array
    ... You create an array, but you never put any values in it, so aText ... you're going to have is a bunch of textboxes with numeric names that you ... > not know how many records the query will pull up. ... > ' I loop through the query here to count how many records and put the ...
    (microsoft.public.inetserver.asp.db)
Loading