I am not sure if this is in the right place. I could not find a forum for Visual Basic. Please move if need be.I am new to VBA I am trying to write some code which accesses external data in an oracle database and populates an attribute in one of my layers. I am having an issue checking to make sure the correct layer is selected. I do not want to have to select a feature then also select the layer in the TOC. Here is my current code
Sub SYNC_SVC_LOC()
Dim dnn As New ADODB.Connection
dnn.Open "Provider=msdaora;" & _
"Data Source=cstard;" & _
"User Id=xxxxx;" & _
"Password=xxxxxx"
Dim pDoc As IMxDocument
Dim pMap As IMap
Dim pClass As IFeatureClass
Dim pLayer As IFeatureLayer
Dim pFSel As IFeatureSelection
Dim pfeat As IFeature
Dim pSelSet As ISelectionSet
Dim pCur As IFeatureCursor
'Get the current map
Set pDoc = ThisDocument
Set pMap = pDoc.FocusMap
Dim pEnumfeat As IEnumFeature
Set pEnumfeat = pMap.FeatureSelection
Dim pEnumLayer As IEnumLayer
Set pEnumLayer = pMap.Layers
Set pLayer = pEnumLayer.Next
Set pfeat = pEnumfeat.Next
Do Until pfeat Is Nothing
Dim StopNumber As String
StopNumber = pfeat.Value(pfeat.Fields.FindField("STOP_NUMBER"))
Dim SQLStr As String
SQLStr = "select sl.sloc_stat, sl.sloc_stat_dt, sl.sloc_strt_nm, sl.sloc_strt_sfx, sl.sloc_suite, sl.sloc_city, sl.sloc_state, sl.sloc_zip, sl.sloc_zip_pl4, sl.sloc_svc_loc_nbr, sl.sloc_dwelling_cd, sl.sloc_bldg_nbr from cstar_prod.svc_loc sl where sl.sloc_stop_number = '" & StopNumber & "'"
Dim rs As ADODB.RecordSet
Set rs = New ADODB.RecordSet
rs.Open SQLStr, dnn
While Not rs.EOF
SlStat = "I"
SlDate = rs(1)
SlSt = rs(2)
SlStFx = rs(3)
SlSuite = rs(4)
SlCity = rs(5)
SlState = rs(6)
SlZip = rs(7)
SlZip4 = rs(8)
SvcLoc = rs(9)
SlDwell = rs(10)
SlBnbr = rs(11)
pfeat.Value(pfeat.Fields.FindField("STATUS")) = SlStat
pfeat.Value(pfeat.Fields.FindField("STAT_DT")) = SlDate
pfeat.Value(pfeat.Fields.FindField("STRT_NM")) = SlSt
pfeat.Value(pfeat.Fields.FindField("STRT_SFX")) = SlStFx
pfeat.Value(pfeat.Fields.FindField("SUITE")) = SlSuite
pfeat.Value(pfeat.Fields.FindField("CITY")) = SlCity
pfeat.Value(pfeat.Fields.FindField("STATE")) = SlState
pfeat.Value(pfeat.Fields.FindField("ZIP")) = SlZip
pfeat.Value(pfeat.Fields.FindField("ZIP_PL4")) = SlZip4
pfeat.Value(pfeat.Fields.FindField("SVC_LOC_NB")) = SvcLoc
pfeat.Value(pfeat.Fields.FindField("DWELLING_CODE")) = SlDwell
pfeat.Value(pfeat.Fields.FindField("BLDG_NBR")) = SlBnbr
pfeat.Store
MsgBox "Service Location Updated!"
rs.MoveNext
Wend
rs.Close
Set pfeat = pEnumfeat.Next
Loop
End Sub
Now as long as I select the feature on the Service Location layer I am fine only because I assume this is the only layer that contains the field "STOP_NUMBER"I would like it to check the feature to make sure it is in the ServiceLocation layer, if it is then continue, if not show a message box and exit sub.I am also looking to add a feature which will show a select box asking which record I would like to sync since there could be more than one record in the oracle database with the same STOP_NUMBER. If you would like to help with that also that would be great, otherwise a question for another thread 🙂Thanks in advanceRay
