Solved: Form Linked with Database

[petedw]

I have a form which i designed using VBA in Word. It has 7 textbox's and a commandbutton.
What i require is coding that copies the text typed in the textbox's into the relevant fields on a database when the commandbutton is clicked on.
Can you guys help me with this please.

Cheers

Pete

[sandam]

What type of database are you using? and what is the table structure like? Do you need to copy the information to many tables or one specifically?....

Let me know these things and I think I can help you. Also, which version of Word are you working in?

[petedw]

I am using an Access Database for the time being.



Above is my table structure. The info to be copied only has to be copied to this table.

Hope you can help!

Pete

[sandam]

Ok, I think I can sort something out

[sandam]

For this to work, you need to add the activex data objects library, at least ver 2.4. But otherwise I think this should work

[vba]
Option Explicit
Private Const ContactConnectString1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source="
Private Const ContactConnectString2 = ";Persist Security Info=False'"
Private Const dbName = "MyDB.mdb" 'just change this to your database's name
Private Const ContactQueryString = "SELECT * From Customer"

Private Const FIELDNAME_1 As String = "CustomerRef"
Private Const FIELDNAME_2 As String = "CustomerName"
Private Const FIELDNAME_3 As String = "CustomerAd1"
Private Const FIELDNAME_4 As String = "CustomerAd2"
Private Const FIELDNAME_5 As String = "CustomerAd3"
Private Const FIELDNAME_6 As String = "Postcode"
Private Const FIELDNAME_7 As String = "Shortname"

Private Field_CustomerRef As String
Private Field_CustomerName As String
Private Field_CustomerAd1 As String
Private Field_CustomerAd2 As String
Private Field_CustomerAd3 As String
Private Field_Postcode As String
Private Field_Shortname As String

Private Sub SaveContactDetails()
Dim saveRst As ADODB.Recordset
Set saveRst = New ADODB.Recordset
saveRst.Open ContactQueryString, ContactConnectString1 + dbName _
+ ContactConnectString2, adOpenDynamic, adLockPessimistic, adCmdTable
Application.ScreenUpdating = False
saveRst.AddNew Array(FIELDNAME_1, FIELDNAME_2, FIELDNAME_3, FIELDNAME_4, FIELDNAME_5, _
FIELDNAME_6, FIELDNAME_7), Array(CVar(Field_CustomerRef), CVar(Field_CustomerName), _
CVar(Field_CustomerAd1), CVar(Field_CustomerAd2), CVar(Field_CustomerAd3), _
CVar(Field_Postcode), CVar(Field_Shortname))
Application.ScreenUpdating = True
saveRst.Close
Set saveRst = Nothing
End Sub

Private Sub CommandButton1_Click()
Field_CustomerRef = TextBox1.Text
Field_CustomerName = TextBox2.Text
Field_CustomerAd1 = TextBox3.Text
Field_CustomerAd2 = TextBox4.Text
Field_CustomerAd3 = TextBox5.Text
Field_Postcode = TextBox6.Text
Field_Shortname = TextBox7.Text
SaveContactDetails
End Sub
[/vba]

[petedw]

I am getting the following error
"Format of the initialization string does not conform to the OLE DB specification"
refering to this part of the coding;

saveRst.Open ContactQueryString, ContactConnectString1 + dbName _
+ ContactConnectString2, adOpenDynamic, adLockPessimistic, adCmdTable

How do i sort this??

[sandam]

ok, i think i may have missed something in the connection string.

try changing ContactConnectString2 to this

[vba]
Private Const ContactConnectString2 = ";Username=Admin;Password="" ;Persist Security Info=False'"
[/vba]

[petedw]

That has now changed the error to:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Any suggestions??

[sandam]

Found this on the web. Perhaps modifying the ContactConnectString 1 to the format below and excluding the myDB variable and the second string. IE just change the string to match your details.

[vba]
ContactConnectString1 = Provider=Microsoft.Jet.OLEDB.4.0;Data _
Source=Drive:\SomePath\myDB.mdb;Jet OLEDB:System _
Database=MySystem.mdw", "Username", "Password"
[/vba]

[petedw]

Now im getting the error

"Could not find installable ISAM."

[sandam]

Try removing this part from the connection string. I think it has to do with working with Access 2002 or 2003 as opposed to Access 2000. they connect in a slightly different way. Saw a forum on this topic but can't seem to find it just now.

[vba]
Jet OLEDB:System Database=MySystem.mdw
[/vba]

[MOS MASTER]

Hi Pete,

I'll attach a example using DAO that I've made for a Dutch client. There's some Dutch language in there but I think it won't botter you.

You can double click on the Word template and execute at once.

The code:[VBA]
Option Explicit
'Set reference to Microsoft ActiveX Data Objects 2.5 (nr may differ) Library
Private Sub cmdCancel_Click()
Unload Me
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End Sub
Private Sub cmdOk_Click()
FillDatabase
Unload Me
End Sub
Sub FillDatabase()
Dim cn As New ADODB.Connection: Dim rst As New ADODB.Recordset
Dim oCtl As MSForms.Control: Dim sPath As String

On Error GoTo FillDatabase_Error

sPath = ThisDocument.Path & "\NAW.mdb"
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open sPath

rst.Open "Contactpersonen", cn, adOpenKeyset, adLockOptimistic
rst.AddNew

For Each oCtl In Me.Controls
If oCtl.Visible And oCtl.Tag = "Save" Then
If oCtl.Text <> "" Then rst.Fields(oCtl.Name).Value = oCtl.Text
End If
Next oCtl

rst.Update
MsgBox "U gegevens zijn met succes toegevoegd", vbExclamation, "Schrijfbevestiging"
rst.Close
cn.Close

Set rst = Nothing
Set cn = Nothing
Exit Sub

FillDatabase_Error:
If Not rst Is Nothing Then
If rst.State = adStateOpen Then rst.Close
End If
Set rst = Nothing

If Not cn Is Nothing Then
If cn.State = adStateOpen Then cn.Close
End If
Set cn = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Onbekende fout!"
End If
End Sub
[/VBA]

Enjoy!

[MOS MASTER]

Glad to see you've made it work!

[petedw]

Yep, cheers again Joost