VBA TO REFER TO ANOTHER CELL (1 Viewer)

dubiousp

Member
Local time
Today, 10:43
Joined
Mar 6, 2020
Messages
86
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

Immoderate Moderator
Staff member
Local time
Today, 05:43
Joined
Feb 28, 2001
Messages
26,996
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:43
Joined
Feb 19, 2002
Messages
42,970
The implication of a subform regardless of whether it is in single, continuous, or data sheet view is that the subform has multiple records related to the record visible on the main form and in which your code is running. The point is that when you put a value into a subform control from code in the main form, you must know ABSOLUTELY which record the subform is positioned on otherwise you will update the wrong record. All forms have a CURRENT record. If the subform is in single view, the record you see is the one that is current and so that is the one that would be updated. However, when the subform is in continuous or ds view, the current record may not actually be even visible in the subform since you can scroll without changing the record pointer. If your subform shows the record Selector, you have a visual clue since the RecordSelector will show a right facing triangle on the row that Access considers current. If that record is dirty, the RecordSelector will show a pencil and if that record is locked by a different process, the RecordSelector will be a circle with a line through it.

To summarize, you probably don't want to be running code in the main form to update the current record of the subform. It is just too easy to update the wrong record.

PS. There is no such thing as a cell in a relational database. That is strictly a spreadsheet concept. A cell is the intersection of a row and a column and has a specific address since the spreadsheet is a two dimensional "flat" object. In a database application you would use a query to select the row based on some unique criteria and then refer to the column name. This is the essence of the difference in approach between coding in a spreadsheet and coding in VBA. In VBA, your references are always relative. Ie. I refer to Me.CustomerID in the code but the assumption is that there is only a single row current at any point in time and that is the row I am operating on.
 
Last edited:

dubiousp

Member
Local time
Today, 10:43
Joined
Mar 6, 2020
Messages
86
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:43
Joined
Feb 19, 2002
Messages
42,970
All the observations should be entered directly into the subform because each observation should generate a new subform record. Why are they being entered into the main form?
 

dubiousp

Member
Local time
Today, 10:43
Joined
Mar 6, 2020
Messages
86
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:43
Joined
May 7, 2009
Messages
19,169
Code:
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

Member
Local time
Today, 10:43
Joined
Mar 6, 2020
Messages
86
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:43
Joined
May 7, 2009
Messages
19,169
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:
Code:
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

Member
Local time
Today, 10:43
Joined
Mar 6, 2020
Messages
86
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:43
Joined
Feb 19, 2002
Messages
42,970
If you are storing data in TWO tables, you have a design flaw. Just because arne loaded your gun with bullets doesn't mean that you are doing this correctly.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:43
Joined
May 7, 2009
Messages
19,169
you are correct, you just Move the code to the command button.
 

Users who are viewing this thread

Top Bottom