Copy from one user form to another

[Stockwell43]

Hello,

I have a question about something I never did before so please be sure to
simplify answer if this is possible.

I have a User Form we'll call it Construction Loans User Form which has the
usual basic information like Date, Customer Name, Address, City, Zip and so
forth. There is a Word from this group uses for Construction loans that has
some of the fields I have on the database. My question is this:

So that the user is not replicating work, is it possible for me to create an
second user form(just don't want to junk up the original form) to mimick the
Word form but have the fields the user is filling out in the Construction
Loans User Form to copy to the same fields on the second form I would create
for the Word form? So in other words, the user fills out the original user
form then clicks a button from that form to open the form to accomdate the
word form. When that form opens, it will already be prefilled with the same
fields from the original form so the user will already have 50% of the form
completed. I do not want to save the information from the second form, it is
strictly to just complete the Word form. Once the word form is printed, the
information can be deleted.

I realize I have a tendency to ramble when you folks my find it simpler to
explain but I have to write a book sometimes so I stay on the right track
without leaving much out if anything. )

Thanks!!

 

[Ken Sheridan]

Firstly, I assume these forms are in Access not Excel; you use the term 'user
form' which is used in Excel, whereas in Access the term commonly used is
simply 'form'.

Assuming it is Access I don't see why you need the second form rather than
simply passing the values from the first form to the Word form, in which the
user can then enter values into the remaining form fields. If you do want to
use a second form, however, then its very easy to do; simply create an
unbound form and add text box controls, each of which uses as its
ControlSource property an expression which references the corresponding
control on the first form, e.g.

=Forms![frmCustomers]![CustomerName]

If a control on the first form is a combo box whose value is a hidden first
column, e.g. CityID in a combo box which shows the city names, then you'd
need to reference the second column of the control with:

=[Forms]![frmCustomers]![cboCity].[Column](1)

The Column property is zero-based, so Column(1) is the second column.

You can then include other unbound text box controls in which the user can
enter other values to be passed to the Word form.

In the first form (frmCustomers in the above example) you simply open the
second form with a button, with code in its Click event procedure like this:

DoCmd.OpenForm "frmMiddleman"

where frmMiddleman is the name of the second (unbound) form.

To pass the values to a Word form you can use a procedure like the following:

Sub FillForm(strTemplate As String, frm As Form)

' Opens a document in Word and inserts values from
' current record in form fields.
' Accepts: path to Word template file - String

On Error GoTo Err_Handler

Dim objWord As Object
Dim objDoc As Object
Dim strAddress As String

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
End If

AppActivate "Microsoft Word"
On Error GoTo Err_Handler

' open Word document in maximised window
objWord.Visible = True
Set objDoc = objWord.Documents.Add(strTemplate)
objWord.WindowState = wdWindowStateMaximize

' insert first and lastname in form fields
objDoc.FormFields("Text1").Result = frm!FirstName
objDoc.FormFields("Text2").Result = frm!LastName

Set objDoc = Nothing
Set objWord = Nothing

Exit_here:
On Error GoTo 0
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume Exit_here

End Sub

The above function is a slightly amended version of one from a demo file of
Access to Word automation operations which I posted some years ago at:


http://community.netscape.com/n/pfx/forum.aspx?msg=23781.1&nav=messages&webtag=ws-msdevapps


You'd need to amend the above procedure o it referenced the form fields on
your Word document and the controls on your Access form rather than:

' insert first and lastname in form fields
objDoc.FormFields("Text1").Result = frm!FirstName
objDoc.FormFields("Text2").Result = frm!LastName

To pass the values from the frmMiddleman form to the word form you'd do so
from a button on frmMiddleman, passing the path to the template for the Word
form and a reference to the current Access for like so:

FillForm "F:\MyFolder\MyTemplates\MyForm.dot", Me

But you could equally well call the procedure from the first form of course,
cutting out frmMiddleman altogether.

You will of course need a reference to the MS Word object library in your
Access file (Tools | References on the VBA menu bar).

Ken Sheridan
Stafford, England

 

[Stockwell43]

Hi Ken,

Thank for your reply!!

That's correct, it is an Access form, sorry about the misinterpretation.

I understand what your saying and I though the same thing. I guess I am
trying to have access do some of the calculating(not being saved in the
table) so when it's transferred it's already there instead of the user doing
it manually. Also, I can keep it all in the database so when the user prints
the word form is completed(I can add the rest of the needed fields to the
second form.

I will give it a whirl but am quite sure it will work fine as you helped me
before and I have have never encountered any problems.

Thank you angain Ken!!!!

Firstly, I assume these forms are in Access not Excel; you use the term 'user
form' which is used in Excel, whereas in Access the term commonly used is
simply 'form'.

Assuming it is Access I don't see why you need the second form rather than
simply passing the values from the first form to the Word form, in which the
user can then enter values into the remaining form fields. If you do want to
use a second form, however, then its very easy to do; simply create an
unbound form and add text box controls, each of which uses as its
ControlSource property an expression which references the corresponding
control on the first form, e.g.

=Forms![frmCustomers]![CustomerName]

If a control on the first form is a combo box whose value is a hidden first
column, e.g. CityID in a combo box which shows the city names, then you'd
need to reference the second column of the control with:

=[Forms]![frmCustomers]![cboCity].[Column](1)

The Column property is zero-based, so Column(1) is the second column.

You can then include other unbound text box controls in which the user can
enter other values to be passed to the Word form.

In the first form (frmCustomers in the above example) you simply open the
second form with a button, with code in its Click event procedure like this:

DoCmd.OpenForm "frmMiddleman"

where frmMiddleman is the name of the second (unbound) form.

To pass the values to a Word form you can use a procedure like the following:

Sub FillForm(strTemplate As String, frm As Form)

' Opens a document in Word and inserts values from
' current record in form fields.
' Accepts: path to Word template file - String

On Error GoTo Err_Handler

Dim objWord As Object
Dim objDoc As Object
Dim strAddress As String

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
End If

AppActivate "Microsoft Word"
On Error GoTo Err_Handler

' open Word document in maximised window
objWord.Visible = True
Set objDoc = objWord.Documents.Add(strTemplate)
objWord.WindowState = wdWindowStateMaximize

' insert first and lastname in form fields
objDoc.FormFields("Text1").Result = frm!FirstName
objDoc.FormFields("Text2").Result = frm!LastName

Set objDoc = Nothing
Set objWord = Nothing

Exit_here:
On Error GoTo 0
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume Exit_here

End Sub

The above function is a slightly amended version of one from a demo file of
Access to Word automation operations which I posted some years ago at:


http://community.netscape.com/n/pfx/forum.aspx?msg=23781.1&nav=messages&webtag=ws-msdevapps


You'd need to amend the above procedure o it referenced the form fields on
your Word document and the controls on your Access form rather than:

' insert first and lastname in form fields
objDoc.FormFields("Text1").Result = frm!FirstName
objDoc.FormFields("Text2").Result = frm!LastName

To pass the values from the frmMiddleman form to the word form you'd do so
from a button on frmMiddleman, passing the path to the template for the Word
form and a reference to the current Access for like so:

FillForm "F:\MyFolder\MyTemplates\MyForm.dot", Me

But you could equally well call the procedure from the first form of course,
cutting out frmMiddleman altogether.

You will of course need a reference to the MS Word object library in your
Access file (Tools | References on the VBA menu bar).

Ken Sheridan
Stafford, England

Hello,

I have a question about something I never did before so please be sure to
simplify answer if this is possible.

I have a User Form we'll call it Construction Loans User Form which has the
usual basic information like Date, Customer Name, Address, City, Zip and so
forth. There is a Word from this group uses for Construction loans that has
some of the fields I have on the database. My question is this:

So that the user is not replicating work, is it possible for me to create an
second user form(just don't want to junk up the original form) to mimick the
Word form but have the fields the user is filling out in the Construction
Loans User Form to copy to the same fields on the second form I would create
for the Word form? So in other words, the user fills out the original user
form then clicks a button from that form to open the form to accomdate the
word form. When that form opens, it will already be prefilled with the same
fields from the original form so the user will already have 50% of the form
completed. I do not want to save the information from the second form, it is
strictly to just complete the Word form. Once the word form is printed, the
information can be deleted.

I realize I have a tendency to ramble when you folks my find it simpler to
explain but I have to write a book sometimes so I stay on the right track
without leaving much out if anything. )

Thanks!!

 

[Stockwell43]

I do have one question:

The code works fine and does what it needs to do but I just have one problem:

I need to subtract =Forms!frmconstructionsloans!LoanAmt by
=Forms!frmconstructionsloans!UnpdPB to = the current available to draw. I
tried this in the control source =Nz([LoanAmt])-NZ([UnpdPB]) but it just show
#Name? in the field. What am I doing wrong?

Thanks!!

Firstly, I assume these forms are in Access not Excel; you use the term 'user
form' which is used in Excel, whereas in Access the term commonly used is
simply 'form'.

Assuming it is Access I don't see why you need the second form rather than
simply passing the values from the first form to the Word form, in which the
user can then enter values into the remaining form fields. If you do want to
use a second form, however, then its very easy to do; simply create an
unbound form and add text box controls, each of which uses as its
ControlSource property an expression which references the corresponding
control on the first form, e.g.

=Forms![frmCustomers]![CustomerName]

If a control on the first form is a combo box whose value is a hidden first
column, e.g. CityID in a combo box which shows the city names, then you'd
need to reference the second column of the control with:

=[Forms]![frmCustomers]![cboCity].[Column](1)

The Column property is zero-based, so Column(1) is the second column.

You can then include other unbound text box controls in which the user can
enter other values to be passed to the Word form.

In the first form (frmCustomers in the above example) you simply open the
second form with a button, with code in its Click event procedure like this:

DoCmd.OpenForm "frmMiddleman"

where frmMiddleman is the name of the second (unbound) form.

To pass the values to a Word form you can use a procedure like the following:

Sub FillForm(strTemplate As String, frm As Form)

' Opens a document in Word and inserts values from
' current record in form fields.
' Accepts: path to Word template file - String

On Error GoTo Err_Handler

Dim objWord As Object
Dim objDoc As Object
Dim strAddress As String

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
End If

AppActivate "Microsoft Word"
On Error GoTo Err_Handler

' open Word document in maximised window
objWord.Visible = True
Set objDoc = objWord.Documents.Add(strTemplate)
objWord.WindowState = wdWindowStateMaximize

' insert first and lastname in form fields
objDoc.FormFields("Text1").Result = frm!FirstName
objDoc.FormFields("Text2").Result = frm!LastName

Set objDoc = Nothing
Set objWord = Nothing

Exit_here:
On Error GoTo 0
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume Exit_here

End Sub

The above function is a slightly amended version of one from a demo file of
Access to Word automation operations which I posted some years ago at:


http://community.netscape.com/n/pfx/forum.aspx?msg=23781.1&nav=messages&webtag=ws-msdevapps


You'd need to amend the above procedure o it referenced the form fields on
your Word document and the controls on your Access form rather than:

' insert first and lastname in form fields
objDoc.FormFields("Text1").Result = frm!FirstName
objDoc.FormFields("Text2").Result = frm!LastName

To pass the values from the frmMiddleman form to the word form you'd do so
from a button on frmMiddleman, passing the path to the template for the Word
form and a reference to the current Access for like so:

FillForm "F:\MyFolder\MyTemplates\MyForm.dot", Me

But you could equally well call the procedure from the first form of course,
cutting out frmMiddleman altogether.

You will of course need a reference to the MS Word object library in your
Access file (Tools | References on the VBA menu bar).

Ken Sheridan
Stafford, England

Hello,

I have a question about something I never did before so please be sure to
simplify answer if this is possible.

I have a User Form we'll call it Construction Loans User Form which has the
usual basic information like Date, Customer Name, Address, City, Zip and so
forth. There is a Word from this group uses for Construction loans that has
some of the fields I have on the database. My question is this:

So that the user is not replicating work, is it possible for me to create an
second user form(just don't want to junk up the original form) to mimick the
Word form but have the fields the user is filling out in the Construction
Loans User Form to copy to the same fields on the second form I would create
for the Word form? So in other words, the user fills out the original user
form then clicks a button from that form to open the form to accomdate the
word form. When that form opens, it will already be prefilled with the same
fields from the original form so the user will already have 50% of the form
completed. I do not want to save the information from the second form, it is
strictly to just complete the Word form. Once the word form is printed, the
information can be deleted.

I realize I have a tendency to ramble when you folks my find it simpler to
explain but I have to write a book sometimes so I stay on the right track
without leaving much out if anything. )

Thanks!!

 

[Stockwell43]

I got it, I was using the wrong name information. )

Firstly, I assume these forms are in Access not Excel; you use the term 'user
form' which is used in Excel, whereas in Access the term commonly used is
simply 'form'.

Assuming it is Access I don't see why you need the second form rather than
simply passing the values from the first form to the Word form, in which the
user can then enter values into the remaining form fields. If you do want to
use a second form, however, then its very easy to do; simply create an
unbound form and add text box controls, each of which uses as its
ControlSource property an expression which references the corresponding
control on the first form, e.g.

=Forms![frmCustomers]![CustomerName]

If a control on the first form is a combo box whose value is a hidden first
column, e.g. CityID in a combo box which shows the city names, then you'd
need to reference the second column of the control with:

=[Forms]![frmCustomers]![cboCity].[Column](1)

The Column property is zero-based, so Column(1) is the second column.

You can then include other unbound text box controls in which the user can
enter other values to be passed to the Word form.

In the first form (frmCustomers in the above example) you simply open the
second form with a button, with code in its Click event procedure like this:

DoCmd.OpenForm "frmMiddleman"

where frmMiddleman is the name of the second (unbound) form.

To pass the values to a Word form you can use a procedure like the following:

Sub FillForm(strTemplate As String, frm As Form)

' Opens a document in Word and inserts values from
' current record in form fields.
' Accepts: path to Word template file - String

On Error GoTo Err_Handler

Dim objWord As Object
Dim objDoc As Object
Dim strAddress As String

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
End If

AppActivate "Microsoft Word"
On Error GoTo Err_Handler

' open Word document in maximised window
objWord.Visible = True
Set objDoc = objWord.Documents.Add(strTemplate)
objWord.WindowState = wdWindowStateMaximize

' insert first and lastname in form fields
objDoc.FormFields("Text1").Result = frm!FirstName
objDoc.FormFields("Text2").Result = frm!LastName

Set objDoc = Nothing
Set objWord = Nothing

Exit_here:
On Error GoTo 0
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume Exit_here

End Sub

The above function is a slightly amended version of one from a demo file of
Access to Word automation operations which I posted some years ago at:


http://community.netscape.com/n/pfx/forum.aspx?msg=23781.1&nav=messages&webtag=ws-msdevapps


You'd need to amend the above procedure o it referenced the form fields on
your Word document and the controls on your Access form rather than:

' insert first and lastname in form fields
objDoc.FormFields("Text1").Result = frm!FirstName
objDoc.FormFields("Text2").Result = frm!LastName

To pass the values from the frmMiddleman form to the word form you'd do so
from a button on frmMiddleman, passing the path to the template for the Word
form and a reference to the current Access for like so:

FillForm "F:\MyFolder\MyTemplates\MyForm.dot", Me

But you could equally well call the procedure from the first form of course,
cutting out frmMiddleman altogether.

You will of course need a reference to the MS Word object library in your
Access file (Tools | References on the VBA menu bar).

Ken Sheridan
Stafford, England

Hello,

I have a question about something I never did before so please be sure to
simplify answer if this is possible.

I have a User Form we'll call it Construction Loans User Form which has the
usual basic information like Date, Customer Name, Address, City, Zip and so
forth. There is a Word from this group uses for Construction loans that has
some of the fields I have on the database. My question is this:

So that the user is not replicating work, is it possible for me to create an
second user form(just don't want to junk up the original form) to mimick the
Word form but have the fields the user is filling out in the Construction
Loans User Form to copy to the same fields on the second form I would create
for the Word form? So in other words, the user fills out the original user
form then clicks a button from that form to open the form to accomdate the
word form. When that form opens, it will already be prefilled with the same
fields from the original form so the user will already have 50% of the form
completed. I do not want to save the information from the second form, it is
strictly to just complete the Word form. Once the word form is printed, the
information can be deleted.

I realize I have a tendency to ramble when you folks my find it simpler to
explain but I have to write a book sometimes so I stay on the right track
without leaving much out if anything. )

Thanks!!