value for record at EOF

[muyBN]

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

 

[Cindy Meister]

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

 

[muyBN]

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 OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet
OLEDBatabase Lo", _
SQLStatement:="SELECT 'ID' FROM '

' order by 'ID';",
SQLStatement1:="", SubType:=wdMergeSubTypeAccess

.DataSource.ActiveRecord = wdLastRecord

'more code necessary?

intID = .DataSource.DataFields("ID").Value
End With
End If

 

[Cindy Meister]

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

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 OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet
OLEDBatabase Lo", _
SQLStatement:="SELECT 'ID' FROM '

' order by 'ID';",
SQLStatement1:="", SubType:=wdMergeSubTypeAccess

.DataSource.ActiveRecord = wdLastRecord

'more code necessary?

intID = .DataSource.DataFields("ID").Value
End With
End If

--
Bryan

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

 

[muyBN]

What I finally ended up doing was creating a query with the records sorted by
the ID. By using the query as the data source now displays the number ID when
I use wdLastRecord.
--
Bryan

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

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 OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet
OLEDBatabase Lo", _
SQLStatement:="SELECT 'ID' FROM '

' order by 'ID';",
SQLStatement1:="", SubType:=wdMergeSubTypeAccess

.DataSource.ActiveRecord = wdLastRecord

'more code necessary?

intID = .DataSource.DataFields("ID").Value
End With
End If

--
Bryan

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


:

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