Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    Chicago, Illinois, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting index in recordset from linked table (A97-2002)

    I have a class module that does some account reconciliation and billing. I've recently split the db, and the following code no longer works:

    Public Function GenAR(strEntID As String)
    'Generates an organizational invoice based on a specified Org ID. Can be used stand alone
    'or within the BillActivityList() function
    Dim dbInvoice As Database
    Dim rsTraining As Recordset, rsConsulting As Recordset
    Dim rsOrgInvoice As Recordset, rsCrsList As Recordset, strTrngSQL As String
    Dim strCntCSQL As String, strInvoiceNum As String, bolIsMemb As Boolean
    Dim strCurOrgID As String
    Dim dblAmtDue As Double, intEntLeft As Integer, dblEntLeftValue As Double
    Dim intEntPDue As Integer, dblThisCrsDiscount As Double, intPntsUsed As Integer
    Dim dblInvDiscnt As Double, strMembSQL As String, rsMembership As Recordset
    Dim strUpDtMembSQL As String

    strMembSQL = "SELECT [Org Membership].[Org ID], [Org Membership].[Renew Date], [Org Membership].[Mem Status], "
    strMembSQL = strMembSQL & "[Org Membership].[Remaining Points] FROM [Org Membership] "
    strMembSQL = strMembSQL & "WHERE ((([Org Membership].[Renew Date]) >= #" & Str(dtEndDate - intDateOffset) & "#))"
    strMembSQL = strMembSQL & " ORDER BY [Org Membership].[Org ID], [Org Membership].[Renew Date];"

    'Open the general databases and recordsets
    Set dbInvoice = CurrentDb

    Set rsOrgInvoice = dbInvoice.OpenRecordset("Org Invoice", dbOpenTable)
    Set rsMembership = dbInvoice.OpenRecordset(strMembSQL)
    Set rsCrsList = dbInvoice.OpenRecordset("Crs List")
    rsCrsList.Index = "PrimaryKey"

    'Initialize list of Org IDs with billable activity
    rsOrgInvoice.Index = "PrimaryKey"
    rsOrgInvoice.MoveLast ' Get last invoice number


    The .index is no longer supported on this type of object.

    What's the best way to fix this? I suppose I could declare a workspace for the linked db, but would executing as an SQL statement do as well?

    sigh

    thanks,

    Tim

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting index in recordset from linked table (A97-2002)

    I'm not sure why you're trying to set the Primary Key index in the first place. You already have an Order By in your SQL, so what's the purpose? But try changing from dbOpenTable to dbOpenDynaset since you're now working with linked tables.
    Charlotte

  3. #3
    New Lounger
    Join Date
    May 2002
    Location
    Chicago, Illinois, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting index in recordset from linked table (A97-2002)

    some of the tables are opened via SQL, but some are opened directly.

    I suppose that this is really a style question - is it better to open a table through the dbTable option or just do it with an SQL statement.

    It seems, that for linked tables at least, that SQL is the better way to go.

    tim

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting index in recordset from linked table (A97-2002)

    You have to use dbOpenDynaser (or one of the other non-table) types for linked tables, unless possibly you're opening it directly in a separate workspace.
    Charlotte

  5. #5
    New Lounger
    Join Date
    May 2002
    Location
    Chicago, Illinois, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting index in recordset from linked table (A97-2002)

    dbOpenDynaser isn't available in A97. I did try it in the AXP version, and it worked - but then I had a crash on a null value. Not enough time to slog through the data, but it appears to be a promising fix, overall.

    Still, I may well just change the recordset from an open table to an SQL statement in order to make it a little more robust.

    thanks, great suggestion.

    tim

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting index in recordset from linked table (A97-2002)

    I think Charlotte meant dbOpenDynaset not dbOpenDynaser.
    Pat

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting index in recordset from linked table (A97-2002)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Right. I had a kitten on my lap "helping" when I typed that. <img src=/S/woops.gif border=0 alt=woops width=58 height=36>
    Charlotte

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting index in recordset from linked table (A97-2002)

    They can be just so helpful can't they !!
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Setting index in recordset from linked table (A97-2002)

    ...blame it on the kitty..... <img src=/S/catty.gif border=0 alt=catty width=19 height=18>
    Attached Images Attached Images

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting index in recordset from linked table (A97-2002)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> Yep! that looks like mine all right ... except mine is just a blur when he's in forward gear! Anyone want a hyperactive kitten? Just kidding of course. He's clawed his way into my heart. <img src=/S/ouch.gif border=0 alt=ouch width=15 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •