Autonumber in an Access Database to be placed in Word document 
Author Message
 Autonumber in an Access Database to be placed in Word document

I have a Word 2000 document template for a medication "contract" with my
patients.

I would like to number the contracts based upon the autonumbering in an
Access 2000 database that is storing the input from the Word input form

The code that stores the input (after clicking the cmdOK button) on the form
is:

Private Sub cmdOK_Click()
Dim intAutonumber As Integer

Call cmdAddRec
ActiveDocument.Bookmarks("PatientName").Range.Text = Me.txtFirstName.Text &
" " & Me.txtLastName.Text
ActiveDocument.Bookmarks("FullName").Range.Text = Me.txtFirstName.Text & " "
& Me.txtLastName.Text
ActiveDocument.Bookmarks("MedRec").Range.Text = Me.txtMedRec
ActiveDocument.Bookmarks("Date").Range.Text = Date
ActiveDocument.Bookmarks("Pharmacy").Range.Text = Me.txtPharmacy
ActiveDocument.Bookmarks("PCP").Range.Text = Me.txtPCP

'close the form
Unload Me
End Sub

Here is the code that adds the record to the Access 2000 database:
Sub cmdAddRec()
On Error GoTo HandleErr
Dim dbMain As New ADODB.Connection
Dim rsPhysician As New ADODB.Recordset
Dim SLQ As String
Dim MyPath As String

MyPath = CurDir
dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info = False;" & _
"Data Source = " & MyPath & "\PainMgmt.mdb"

SLQ = "SELECT * FROM tblOpioidRx"

    'rsPhysician.Open SLQ, dbMain, adOpenDynamic, adLockOptimistic
    rsPhysician.Open SLQ, dbMain, adOpenKeyset, adLockPessimistic
    If Len(Me.txtMedRec > 0) Then
    rsPhysician.AddNew

    'rsPhysician.Find ("[PhysicianID] = " & Me.cboFindRecord.Column(0))
            'If Not rsPhysician.BOF Then

                    rsPhysician("FullName") = Me.txtFirstName & ", " &
Me.txtLastName
                    rsPhysician("medrec") = Me.txtMedRec
                    rsPhysician("PCP") = Me.txtPCP
                    rsPhysician("Pharmacy") = Me.txtPharmacy
                    rsPhysician("Date") = Date
                    rsPhysician.Update
                    rsPhysician.Close
            'End If
    Else
    MsgBox "Please insert the Medical Record Number", vbCritical, "Important
Message"
    Me.txtMedRec.SetFocus
    End If
ExitHere:
    Exit Sub
HandleErr:
    MsgBox Err.Description & vbCrLf & Err.Number, _
     vbCritical
    Resume ExitHere
End Sub

thanks in advance, Barth



Fri, 17 Jun 2005 04:12:54 GMT  
 Autonumber in an Access Database to be placed in Word document
Hi Barth,

You can probably make use of the information in the article Access a
database and insert into a Word document the data that you find there at:

http://www.mvps.org/word/FAQs/InterDev/GetDataFromDB.htm

to achieve what you are after.

Please post any response to the newsgroups for the benefit of others who may
also be following the thread.

Hope this helps,
Doug Robbins - Word MVP

Quote:
> I have a Word 2000 document template for a medication "contract" with my
> patients.

> I would like to number the contracts based upon the autonumbering in an
> Access 2000 database that is storing the input from the Word input form

> The code that stores the input (after clicking the cmdOK button) on the
form
> is:

> Private Sub cmdOK_Click()
> Dim intAutonumber As Integer

> Call cmdAddRec
> ActiveDocument.Bookmarks("PatientName").Range.Text = Me.txtFirstName.Text
&
> " " & Me.txtLastName.Text
> ActiveDocument.Bookmarks("FullName").Range.Text = Me.txtFirstName.Text & "
"
> & Me.txtLastName.Text
> ActiveDocument.Bookmarks("MedRec").Range.Text = Me.txtMedRec
> ActiveDocument.Bookmarks("Date").Range.Text = Date
> ActiveDocument.Bookmarks("Pharmacy").Range.Text = Me.txtPharmacy
> ActiveDocument.Bookmarks("PCP").Range.Text = Me.txtPCP

> 'close the form
> Unload Me
> End Sub

> Here is the code that adds the record to the Access 2000 database:
> Sub cmdAddRec()
> On Error GoTo HandleErr
> Dim dbMain As New ADODB.Connection
> Dim rsPhysician As New ADODB.Recordset
> Dim SLQ As String
> Dim MyPath As String

> MyPath = CurDir
> dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Persist Security Info = False;" & _
> "Data Source = " & MyPath & "\PainMgmt.mdb"

> SLQ = "SELECT * FROM tblOpioidRx"

>     'rsPhysician.Open SLQ, dbMain, adOpenDynamic, adLockOptimistic
>     rsPhysician.Open SLQ, dbMain, adOpenKeyset, adLockPessimistic
>     If Len(Me.txtMedRec > 0) Then
>     rsPhysician.AddNew

>     'rsPhysician.Find ("[PhysicianID] = " & Me.cboFindRecord.Column(0))
>             'If Not rsPhysician.BOF Then

>                     rsPhysician("FullName") = Me.txtFirstName & ", " &
> Me.txtLastName
>                     rsPhysician("medrec") = Me.txtMedRec
>                     rsPhysician("PCP") = Me.txtPCP
>                     rsPhysician("Pharmacy") = Me.txtPharmacy
>                     rsPhysician("Date") = Date
>                     rsPhysician.Update
>                     rsPhysician.Close
>             'End If
>     Else
>     MsgBox "Please insert the Medical Record Number", vbCritical,
"Important
> Message"
>     Me.txtMedRec.SetFocus
>     End If
> ExitHere:
>     Exit Sub
> HandleErr:
>     MsgBox Err.Description & vbCrLf & Err.Number, _
>      vbCritical
>     Resume ExitHere
> End Sub

> thanks in advance, Barth



Fri, 17 Jun 2005 16:57:43 GMT  
 Autonumber in an Access Database to be placed in Word document
Hi Doug

In order for this code (from
http://www.mvps.org/word/FAQs/InterDev/GetDataFromDB.htm) to run, you must
establish a reference, in your template, to the Microsoft DAO 3.51 Object
Library and Microsoft Datasource Interfaces. Is this OK to do even if I am
already using an ADODB Connection?

Second question: Is there not a way to do this while I have the
ADODB.Connection open?
Here is the code that I am using to add a new record to the dataset. Is
there a way to obtain the autonumber record number of this new record and
place it in a bookmark in the Word document?

Dim dbMain As New ADODB.Connection
Dim rsPhysician As New ADODB.Recordset
Dim SLQ As String
Dim MyPath As String

MyPath = CurDir
dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info = False;" & _
"Data Source = " & MyPath & "\PainMgmt.mdb"

SLQ = "SELECT * FROM tblOpioidRx"

    rsPhysician.Open SLQ, dbMain, adOpenKeyset, adLockPessimistic
    rsPhysician.AddNew
                    rsPhysician("FullName") = Me.txtFirstName & ", " &
Me.txtLastName
                    rsPhysician("medrec") = Me.txtMedRec
                    rsPhysician("PCP") = Me.txtPCP
                    rsPhysician("Pharmacy") = Me.txtPharmacy
                    rsPhysician("Date") = Date
                    rsPhysician.Update
     rsPhysician.Close

Thanks, Barth



Fri, 17 Jun 2005 23:01:58 GMT  
 Autonumber in an Access Database to be placed in Word document

Quote:

> Second question: Is there not a way to do this while I have the
> ADODB.Connection open?

I'd also like to know if the ADODB techniques are available from
Word VBA code.


Sun, 10 Jul 2005 11:18:53 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Saving an Embedded Word Document to an Access Database

2. Word documents in Access database

3. merging data from a secured Access database into a Word document

4. Creating a Word document from information stored in an Access database

5. Storing Word documents in an Access database.

6. Retrieving Word document from Access database.

7. Retrieving Word document from Access database.

8. Storing Word Documents in an Access Database

9. Using Access Database - need to put a word document in

10. Can you place variables in a Word document?

11. acessing textboxes placed on a word document

12. Place text at Footer of Word Document

 

 
Powered by phpBB® Forum Software