VBA Help

RaymondGoins — Wed, 02 Nov 2011 14:47:40 GMT

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 advance

Ray

Re: VBA Help

JimBarry — Mon, 28 Nov 2011 15:44:18 GMT

Moving this thread to the ArcGIS/ArcObjects forum.

topic VBA Help in ArcObjects SDK Questions

VBA Help

Re: VBA Help