hi all,
I am working with location data in DMS (degrees minutes seconds), where each part has its own field: fldCentroidlatitudedegrees, fldCentroidlatitudeminutes, etc. Get the idea? I would like to automatically calculate Decimal Degrees based on the values in the aforementioned fields. I have written this formula, which works in a query:
fldLatDecCalculated: ((((tblSite.fldCentroidlatitudesec/60)+tblSite.fldCentroidlatitudemin)/60)+tblSite.fldCentroidlatitudedeg)*(-1)
I have created two new fields to store this data: fldLatDec and fldLongDec; both are nvarchar(50).

Based on another example, this is my plan for Latitude in decimal degrees:
1. In the form, ensure fldLatDec is fldLatDec and include fldLatDecCalculated (and the formula);
2. In VBA, write fldLatDec = fldLatDecCalculated;
3. Write an update query to update fldLatDec for all sites in the database;
4. Repeat for longitude.


1. frmSites (in SQL, important bit in bold):
SELECT tblSite.fldSiteID, tblSite.fldSiteCode, tblSite.fldSiteName, tblSite.fldFieldBoreCodes, tblSite.fldwetlandtype, tblSite.fldAlternativesitecode, tblSite.fldCentroidlatitudedeg, tblSite.fldCentroidlatitudemin, tblSite.fldCentroidlatitudesec, tblSite.fldCentroidlongitudedeg, tblSite.fldCentroidlongitudemin, tblSite.fldCentroidlongitudesec, tblSite.fldDistancetocoast, tblSite.fldAltitudeGPS, tblSite.fldAltitudeTOPO, tblSite.fldIBRAregion, tblSite.fldBrieflocation, tblSite.fldComments, tblSite.fldRivercatchment, tblSite.fldStrahlerStreamOrder, tblSite.[_fldProjectID], tblSite.fldDataImported, tblSite.[_fldSiteTypeID], tblSite.fldImportVersion, tblSite.fldImportID, tblSite.fldOreBody, tblSite.fldEasting, tblSite.fldNorthing, tblSite.fldProjection, tblSite.fldDatum, tblSite.fldZone, tblSite.fldDistance, tblSite.fldDirection, tblSite.fldLocality, tblSite.fldHabitat, ((((tblSite.fldCentroidlatitudesec/60)+tblSite.fldCentroidlatitudemin)/60)+tblSite.fldCentroidlatitudedeg)*(-1) AS fldLatDecCalculated, (((tblSite.fldCentroidlongitudesec/60)+tblSite.fldCentroidlongitudemin)/60)+tblSite.fldCentroidlongitudedeg AS fldLongDecCalculated, tblSite.fldLatDec AS fldLatDec, tblSite.fldLongDec AS fldLongDec
FROM tblSite
ORDER BY Left(fldSiteCode,minof(Len(fldSiteCode),3)), Val(Right(fldSiteCode,maxof(0,Len(fldSiteCode)-3))), tblSite.fldSiteName;
2. VBA in Form_frmSites
I based the idea on this example for another table, where I equate a number of fields to calculated expressions in Form_frmTaxon:
Private Sub Form_Current()
    Dim void As Variant
    
    void = handlebuttons(Me)
    Me.cmbQuickFind = Me.fldLowestIDNC
    SetEditing Me, AtNewRecord(Me)
    Me.fldLowestIDNC.SetFocus
    Me.fldLevelID.Value = Me.fldLevelIDCalculated.Value
    Me.fldSpeciesRAW.Value = Me.fldSpeciesRAWCalculated.Value
    Me.fldLoIDNotLetter.Value = Me.fldLoIDNotLetterCalculated.Value
    Me.fldLoIDsp.Value = Me.fldLoIDspCalculated.Value
    Me.fldSpecies.Value = Me.fldSpeciesCalculated.Value
    Me.fldLoIDStatus.Value = Me.fldLoIDStatusCalculated.Value
    
End Sub
In the example above, I found I had to add each of the links (equates) in this Private Sub and not create a new sub. Looking in Form_frmSites where I want to work with Latitude, similar code already exists:

Private Sub Form_Current()
    Dim i As Long
    
    void = handlebuttons(Me)
    Me.cmbQuickFind = Me.fldSiteID
    SetEditing Me, AtNewRecord(Me)
    Me.fldSiteCode.SetFocus
    If AtNewRecord(Me) Then
        If Me.cmbFilterProject = 0 Then
            Me.[_fldProjectID].DefaultValue = Me.[_fldProjectID].ItemData(0)
        Else
            Me.[_fldProjectID].DefaultValue = Me.cmbFilterProject
        End If
        Me.tabSubForms.Pages("pgBoreDetails").Visible = False
        Me.tabSubForms.Pages("pgCreateBoreAssociation").Visible = True
    Else
        If DCount("[fldBoreDetailsID]", "tblBoreDetails", "[_fldSiteID]=" & Me.fldSiteID) = 0 Then
            Me.tabSubForms.Pages("pgBoreDetails").Visible = False
            Me.tabSubForms.Pages("pgCreateBoreAssociation").Visible = True
        Else
            Me.tabSubForms.Pages("pgBoreDetails").Visible = True
            Me.tabSubForms.Pages("pgCreateBoreAssociation").Visible = False
        End If
        Me.frmSites_SubSiteVisits.Form.FilterOn = False
    End If
End Sub
I assumed I could simply add in my link (equates) - Me.fldLatDec.Value = Me.fldLatDecCalculated.Value - like so:
Private Sub Form_Current()
    Dim i As Long
    
    void = handlebuttons(Me)
    Me.cmbQuickFind = Me.fldSiteID
    SetEditing Me, AtNewRecord(Me)
    Me.fldSiteCode.SetFocus
    Me.fldLatDec.Value = Me.fldLatDecCalculated.Value
    If AtNewRecord(Me) Then
        If Me.cmbFilterProject = 0 Then
            Me.[_fldProjectID].DefaultValue = Me.[_fldProjectID].ItemData(0)
        Else
            Me.[_fldProjectID].DefaultValue = Me.cmbFilterProject
        End If
        Me.tabSubForms.Pages("pgBoreDetails").Visible = False
        Me.tabSubForms.Pages("pgCreateBoreAssociation").Visible = True
    Else
        If DCount("[fldBoreDetailsID]", "tblBoreDetails", "[_fldSiteID]=" & Me.fldSiteID) = 0 Then
            Me.tabSubForms.Pages("pgBoreDetails").Visible = False
            Me.tabSubForms.Pages("pgCreateBoreAssociation").Visible = True
        Else
            Me.tabSubForms.Pages("pgBoreDetails").Visible = True
            Me.tabSubForms.Pages("pgCreateBoreAssociation").Visible = False
        End If
        Me.frmSites_SubSiteVisits.Form.FilterOn = False
    End If
End Sub

This gave me a run-time error '2465': Samples Database can't find the field 'fldLatDecCalculated' referred to in your expression. I think this is odd because as I typed in the code, it recognised the name fldLatDecCalculated.
So I thought, in the taxon example the Private Sub sets the focus on the primary key (Me.fldLowestIDNC.SetFocus), whereas this one moves it to fldSiteCode, which must be for the purpose of the existing code. So I tried setting the focus to the primary key - Me.fldSiteID.SetFocus:

Private Sub Form_Current()
    Dim i As Long
    
    void = handlebuttons(Me)
    Me.cmbQuickFind = Me.fldSiteID
    SetEditing Me, AtNewRecord(Me)
    Me.fldSiteCode.SetFocus
    If AtNewRecord(Me) Then
        If Me.cmbFilterProject = 0 Then
            Me.[_fldProjectID].DefaultValue = Me.[_fldProjectID].ItemData(0)
        Else
            Me.[_fldProjectID].DefaultValue = Me.cmbFilterProject
        End If
        Me.tabSubForms.Pages("pgBoreDetails").Visible = False
        Me.tabSubForms.Pages("pgCreateBoreAssociation").Visible = True
    Else
        If DCount("[fldBoreDetailsID]", "tblBoreDetails", "[_fldSiteID]=" & Me.fldSiteID) = 0 Then
            Me.tabSubForms.Pages("pgBoreDetails").Visible = False
            Me.tabSubForms.Pages("pgCreateBoreAssociation").Visible = True
        Else
            Me.tabSubForms.Pages("pgBoreDetails").Visible = True
            Me.tabSubForms.Pages("pgCreateBoreAssociation").Visible = False
        End If
        Me.frmSites_SubSiteVisits.Form.FilterOn = False
     End If
     Me.fldSiteID.SetFocus
     Me.fldLatDec.Value = Me.fldLatDecCalculated.Value
End Sub
... run-time error '2110': Samples Database can't move focus to the control fldSiteID.
What now? Maybe the Dim bit, but I am rather out of my depth to what that means.