Desperately need help with Array!

[Guest]

Hi,

I have never worked with Arrays and I need some help. I found some
suggestions from other posted questions and I am trying to make their code
work for what I am trying to do.

I have a Site table with 205 records - one record for each of our sites
(multiple fields each record). Supervisors are each responsible for multiple
sites. For each Supervisor, I would like to export their site(s) information
to separate tabs in an Excel workbook where they will add current month's
metric data. Once the Supervisors have updated the workbook, I will need to
upload the Metrics into the database. Is the best way to accomplish this?
So far, I cannot compile my code as it keeps telling me that 'Method or Data
Member not found' and highlights the line with (vSupID = rst.SupId). SupId
is a text field and is the PK in tblSupervisor.

Please Help! Any advice would be greatly appreciated.
My code is below.

Arlene

Option Compare Database
Option Explicit
Public vSupID As String

Public Sub Export_Metrics()
Dim rst As DAO.Recordset
Dim vWorkbook As String
Dim vPath As String
Dim vFileName As String

vFileName = Format(Date, "mmmm_yyyy") & "_Metrics.xls"
vPath = "\\chcwp03fs\fo_common$\Database\Metrics\"
vWorkbook = vPath & vFileName

Set rst = CurrentDb.OpenRecordset("tblSupervisor", dbOpenDynaset)
Do Until rst.EOF
vSupID = rst.SupId
Debug.Print SupId
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryExportMetrics, vWorkbook, , vSupID
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Public Function Selected_SupId()
Selected_SupId = vSupID
End Function

 

[Brendan Reynolds]

You can't refer to fields of a recordset using dot syntax (rst.SupID) in
recent versions of DAO (you could in DAO 2.x, but that was back in the
Access 2 timeframe). You need to use either bang syntax (rst!SupID) or
quotes - rst("SupID") or rst.Fields("SupID"). When you use dot syntax, VBA
thinks you're trying to use a property of method of the Recordset object,
hence the error message.

 

[Brendan Reynolds]

BTW: I don't see any array in this code.

 

[Bas Cost Budde]

Instead of

rst.SupID

write

rst!SupID

 

[Guest]

Brendan,

You are right about the array. I was working on two different approaches to
the solution and didn't know if using an array was a better choice. I
corrected my code and it works now, but which would be the the best way of
doing something like this?

Thanks to all of you for your help with this. It really is great that we
have you guys to turn to when we get stuck on something and can't figure out
what to do!

 

[Brendan Reynolds]

I'm glad that I was able to help with the code. I couldn't comment on
whether this is the best method or not, but with only a couple of hundred
records to deal with, you're unlikely to see much difference between one
method and another anyway.