Open New Form with Identical Info as Original Form

[ryguy7272]

I have a Form which I right-clicked, copied, and pasted to create another
(identical) Form to my project. I gave this new Form a different name and
changed the Caption. I am trying to come up with a way of running code to
copy all the data from the original Form to the new Form. Basically, the
both forms (which are identical) consist of about 8 textBoxes and 3
ComboBoxes. Is there an easy way to use code to copy the data from each
object from OldForm and transfer it to NewForm?

Thanks!
Ryan--

 

[David C. Holley]

Before we go down that road. What are you trying to accomplish? What is the
purpose for having two forms that are identical? What's the bigger picture?
There are options.

 

[Jeff Boyce]

I'll expand on David's response...

Forms do not store data. Tables store data. Are you saying that you have
two tables with identical structure, and are trying to use two forms to
store (the same) data in both tables?

If so, why?! Identical table structure in Access sounds suspiciously like a
spreadsheet ... and Access is not a spreadsheet.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[ryguy7272]

Ok, this is for a financial firm. I just found out that the purpose is to
reduce the trader from entering identical information when a trade is
crossed. A cross is the opposite side of the trade, so if the first trade
was a sell the cross is a buy. Everything on the ticket is identical except
the side will change from buy to sell or vise versa.

That may not be an optimal use of Access, but it seems to be what the guy
that I’m working with wants. Is this doable or not really?

Thanks!
Ryan--

 

[John Spencer]

Yes, it is doable. How to do it kind of depends on a few factors.

One method using VBA:
Open the new form in add mode
Assign values between the two forms except for the primary key which HAS to be
different.

One method would be to set the tag property of each control on the original
form that needs to be copied to "CrossData".

You can then use VBA on a button click event that looks like the following
UNTESTED code:

Private Sub btnAddCross()

Dim strCross As String
Dim ctl As Control

strCross = "NameOfTheCrossForm"
DoCmd.OpenForm strCross, acNormal, , , acFormAdd

For Each ctl In Me.Controls
If ctl.Tag = "CrossData" Then
Forms(strCross)!Controls(ctl.Name) = ctl.Value
End If
Next ctl

End Sub


Another way would be to use an insert query to build the record value and then
open the CrossData form to that record. The advantage of the first method is
that the record is not automatically saved and can be cancelled.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[ryguy7272]

Thanks for the help John! I’m trying your code and attempting to get this
working, but something is not quite right. The two forms are named ‘Trades’
and ‘TradesCross’; I want to copy all values from Trades, to TradesCross. I
went into DesignView on Trades and right-clicked each control and clicked
Properties (this is actually a sub-form, named ‘Options’, embedded in a form,
but I don’t think that matters). I found the Tag on the ‘Other’ tab. I have
a control named ‘OptionsNo’ so I named the Tag the same, ‘OptionsNo’. Same
for all controls. Is that right? I am working with this code now:
Private Sub btnAddCross_Click()

Dim strCross As String
Dim ctl As Control

strCross = "TradesCross"
DoCmd.OpenForm strCross, acNormal, , , acFormAdd

For Each ctl In Me.Controls
If ctl.Tag = "TradesCross" Then
Forms(strCross)!Controls(ctl.Name) = ctl.Value
End If
Next ctl

End Sub

When the code fires, TradesCross opens, but all values on all Controls are
blank. I think the problem may be here:
If ctl.Tag = "TradesCross" Then

I tried the code behind the sub-form and the form. This seems pretty
simple, but I can’t figure out why it’s not working.

Any suggestions would be greatly appreciated.

Thanks!
Ryan--

 

[Jeff Boyce]

What's the relationship in the underlying data?

Any chance you could use a main form/subform construction?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

I'm not sure. But are you trying to copy data from the FORM or from a sub-form
on the the form? Or are you trying to copy data from the form and the sub-form?

If you are trying to copy from the sub-form then the code needs to be different.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[ryguy7272]

Yes! Right! The data is in the sub-form! The sub-form is called 'Options'.
I changed the code a bit.
From:
strCross = "TradesCross"

To:
strCross = "Options"

From:
If ctl.Tag = "TradesCross" Then

To:
If ctl.Tag = "Options" Then

Now, when the code fires, the Options form opens (this is the sub-form for
'Trades' and 'TradesCross'). I think I have to open the TradesCross and the
Options and somehow reference both! I think that's the key here.

I found this:
http://allenbrowne.com/ser-57.html

Not exactly sure how to adapt the code, but I'll have a look at it tonight.

Any ideas?
Thanks!!

 

[John Spencer]

Did you solve your problem? As a wild UNTESTED guess on a solution, you might
be able to usde

Private Sub btnAddCross()

Dim strCross As String
Dim ctl As Control

strCross = "NameOfTheCrossForm"
DoCmd.OpenForm strCross, acNormal, , , acFormAdd

For Each ctl In Me.SubFormControlName.Form.Controls

If ctl.Tag = "CrossData" Then
Forms(strCross).SubFormControlName.Form.Controls(ctl.Name) = ctl.Value
End If

Next ctl

End Sub

If the subformControl is named OPTIONS then you can replace subFormcontrolName
with Options. The name of the control is not necessarily the name of the form
that the control is using. The name of the subform control might be something
like Child1.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[ryguy7272]

Thanks for sticking with this John. Now I'm convinced that this is possible,
but still not sure how best to do it.


Ya know, the SubForm is the same in both forms; both 'Trades' and
'TradesCross' use 'Options' as a SubForm. As I think about it now, there is
now a way the same SubForm can display slightly different data, right. I
kept the original 'Options' SubForm and created a new SubForm named
'OptionsCross'. 'OptionsCross' is now the SubForm of 'TradesCross'

When I run the code I get this:
Compile error
Method or data member not found

The error seems to occur on this line:
For Each ctl In Me.OptionsCross.Form.Controls

Here is the code that I'm working with now:
Private Sub btnAddCross_Click()

Dim strCross As String
Dim ctl As Control
strCross = "TradeCross"
DoCmd.OpenForm strCross, acNormal, , , acFormAdd
For Each ctl In Me.OptionsCross.Form.Controls
If ctl.Tag = "CrossData" Then
Forms(strCross).SubFormControlName.Form.Controls(ctl.Name) = ctl.Value
End If
Next ctl
End Sub

The code is behind the 'Options' SubForm.

I looked at this:
http://mvps.org/access/forms/frm0031.htm

Great site for SubForms. Still can't get the code working though.

 

[ryguy7272]

Still struggling with this. Any thoughts anyone???
Thanks!

 

[Douglas J. Steele]

Are you certain that the name of the subform control on the parent form is
OptionsCross?

Depending on how you added the subform, the name of the subform control can
be different than the name of the form being used.

 

[ryguy7272]

What does this mean, ‘Depending on how you added the subform’? How can I
check the name of the subform? If I go into Design View on the parent form,
I can click on the upper-left corner of the subform, get a small black
square, and under All (tab) I see the name of the subform is ‘Options’. If I
click back on the parent-form and then click back on the subform, but this
time get an orange border around the whole subform, and under All (tab) I see
the name of the subform is ‘Options’. Same thing!! I really think that’s
the name of the subform, bu the code keeps erroring here:
For Each ctl In Me.Options.Form.Controls

Message is ‘method or data member is not found’

Code:
Private Sub btnAddCross_Click()

Dim strCross As String
Dim ctl As Control

strCross = "TradesCross"
DoCmd.OpenForm strCross, acNormal, , , acFormAdd

For Each ctl In Me.Options.Form.Controls
If ctl.Tag = "CrossData" Then
Forms(strCross).TradesCross.Form.Controls(ctl.Name) = ctl.Value
End If

Next ctl

End Sub

BTW, I added ‘TradesCross’ to all objects in the subform.

I saw this online:
Forms![main form name]![subform control name].Form![control name]

I think that is for referencing the form from a module, not from the code
behind the form, right. Now, my code is behind the form.

I’d love to get this working soon.
Any ideas???

Thanks again!
Ryan---

 

[Douglas J. Steele]

There are a number of ways in which you can add a form as a subform. The two
that come to mind immediate are dragging one form on top of another form,
and dragging the subform control onto the parent form and letting the wizard
do the work for you.

Don't click on the upper-left corner of the subform, just click on the
control that holds the subform.

While Options isn't listed on Allen Browne's page of unacceptable names at
http://www.allenbrowne.com/AppIssueBadWord.html, Option is, and it wouldn't
actually surprise me to learn that Options isn't allow either. Try changing
the name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

What does this mean, 'Depending on how you added the subform'? How can I
check the name of the subform? If I go into Design View on the parent
form,
I can click on the upper-left corner of the subform, get a small black
square, and under All (tab) I see the name of the subform is 'Options'.
If I
click back on the parent-form and then click back on the subform, but this
time get an orange border around the whole subform, and under All (tab) I
see
the name of the subform is 'Options'. Same thing!! I really think that's
the name of the subform, bu the code keeps erroring here:
For Each ctl In Me.Options.Form.Controls

Message is 'method or data member is not found'

Code:
Private Sub btnAddCross_Click()

Dim strCross As String
Dim ctl As Control

strCross = "TradesCross"
DoCmd.OpenForm strCross, acNormal, , , acFormAdd

For Each ctl In Me.Options.Form.Controls
If ctl.Tag = "CrossData" Then
Forms(strCross).TradesCross.Form.Controls(ctl.Name) = ctl.Value
End If

Next ctl

End Sub

BTW, I added 'TradesCross' to all objects in the subform.

I saw this online:
Forms![main form name]![subform control name].Form![control name]

I think that is for referencing the form from a module, not from the code
behind the form, right. Now, my code is behind the form.

I'd love to get this working soon.
Any ideas???

Thanks again!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Are you certain that the name of the subform control on the parent form
is
OptionsCross?

Depending on how you added the subform, the name of the subform control
can
be different than the name of the form being used.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)





.