Re: Array Help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.


"Steve Sanford" <limbim53 at yahoo dot com> wrote in message
news:D4251726-EE2F-444B-B33E-CAC5758461A9@xxxxxxxxxxxxxxxx
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: Array Help
    ... Dim strSQL As String ... the Access "INSERT INTO" SQL query is: ... one field is changed for each query, I was thinking that I could loop though ... I am not sure how to do a loop through the array or write any code for it as ...
    (microsoft.public.access.modulesdaovba)
  • 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: transfering all MySQL rows to an array
    ... mysql function to move all the data obtained in a query to an ... Just consider that any function is using a loop to read the entire result set, ...
    (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)