Error 438 on ActiveControl.Value

[oxicottin]

Hello, I have a form that has a control and its value is a decimal. I want to move that value to the control I opened from and I keep getting an error:

Run-time error '438':
Object doesn't support this property or method

Debug takes me to my only vba im using on the button on the form.

Private Sub Command45_Click()
Screen.ActiveControl.Value = txtDecimal
End Sub

Any ideas? Thanks....

Thanks,
SoggyCashew.....

 

[combo]

Try [tt]Msgbox Screen.ActiveControl.Name[/tt] first to check what is active.

combo

 

[Andrzejek]

Wouldn't [tt]Command45[/tt] command button be the ActiveControl since user just clicked on it?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

Wouldn't Command45 command button be the ActiveControl since user just clicked on it?

Yup, and thus the error. A command button does not have a value property and hence 438.

Can you explain in detail what you mean by this?

I want to move that value to the control I opened from

 

[oxicottin]

Ok, I have a form and on that form there is a text box that I want to use a picker to calculate time difference. I have a double click event on that text box that opens a form and I do some calculations on the form by pressing a button and the results is then given out to two text boxes one being (txtDecimal). Now I want to move that result back to the form and textbox I started out with by pressing a button. Below is what I have:

Private Sub cmdMoveToMain_Click()
Dim sDecimal As Double
sDecimal = Me.txtDecimal

Screen.ActiveControl.Value = sDecimal
End Sub

And I still get an error 438. Both values are a decimal value. Thanks....

Thanks,
SoggyCashew.....

 

[MajP]

can you post the code in the double click event to open the form? There are several possible ways to do this depending on how you open the form.

 

[oxicottin]

Ok here is the sample. Start off with the main form and double click in the text box. You have to select a start time and an end time then hit the calculate button to populate the values needed. Next hit the move to form button and that gives me the error. Thanks..

Thanks,
SoggyCashew.....

 

[MajP]

Here is a generic function to get a value from a popup.

Public Function getValueFromPopUp(formName As String, PopUpControlName As String) As Variant
  'FormName: Name of the popup form
  'PopupControlName: Name of the control on the pop up/dialog that you want the value
  Dim frm As Access.Form
  DoCmd.OpenForm formName, , , , acFormEdit, acDialog
  'wait until form is closed or hidden
  'The popup needs an OK button that hides the popup(me.visible = false), and a Cancel button that just closes it
  If CurrentProject.AllForms(formName).IsLoaded Then
    Set frm = Forms(formName)
    getValueFromPopUp = frm.Controls(PopUpControlName).Value
    DoCmd.Close acForm, formName
  End If
End Function

On your pop up form add a Ok and Cancel Button
Private Sub cmdOk_Click()
  Me.Visible = False
End Sub
Private Sub cmdCancel_Click()
  DoCmd.Close acForm, Me.Name
End Sub

Then call it like this

Private Sub Text0_Click()
  Me.Text0 = getValueFromPopUp("frm_CalDateTimeElapsed", "txtDecimal")
End Sub

 

[oxicottin]

Works perfect Thank you once again MajP....

Thanks,
SoggyCashew.....

 

[MajP]

Keep that procedure in your library, it is a very flexible. This way you do not have to hard-wire anything in the popup or calling form, and multiple forms and controls can use the same pop up form. This method requires the form to be open dialog.

If you do not want to open the popup dialog, I do it this way. I trap the close event of the popup form. Again, I do not have to hardwire anything in the popup allowing multiple forms and controls to use the same popup

Public WithEvents frm As Access.Form
Private Sub Text0_Click()
  DoCmd.OpenForm "frm_CalDateTimeElapsed"
  Set frm = Forms("frm_CalDateTimeElapsed")
End Sub
Private Sub frm_Close()
  'This traps the close event of the popup.
  Me.Text0 = frm.txtDecimal
End Sub

 

[oxicottin]

MajP, I have a question... Everything works but I ran into a scenario. The default for (Text0) is 10 and if I had changed it using the popup picker and then later decided to open the picker again and said na it doesn't need changed so I chose the cancel on the frm_CalDateTimeElapsed form then what happens is when the popup hides the (Text0) defaults back to 10 instead of staying the same number it was. I remember using .OldValue in another DB but I don't know how it would be of use.

HERE is a updated example with your code..

Thanks,

Thanks,
SoggyCashew.....

 

[MajP]

so do you want to pass the current value to the pop up?

 

[oxicottin]

can I do that? Are you meaning if the current value is 3.83 in the Text0 text box and I clicked on it and opened the popup frm_CalDateTimeElapsed it would show the values that I had prior? if that would work then yes.... But the way it is now if I close the popup using the cancel it would default the Text0 to 10 which is not what I want.

Thanks,
SoggyCashew.....

 

[MajP]

If you cancel out it should return null, but for some reason (I do not understand) it is returning an empty string "".

With that said, when you call it check if a value was returned or not. If not then do not set the value of the text0

 Dim returnedValue As Variant
 returnedValue = getValueFromPopUp("frm_CalDateTimeElapsed", "txtDecimal")
 If returnedValue & "" <> "" Then Me.Text0 = returnedValue

 

[oxicottin]

That worked for me not canceling out what I already had in my text0 thanks! I been messing around and I also found a way this is also working. I took this from a DB I used/use a long time ago and I don't know where I found the code.

In a module add:

Option Compare Database
Public gtxtDecimalTarget As TextBox    'Text box to return the Decimal to


Public Function DecimalFor(txt As TextBox)
'Purpose:   Open the popup form, identifying the text box to return the length to.
'Arguments: txt = the text box to return the decimal to.

    Set gtxtDecimalTarget = txt
    DoCmd.OpenForm "frm_CalDateTimeElapsed", windowmode:=acDialog

End Function

On the popup --- buttons OK and Cancel add:

Private Sub btnCancel_Click()
 DoCmd.Close acForm, Me.Name
End Sub

Private Sub btnOk_Click()

   On Error Resume Next
    'Purpose:   Transfer the result back to the calling text box (if there is one), and close.

    If Me.btnOk.Enabled Then
        If gtxtDecimalTarget = Me.txtDecimal Then
            'do nothing
        Else
            gtxtDecimalTarget = Me.txtDecimal
        End If
    End If
    gtxtDecimalTarget.SetFocus
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

And lastly on the controls On Click event just add =DecimalFor([YourTextBoxName])

Thanks,
SoggyCashew.....

 

[MajP]

Yes, you could always use a public variable do pass and return values from pop ups. It is a little less flexible in that you have to declare the public variable and hardwire the popup. so if you had a lot of different pop ups it gets a little harder to manage. However, with that approach you could return multiple values from the pop up. Declare multiple public variables, and set them
Else
gtxtDecimalTarget = Me.txtDecimal
someothervariable = me.someothercontrol
End If

 

[oxicottin]

MajP, one last question... (I hope) on this. When I move the value from frm_CalDateTimeElapsed txtDecimal textbox to the form MAIN textbox it looks fine but when I click in the textbox the value didn't move as a 2 decimal it moved like 8.4333333333333333333.

Now if I go to the textbox txtDecimal on frm_CalDateTimeElapsed and change the formula to =Round([txtDiff]/60,2) then it shows as a two decimal like I want it but I don't want to round so I tried =Int([txtDiff])/60 which gives me the number im looking after in in my MAIN form but not the saved number because when I click in the text box on my main form it shows 8.4333333333333333333 again. How can I only get two decimals using =Int([txtDiff])/60

Thanks,
SoggyCashew.....

 

[MajP]

=Int([txtDiff])/60
That is not going to work. Assume txtDiff = 5.345. If you take int of that you get 5. But 5/60 is going to give some repeating decimal. The reason the main form may show the number you want is that there is a format applied to the main form.
I do not understand when you say you do not want to round, it sounds like you do. The issue may be that there are different way to round (round up, round down, bankers rounding). Can you give an example of what you want, and why rounding does not work.

 

[oxicottin]

Ok, In the image below the hours/minutes is 9:35 which is correct but the decimal is 9.59 which is incorrect and the formula is =[txtDiff]/60 for the txtDecimal textbox. The correct answer I need is 9.58 BUT that's not it. If you look at the MAIN text box number when I double clicked in it to open the popup the true number showed 9.586666666667 that's why I tried the =Int([txtDiff])/60 because I don't want to round up I just want to move the decimal two places. Hope I explained it better...

The decimal is time Just like the Hours/Minutes and on my chart there is not 59 in decimals for 35 minutes but there is 58 that's why I don't want to round up...

Link

Thanks,
SoggyCashew.....

 

[MajP]

if you want to round down something to 2 decimal places. Multiply it first by 100 and take the int of that. Then multiply back by 100
9.58667 * 100 = 958,667
int of that = 958
958 / 100 = 9.58