Solved: Refering to Access and extracting relevant data

[petedw]

Hi,

I am using forms within Word and would like to tell word to retrieve date from a Database "DB1".
The database contains the fields

CustomerRef
CustomerName
CustomerAd1
CustomerAd2
CustomerAd3
PostCode
Shortname

What i would like to happen is..... when i type in a customers reference number in Word, it looks for that reference number in the Database "DB1" CustomerRef field and if it finds it, inserts the relevant data to go with it (CustomerName, CustomerAd1, CustomerAd2, CustomerAd3, PostCode, Shortname).

I hope someone can help me with this.

Many Thanks

Pete

[MOS MASTER]

Hi Pete, :yes

You can use DAO or ADO (and others) for this!

This is one I made a long time ago with DAO:
Option Explicit
Private Sub UserForm_Initialize()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = OpenDatabase(ActiveDocument.Path & "\Contactpersonen.mdb")
Set rst = dbs.OpenRecordset("Select Naam FROM Contacts;")
Do While Not rst.EOF
Me.cboContactpersonen.AddItem rst("Naam")
rst.MoveNext
Loop
Set rst = Nothing
Set dbs = Nothing
End Sub
Private Sub cmdInvoegen_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Me.Hide
If Me.cboContactpersonen.ListIndex = -1 Then
Beep
MsgBox "U moet eerst een keuze maken", 64, "Maak uw Keuze"

Exit Sub
Else
Set dbs = OpenDatabase(ActiveDocument.Path & "\Contactpersonen.mdb")
Set rst = dbs.OpenRecordset("Select * FROM Contacts WHERE Naam = '" & _
Me.cboContactpersonen.Text & "';")
With ActiveDocument
.Bookmarks("bmBedrijf").Range.Text = "" & rst.Fields("Bedrijf")
.Bookmarks("bmNaam").Range.Text = "" & rst.Fields("Naam")
.Bookmarks("bmAdres").Range.Text = "" & rst.Fields("Adres")
.Bookmarks("bmPlaats").Range.Text = "" & rst.Fields("Postcode") & " " & rst("Plaats")
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.Bookmarks("bmTelefoon").Range.Text = "" & rst.Fields("Telefoon")
.Bookmarks("bmMobiel").Range.Text = "" & rst.Fields("Mobiel")
.Bookmarks("bmFax").Range.Text = "" & rst.Fields("Fax")
.Bookmarks("bmwww").Range.Text = "" & rst.Fields("www")
.Bookmarks("bmEmail").Range.Text = "" & rst.Fields("Email")
.Bookmarks("bmTaal").Range.Text = "" & rst.Fields("Taal")
.Bookmarks("bmMemo").Range.Text = "" & rst.Fields("Memo")
End With
End If
Set rst = Nothing
Set dbs = Nothing
Unload Me
End Sub


Some of it is in Dutch but I think you get the big picture over here!

I'm using a combobox to select the person I want and the code fills in the blanks in the document.

See the attachment for an example! :whistle:

[petedw]

Joost,

This code is great. I need an extra line adding though.....

If i type something into the box that doesnt match up to the information that is available for selection i get a warning box popping up that says "Microsoft Forms - Invalid property value".
Is it possible for me to change this so that warning box contains the following message "Please add this member to the database"

I hope you understand what i require

Many Thanks again

Pete

[MOS MASTER]

Hi Pete, :D

Glad you like it, it was some work to be done...so you're welcome!

The combo has it's MachRequired property set to true exactly for this reason.
This is to make shure that you always have to pick someone from the list.

Yes I agree the default warning that you receive is not a message that tell's you that much.

I personally don't know an easy way to change this message when matchRequired property is on.

I could think of a sollution that makes you type in the combo the name you want and then use a like operator in you're sql to the recordset to find a match. If there is no match then you could pop-up a msgbox. (Shouldn't be that much work but I doubt if it can ever be as reliable than forcing people to choose from the list of contacts..(To me the method used now is the closed to a 100% garantee)

But you can try it out.

Enjoy! :whistle:

[petedw]

Ok then, i will stick with that. Was just wondering if there was an easy way to do it but obviously not.

Thanks

Pete

[MOS MASTER]

Nopes none that I can think of..:yes