Acc2K - Read Array into a New Table

[MindBender77]

Hello All,
I'm not very familiar using arrays and found myself getting more confused with each reference source I read. I have an array that I am trying to read into a new table or a temp table.
Here is the function so far:

Function SplitMemo()
Dim astrMemo,memo
Dim i as integer
memo = Dlookup("[Ordernotes]","tbl_test1")
astrmemo = split(memo,"\")

For i = 0 to UBound(astrMemo)
Debug.print astrMemo(i)
Next

'I assume appending the array to a table begins here
End Function

Any help is most appreciated,
Bender

[ADezii]

Your details are a little sketchy, but I'll provide you with a Generic Template for populating a Table with Values from an Array. Let's assume you have an Array of Strings consisting of 6 Last Names, and that you wanted to populate a Table named tblNames with these Values into a Field named [Last]:

Dim astrLastNames(1 To 6) As String
Dim intCounter As Integer
Dim strSQL As String

astrLastNames(1) = "Flintstone"
astrLastNames(2) = "Wilson"
astrLastNames(3) = "Johnson"
astrLastNames(4) = "Munster"
astrLastNames(5) = "Reveree"
astrLastNames(6) = "McGettigan"

For intCounter = LBound(astrLastNames) To UBound(astrLastNames)
  strSQL = "INSERT INTO tblNames ([Last]) Values ('" & _
  astrLastNames(intCounter) & "');"
  CurrentDb.Execute strSQL, dbFailOnError
Next

[MindBender77]

My apologies for the lack of details and I do thank you for the clarifications. However, I do have a further question concerning Array Usage. Using your example as reference, say that the astrLastNames are populated from a temp table's memo field. What if it is not known that astrLastName(1) = "Flintstone " only that it is the first line delimited from the memo field? Would your template be coded as such?:

dim memo as string
astrMemo as string
dim astrLastNames(1 to 6) as string

memo = dlookup("[Field1]","Table1","[Field1] = some criteria")
astrMemo = Split(memo,"\")

astrLastNames(1) = astrMemo(1) ' Is this possible?
'More code here.....

Bender

Dim astrLastNames(1 To 6) As String
Dim intCounter As Integer
Dim strSQL As String

astrLastNames(1) = "Flintstone"
astrLastNames(2) = "Wilson"
astrLastNames(3) = "Johnson"
astrLastNames(4) = "Munster"
astrLastNames(5) = "Reveree"
astrLastNames(6) = "McGettigan"

[ADezii]

My apologies for the lack of details and I do thank you for the clarifications. However, I do have a further question concerning Array Usage. Using your example as reference, say that the astrLastNames are populated from a temp table's memo field. What if it is not known that astrLastName(1) = "Flintstone " only that it is the first line delimited from the memo field? Would your template be coded as such?:

dim memo as string
astrMemo as string
dim astrLastNames(1 to 6) as string

memo = dlookup("[Field1]","Table1","[Field1] = some criteria")
astrMemo = Split(memo,"\")

astrLastNames(1) = astrMemo(1) ' Is this possible?
'More code here.....

Bender

Given your unique circumstances and a "\" Delimiter, the syntax would be something like:

Dim strMemo As String
Dim varMemo As Variant
Dim intCounter As Integer
Dim strSQL As String

strMemo = "First\Second\Third\Fourth\Fifith\Sixth"

varMemo = Split(strMemo, "\")

For intCounter = LBound(varMemo) To UBound(varMemo)
  strSQL = "INSERT INTO tblNames ([Last]) Values ('" & _
           varMemo(intCounter) & "');"
  CurrentDb.Execute strSQL, dbFailOnError
Next

[MindBender77]

Thank you for your assistance, ADezii. With some minor tweaking, things work perfectly.

Thanks Again,
Bender

[ADezii]

Thank you for your assistance, ADezii. With some minor tweaking, things work perfectly.

Thanks Again,
Bender

You are quite welcome.