Software optimize

How do I... Fill Word form fields with Access data?

When you need to bring your Access data into a Word form, a little VBA can expedite the task. Susan Harkins walks through an example to demonstrate the process.

Access reports provide a flexible and easy-to-use tool for sharing data, but sometimes, it's the wrong tool. For instance, you may find that you can't exactly reproduce an existing paper form in Access, whereas you can in Word. Now, you might not care what the report (paper form) looks like, but perhaps management does. When this is the case, you may find it necessary to transfer Access data to Word forms. The good news is that Word and Access play well together.

Note:This information is also available as a download, which includes text files containing the VBA code from Listing A and Listing B.

The automated solution requires three pieces:

  • A method of identifying the Access data you want to transfer
  • A bit of VBA code to automate the process
  • A Word document that represents the form you need to fill.
We'll work with existing data in the Northwind database (the sample database that comes with Access). Specifically, the example will transfer data from the Customers table, via a form, to the Word document shown in Figure A.

Figure A: The highlighted areas are text fields.

Creating the Word form

The Word form isn't as difficult to create as it might look. The example form consists of five simple tables that contain form fields. In this context, a form is a document that contains fill-in blanks or form fields, in which you enter information. A field is a predefined cell for entering and storing information.

The form in Figure A uses five two-row tables to simulate a form. You don't need the tables, but they help organize and manage the data. To insert a table, choose Insert from the Table menu and then choose Table. In the resulting Insert Table dialog box, enter the appropriate number of columns and rows (see Figure B). To create this form, you'll need five tables as follows:

Columns Rows
2 2
2 2
1 2
4 2
2 2

Figure B: Identify the number of rows and columns in each table.

The example form also uses shading and text in the first row to distinguish between the heading and actual information. You can skip the formatting if you like; it isn't critical to the technique. Alter the width of each cell column, using Figure A as a guide, if you like.

The example Word document contains a matching field for each column in the Northwind's Customers table. You don't have to accommodate each field; transfer just the data you need. However, you must associate a Word field with each underlying Access field that you want to copy to the Word form. To add the first field, complete the following steps in the Word form:

  1. Display the Forms toolbar by choosing Toolbars from the View menu and selecting Forms.
  2. In the document, click the table cell beneath the Customer Number heading.
  3. Click the Text Form Field button on the Forms toolbar.
  4. Double-click the field to display the Text Form Field Options dialog box.
  5. Rename the field fldCustomerID and click OK.

Repeat steps 2 through 5 to add a text field for each heading, using the following list to name each field:

Word field heading Field name Corresponding field in Access table
Customer Number fldCustomerID CustomerID
Customer fldCompanyName CompanyName
Contact fldContactName ContactName
Title fldContactTitle ContactTitle
Street Address fldAddress Address
City fldCity City
Region fldRegion Region
ZIP Code fldPostalCode PostalCode
Country fldCountry Country
Phone Number fldPhone Phone
Fax Number fldFax Fax

The field heading doesn't have to match the field names. However, notice that the Word field names match the Access field names, with an additional fld prefix. It isn't necessary to name the Word fields similarly to their corresponding Access fields, but doing so is self-documenting and simplifies your work.

After adding all the fields, click the Protect Form button. Doing so disables a number of features, which will keep casual users from altering your form. Then, save the form as CustomerSlip.doc and close it.

Identifying the Access data

The most common method you'll probably use to identify a specific record is an Access form. To simply the example, use the existing Customers form in Northwind. If you'd rather not alter the actual object, use a copy or use the AutoForm wizard to generate a quick form based on the Customers table. When you have a form bound to the Customers table, add a command button to the form and name it cmdPrint, as shown in Figure C.

Figure C: The Print Customer Slip command button executes the VBA code that will pass Access data to the Word form.

With the form still in Design view, click the Code button to launch the form's module. Add the code in Listing A to the module. The cmdPrint button's Print event does all the work of copying data for the current customer in the Customers form to the Word document (CustomerSlip.doc).

Listing A

Private Sub cmdPrint_Click()

'Print customer slip for current customer.

Dim appWord As Word.Application

Dim doc As Word.Document

'Avoid error 429, when Word isn't open.

On Error Resume Next

Err.Clear

'Set appWord object variable to running instance of Word.

Set appWord = GetObject(, "Word.Application")

If Err.Number <> 0 Then

'If Word isn't open, create a new instance of Word.

Set appWord = New Word.Application

End If

Set doc = appWord.Documents.Open("C:\WordForms\CustomerSlip.doc", , True)

With doc

.FormFields("fldCustomerID").Result = Me!CustomerID

.FormFields("fldCompanyName").Result = Me!CompanyName

.FormFields("fldContactName").Result = Me!ContactName

.FormFields("fldContactTitle").Result = Me!ContactTitle

.FormFields("fldAddress").Result = Me!Address

.FormFields("fldCity").Result = Me!City

.FormFields("fldRegion").Result = Me!Region

.FormFields("fldPostalCode").Result = Me!PostalCode

.FormFields("fldCountry").Result = Me!Country

.FormFields("fldPhone").Result = Me!Phone

.FormFields("fldFax").Result = Me!Fax

.Visible = True

.Activate

End With

Set doc = Nothing

Set appWord = Nothing

Exit Sub

errHandler:

MsgBox Err.Number & ": " & Err.Description

End Sub

How it works

First, the code creates an instance of Word. If Word is already open, it uses the currently running instance. The current code uses the expression Err.Number <> 0 because Microsoft is notorious for changing error numbers from version to version. You might want to be more specific by changing 0 to 429 (or the appropriate error number for your version if different).

Next, the Open method identifies the Word form (document). In this case, that's CustomerSlip.doc in the WordForms folder on the C:\ drive. The optional True value opens the file as read-only. Update the path to accommodate your system. In fact, you might consider moving the filename and path to constants in the General Declarations section. That way, you can more easily update the code if you change the filename or move it.

The With block copies the data from each Access field to its corresponding Word field. After copying all the data, the Visible and Activate methods display and then select the filled in Word form, as shown in Figure D.

Figure D: The form will look like this after all the data is copied and filled.

The error handling is minimal. Be sure to thoroughly test your production solution and accommodate all possible errors. The most common errors you'll run into are a missing Word file, an incorrect path, or mismatched field names.

Filling multiple Word forms

Currently, the example works with only one record, the form's current record. Sometimes, you'll want to pass multiple records. Fortunately, it isn't difficult to enhance the existing code to handle multiple records and forms, as shown in Listing B.

Listing B

Private Sub cmdPrint_Click()

'Print customer slip for current customer.

Dim appWord As Word.Application

Dim doc As Word.Document

Dim rst As ADODB.Recordset

'Avoid error 429, when Word isn't open.

On Error Resume Next

Err.Clear

'Set appWord object variable to running instance of Word.

Set appWord = GetObject(, "Word.Application")

If Err.Number <> 0 Then

'If Word isn't open, create a new instance of Word.

Set appWord = New Word.Application

End If

'Populate recordset object.

Set rst = New ADODB.Recordset

rst.Open Me.RecordSource, CurrentProject.Connection

'Cycle through records to fill Word form fields.

Do While Not rst.EOF

Set doc = appWord.Documents.Open("C:\WordForms\CustomerSlip.doc", , True)

With doc

.FormFields("fldCustomerID").Result = rst!CustomerID

.FormFields("fldCompanyName").Result = rst!CompanyName

.FormFields("fldContactName").Result = rst!ContactName

.FormFields("fldContactTitle").Result = rst!ContactTitle

.FormFields("fldAddress").Result = rst!Address

.FormFields("fldCity").Result = rst!City

.FormFields("fldRegion").Result = rst!Region

.FormFields("fldPostalCode").Result = rst!PostalCode

.FormFields("fldCountry").Result = rst!Country

.FormFields("fldPhone").Result = rst!Phone

.FormFields("fldFax").Result = rst!Fax

.Visible = True

.Activate

'.PrintOut

'.SaveAs "'" & rst!CustomerID & "'"

rst.MoveNext

End With

Loop

Set doc = Nothing

Set appWord = Nothing

Exit Sub

errHandler:

MsgBox Err.Number & ": " & Err.Description

End Sub

Instead of retrieving data from the actual form (using the Me identifier), this procedure uses a Recordset object to pass data from multiple records. Be sure to update the .FieldForm statements by referring to the recordset (rst!) instead of the Access form (Me!), as the previous procedure did.

Our example uses the form's RecordSource property to retrieve data from the Access form's underlying data source (the Customers table). Consequently, the form isn't synchronized to the procedure, so the form's current record never changes. We're just using the form's recordset and print button to keep the example simple. When applying this technique to your database, you can use any valid recordset source.

Notice that the code has two commented statements toward the end of the With block. Those are actions you might possibly want to take for each Word form. The first prints the completed form; the second saves the form to the current directory using the current record's primary key value as the form's name. You could use either or both. If you don't save the current record, Word dumps it when the Do While loop processes the next record.

The downside is performance. If you're working with thousands of records, this process will take a few minutes to complete.

Shortcut to Word forms

Filling a Word form with Access data can be a useful when your data is in Access and you must use Word's formatting features to create just the right display. Fortunately, the process is simple, whether sharing one record or thousands.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

70 comments
fishmanm
fishmanm

Helo! Im using Word 2010 and Access 2010 and can not seem to make the code work. the Word document is opening but that is it! Does anyon has an answer?

rankhornjp
rankhornjp

Any ideas on why I would have to double click the button to get the Word document to open? Script works GREAT, except for that! Thanks

statca
statca

Hi all, New to access and VB, I used the first script in the article to fill a word form with access data. Works great until I want data from a second table. I am sure it is just a minute problem but it keeps me far away from my goal. I would be very thankful for some help!!

bestleonard
bestleonard

It looks like there is a document written for each record. I need all records written to one document, obviously with a page break between records.

jeffoiler
jeffoiler

my Word database field is pulling it's data from Excel. The data pulls correctly when it's NOT being loaded into a Word table, but when I try to load that same data in a Word table, it gives the error 'Error! Not a valid result for table.' Any ideas how to get data to load within a Word table? BTW, the data I'm loading is only a 1 cell number.

warroyave
warroyave

I have followed the instructions in the article by Susan Harkins, however I am having trouble with Memo Fields. If the field contains less thatn 255 characters, it fills in the word bookmark fine, if it contains more than that, it fails to fill in. Any solutions?

jayke001
jayke001

I think this is a great article/blog. I'm happy and glad that Techrepublic makes these past articles available/republishes them for us. I am using Windows XP, Word and Access 2003. References include: Microsoft Word 12.0 Object Library and Microsoft Office 12.0 Object Library. My problem was not in seeing the Word document after running the code. The solution was a line of code, the new line has the astericks at the end of it: Set appWord = GetObject(, "Word.Application") If Err.Number 0 Then 'If Word isn?t open, create a new instance of Word. Set appWord = New Word.Application End If appWord.Visible = True '*** Set doc = appWord.Documents.Open("C:\Documents and Settings\user\Desktop\AccessTransferTest.doc", , True)

ddejarnett
ddejarnett

I followed Susan's instructions exactly, but I cannot get data to populate my Word form. Word opens, but no data appears. Can anyone point me in the right direction for correcting my issue? Thanks!

carlbjames01
carlbjames01

This is working for me perfectly. Really appreciate the article. I need the exact same process for an EXCEL form. Is there similar code out there I could reference? I've looked around, but can't find it.

wilfredo.molina
wilfredo.molina

Howdy. I need to apply this code to print different word documents using check box.

wilfredo.molina
wilfredo.molina

I need to apply this code to print differente word documents.

Tink!
Tink!

I want to fill in multiple records on the same Word document? Such as a sheet of labels using records from the Access DB. Or a custom report that takes data from Access and lines it up in the Word document.

mlevit
mlevit

Hi, I've been trying to get it to work with Office 2007 but it doesn't seem to want to. The Word document opens but none of the form fields are filled in from the Access database. Any ideas? Thanks

dave.lawson
dave.lawson

This looks great, but I get a compile error when I try to run the code at .......... Dim appWord As Word.Application Dave

skeelie
skeelie

I have tried this with Word 2000 and vb.net 2003 and it doesn't work. My code variation that sort of works is: .FormFields.Item("ProjectN").Result = rs.Fields("ProjectN").Value I have searched for over a month trying to figure this out. I only have 8 fields, and about 20 REF's, In the final doc, the data from the table fields don't match up with the result of the doc fields, My Refs never update(I do have calculate on exit checked). And, Word Object model in vb.net insists I use a .DocumentClass with the Formfields but there is no Formfield I can use during coding. I'm sure it can be done with Word 2000 , (?) finding documentation and examples for word2000 programming is not easily found. Can someone Please, please help me! Thanks

msigsworth
msigsworth

How would I modify this code to allow for looping of records within a single form, rather than creating a new Word form for each record? For example, assume multiple but unknown number of phone numbers for each client.

icharalampidis
icharalampidis

Hi I used the above code, but my MSword application is not opening. What am I doing wrong?

strannik
strannik

Ok, I am trying to figure out the syntax for a multiple table SQL nested JOIN, and I am either getting syntax errors or the latest: "Too many fields" error. I cannot find what is wrong. Here is the query I am trying to get right: SELECT EE_REPORT_DATA1.*, EE_REPORT_DATA2.*, EE_REPORT_DATA3.* FROM ((EE_REPORT_DATA1 INNER JOIN EE_REPORT_DATA2 ON EE_REPORT_DATA1.ID = EE_REPORT_DATA2.CHILDID2) INNER JOIN EE_REPORT_DATA3 ON EE_REPORT_DATA3.CHILDID3 = EE_REPORT_DATA1.ID) WHERE EE_REPORT_DATA1.ID = vRPT_ID; Any clues? Thanks, John

strannik
strannik

I have a word form with about 300 fields (so it won't fit in 1 table - I use 3 instead). Currently I have it working where on submission, it opens 3 recordsets in a row, and fills in fields with part of the form in each case. The primary key in table 1 (replication id) is duplicated in a "child_id#" field in the other two tables. When I try to use a similar method to pull the data back into the form, the first part of the form fills in OK, but it can't find the 2nd and 3rd part. I am using the following method to get the data: 'enter the id to fill in the form: vRPT_ID = ActiveDocument.FormFields("RPT_ID").Result vGetSql = "SELECT EE_REPORT_DATA1.* " & _ "FROM EE_REPORT_DATA1 " & _ "WHERE EE_REPORT_DATA1.[ID]='" & vRPT_ID & "';" vRecordSet.Open vGetSql, vConnection, adOpenKeyset, adLockOptimistic With vRecordSet If Not .EOF Then 'Yes then fill in the form to the database stored values If vRecordSet("ID") "" Then vRPT_ID = vRecordSet("ID") If vRecordSet("Date") "" Then vText1date = vRecordSet("Date") If vRecordSet("Report") "" Then vText2Report = vRecordSet("Report") 'etc. etc. 'Fill form field's bookmarks ActiveDocument.FormFields("RPT_ID").Result = vRPT_ID ActiveDocument.FormFields("Text1date").Result = vText1date ActiveDocument.FormFields("Text2Report").Result = vText2Report ActiveDocument.FormFields("Text3Toolname").Result = vText3Toolname 'all going well so far Else MsgBox "Sorry, No matching records found!" End If End With vRecordSet.Close vConnection.Close Set vRecordSet = Nothing Set vConnection = Nothing 'DB SECTION 2 'Make a connection to the database (copy & paste) vConnection.ConnectionString = _ "data source=" & vPath & "\" & vFileName & ";" & _ "Provider=Microsoft.Jet.OLEDB.4.0;" 'Open the connection for data exchange (copy n paste) vConnection.Open 'Test Connection and let user knows it is working. ? 'Change if this message is annoying vConnectionState = vConnection.State If vConnectionState 1 Then MsgBox "Cannot connect to the database!", vbCritical End If vGetSql2 = "SELECT EE_REPORT_DATA2.* " & _ "FROM EE_REPORT_DATA2 " & _ "WHERE EE_REPORT_DATA2.[CHILDID2]='" & vRPT_ID & "';" vRecordSet.Open vGetSql2, vConnection, adOpenKeyset, adLockOptimistic 'if a match is not found then show it With vRecordSet If Not .EOF Then 'fill in the form to the database stored values If vRecordSet("CHILDID2") "" Then vID_PROP = vRecordSet("CHILDID2") If vRecordSet("C1") "" Then vDropdown1 = vRecordSet("C1") etc. etc. The first part of the form fills in fine, and then it can't find the 2nd table stuff. The CHILDID2 is identical to the vRPT_ID. I know there has to be a better way to do this than sequential access to tables, but if I can get it to work this way it would be better for now - any ideas? Thanks, John

Rahul_Bharaktiya
Rahul_Bharaktiya

after putting all the information the Word form is not merging the form value in to the word document,it is just opening the word form nothing else

tfos
tfos

Help I can get the VB to compile but the form comes up with the field names, not the data. Maybe the form is wrong or I need another Reference Library? Here are my VB references: Visual Basic for Applications Microsoft Access 11.0 Object Library OLE Automation Microsoft DAO 3.6 Object Library Microsoft Word 11.0 Object Library I have the form - with the "type" as text and the put the "default text" as fldCustomerID on the form. Is this what you meant by "renaming the field fldCustomerID?" There was no picture once I followed the Form properties. Thank you

ryanjhofman
ryanjhofman

Great !! but how do you get checkbox's to check- I have an access Form with a checkbox and when the box is checked, i get the value to check the box and the box on the form field does not check - can anyone "check" this out for me please :) thank you!

eva
eva

I too tried the methods mentioned above and was unsuccessful. When I used the Listing A code, I get a Microsoft Visual Basic error "Compile Error: User-defined type not defined" and the code "Dim appWord As Word.Application" is highlighted. I get this error after I click on the print button I created in Access. When I tried using Listing B code nothing happens. No error, but nothing gets populated in my Word document. I'm currently running Office 2003. Please help. Thank you in advance. Aloha!!!

rankhornjp
rankhornjp

The "Me!" before each field name identifies the current table. Try changing that to a different table. Or use script 2 and mod to pull the information you want from both tables into a recordset and the move to Word doc.

bipsync
bipsync

hi statca and hi all.. if anyone can help me.. im using office 2007.. getting an error "Error - User defined type not defined" and its highlighting the code "appWord As Word.Application" is it because of the 2007 version or something else? can anyone look into the codes and make it work for 2007 version of access.. thanx..

ssharkins
ssharkins

That's a good question. I think -- off the top of my head, I'd use each field's Exit macro to ask the user if there's another value, and use the Exit macro's to build short module-level strings -- or better yet, a custom Collection to store multiple values for each field. Then, could use the final field's Exit macro the same, only call a procedure that concatenates all the values, connects to the database, and so on.

ssharkins
ssharkins

Are you getting an error or is nothing at all happening?

ssharkins
ssharkins

The easiest way to debut a SQL statement is in the SQL window. For some reason, Access displays a better error message, one that actually provides reasonable clues to finding the error. Simply cut and paste the SQL statement into the SQL window and run it. Just in case, the SQL window is part of the Query design window. Open a blank query in Design view, and choose SQL view from the View button. That will open a blank window -- just paste the SQL statement in and click Run.

ssharkins
ssharkins

I think it would be easier to create one recordset that contained all of the data you needed, using a SQL JOIN.

Rahul_Bharaktiya
Rahul_Bharaktiya

this is code which i am using which is not merging the data from the access form to the word document. Private Sub cmdPrint_Click() 'Print customer slip for current customer. Dim appWord As Word.Application Dim doc As Word.Document 'Avoid error 429, when Word isn't open. On Error Resume Next Err.Clear 'Set appWord object variable to running instance of Word. Set appWord = GetObject(, "Word.Application") If Err.Number 0 Then 'If Word isn't open, create a new instance of Word. Set appWord = New Word.Application End If Set doc = appWord.Documents.Open("K:\Personal Lending\Dealer Alert\Customerslip.doc", , True) With doc .FormFields("fldCustomerID").result = Me!CustomerID .FormFields("fldCompanyName").result = Me!CompanyName .FormFields("fldContactName").result = Me!ContactName .FormFields("fldContactTitle").result = Me!ContactTitle .FormFields("fldAddress").result = Me!Address .FormFields("fldCity").result = Me!City .FormFields("fldRegion").result = Me!Region .FormFields("fldPostalCode").result = Me!PostalCode .FormFields("fldCountry").result = Me!Country .FormFields("fldPhone").result = Me!Phone .FormFields("fldFax").result = Me!Fax .Visible = True .Activate End With Set doc = Nothing Set appWord = Nothing Exit Sub errHandler: MsgBox Err.Number & ": " & Err.Description End Sub

ssharkins
ssharkins

Use the "bookmark" property to name the form fields. I think if you make that change, it'll work fine for you. Let me know.

ssharkins
ssharkins

Be sure to reference the Microsoft Office Object Library. Using the VB Editor, choose References from the Tools menu, check, the library, close the dialog, and return to the document and try again.

ury2ok2000
ury2ok2000

Hello, You are getting that error because you have not added the REFERENCES for VBA to understand the defined object you are trying to use (Word.Application), hence the type not defined error. To fix this you need to add a reference to the Word Object Library . In the IDE (of VBA) go to Tools-> References. Look for and Check Microsoft Word 12.0 Object Library. Took me awhile to track down this info myself but I remembered references from my VB6 days. Not sure why the article doesn't mention it as it is a pretty significant step. Hope this helps you.

strannik
strannik

Using the SQL interface is actually what I was doing, but I did a bit more research and understood what was meant by "Too many fields defined". My form has a little over/less than 300 fields - the reason I broke it up between 3 tables in the first place. The nested Join SQL actually works - but not if you try to load more than 255 fields into the recordset, which apparently also has this limitation. I had the SQL load the data from two tables instead of 3, and it loaded just fine (just under the 255 limit). So I think I am back to using at least two recordsets to populate the form with data, unless there is some other UPDATE method that allows it to keep going (I thought I saw something about that, but I think it related to DAO rather than ADO). Any Suggestions? Thanks for all of you help thus far!

strannik
strannik

I think you are probably right. I haven't yet been able to figure out how to do that. Do I need to link the tables in some way for that to work? Do there have to be any fields in common (other than ID)?. Can you point to any examples? Thanks, John

ssharkins
ssharkins

Did you name the Word form fields correctly -- fldCustomerID, and so on?

ssharkins
ssharkins

That is certainly good news. I am interested in why you need some many controls in a single form. The truth is, I've never run into anyone that's actually needed that much information in a single form.

strannik
strannik

Well thanks for all your help! Ironically, the way to solve the problem was to go back to consecutively loading recordsets, but this time the SQL is better. I load the first two thirds of the form (from the first two tables), thusly: GetSql = "SELECT EE_REPORT_DATA1.*, EE_REPORT_DATA2.* " & _ "FROM EE_REPORT_DATA1 INNER JOIN EE_REPORT_DATA2 ON EE_REPORT_DATA1.ID = EE_REPORT_DATA2.CHILDID2 " & _ "WHERE EE_REPORT_DATA1.[ID]='" & vRPT_ID & "';" And then close that recordset, and open the next one to get the last third from the last table: GetSql2 = "SELECT EE_REPORT_DATA3.* " & _ "FROM EE_REPORT_DATA1 INNER JOIN EE_REPORT_DATA3 ON EE_REPORT_DATA1.ID = EE_REPORT_DATA3.CHILDID3 " & _ "WHERE EE_REPORT_DATA1.[ID]='" & vRPT_ID & "';" I can now build a word form of nearly infinite length. It just takes longer to load ;-). Thanks again, and keep writing those super books! John

ssharkins
ssharkins

Well, I totally messed that one up -- I apologize. I realize now that you're discussing the limit on fields in a table or recordset and not the limit on controls in a form. It was early, I hadn't had coffee, I had a grandbaby underfoot, I had sinus medication, I had... just pick one. :) As far as I know, there is no way around the limit on fields in a table or recordset.

ssharkins
ssharkins

A colleague, Gustav Brock, reminded me of a fairly easy solution -- embarrassed that I didn't think of it myself. Access considers a subform a control and counts it as such, so you can have up to 255 subforms and those subforms can have 255 controls. If you set the subform border to transparent, users won't even know they're working with subforms, although you'll have to automate the selection process a bit most likely. Have you considered a tab control or multiple linked forms instead?

ssharkins
ssharkins

Well, I'm glad you were able to troubleshoot your problem, but I'm really no help. I'll ask around to see if there's a way around this limitation using recordsets, but I'm not aware of it off the top of my head. Typically, when you reach a limit, you just have to improvise or rethink your strategy. I seldom run into anyone who actually needs to push the limitations envelope!

strannik
strannik

OK, that is a big help, but I am still trying to get my head around how to do a JOIN in this context. I have just picked up your book, and it is the best I have seen on these topics so far (good job!). Your book talks about how it works in the context of related tables and all of the code samples seem to be for related tables, but I still don't see how to do it for tables that aren't related. Any code samples you could point me to? (in your book or elsewhere?). Sorry to be so dense, but I know if I just see a relevant example, then I will get it. Thanks, John

ssharkins
ssharkins

You don't need a permanent relationship to use SQL JOIN. The JOIN will do all the work for you.

strannik
strannik

I was opening a seperate recordset for each table, since I couldn't initialy figure out a different way to it (I'm a bit new at VBA and Access to say the least). This worked well for filling in the initial form and populating the record, but I haven't been able to get similar logic to work for repopulating the form fields from the database. I could rewrite the whole thing to use SQL JOINs (which I apparently should have done in the first place), but if there is a way to fix the current method it might be quicker. I only have 1 field in common in the 3 tables: Table 1: Main Primary Key Rep. Id Table 2: Child ID (copy of above, not primary key - there is an additional autoincrement number just for this table). Table 3: Same as Table 2. Am I correct in assuming that if I want to use SQL JOINs that I have to form a relationship between the Main Rep. ID in Table 1 and the CHILDIDs in Table 2 and 3? Thanks

ssharkins
ssharkins

Before we go any further, I admit that I assumed you were dealing with three related tables and that may not be the case. If they are related tables -- related by a common field, such as a primary/foreign key arrangement, the SQL JOIN will work. However, it appears to me that you're closing the Recordset object after the first round of populating -- comment out the following statements: Set vRecordSet = Nothing Set vConnection = Nothing Let me know if that gets you any closer.

Rahul_Bharaktiya
Rahul_Bharaktiya

Hi Now it is merging but still not passing few fields from access form to the word document

Loo76
Loo76

I'm having the same problem - did you come up with a solution?

Rahul_Bharaktiya
Rahul_Bharaktiya

Yes i rechecked it ,but still its not working,it is just opening the form in the read only, and not transfering the data from from the Access form to word document