COPY SUBFORM VALUE TO MAIN FORM

P

pauld

I'm using Access 2003.

I'm trying to set the value of a control on the Main form (main form is
names EDIT INCOMPLETE ENTRIES) equal to the value of a control on the Sub
Foem (sub form is named FIND PHRF). The control is named OWCRating.

I've tried the SetValue macro with the Item set to [Forms]![EDIT INCOMPLETE
ENTRIES]![OWCRating] and the Expression set to [Forms]![EDIT INCOMPLETE
ENTRIES]![FIND PHRF].[Form]![OWCRating]. I get an error message saying I'm
trying to reference an OLE object that isn't an OLE object.

Is SetValue the best way to do this? Or is there a better way?

Could the problem be with the names of the Main From and Sub Form?

I don't understand Visual Basic so that's why I'm trying to use the "canned"
Macros.

I'd appreciate any advice or help available.

Thanks.
 
T

Tom Wickerath

Hi Paul,

You should be able to simply use an expression as the Control Source for a
text box on your main form, something like this:

=[NameOfSubformControl].[Form].[NameOfTextbox]


where [NameOfSubformControl] is the name of the control that holds your
subform. This may or may not be the same name as the subform itself. I have a
sample database that you can download, "Bavant Marine Services", from this
location to see an example (see Technician Master Form):

http://www.access.qbuilt.com/html/subtotals.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

Hi Paul,

Are both controls named OWCRating (the one on the main form and the one on
the subform) combo boxes? If so, the first thing you will need to verify is
that the fields that serve as the Control Source for each combo box are the
same data type and field size, since you are trying to copy data from one to
the other. I can't say that I've ever tried to do anything similar, but you
might try air code like this behind the command button on the main form. I
consider this "air code" because I have not tested it. In the code below,
[SubformContainerName] refers to the name of the control that holds the
subform.

Private Sub cmdCopyRec_Click()
On Error GoTo ProcError

Me.OWCRating = [SubformContainerName].[Form].[OWCRating]

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyRec_Click..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
P

pauld

The OWCRating control on the subform and the OWCRating on the main form are
not combo boxes. They are one of the data fields from the Tables.

I use a combo box to select a particular record from a Table and display
parts of that record on the main form. I then use a combo box on the suform
to select a particular record from a different Table and display parts of
that record on the subform.

In both case, once the form/subform is populated, I look at the controls
called OWCRating (to compare whether the OWCRating submitted by the race
entrant is equal to the OWCRating assigned by the Handicapping fleet). If
they are different, I'd like to be able to click a command button and move
the value on the subform over to the main form. I want to do this via a
command button because there may be cases where I don't want to change the
value.

In both cases, the data type is a "Number" but one is Integer/Auto decimal
and the other is Double with zero decimal. Do these need to be exactly the
same ?

I can usually figure out how to do everytrhing using MS Access Macros, but
this one has me stumped.

I'm not a Visual Basic programmer and just don't understand the code you
provided.

MS access Help seems to imply that I can do this via SetValue.

Does it matter that my Form names are EDIT IMCOMPLETE ENTRIES and FIND PHRF
(separated words and in caps)? Should I change the Form names to
EditIncompleteEntries and FindPhrf ? Does it really matter?

When I follow the example in MS Help, I get an error message that says I'm
trying to reference an OLE object that isn't an OLE object. I don't know what
an OLE object is.

Thanks for your replies. This is a nice forum.

I hope you can help further...

Tom Wickerath said:
Hi Paul,

Are both controls named OWCRating (the one on the main form and the one on
the subform) combo boxes? If so, the first thing you will need to verify is
that the fields that serve as the Control Source for each combo box are the
same data type and field size, since you are trying to copy data from one to
the other. I can't say that I've ever tried to do anything similar, but you
might try air code like this behind the command button on the main form. I
consider this "air code" because I have not tested it. In the code below,
[SubformContainerName] refers to the name of the control that holds the
subform.

Private Sub cmdCopyRec_Click()
On Error GoTo ProcError

Me.OWCRating = [SubformContainerName].[Form].[OWCRating]

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyRec_Click..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

pauld said:
Tom,

Thanks for your suggestion.

Let me provide a bit more info on what these controls are...

The control on the subform is called OWCRating and is displayed on the
subform as a result of selecting a specific record from the Table (named
PHRF) via a combo box.

The control on the mainform is also called OWCRating and it is selected from
another Table (named ENTRIES) via a combo box and is displayed on the
mainform.

These are not "text" boxes. They are controls that display a data field of a
record from the Table.

I simply want to be able to change the value of the OWCRating on the
mainform (which is displaying the specific record from the ENTRIES Table) if
the value of the OWCRating on the subform (which is displaying a specific
record from the PHRF Table) is different. I want to do this via a command
button rather than automate it because there may be times when having
different values is OK
Click to expand...
Click to expand...
 
T

Tom Wickerath

Hi Paul,
The OWCRating control on the subform and the OWCRating on the main form are
not combo boxes. They are one of the data fields from the Tables.
Click to expand...

What type of controls are you using to display the OWCRating data on the
main form and the subform? Previously, you stated "These are not "text"
boxes", and you've just told me that they are not combo boxes. So what type
of control are you using? My guess at this point is that they are indeed text
box controls.
In both cases, the data type is a "Number" but one is Integer/Auto decimal
and the other is Double with zero decimal. Do these need to be exactly the
same ?
Click to expand...

Well, certainly you cannot stuff a double that exceeds the maximum possible
value for an integer into a field with field size integer. You most likely do
not need the one to be a Double; I would change that one to Integer.
I can usually figure out how to do everytrhing using MS Access Macros, but
this one has me stumped.
Click to expand...

Sorry, but I try to stay as far away from macros as I can. In Access 2003
and all prior versions, one cannot trap for errors and handle them gracefully
when using macros. Any errors that occur will cause a really ugly macro error
dialog to be presented to your users. The newest version of Access, Access
2007 allows for error trapping, so macros are more appropriate to consider
using for Access applications created with Access 2007. Consider the
following quote:

From: "Inside Relational Databases, 2nd Edition, by Mark Whitehorn and Bill
Marklyn, published by Springer, p 151

"Macros offer the next level down, extending the functionality of the GUI.
Macros are still limited, however, and do not provide anything like the
enormous flexibility of a programming language. Both the macro and the
programming languages take some effort to learn and, surprisingly, often
require relatively different skills; in other words, a good working knowledge
of macros may not make it much easier to convert to using the programming
language. Perhaps even more surprisingly, I do not believe that programming
is fundamentally more difficult to learn. Macros are easier to use but not by
orders of magnitude."

"If you are new to RDBMSs, I suggest (with as much deference as possible)
that you may well not be in a position to judge whether you need macros or
programming. In that case, my advice is clear. Unless you are sure that your
needs really are simple, don't bother learning to use macros. Once you find
that you need more than the GUI offers, go straight to the programming
language. In this way you avoid the pain of climbing one learning curve only
to discover that the view from the top is unsatisfactory and another climb
awaits you."

I'm not a Visual Basic programmer and just don't understand the code you
provided.
Click to expand...

Try Access Basics, written by Access MVP Crystal:
http://www.accessmvp.com/Strive4Peace/Index.htm

MS access Help seems to imply that I can do this via SetValue.
Click to expand...

Don't know on that one.
Does it matter that my Form names are EDIT IMCOMPLETE ENTRIES and
FIND PHRF (separated words and in caps)? Should I change the Form
names to EditIncompleteEntries and FindPhrf ? Does it really matter?
Click to expand...

This should not matter. However, I encourage you to avoid using special
characters, including spaces, in anything that you assign a name to in
Access. You will be doing yourself a favor in the long run to adopt more
standard naming conventions.

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


Did you try the code I suggested, even if you may not understand it?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
P

pauld

Sorry...I guess I was thinking about text boxes that are create for
calculation purposes.

The OWCRating on the mainform and subform are both text boxes.

I've used the SetValue macro many times (for example to set a field to
"done" or or a particular number) where the Item is a particular field and
the Expression is (for example "DONE")

I will change the formats of both OWCRatings to Integer.

I'd like to try the code you provided but where and how do I do that?
 
N

Nicholas Meyer

gj
pauld said:
The OWCRating control on the subform and the OWCRating on the main form
are
not combo boxes. They are one of the data fields from the Tables.

I use a combo box to select a particular record from a Table and display
parts of that record on the main form. I then use a combo box on the
suform
to select a particular record from a different Table and display parts of
that record on the subform.

In both case, once the form/subform is populated, I look at the controls
called OWCRating (to compare whether the OWCRating submitted by the race
entrant is equal to the OWCRating assigned by the Handicapping fleet). If
they are different, I'd like to be able to click a command button and move
the value on the subform over to the main form. I want to do this via a
command button because there may be cases where I don't want to change the
value.

In both cases, the data type is a "Number" but one is Integer/Auto decimal
and the other is Double with zero decimal. Do these need to be exactly the
same ?

I can usually figure out how to do everytrhing using MS Access Macros, but
this one has me stumped.

I'm not a Visual Basic programmer and just don't understand the code you
provided.

MS access Help seems to imply that I can do this via SetValue.

Does it matter that my Form names are EDIT IMCOMPLETE ENTRIES and FIND
PHRF
(separated words and in caps)? Should I change the Form names to
EditIncompleteEntries and FindPhrf ? Does it really matter?

When I follow the example in MS Help, I get an error message that says I'm
trying to reference an OLE object that isn't an OLE object. I don't know
what
an OLE object is.

Thanks for your replies. This is a nice forum.

I hope you can help further...

Tom Wickerath said:
Hi Paul,

Are both controls named OWCRating (the one on the main form and the one
on
the subform) combo boxes? If so, the first thing you will need to verify
is
that the fields that serve as the Control Source for each combo box are
the
same data type and field size, since you are trying to copy data from one
to
the other. I can't say that I've ever tried to do anything similar, but
you
might try air code like this behind the command button on the main form.
I
consider this "air code" because I have not tested it. In the code below,
[SubformContainerName] refers to the name of the control that holds the
subform.

Private Sub cmdCopyRec_Click()
On Error GoTo ProcError

Me.OWCRating = [SubformContainerName].[Form].[OWCRating]

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyRec_Click..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

pauld said:
Tom,

Thanks for your suggestion.

Let me provide a bit more info on what these controls are...

The control on the subform is called OWCRating and is displayed on the
subform as a result of selecting a specific record from the Table
(named
PHRF) via a combo box.

The control on the mainform is also called OWCRating and it is selected
from
another Table (named ENTRIES) via a combo box and is displayed on the
mainform.

These are not "text" boxes. They are controls that display a data field
of a
record from the Table.

I simply want to be able to change the value of the OWCRating on the
mainform (which is displaying the specific record from the ENTRIES
Table) if
the value of the OWCRating on the subform (which is displaying a
specific
record from the PHRF Table) is different. I want to do this via a
command
button rather than automate it because there may be times when having
different values is OK
Click to expand...
Click to expand...
Click to expand...
 
P

pauld

Many thanks for the assistance.

I discovered what I'll call a "cockpit error" in that I was not using the
Control Name of the SubForm, but the actual Form name.

Once I used the Control Name, the SetValue Macro worked according the Access
Help.

Again, thanks for the help !

Nicholas Meyer said:
gj
pauld said:
The OWCRating control on the subform and the OWCRating on the main form
are
not combo boxes. They are one of the data fields from the Tables.

I use a combo box to select a particular record from a Table and display
parts of that record on the main form. I then use a combo box on the
suform
to select a particular record from a different Table and display parts of
that record on the subform.

In both case, once the form/subform is populated, I look at the controls
called OWCRating (to compare whether the OWCRating submitted by the race
entrant is equal to the OWCRating assigned by the Handicapping fleet). If
they are different, I'd like to be able to click a command button and move
the value on the subform over to the main form. I want to do this via a
command button because there may be cases where I don't want to change the
value.

In both cases, the data type is a "Number" but one is Integer/Auto decimal
and the other is Double with zero decimal. Do these need to be exactly the
same ?

I can usually figure out how to do everytrhing using MS Access Macros, but
this one has me stumped.

I'm not a Visual Basic programmer and just don't understand the code you
provided.

MS access Help seems to imply that I can do this via SetValue.

Does it matter that my Form names are EDIT IMCOMPLETE ENTRIES and FIND
PHRF
(separated words and in caps)? Should I change the Form names to
EditIncompleteEntries and FindPhrf ? Does it really matter?

When I follow the example in MS Help, I get an error message that says I'm
trying to reference an OLE object that isn't an OLE object. I don't know
what
an OLE object is.

Thanks for your replies. This is a nice forum.

I hope you can help further...

Tom Wickerath said:
Hi Paul,

Are both controls named OWCRating (the one on the main form and the one
on
the subform) combo boxes? If so, the first thing you will need to verify
is
that the fields that serve as the Control Source for each combo box are
the
same data type and field size, since you are trying to copy data from one
to
the other. I can't say that I've ever tried to do anything similar, but
you
might try air code like this behind the command button on the main form.
I
consider this "air code" because I have not tested it. In the code below,
[SubformContainerName] refers to the name of the control that holds the
subform.

Private Sub cmdCopyRec_Click()
On Error GoTo ProcError

Me.OWCRating = [SubformContainerName].[Form].[OWCRating]

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyRec_Click..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom,

Thanks for your suggestion.

Let me provide a bit more info on what these controls are...

The control on the subform is called OWCRating and is displayed on the
subform as a result of selecting a specific record from the Table
(named
PHRF) via a combo box.

The control on the mainform is also called OWCRating and it is selected
from
another Table (named ENTRIES) via a combo box and is displayed on the
mainform.

These are not "text" boxes. They are controls that display a data field
of a
record from the Table.

I simply want to be able to change the value of the OWCRating on the
mainform (which is displaying the specific record from the ENTRIES
Table) if
the value of the OWCRating on the subform (which is displaying a
specific
record from the PHRF Table) is different. I want to do this via a
command
button rather than automate it because there may be times when having
different values is OK
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Populate main form from subform record 0
Drop down list control value in vba 2
Problem with form--subform 5
edit button for main and subform 1
How to choose a form record based on selection from a subform? 6
Referencing Subform Controls/Properties 2007 3
Link subform ID control 3
Subform - Access 2003 2

Top