Import data from Access to MS Word form using VBA?

[abvnewbie]

Need some help. I have Word form that uses formfields to format user input text. The form is similar to a shipper and requires input of things like customer names and material part numbers.

The form, all the input dialog boxes and the underlying AutoOpen VBA code work great, but there's a section of the form that I would like to automate. Instead of having the user fill in every field I would like to import data from an Access database. Much of the data needed is sitting in an Access database (this is stuff like customer addresses and part attribute data such as part description, current revision, etc). I would like to reduce the required user input to simply a part number then have the VBA code pull the remaining info from the Access db into the appropriate formfields in the Word form. Is this possible? If so can someone possibly post some sample code on how to do this? Thx in advance.

[MOS MASTER]

Hi and welcome to VBAX! :hi:

Years ago I've written an KB-article to do exactly this see:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=490

It has a full functional access db and worddocument as an example.

perhaps it's not 100% what you need but I'm sure it will get you started.

HTH

[abvnewbie]

Thanks for the reply MOS MASTER. Excellent info and I'm almost there. Just need to understand the syntax for the code line:


Set rst = dbs.OpenRecordset("Select * FROM Contacts WHERE Name = '" & _
Me.cboContacts.Text & "';")

When I substitute the header names for the database that I'm actually using I get a "object not found in this collection" even though the value is there so I'm thinking there's something embedded in the syntax of the above line that's adding to the string of the value I'm looking for in the database.

The resultant code looks like this:


Sub mDatabaseOpen()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
DocNumberEntry.Show
vDocNumber = DocNumberEntry.TextBox1.Value
Set dbs = OpenDatabase("K:\D-BASE\PDM.mdb")
Set rst = dbs.OpenRecordset("Select * FROM DCR WHERE Prefix = '" & _
vDocNumber & "';")
vDescription = rst.Fields("Description")
MsgBox ("Description is: " & vDescription)
' unload resources
Unload DocNumberEntry
Set dbs = Nothing
Set rst = Nothing
End Sub

[MOS MASTER]

Hi,

You're most welcome glad I could help! :yes



When I substitute the header names for the database that I'm actually using I get a "object not found in this collection" even though the value is

The error seams to point me to the following question. The code returns a recordset of db columns with values.

The question is does: Select * FROM DCR WHERE Prefix = '" & _
vDocNumber & "';" return a recordset and does it hold the column: Description?

It's quite hard to debug from just looking at your code. Do you perhaps have a sample db and document to work with?

That way I can easily step through the code to see what's up.

HTH

[abvnewbie]

MOS MASTER, you hit the nail right on the head. Apparently the heading to the column in the database had a misspelling that returned a recordset with the heading "Descrption", hence the problem. There's another field I'd like to search on as an alternate, however the originator of that database has labeled the column "DOC / DWG #". I would rather search on that column as the doc numbers are formatted correctly. However, with this line of code:


Set rst = dbs.OpenRecordset("Select * FROM DCR WHERE DOC / DWG # = '" & vDocNumber & "';")

I get run-time error '3075': Syntax error in query in expression 'DOC / DWG #= '"{value}"

I'm lead to believe the syntax of the dbs.OpenRecordset() doesn't allow the use of "/" in the query index value. Any truth to this?

[MOS MASTER]

Hi, :yes

I get run-time error '3075': Syntax error in query in expression 'DOC / DWG #= '"{value}"

DOC / DWG #
This must be the worst column name I've ever seen in my career!!!! (The person who did this had no idea what he's doing...)

There are 4 things wrong with this name:
* Use of / is ilegal
* Use of # is ilegal
* Spaces in column name (Dumb)
* Not very descriptive (Bad practise)

I don't know we can do a lot of syntax tricks here to fix this. And even if we could it would make the code unreadable.

I suggest you to do the following:
1. Open the Access db
2. Go to the Query view
3. Create a new Query (Wizard) based upon the table DCR
4. Save it and rename it to: qryDCR
5. Open the Query in SQL view
6. You see the bad column name in there something like: [DOC / DWG #]
7. Change this to: [DOC / DWG #] AS newColumnName (change the name to something logical to you) The AS statement renames the column for you in the Query
8. Change the OpenRecordSet line to:

Set rst = dbs.OpenRecordset("Select * FROM qryDCR WHERE newColumnName = '" & vDocNumber & "';")
FYI the above query asumes the vDocNumber is defined as String in the db. If however it is defined as Number then change to:

Set rst = dbs.OpenRecordset("Select * FROM qryDCR WHERE newColumnName = " & vDocNumber & ";") (the single quotes are dropped)

Now you query from the Access query instead of the table directly and your code is more readable.

HTH :whistle:

[abvnewbie]

lol. I absolutely share your opinions on the column header. And would you believe the database was created by someone who is supposed to be a programming guru. I think it would be easiest to simply for the column heading to be fixed in the database.

I've got yet another headache with this record set thingy. When a field in the database is empty it either returns a null value or simply doesn't create the record for that particular variable. In the example below, the field in the access database for "Outstanding ECOs" is empty. When I try to write the value [rst.Fields("OutstandingECOs")] to the form field on the document form it gives a runtime error "invalid use of Null".



ActiveDocument.FormFields("outeco" & vDocIndex).Result = rst.Fields("OutstandingECOs")

I'm sure there's a simple explanation...or at least hope there is.

[MOS MASTER]

Hi, :yes

lol. I absolutely share your opinions on the column header. And would you believe the database was created by someone who is supposed to be a programming guru. I think it would be easiest to simply for the column heading to be fixed in the database.

He's most definitly not! :rofl: And it's very common when a person know's a bit more then the rest about Office he's considerd the (company) expert... And yes most people start acting if they are as well...


I've got yet another headache with this record set thingy. When a field in the database is empty it either returns a null value or simply doesn't create the record for that particular variable. In the example below, the field in the access database for "Outstanding ECOs" is empty. When I try to write the value [rst.Fields("OutstandingECOs")] to the form field on the document form it gives a runtime error "invalid use of Null".

Ofcourse there's a good explanation for this but first a compliment on your own performance! :yes You make it very easy for me to answer your question because you put in the time to write down a clear problem description. (that's nice!!!!)

So as you saw you get problems when the db return a null value. And there are a lot of ways to deal with this situation. In my example (DAO) this doesn't happen because I used a trick see:


Call FillBookmark("" & rst.Fields("Name"), "bmName")


Please note the "" at the beginning of the first parameter when calling the FillBookmark method.

By passing in a "" I pass in a Empty string and that way the code is tricked and thinks it's not empty anymore.

I'd say try it out.

HTH

[abvnewbie]

Yes indeed. Quite simple and yet I somehow didn't think of it in the many work-arounds that I managed to come up with. All is right with the world again! Thanks so much for all the help.
:beerchug:

[MOS MASTER]

Yes indeed. Quite simple and yet I somehow didn't think of it in the many work-arounds that I managed to come up with. All is right with the world again! Thanks so much for all the help.
:beerchug:

You did a lot of work yourself so I'm glad I could help you allong! :yes