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.
--
Brendan Reynolds (MVP)
"swedbera" <(E-Mail Removed)> wrote in message
news:E2C38C9E-73F5-4EE9-ACDE-(E-Mail Removed)...
> 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" wrote:
>
>> BTW: I don't see any array in this code.
>>
>> --
>> Brendan Reynolds (MVP)
>>
>> "Brendan Reynolds" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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 (MVP)
>> >
>> >
>> > "swedbera" <(E-Mail Removed)> wrote in message
>> > news:644F6537-C87D-47B4-B43C-(E-Mail Removed)...
>> >> 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
>> >>
>> >
>> >
>>
>>
>>
|
Similar Threads
