Use form values in another form (1 Viewer)

[ajudante]

I have form1 collecting two values. form2 has calculations that refer to the values collected in form1. For example: [Forms]![form1]![field1]+[Forms]![form1]![field2]. But this only works when form1 is open. Once form1 is closed, all calculations in form2 give an error (#Name?). I am an Access beginner and obviously there is something I am missing. I spent 8 hours yesterday reading Access forums but unfortunately I still can't make this work as I couldn't find a clear solution. Any clues?

 

[arnelgp]

save the value of the textbox to a Global variable or a Tempvar then use these variable in your calculation.
or you can have a Query, based on the table of form1, with calculated column that will show the calculation.
or use dLookup() function in your calculation.

 

[CJ_London]

I am an Access beginner and obviously there is something I am missing

not really, you can only reference forms that are open. Does form 2 need to be opened without any reference to form1? i.e. the fields should be blank or hidden?

Perhaps explain exactly what you want to do, rather than how you are trying to do it. There may be other ways

 

[MajP]

Their are a couple ways to do it. One way is like Arnelgp does with a global or tempvar.
I do the same technique all the time and works on pop ups.
1) I put a OK, Cancel button on the pop up
2) OK makes the form inivisible but open, Cancel closes the form
3)The calling form looks to see if the pop up is loaded (even if invisible)
4)If loaded then take the values (because you hit OK), Then close the form invisible but open popup.
5)If not loaded then do nothing because the form is closed and the user hit cancel.

 

[Uncle Gizmo]

Both Arnel and Colin (CJ) have given you excellent information. The problem is when the form is closed then you lose access to the information within that form. Arnel's suggestion is to put the information somewhere else, and Colin's suggestion is not to close the form. Only you can decide which is the best way to go. If you want more help, then provide a more detailed explanation of what you are doing. It is more than likely it's been done before and we can point you in the right direction.

 

[ajudante]

Thank you for all your quick replies. However, I am a beginner. How do I create a global variable, how do I save it in form1 and how to I use it in form2? Let me be clearer what I want to do. form1 has textbox1 and textbox2 asking user input. It also has a button that the user clicks after filling the boxes. The button closes form1 and opens form2. form2 has textbox3 and textbox4 asking more user input, and then textbox5 and textbox6 with formulas referencing the other 4 textboxes. But how to I reference textbox1 and textbox2 so that the formulas work after form2 is closed?

 

[CJ_London]

ok anothe roption is to use the openargs parameter for docmd.openform. You can populate that with your text1 and text2 values from form 1 and when form 2 opens it references the openargs property

docmd.openform "form2",,,,,Text1 & ":" & text2

and in form2 open event

tbox1=split(openargs,":")(0)
tbox2=split(openargs,":")(1)

 

[arnelgp]

goto VBA, then add a module. add this code

option compare database

Global gbl_number1 as double
Global gbl_number2 as double

***
on the Click Event of form1 (that closes the form and open form2) add this code before opening form2:

gbl_number1 = Val(Me.textbox1 & "")
gbl_number2 = Val(Me.textbox2 & "")

docmd.openform "form2"
docmd.close acForm, me.Name

change the calculation of form2 and use the global variable in your calculation.

 

[ajudante]

goto VBA, then add a module. add this code

option compare database

Global gbl_number1 as double
Global gbl_number2 as double

***
on the Click Event of form1 (that closes the form and open form2) add this code before opening form2:

gbl_number1 = Val(Me.textbox1 & "")
gbl_number2 = Val(Me.textbox2 & "")

docmd.openform "form2"
docmd.close acForm, me.Name

change the calculation of form2 and use the global variable in your calculation.

Thanks. I did this, but I still get a #Name? error in textbox5 and textbox6. How do I reference the global variable in form2? Now I have in textbox5: "=[gbl_number1]". But I get #Name?..

 

[ajudante]

ok anothe roption is to use the openargs parameter for docmd.openform. You can populate that with your text1 and text2 values from form 1 and when form 2 opens it references the openargs property

docmd.openform "form2",,,,,Text1 & ":" & text2

and in form2 open event

tbox1=split(openargs,":")(0)
tbox2=split(openargs,":")(1)

Thanks. But do you mean this:

docmd.openform "form2",,,,,textbox1 & ":" & textbox2

and in form2 open event

textbox1=split(openargs,":")(0)

textbox2=split(openargs,":")(1)

Or do you really mean Text1 and tbox1, rather than textbox1?

If it's the code I attach above, it gives me a Run-time error 13 Type mismatch

 

[CJ_London]

I don't know what you are calling your controls and fields - if your controls are really called textbox1 and textbox2 then yes - but I would advise you give them meaningful names so it will be easier to follow in a month or two when you have forgotten what you have done here

if it's the code I attach above, it gives me a Run-time error 13 Type mismatch

if textbox1 and textbox2 are numbers/dates etc then you need to convert to the correct datatype - see the functions clng, cdate, cint, cdbl etc

if these boxes are unbound then you need to include an = sign

textbox1="=" & split(openargs,":")(0)

 

[ajudante]

I don't know what you are calling your controls and fields - if your controls are really called textbox1 and textbox2 then yes - but I would advise giving them meaningful names and it will be easier to follow in a month or two when you have forgotten what you have done here

textbox1 and textbox2 are the names of the controls in form1. I want the values (i.e. numbers) that the user inserts in these textboxes to appear in textbox5 and textbox6 of form2, after form1 is closed. But the code I wrote above gives me a Run-time error 13 Type mismatch error

 

[ajudante]

So there are two possible solutions, but in both of them I am getting errors.

Solution 1 is a Global Variable, with code below.

In a VBA Module:

Option Compare Database
Global Var1 As Single
Global Var2 As Single

In form1 button:

Private Sub Continue_button_Click()
Var1 = Val(Me.textbox1 & "")
Var2 = Val(Me.textbox2 & "")
DoCmd.OpenForm "form2"
DoCmd.Close acForm, Me.Name
End Sub

In a form2 textbox:
=[Var1]
and in the other
=[Var2]

In the textboxes, I get the #Name? error


Solution 2 is OpenArgs, with code below

In the form1 button

Private Sub Continue_button_Click()
docmd.openform "form2",,,,,textbox1 & ":" & textbox2
doCmd.Close acForm, Me.Name
End Sub

and in the form2 load event

Var1=split(openargs,":")(0)
Var2=split(openargs,":")(1)

and in form2 textbox5
=[Var1]
and textbox6
=[Var2]

This gives me a Run-time error 13 Type mismatch error

In both solutions, there is something line of code that is wrong or some code missing, but I can't figure out what..

 

[MajP]

I want the values (i.e. numbers) that the user inserts in these textboxes to appear in textbox5 and textbox6 of form2, after form1 is closed. But the code I wrote above gives me a Run-time error 13 Type mismatch error

I would think using example 2

Var1=split(openargs,":")(0)
Var2=split(openargs,":")(1)

and in form2 textbox5
=[Var1]
and textbox6
=[Var2]

you just set the values of these textboxes. Not sure you need var1 and var2

Me.textBox5 = split(openargs,":")(0)
me.textbox6 =split(openargs,":")(1)

However open args passes a STRING, so you may need to cast it to not to get a type mismatch
something like (cdate(split(openargs,":")(0))
or
(cdbl(split(openargs,":")(0))

VBA and access are pretty good at figuring out the data type, but not always.

 

[ajudante]

I would think using example 2

you just set the values of these textboxes. Not sure you need var1 and var2

Me.textBox5 = split(openargs,":")(0)
me.textbox6 =split(openargs,":")(1)

However open args passes a STRING, so you may need to cast it to not to get a type mismatch
something like (cdate(split(openargs,":")(0))
or
(cdbl(split(openargs,":")(0))

VBA and access are pretty good at figuring out the data type, but not always.

Thanks. But where shall I apply the cbdl function? in form1 button? or in form2 load event? How?

I now have:

In the form1 button

Private Sub Continue_button_Click()
docmd.openform "form2",,,,,textbox1 & ":" & textbox2
doCmd.Close acForm, Me.Name
End Sub

and in the form2 load event

Me.textbox5= (cdbl(split(openargs,":")(0))
Me.textbox6 =(cdbl(split(openargs,":")(1))

and in form2 textbox5 control source
=[textbox5]
and textbox6
=[textbox6]

I still get Run-time error 13 Type mismatch error in the form1 button.

Also, if I define textbox5 in the load event, as in the code above, then what shall I write in the control source of the textbox itself. =textbox5??

 

[MajP]

I still get Run-time error 13 Type mismatch error in the form1 button.

Always tell us the exact line where the code breaks. Makes helping a lot easier.
docmd.OpenForm (FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)
"form2",view,filter,where,condition,datamode,windowmode,textbox1 & ":" & textbox2
"form2", , , , ,textbox1 & ":" & textbox2
Looks to me you are missing one comma so the arguments are in the windowmod and thus a type mismatch.

 

[MajP]

Also, if I define textbox5 in the load event, as in the code above, then what shall I write in the control source of the textbox itself. =textbox5??

I do not know what you want for a control source or if you want it unbound. The point is the value of the control can be set directly. No need to set it to a variable first. Further no way to reference that variable from the control, anyways.

 

[ajudante]

I do not know what you want for a control source or if you want it unbound. The point is the value of the control can be set directly. No need to set it to a variable first. Further no way to reference that variable from the control, anyways.

Thank you. Do you mean I dont need anything in the load event of form2 and should insert the split openargs formulas as the textbox5 and textbox7 control sources? As in:

In the form1 button

Private Sub Continue_button_Click()
docmd.openform "form2",,,,,,textbox1 & ":" & textbox2
doCmd.Close acForm, Me.Name
End Sub

and in form2 textbox5 control source
=(cdbl(split(openargs,":")(0)))
and textbox6
=(cdbl(split(openargs,":")(1)))

I get errors in textbox5 and 6: "The expression you entered has an invalid .(dot) or ! operator or invalid parentheses. You may have entered an invalid identifier or types parentheses following the Null constant."..

 

[MajP]

No you set the VALUE of the textboxes in the forms load event. If 5 and 6 are not bound to anything, do not set any control source. If they are bound to a field then the field is the control source. Do 5 and 6 relate to anything or are they unbound textboxes?

 

[ajudante]

No you set the VALUE of the textboxes in the forms load event. If 5 and 6 are not bound to anything, do not set any control source. If they are bound to a field then the field is the control source. Do 5 and 6 relate to anything or are they unbound textboxes?

Thank you

Let me make this clearer:

* In textbox5 of form2 I need the value that the user inserted in textbox1 of form1.

* In textbox6 of form2 I need the value that the user inserted in textbox2 of form1.

If I set the value of the form2 textboxes in the form2 load event I have:

In the form1 button

Private Sub Continue_button_Click()
docmd.openform "form2",,,,,textbox1 & ":" & textbox2
doCmd.Close acForm, Me.Name
End Sub

In the form2 load event

Me.textbox5= (cdbl(split(openargs,":")(0)))
Me.textbox6 =(cdbl(split(openargs,":")(1)))

And the form2 textbox5 and textbox6 control source is blank

The error I get now is Run-time error '94': Invalid use of Null, in line Me.textbox5= (cdbl(split(openargs,":")(0)))