Word XP and Access XP 
Author Message
 Word XP and Access XP

I have a Word XP UserForm that the user inputs information into which fills
in the document form fields.  Once the UserForm is complete, some of the
information is passed to an Access Database as a new RecordSet, and fills in
the New RecordSet ID Number at the top of the document.

The code I have for this works fine importing data in to the main table of
the database.

My problem is that I have four sub tables to this main table that I need to
add information that is part of this same New RecordSet.
Two of the tables are just checkbox information.

How should I add the New RecordSet information to the sub tables?

Do I need to close and open the connection for each table, or is there a
better way (Join Query or something else)?

How can I automatically add the current user name into a field in the main
table of the
database when the New RecordSet is created?

Here is the working code that I have so far, please help.
Thank you.

Private Sub CommandButton1_Click()
' declare new connection, recordset and variables
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim MyNumber
Dim vContactFN, vContactLN, vConcernFN, vConcernLN, vConcernRelationship,
vGender, vFollowUp, vRFSID As String

' provide connection string for data using Jet Provider for Access Database
vConnection.ConnectionString = "data source=p:\public folder\access\it
administrative db\request for services spreadsheet.mdb;" & _
    "Provider=Microsoft.Jet.OLEDB.4.0;"
' open connection
vConnection.Open

' set variables from text entered into bookmarks
vContactFN = ActiveDocument.FormFields("bkContactFN").Result
vConcernFN = ActiveDocument.FormFields("bkConcernFN").Result
vConcernRelationship = ActiveDocument.FormFields("bkConcernR").Result
vGender = ActiveDocument.FormFields("bkConcernGender").Result

 ' open a new version of the temporary RecordSet accessing the Request For
Service db
    vRecordSet.Open "SELECT* FROM RFSMain", vConnection, adOpenKeyset,
adLockOptimistic
    vRecordSet.AddNew

    ' only set variables with some data entered into the field
    If vContactFN <> "" Then vRecordSet!CPerson = vContactFN
    If vConcernFN <> "" Then vRecordSet!PersonC = vConcernFN
    If vConcernRelationship <> "" Then vRecordSet!Relationship =
vConcernRelationship
    If vGender <> "" Then vRecordSet!Gender = vGender

    ' update the new RecorSet to DB
    vRecordSet.Update
    vRecordSet.MoveLast

    MyNumber = vRecordSet(0) '<<retrive new number

    ActiveDocument.FormFields("bkRFSID").Result = MyNumber

    ' close object
    vRecordSet.Close
    vConnection.Close

    ' clear object to free up memory
    Set vRecordSet = Nothing
    Set vConnection = Nothing

 UserForm8.Hide
 Unload Me

 UserForm7.Show

End Sub



Wed, 21 Sep 2005 05:53:44 GMT  
 Word XP and Access XP
Quote:
> Do I need to close and open the connection for each table, or is there a
> better way (Join Query or something else)?

Depends on yr datamodell.

If you have generated a Main ID for the Main table prior to adding
new records, you can design a Join query in which the Main ID
is entered in yr subtable(s)

If y're using AutoIncrement fields as ID fields, the ID value
will not be available until the recordset is updated.
You wud force Jet to throw in an error message telling you
invalid use of NULL on the subtable (it's related Main ID field)
In this case, you wud indeed need to open up a seperate recordset
for each subtable.

Krgrds,
Perry



Quote:
> I have a Word XP UserForm that the user inputs information into which
fills
> in the document form fields.  Once the UserForm is complete, some of the
> information is passed to an Access Database as a new RecordSet, and fills
in
> the New RecordSet ID Number at the top of the document.

> The code I have for this works fine importing data in to the main table of
> the database.

> My problem is that I have four sub tables to this main table that I need
to
> add information that is part of this same New RecordSet.
> Two of the tables are just checkbox information.

> How should I add the New RecordSet information to the sub tables?

> Do I need to close and open the connection for each table, or is there a
> better way (Join Query or something else)?

> How can I automatically add the current user name into a field in the main
> table of the
> database when the New RecordSet is created?

> Here is the working code that I have so far, please help.
> Thank you.

> Private Sub CommandButton1_Click()
> ' declare new connection, recordset and variables
> Dim vConnection As New ADODB.Connection
> Dim vRecordSet As New ADODB.Recordset
> Dim MyNumber
> Dim vContactFN, vContactLN, vConcernFN, vConcernLN, vConcernRelationship,
> vGender, vFollowUp, vRFSID As String

> ' provide connection string for data using Jet Provider for Access
Database
> vConnection.ConnectionString = "data source=p:\public folder\access\it
> administrative db\request for services spreadsheet.mdb;" & _
>     "Provider=Microsoft.Jet.OLEDB.4.0;"
> ' open connection
> vConnection.Open

> ' set variables from text entered into bookmarks
> vContactFN = ActiveDocument.FormFields("bkContactFN").Result
> vConcernFN = ActiveDocument.FormFields("bkConcernFN").Result
> vConcernRelationship = ActiveDocument.FormFields("bkConcernR").Result
> vGender = ActiveDocument.FormFields("bkConcernGender").Result

>  ' open a new version of the temporary RecordSet accessing the Request For
> Service db
>     vRecordSet.Open "SELECT* FROM RFSMain", vConnection, adOpenKeyset,
> adLockOptimistic
>     vRecordSet.AddNew

>     ' only set variables with some data entered into the field
>     If vContactFN <> "" Then vRecordSet!CPerson = vContactFN
>     If vConcernFN <> "" Then vRecordSet!PersonC = vConcernFN
>     If vConcernRelationship <> "" Then vRecordSet!Relationship =
> vConcernRelationship
>     If vGender <> "" Then vRecordSet!Gender = vGender

>     ' update the new RecorSet to DB
>     vRecordSet.Update
>     vRecordSet.MoveLast

>     MyNumber = vRecordSet(0) '<<retrive new number

>     ActiveDocument.FormFields("bkRFSID").Result = MyNumber

>     ' close object
>     vRecordSet.Close
>     vConnection.Close

>     ' clear object to free up memory
>     Set vRecordSet = Nothing
>     Set vConnection = Nothing

>  UserForm8.Hide
>  Unload Me

>  UserForm7.Show

> End Sub



Wed, 21 Sep 2005 09:57:39 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Automating Excel XP from Word XP

2. Access XP => Outlook XP

3. Access xp sending email via outlook xp

4. Access xp sending email via outlook xp

5. Word automation through Access XP

6. Trouble Opening Word XP from Access

7. On XP workstations, my access app get hung up using shell cmd to swap to Word

8. access automation for speech support in Word XP

9. access automation for speech support in Word XP

10. Word XP Database Access 2002

11. access automation for speech support in Word XP

12. DatePicker problem under Windows XP and Office XP

 

 
Powered by phpBB® Forum Software