Recordsets and Bookmarks

[Scafidel]

I am in the process of duplicating and dividing my HUGE single-tabled form
into a main form with several tables, queries, and subforms. Currently the
form merges with a Word template using different bookmarks, i.e., Person1,
Person2, Person3, Clause1, Clause2, Clause3, etc., as needed. How do I now
name and bookmark these recordsets from the subforms in the VBA code and Word
to complete the merge as before?

 

[Arvin Meyer [MVP]]

Try importing into a single Access table adding an autonumber Primary key
after the importation. Now run a succession of queries to move the data to
the additional tables:

SELECT tblMyData.* INTO tblNewData
FROM tblMyData
WHERE (((tblMyData.Person3) Is Not Null));

When you are satisfied that the proper data has been retrieved, delete the
column Person3 in the main table (tblMyData) and any columns which describe
only Person3 data. Also delete those columns in the new table (tblNewData)
that don't pertain to Person3. Continue this with Person2 and Person1. If
Clause is not an attribute of the main table or the new table, it will also
need it's own table, etc. ... etc.

 

[Peter Hibbs]

If you are using Access 2003 you could try using the Excel to Access
Converter Utility program at :-

http://www.rogersaccesslibrary.com/...me='Excel to Access Converter Utility program'

which should make it easier.

HTH

Peter Hibbs.

 

[Scafidel]

I have already been able to copy/move the records, mostly copying to Excel,
arranging and copying to an Access table. The problem is that now what was
Person1, Person2, Person3, appearing in three fields of the form and
consequently three bookmarks, now all appear in one recordset of the subform.
Simply, how are parts of a subform recordset "named" for separate bookmarks?
Thanks
--
Scafidel
Lafayette, Louisiana

Try importing into a single Access table adding an autonumber Primary key
after the importation. Now run a succession of queries to move the data to
the additional tables:

SELECT tblMyData.* INTO tblNewData
FROM tblMyData
WHERE (((tblMyData.Person3) Is Not Null));

When you are satisfied that the proper data has been retrieved, delete the
column Person3 in the main table (tblMyData) and any columns which describe
only Person3 data. Also delete those columns in the new table (tblNewData)
that don't pertain to Person3. Continue this with Person2 and Person1. If
Clause is not an attribute of the main table or the new table, it will also
need it's own table, etc. ... etc.

 

[Arvin Meyer [MVP]]

I'm not absolutely sure what you are asking, because a bookmark in Access is
a specific place (row) in a recordset, not the same as a Word bookmark. Data
that looks like this in Excel:

CompanyID Person1 Person2 Person3

should look like this in Access:

CompanyID Person1
CompanyID Person2
CompanyID Person3
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have already been able to copy/move the records, mostly copying to Excel,
arranging and copying to an Access table. The problem is that now what
was
Person1, Person2, Person3, appearing in three fields of the form and
consequently three bookmarks, now all appear in one recordset of the
subform.
Simply, how are parts of a subform recordset "named" for separate
bookmarks?
Thanks

 

[Scafidel]

With the one table, each field appeared as a single record and in a single
text box of the form, so I was able to reference each field by name/bookmark
and merge to Word using this code (in part).
.ActiveDocument.Bookmarks("Person1").Select
.Selection.Text = (CStr(Forms!TAB!Person1))
.ActiveDocument.Bookmarks("Person2").Select
.Selection.Text = (CStr(Forms!TAB!Person2))
Now, a subform has replaced 3 text boxes and my Person1, Person2, Person3
all appear under one field of a subform, Person, but in different records. I
know this to be a recordset, but I don't know how to reference the three
different records in Code and Word Bookmarks.
Thanks

 

[David W. Fenton]

With the one table, each field appeared as a single record and in
a single text box of the form, so I was able to reference each
field by name/bookmark and merge to Word using this code (in
part).
.ActiveDocument.Bookmarks("Person1").Select
.Selection.Text = (CStr(Forms!TAB!Person1))
.ActiveDocument.Bookmarks("Person2").Select
.Selection.Text = (CStr(Forms!TAB!Person2))
Now, a subform has replaced 3 text boxes and my Person1, Person2,
Person3 all appear under one field of a subform, Person, but in
different records. I know this to be a recordset, but I don't
know how to reference the three different records in Code and Word
Bookmarks. Thanks

You need to navigate from one record to the next. If it's in a form,
you can use the DoCmd.RunCommand acCmdRecordsGoToNext. If it's a
recordset, then you'd just issue the .MoveNext command.

None of this has anything to do with Bookmarks in Access.