access a field in access from word via vba

[Telsa]

I really need help with this little thing... I just can't seem to get it to work. Working with Word 2000 and Access 2000.

This is the code I try and get an error... it's in a Word module...

Dim acApp As Object

Set acApp = GetObject(, "Access.Application&quot
With acApp
DoCmd.SelectObject acForm, "Add SW Product", True
strProdNum = acApp.Screen.ActiveControl.Value
End With

Access is open and so is the form with the record needing to be accessed. There is a button on the form to load word and eventually run this code so it will start a mailmerge.

I'm just trying to capture one little field for my sql in the merge.

Why won't this work???

Any clues??

Mary )

[sig][/sig]

 

[kathryn]

Mary,

What is the error you are getting?

You could try adding this line right after the docmd line:

msgbox acApp.Screen.ActiveControl.name

That would tell you what control Access considers active.

Kathryn [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[Telsa]

It says &quot;Object doesn't support this method or property.&quot;

Ugh! That says a lot!

Mary ) [sig][/sig]

 

[kathryn]

Mary,

On what line do you get the error message? [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[Telsa]

On this line

strProdNum = acApp.Screen.ActiveControl.Value


I think it is because it is an DAO command and Access uses ADO. Do you know of an ADO equivalent? How would I could make the form the active form? Something like doing an Alt Tab to get to the form??? [sig][/sig]

 

[kathryn]

In rereading your initial post, you are clicking a button on an Access form. This button runs code which opens Word, and then runs code in Word which refers back to Access. Is that correct? Can you explain what you are trying to do? To refer to the current form from the code behind the button on the form, you can just use the keyword Me.

I'm just wondering if there isn't a cleaner way to do what you want.
[sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[Telsa]

I'm wondering too if there is a cleaner way...

I didn't write the original code for this database but have to make it work with a new Word merge file. So I'm in new territory here.

What I need to do is capture the data in for one record and one field (ProdNum) of the Access form to use against a query that will merge with a Word file.

Can you think of an easy way for Word to capture that Access field in the form so it can be used in the MailMerge section of Word?

YOu know, as I think about this... I'm wondering if it wouldn't be better to have a new query created during runtime and then go to Word to start the merge process.

I'll be merging 4 different documents then combining them into one since 3 have to be the &quot;catalog&quot; format and the final doesn't.

I don't have much resources to look these things up so am very dependent upon these forums to kinda guide me a bit of insight.

Thanks!

Mary ) [sig][/sig]

 

[kathryn]

Have you checked out the following MS Knowledgebase article?

http://support.microsoft.com/support/kb/articles/Q131/5/83.asp?LN=EN-US&SD=gn&FR=0

[sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[Telsa]

Hmmm... That might work in Access 2000. I'll have to see. I'm working with tables in Word but might still be able to make this work.

Thanks for looking it up!!!

Mary ) [sig][/sig]

 

[Telsa]

Hmmm.... my issue is all the fields I need are not on the form but in a query. Do you think CStr will work for queries?

Mary } [sig][/sig]

 

[kathryn]

Yes, all CStr does is convert all data to strings. Is the query that the fields in all included in the query upon which the form is based? if so, you can still refer to them, even if you haven't placed them on the form.

[sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[DougP]

I have created a lot of apps using Word and Access
But it is slightly different than you want to do.
If you open your Word doc and then click 'Tools' menu
then click 'Mail Merge' (you can connect a Word doc to an Access table or query). See more below.

We took a step further and Opened Access first to a form found a record we wanted to merge in our Word doc then made a query that looked at a key field on the access form.

So the Word doc was mail merged to that query. the query returned just the one record instead of the whole table. Next we made a button in the Access form that launched Word and the approriate document which loaded and then merged by itself.
We then created a macro in Word to print it out and then exit Word which brought us back to Access for the next one.

If you want to know more check out my FAQ

Shell to Word, Open a Doc and run a Macro
faq181-28

In Word to Mail Merge to Access query

1. Open Word doc to merge
2. Click 'Tools' menu then 'Mail Merge'
3. In the Mail merge dialog box click 'Create' button
4. Click 'Active window'
5. Click 'Get Data' button
6. Open 'Data Source'
7. In the 'Files of type' box (at the bottom) find MS Access Databases .MDB or .MDE
8. Find your Access database ...
9. Click the 'Queries' TAB at the top (It will then show a list Queries)
10. Choose the one you want.
11. It will launch Access and then be back at Word
12. Click &quot;Edit main document&quot; button
13. Now in Word you will have a new tool bar at the top.
13a. Put the cursor in Word where you want the field to go.
14. Click the Insert Merge button.
15. A list of the fields will show from your query.
16. When you click the field you want it instantly insert where the cursor is (See 13a if that not where you want it)
17. Add all the fields you want.
18. Now on that new toolbar click the click the <<ABC>> button. It will merge and show the data.

If you have more than one record click the |< < > >| navigation buttons (just like in Access)

NOTE: never close Access when you have a Word Doc open that is merging to it, or you could risk messing up the link.

OK
[sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]