MS Access - Pull data from one form to another?

Question

Hey Y'all

I'm trying to make data from a text box on one form, "My Profile" copy over to another form, "Today's Report".  Is this possible?  I have tried all sorts of things but the farthest I have gotten is the data that I want pops up for a second and then is replaced with a #Name error.   Any suggestions?  I am super new to access so any help would be greatly appreciated.

This is what I put into the txtbox on the "Today's Report" form.

=([Forms]![My Profile]![txtProteinCalories].[Value])

Answer 1

Try the common syntax:

=[Forms]![My Profile]![txtProteinCalories]

Answer 2

All text boxes are controls; it's just the generic name for the various types of object in a form or report; text boxes, list boxes combo boxes, labels, buttons etc.

As you want to pass only one value to the Today's Report form it would be overkill to use the basArgs module, as it's specifically designed for passing multiple values.  In your case you can do it as the OpenArgs option of the OpenForm method.  You'll need to use VBA code for this, not a macro, as the OpenForm macro action does not include an OpenArgs option.  So, let's say you want to open the Today's Report form with a button on the My Profile form, and at the same time close the My profile form, you'd first add a command button to the My Profile form in form design view, and then put the following code in its Click event procedure:

    DoCmd.OpenForm "Today's Report", OpenArgs:=Me.txtProteinCalories

    DoCmd.Close acForm, Me.Name

If you want the My Profile form to remain open when the Today's Report form opens, omit the second line above.

Then, let's say you want to assign the value to a text box control also named txtProteinCalories in the Today's Report form, you'd put the following code in that form's Load event procedure:

    If Not IsNull(Me.OpenArgs) Then

        Me.txtProteinCalories = Me.OpenArgs

    End If

The Me keyword in the above code is just a shorthand way of referring to the form in whose module the code is running.  Strictly speaking it’s a reference to the current instance of the class, but we don't need to worry about that here.

If you are unfamiliar with entering code into a form's, report's, report section's  or control's event procedures, this is how it's done in form or report design view:

1.  Select the form, report, section or control as appropriate and open its properties sheet if it's not already open.

2.  Select the relevant event property in the Event tab, and select the 'build' button (the one on the right with 3 dots).

3.  Select Code Builder in the dialogue and click OK.  This step won't be necessary if you've set up Access to use event procedures by default.

4.  The VBA editor window will open at the event procedure with the first and last lines already in place.  Enter or paste in the code as new line(s) between these.

Answer 3

So do I need to put these "Add args" and "DoCmd" inside of the text boxes?  Or do I need to put them inside macros or something?

And what's the difference between just a regular textbox and a control?

I'm sorry I'm so ignorant.

And yes, I have already done all the calculations in a text box on form "My Profile" and I need to put that value into "Today's Report".  So does that mean I only need the bottom portion?  

I'm kind of a layman when it comes to this stuff.

Thank you so much for all your comments Ken!  You rock!

Answer 4

In mathematics the term argument refers to an independent quantity on which the value of a function depends, or on which an operator acts.  In Access it means much the same, but as well as being passed into a function, whether built in or user-defined, it can also be passed to a form or report in which the value is used in some way.  This appears to be what you are attempting, and Access provides an OpenArgs (opening arguments) mechanism to do this when opening a form or report.

The OpenArgs option of the OpenForm method is a single value, which can be of any data type, so if you need to pass more than one value into a form it is necessary to parse the value into its component values.  The basArgs module in my demo provides a way to do this without having to write any parsing code yourself.  You simply have to paste the module into your database, and then call it as described in the demo's opening form.  I'd recommend passing named arguments.

You do not have to input anything for this at runtime, the arguments are taken from the values in the calling form.  In my demo the name of each argument is taken from a text box, but you'd name it in the code.  Let's say you wish to pass two values named Foo and Bar, whose values are in txtFoo and txtBar text box controls in the form, and then pass these to a form named frmFooBar, at the same closing the current form, the code would be:

    AddArg args, "Foo", Me.txtFoo

    AdArg args, "Bar", Me.txtBar

    DoCmd.OpenForm "frmFooBar", OpenArgs:=args

    DoCmd.Close acForm, Me.Name

In the frmFooBar form the string expression passed  into the form becomes the value of the form's OpenArgs property, form which you then have to extract the individual values.  Let's same you want to multiply the values of Foo and Bar and add the result to the value in a txtMyNumber, and insert the result into a control txtMyResult, then the code would be like this:

   Dim intFoo as Integer

   Dim intBar As Integer

   intFoo = Arg(args, "Foo")

   intBar = Arg(args, "Bar")

   Me.txtResult = (intFoo*inBar)+Me.txtMyNumber

If the calling is open then you could of course reference the txtFoo and txtBar controls in the calling form, but you cannot do so if it has been closed, as appears to be so in your case on the basis of the behaviour you are experiencing.  You have to pass the values to the form, as above.  With only two values it would be a simple task to parse a value passed as a simple comma-delimited list, without the use of the basArgs module, but where it is necessary to pass more values the module provides an easy way to do so.

If you only need to pass a single value, then you can simply pass it as the OpenArgs option of the OpenForm method of course, e.g.

    DoCmd.OpenForm "frmFoo", OpenArgs:=Me.txtFoo

and then reference the OpenArgs property of the frmFoo form anywhere in that form to get the value.

Answer 5

It sounds like your form "My Profile" is closed.

You can only pull values from a form when it is open.