Help with Combobox and layer select

[RaymondGoins]

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

[ValentinaBoycheva]

To your first question:

Get the feature's class and cast it to a dataset. Then compare the dataset's name/fullname with the featurelayer's name.

Dim pDS As IDataset
    Set pDS = pFeat.Class
    If pDS.Name = "..." 'your code

To your second question:

Create an OID array of all features that belong to the Service Location layer. Then, when the user selects one particular feature, get the feature with

IFeatureLayer.FeatureClass.GetFeature(OID)

and then update the values.

And finally, if you do bulk updates and want to speed up the processing, instead of retrieving the index inside the loop like this:

pfeat.Value(pfeat.Fields.FindField("STATUS")) = SlStat

get all indexes outside the loop:

idxStat = pfeat.Fields.FindField("STATUS")
etc...

Then inside the loop:

pfeat.Value(idxStat) = SlStat

Cheers,
Valentina Boycheva

[RaymondGoins]

Thanks so much. Your first suggestion worked like a charm. As far as the second or bonus part. I seem to be having some difficulties with. I want to be able to select a row from the oracle query not a feature from my feature class. The feature is already selected I need to select a row from the query in order to update the feature. Here is a breakdown of what I would like to do

1. Select feature on ServiceLocation layer.
2. Macro starts query based on value I have entered in the STOP_NUMBER field of the feature.
3. Query runs and return a row or maybe multiple rows.
4. If single row then just update selected feature if Multiple rows, show combo box with chance to select the row from the query to update the feature with.

I have most working now except the part when the query returns multiple rows. right now it will actually loop through the results and update the feature x amount of times. So you end up with the last row returned from the query. I have the query working fine just seem to have a problem getting the results into a combo box. Since this is actually my first VBA project in ArcGIS I really don't have a clue on how to create the combo box. When I follow the instructions in some other threads I always get back an error that an object is required.

Do I have to create the comboBox object somewhere in order to reference it in my script??

Thanks again for your help

Ray

[ValentinaBoycheva]

Glad the 1st part worked out.

You probably have a button that executes the macro. What you need is a form, displaying the combobox and two buttons - one to retrieve the selected feature, store it in memory, run the Oracle query and populate the combobox, and another one to update the field with the selected row. So, open Visual Basic Editor, then create a form (Insert/UserForm). Then select View/Toolbox, if its window is not visible. You can bring more controls through Tools/Additional Controls. The form itself is called through a UIButtonControl.

Another way to go is create a custom toolbar and add to it UIButtonControls and UIComboboxControl.

Just as a side note - ArcMap 10 is the last version to support VBA. Once you get comfortable with ArcObjects and rapid development in VBA, it's better to move on to the NET languages and/or Python.

http://blogs.esri.com/dev/blogs/arcgis/archive/2011/09/30/update-to-arcgis-10-and-10.1-deprecation-p...

Good luck!
Valentina Boycheva

[RaymondGoins]

Thanks for the heads up. I do code a lot, but web based code. I am very good with PHP. I was looking to use python but not familiar with it and how to implement it with arcGIS. Do you know of a good tutorial on how to accomplish what I want, mainly the database connections??

Thanks

Ray

[ValentinaBoycheva]

Here's a free ESRI tutorial:
http://training.esri.com/gateway/index.cfm?fa=catalog.webCourseDetail&CourseID=1868

A paid one:
http://training.esri.com/gateway/index.cfm?fa=catalog.courseDetail&CourseID=50121644_10.x

Check out also the DevSummit proceedings:

http://proceedings.esri.com/library/userconf/devsummit11/index.html

http://proceedings.esri.com/library/userconf/devsummit10/tech.html

Tons of other Python tutorials online. I usually search with Google and then select on the left pane More search tools/last year.

Python connects to Oracle through cx_Oracle - this works just fine.
Interfaces - wxPython or TkInter

Regards,
Valentina Boycheva