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

[timmg]

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

[charlotte]

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.

[timmg]

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

[charlotte]

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.

[timmg]

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

[patt]

I think Charlotte meant dbOpenDynaset not dbOpenDynaser.
Pat

[charlotte]

<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>

[patt]

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

[MarkD]

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

[charlotte]

<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>