cuzza_fin
11-13-2013, 06:47 PM
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.
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.