RE: value for record at EOF by Cindy-M
Cindy-M
Wed Jun 28 03:21:03 CDT 2006
Since I'm not familiar with your data source, there may be something I'm
missing in the scenario, but the kinds of things I'd check if this were
happening to me
1. Is ID what you see as the field name in the merge field if you press
Alt+F9? (Word can change field names if they contain spaces or something it
doesn't like. Unlikely in this case, but when trouble-shooting...)
2. If you use other index values instead of wdLastRecord, such as
wdFirstRecord, 1, 3, and 5, do you get any different result? Or is it always
20?
3. It's possible that the result of the Sort Order isn't what you're
expecting, keep that in mind when doing (2)
4. As this is 2002 or 2003: Has any kind of selection been made in the
Recipients dialog box?
--
-- Cindy
"muyBN" wrote:
> Thanks, Cindy. What you have suggested was one of the bees in my bonnet that
> kept me up all night. I just now tried it again to make sure I wasn't
> attempting a blurry-eyed bumble last night (no relation to bees, mind you).
> Unfortunately, the value of the ID still displays as 20 (the quantity of
> records) instead of 24 (the value of the ID field of record #20, which is
> what I'm after). Could there be anything else I need to do?
>
> The only other thing I can think of to do, at least on an interim basis, is
> to open a new document, insert the field for the last record, and pick up the
> variable value from there; but I would rather do it "programatically"--which
> just seems a "cleaner" way of doing it and more educational besides (after
> all, I am a college computer instructor--but not in databases yet, mind you).
>
> I'll list my complete code below in case there's anything you can see amiss.
> It's macro code that I recorded. Again, I bet there's a "cleaner" way to do
> this with a regular SQL statement, so if you have a suggested SQL statement
> (which I didn't have success with either) in lieu of the macro code, I'd
> appreciate that as well.
>
> If intID = 0 Then
> With ActiveDocument.MailMerge
> .OpenDataSource Name:="[path\DBname]", ConfirmConversions:=False,
> ReadOnly:=False, LinkToSource:=True, _
> AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
> WritePasswordDocument:="", WritePasswordTemplate:="", _
> Revert:=False, Format:=wdOpenFormatAuto,
> Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin; " & _
> "Data Source=[path\DBname];Mode=Read;Extended Properties="""";Jet
> OLEDB:System database="""";Jet OLEDB:Registry Path="""";" & _
> "Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet
> OLEDB:Database Lo", _
> SQLStatement:="SELECT 'ID' FROM '[table]' order by 'ID';",
> SQLStatement1:="", SubType:=wdMergeSubTypeAccess
>
> .DataSource.ActiveRecord = wdLastRecord
>
> 'more code necessary?
>
> intID = .DataSource.DataFields("ID").Value
> End With
> End If
>
> --
> Bryan
>
>
> "Cindy Meister" wrote:
>
> > Hi Brian
> >
> > You first have to literally "page" to the record in question in order to get
> > the value for a field. Do this, first
> >
> > ActiveDocument.MailMerge.DataSource.ActiveRecord = wdLastRecord
> > --
> > -- Cindy
> >
> >
> > "muyBN" wrote:
> >
> > > (Using Word 2003)
> > >
> > > I have 20 records in a database and the ID of the 20th record is 24.
> > > Whenever I attempt to extract the ID# of the last record, it only shows the
> > > count (in other words, returns 20 instead of 24), using this code:
> > >
> > > jbID = ActiveDocument.MailMerge.DataSource.DataFields("jbID").Value
> > >
> > > If someone can show me an example of what should go before this so as to
> > > return the value of the last record, I'd appreciate it. I've tried different
> > > things with recordsets and EOF but I keep getting errors, even after setting
> > > up all the references for ADO and DAO. I'm connecting to the database since
> > > it's returning the record count, but I can't seem to pull up the actual value
> > > of the ID field in the last record.
> > >
> > > Many thanks in advance for your help.
> > > --
> > > Bryan