UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Vba 7.1 Accessing Values In An Array, Access 2016    
 
   
bmcellis
post Apr 15 2019, 01:47 PM
Post#1



Posts: 30
Joined: 28-December 10



Feeling like a beginner dealing with arrays. I have a table that is a list of file names and column names because we're dealing with shared data and sometimes formats are correct and sometimes they're not.
The first row of a linked XL file is the file name and the rest of the rows are the column names that are supposed to be there in the format they are supposed to have. I want to read the entire column into an array to access later. The code works without errors and the count of records is correct, but when I run this the output array only shows me the first record value. It doesn't seem to increment to get the rest of the column into the array.


Option Compare Database

Private Sub CheckForamt_Click()

Dim RST1 As DAO.Recordset
Dim RST2 As DAO.Recordset
Dim RST3 As DAO.Recordset

Dim MyPath As String
Dim MySQL1 As String
Dim MySQL2 As String

Dim RecordCount As Integer
Dim FieldCount As Integer

Dim I As Integer

MyPath = Me.FilePath & "\"

MySQL1 = "select '" & Me.File1 & "' from tbl_FileNames"

Set RST1 = CurrentDb.OpenRecordset(MySQL1, dbOpenDynaset)
RST1.MoveLast
RecordCount = RST1.RecordCount
ReDim Array1(RecordCount) As Variant

'This part seems to be working with no errors
RST1.MoveFirst
I = 0
Do Until RST1.EOF
Array1(I) = RST1.Fields(0)
I = I + 1
RST1.MoveNext
Loop

''This part runs with no errors but I always get the first record in the column showing up on the screen 11 times and never see any of the other values.
'The number of times is correct but it should be showing me a file name and a whole list of column names.
RST1.MoveFirst
I = 0
Do Until RST1.EOF
MsgBox Array1(I)
RST1.MoveNext
I = I + 1
Loop

MsgBox "done"
RST1.Close
End Sub
Go to the top of the page
 
theDBguy
post Apr 15 2019, 02:02 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,039
Joined: 19-June 07
From: SunnySandyEggo


Hi. I think maybe because you populated the entire array with the first column's value only when you did this:
CODE
Array1(I) = RST1.Fields(0)
Try doing it this way:
CODE
Array1(I) = RST1.Fields(I)

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Apr 15 2019, 02:13 PM
Post#3



Posts: 5,877
Joined: 11-November 10
From: SoCal, USA


Option Compare Database
Option Explicit

this is not related to your array question, but i recommend adding the above statement to every module in your database. Option Explicit requires declaration of every variable used in the module. it's very useful for debuggin; i'd consider it a best practice. to automatically add the statement to every new module, you can...well, i don't really know how to find it in A2016. in older versions, you would open any module, then choose Tools | Options | and then checkmark the box next to Require Variable Declaration. maybe that will be enough to help you find it in A2016.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
bmcellis
post Apr 15 2019, 02:14 PM
Post#4



Posts: 30
Joined: 28-December 10



The select statement pulls only one column. There will only ever be one column for the array (single dimensional array).

Thank you though.
Go to the top of the page
 
theDBguy
post Apr 15 2019, 02:20 PM
Post#5


Access Wiki and Forums Moderator
Posts: 75,039
Joined: 19-June 07
From: SunnySandyEggo


Sorry, I guess I misunderstood the intent. I thought you wanted to put multiple columns in an array.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ADezii
post Apr 15 2019, 03:46 PM
Post#6



Posts: 2,264
Joined: 4-February 07
From: USA, Florida, Delray Beach


You could try a different approach, as in (NOT TESTED):
CODE
Dim I As Integer
Dim RST1 As DAO.Recordset
Dim Array1() As Variant
Dim MySQL1 As String
Dim MyPath As String

MyPath = Me.FilePath & "\"

MySQL1 = "select '" & Me.File1 & "' from tbl_FileNames"

Set RST1 = CurrentDb.OpenRecordset(MySQL1, dbOpenDynaset)

With RST1
  .MoveLast: .MoveFirst
   RecordCount = .RecordCount
     If RecordCount = 0 Then Exit Sub
    
   ReDim Array1(1 To RecordCount)
    
   For I = LBound(Array1) To UBound(Array1)
     Array1(I) = .Fields(0)
       .MoveNext
   Loop
End With

'Initiate Playback of Array
For I = LBound(Array1) To UBound(Array1)
  Debug.Print Array1(I)
Loop

RST1.Close
Set RST1 = Nothing
Go to the top of the page
 
projecttoday
post Apr 15 2019, 04:10 PM
Post#7


UtterAccess VIP
Posts: 10,697
Joined: 10-February 04
From: South Charleston, WV


Looks to me like that second loop should work, but why are you looping through the recordset and not the array (as shown in ADezii's code)? Have you stepped through both loops? How did you determine the first one is working?

For's are terminated with Next, not Loop, I believe.


--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th April 2019 - 11:50 PM