Fill in Word Text Form Fields from multiple Access forms

[kegoosse]

Hello,

I'm currently trying to fill Text Form Fields in Word from Access data.
This works as long as I use the data of the form where the command button is located. In other words as long as I refer to the data with "Me!..."

However, I also need to include data from other forms. I thought this would be very simple but the values aren't copied. In the example below, the last line that starts with ".FormFields" - .FormFields("Uparam_pH").Result = Form!Dataanalyse_urineparam!pH - doesn't seem to work.

Anyone have an idea on what I might need to add to my code to make it work?

Thanks in advance



Private Sub Command457_Click()

'Print to word'.
Set appWord = CreateObject("Word.Application")
'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("H:\DRAFT BL - UR - VITR.docx", , True)
With doc
.FormFields("Geg_Verslagnr").Result = Me!Dossier_Nummer
.FormFields("Geg_Notitienr").Result = Me!Notitie_Nummer
.FormFields("Geg_TitelMag").Result = Me!Magistraten_Aanspreektitel
.FormFields("Geg_NaamMag").Result = Me!Onderzoeksrechter_aanstelling
.FormFields("Geg_FunctieMag").Result = Me!Functie
.FormFields("Geg_Rechtsgebied").Result = Me!Magistraten_Rechtsgebied
.FormFields("Geg_AdresMag").Result = Me!Magistraten_Adres
.FormFields("Geg_TijdAfname").Result = Me!Tijdstip_afname
.FormFields("Geg_NaamSlachtoffer").Result = Me!Naam
.FormFields("Geg_Opdracht").Result = Me!Opdracht
.FormFields("Geg_Prelev").Result = Me!Prevelementen
.FormFields("Geg_TitelGenees").Result = Me!Wetsgeneesheren_Aanspreektitel
.FormFields("Geg_NaamGenees").Result = Me!Wetsgeneesheer
.FormFields("Geg_InstituutGenees").Result = Me!Institituut
.FormFields("Geg_DatumOntv").Result = Me!Datum_Ontvangst
.FormFields("Geg_Koerier").Result = Me!Koerier
.FormFields("Geg_Levering").Result = Me!Opgehaald_geleverd
.FormFields("Geg_Startanalyse").Result = Me!Startdatum_analyse
.FormFields("Geg_Eindanalyse").Result = Me!Einddatum_analyse
.FormFields("Uparam_pH").Result = Form!Dataanalyse_urineparam!pH
.Visible = True
.Activate
End With
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description

End Sub

[OBP]

The first point is, is the Form Dataanalyse_urinparam open at this point?
Second if it is open does PH actually have a value?
msgbox Form!Dataanalyse_urineparam!pH
should tell you.

[kegoosse]

I indeed figured that the form would have to be open and I tried including the following code, but still it doesn't work

...
.FormFields("Geg_Eindanalyse").Result = Me!Einddatum_analyse
DoCmd.OpenForm "Dataanalyse_urineparam", acNormal, "", "", , acNormal
.FormFields("Uparam_pH").Result = Forms!Dataanalyse_urineparam!pH
DoCmd.Close acForm, "Dataanalyse_urineparam"
.Visible = True
...

Is there some other code I need to use to open the form and make it visible?

(And yes, pH has a value)

[OBP]

When you open the form, on the next line put

msgbox " form value = " & Forms!Dataanalyse_urineparam!pH

that will tell you what value the VBA code is seeing on the form.

The next point is how does the newly opened form know which record to goto?

[kegoosse]

You mean like this?

...
.FormFields("Geg_Eindanalyse").Result = Me!Einddatum_analyse
DoCmd.OpenForm "Dataanalyse_urineparam", acNormal, "", "", , acNormal
MsgBox " form value = " & Forms!Dataanalyse_urineparam!pH
.FormFields("Uparam_pH").Result = Forms!Dataanalyse_urineparam!pH
DoCmd.Close acForm, "Dataanalyse_urineparam"
.Visible = True
...

Sadly, I'm not getting a message box. I guess this might mean the form isn't visible?

The form's record source is a query that filters for 1 selected record, and the selection has already been made before I start the VBA command. In other words there's only 1 record.

Thanks for the help so far.

[OBP]

OK, now open the Query manually, is there a record?
If there is then open the form form manually to see if the record comes across.

[kegoosse]

The query contains the record, but the record doesn't come across the form when I open it manually. Don't really understand how come ...
When I open the form with a command button it works fine.

Given this issue I tried the following VBA code (without closing the form an query I open)
...
.FormFields("Geg_Eindanalyse").Result = Me!Einddatum_analyse
DoCmd.OpenQuery "Query_urineparam"
DoCmd.OpenForm "Dataanalyse_urineparam", acNormal, "", "", , acNormal
.FormFields("Uparam_pH").Result = Forms!Dataanalyse_urineparam!pH
.Visible = True
...

Again the query opens with the correct record, but the form opens without the record.

I guess this might be a schoolboy error, but I don't understand why the form doesn't get filled.

[kegoosse]

I've adapted my code a bit and now the form opens with the correct record information.

...
.FormFields("Geg_Eindanalyse").Result = Me!Einddatum_analyse
DoCmd.OpenQuery "Query_urineparam"
DoCmd.OpenForm "Dataanalyse_urineparam", acNormal, "", "", acEdit, acNormal
.FormFields("Uparam_pH").Result = Forms!Dataanalyse_urineparam!pH
.Visible = True
...

However, the Word Form Fields still don't get filled in, which returns me to my original problem :s

[OBP]

Well at least you knew what the original problem was, I assume that if you put the msgbox back it it gives you a value.

On the form that contains all the other data is there room for a hidden field, if there is add asmall unbound text box.
Move your current code that opens the form to before the other values are transferred to word.
After the form opening line add

me.newtextboxname = Forms!Dataanalyse_urineparam!pH

where newtextboxname is the actual name of your new text box.

You could even transfer the data and check it prior to exporting to word.

So that you can check if a value gets transferred to your first form if it does use the new text box to transfer the data to word.

[kegoosse]

The message box still doesn't appear

I'm sure it has to do with the inability of my current code to get the data from a different form, because when I move the information to the form where the command button is everything goes right.

Moving everything to a single form like you suggest, however, isn't an option because I have > 300 different items that need to be copied to Word, and a single form is limited to 255 different items.

Any other suggestions on what adaptations can be made to get that data from that form?


Thanks for the help so far OBP

[kegoosse]

I got it to work :)

I use this code

.FormFields("Uparam_pH").Result = Forms("Dataanalyse_urineparam").pH

Does anyone know why this works and the version with the exclamation marks doesn't?

[OBP]

Well done.
I have no idea why that would work when the other "standard" code for transferring data doesn't, which is what I was trying to get to the heart of.
You said in your previous post that you have over 300 items to transfer to word.
There is an alternative method than opening forms to do this, you can use VBA Recordsets to do so based on queries.

[kegoosse]

I'm not familiar with VBA Recordsets. It's actually the first time I'm using VBA based on some tutorials I find on the net, so I'm sure I'm not working in the most elegant way.
I am however closing in on my deadline ... I'll look into it if a have some time left. Is it something that you would often use when you work with VBA Access?

[OBP]

Yes VBA recordset can be used for updating related tables with data generated in a form for a different table, a classic example would be
Wharehouse Stock
Accounts
Orders
Invoices
When an order is made, the VBA updates the wharehouse stock with the order quantity
When the Invoice is paid the Order is completed and the accounts updated with the amount.

They can be used for things like creating Mailing Addresses when using VBA to send an email.

This piece of code updates an "Audit Trail Table" with who did what on the currently open form.


Set rstable = db.OpenRecordset("Action Audit Trail")
With rstable
.AddNew
!UserID = lngMyEmpID ' lngMyEmpID should be replaced with whatever User ID key field Public Variable you use
!Form = Me.Name
!action = action
![Action Date/Time] = Now()
.Update
.Bookmark = .LastModified
End With
rstable.Close

That is not the whole code just the part that updates (adds new record) the table.