VBA TO REFER TO ANOTHER CELL

[dubiousp]

Morning Im trying to use dbl click to copy a value to another form

Private Sub Heart_Rate_DblClick(Cancel As Integer)

Dim OV As String
Dim HR As String

OV = Me.O2.Value
HR = Me.Heart_Rate.Value

'Me!Subform1.Form.RecordSource

Me!NEWS2.Pulse = HR


Pulse is located on a sub form called news2, so from the above Me! is current form

 

[The_Doc_Man]

If the item you want is in a subform, the subform control has a name that can (and SHOULD) differ from the name of the form loaded to that control. You came close with the item that was commented out, but you apparently got confused. I think there is something else going on in that fragment you showed, so I can't be sure. The syntax of Me!NEWS.Pulse cannot work because whatever NEWS is, syntactically it has to be a control and I know of no standard Access form controls having a property called "pulse." That can't work unless you created a custom object for that purpose. I'm betting you didn't do that.

For the code in the main form to get to the pulse in the subform you might use Me!Subform1.Form.Pulse - as long as that subform isn't in continuous mode or datatsheet view. If you are looking at multiple records at once in the sub-form, you do better to use some other method of specifying which one you need.

 

[dubiousp]

Good morning thank you for the responses, as you probally gathered this is a new world Im dabbling with. The information being entered firstly is a set of observations related to a patient which would be heart rate, oxygen pluse a number of others. These are being entered into a data sheet view as I felt this the easiest way for a medic to view change, the news is a seperate datasheet that uses similar values to the observations,

My thought process was that on occassions if I wanted to transfer the heart rate or oxygen value I could do with a double click

 

[dubiousp]

The main form is incident and patient information, observations and news are a 2nd action the db will eventually have a record set in the event of medical resus..

NEWS2 may or maynot be used depending on the situation hence why if a set of observations were carried out a doube click would only transfer certain values

I have tried below but beleive of on the wrong track

Private Sub O2_DblClick(Cancel As Integer)
Dim strFrmName As String
Dim OO As Integer

OO = O2
strFrmName = "NEWS2"
DoCmd.OpenForm NEWS2, acNormal
With Forms(NEWS2)

SPO2_Value_1 = O2



End With



End Sub

 

[arnelgp]

Private Sub O2_DblClick(Cancel As Integer)
    Dim OV As String
    Dim HR As String
    Dim subForm As Form
    Dim tim As Variant

    OV = Me.O2.Value
    HR = Me.Heart_Rate.Value
    tim = Me![Time].Value

    'Me!Subform1.Form.RecordSource

    Set subForm = Me.Parent![NEWS2 subform].Form
    With subForm.RecordsetClone
        .AddNew
        ![N2 TIME] = tim
        !Pulse = HR
        .Update
        subForm.Bookmark = .LastModified
    End With
    Set subForm = Nothing

End Sub

 

[dubiousp]

WOW many thanks worked a treat, may I just ask another question although it may need another thread

The news2 table calculates a total based on change of cell which works on entering value manually.. Using this approach the calculation does not take place unless I re enter the value manually

 

[arnelgp]

on [NEWS2 subform], change this from:

Private Sub Pulse_AfterUpdate()

to:

Public Sub Pulse_AfterUpdate()

now go back to Observations subform, and change the code to call
the Public Sub:

Private Sub O2_DblClick(Cancel As Integer)
    Dim OV As String
    Dim HR As String
    Dim subForm As Form
    Dim tim As Variant

    OV = Me.O2.Value
    HR = Me.Heart_Rate.Value
    tim = Me![Time].Value

    'Me!Subform1.Form.RecordSource

    Set subForm = Me.Parent![NEWS2 subform].Form
    With subForm.RecordsetClone
        .AddNew
        ![N2 TIME] = tim
        !Pulse = HR
        .Update
        subForm.Bookmark = .LastModified
    End With
    subForm.Pulse_AfterUpdate
    Set subForm = Nothing

End Sub

 

[dubiousp]

End With subForm.Pulse_AfterUpdate Set subForm = Nothing

t

Thanks vey much works a treat,, if i chose to covert to a button operation would it be as simple as copy the code to one click btn

 

[arnelgp]

you are correct, you just Move the code to the command button.