none
redirecting a calculated expression to known expression (runtime 2465 & 2110) RRS feed

  • Question

  • 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:
    [CODE]fldLatDecCalculated: ((((tblSite.fldCentroidlatitudesec/60)+tblSite.fldCentroidlatitudemin)/60)+tblSite.fldCentroidlatitudedeg)*(-1)[/CODE]
    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):
    [CODE]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;
    [/CODE]

    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:
    [CODE]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[/CODE]

    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:

    [CODE]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[/CODE]

    I assumed I could simply add in my link (equates) - Me.fldLatDec.Value = Me.fldLatDecCalculated.Value - like so:
    [CODE]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[/CODE]


    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:

    [CODE]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[/CODE]

    ... 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.
    Thursday, November 14, 2013 1:48 AM