Private Sub SubName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'    MsgBox ("On exit")
'    SubID.Caption = GetSubID(SubName.Value)
'    Address.Value
'    City.Value
'    State.Value
'    Zipcode.Value
End Sub



Private Sub UserForm_Initialize()
'    Dim rs As ADODB.Recordset
'    Dim cn As ADODB.Connection
    Dim SQL_Query As String

    With SubSearch
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.height) - (0.5 * .height)
    End With

' This section determines the initial values for the textboxes

    SubName.Value = ActiveCell.Value
    Address.Value = ActiveCell.Offset(1, 0).Value
    City.Value = ExtractCity(ActiveCell.Offset(2, 0).Value)
    State.Value = ExtractState(ActiveCell.Offset(2, 0).Value)
    Zipcode.Value = ExtractZipcode(ActiveCell.Offset(2, 0).Value)
    ActiveCell.ID = "SubID: " & GetSubID(SubName.Value)
    SubID.Caption = GetSubID(ActiveCell.Value)
'    SubName.AddItem "<< (+) ADD A NEW SUBCONTRACTOR >>", 0
'    MsgBox ("There are " & SubName.ListCount & " list items")

End Sub

Private Sub SubName_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

'        Do While SubName.ListCount > 0
'            SubName.RemoveItem (0)
'        Loop


        If SubName.Value <> "" And Len(SubName.Value) > 0 Then
            Call mysqlSearch(SubName.Value, "entities", "Name")
            SubName.DropDown
        End If



'Try on backspace key press, bring attention to box, (this should hide the combobox)
'Then on select of contractor get SubID and associated address.
'Allow for up and down arrow selection`

End Sub




Private Sub OK_Click()
    'Adds selected Subcontractor to current cell
    ActiveCell = SubName.Value

    'Adds address to cell below active cell
    ActiveCell.Offset(1, 0).Value = Address.Value

    ActiveCell.Offset(2, 0).Value = City.Value & ", " & State.Value & " " & Zipcode.Value
    ActiveCell.ID = "SubID: " & GetSubID(SubName.Value)
    Call AddUpdateAddress(SubID.Caption, Address.Value, City.Value, State.Value, Zipcode.Value, ActiveCell.Offset(3, 0).Value)
    Unload Me

End Sub
Private Sub CancelButton_Click()
    Unload Me
End Sub

Function AddUpdateAddress(SubID As String, Address As String, City As String, State As String, Zipcode As String, Website As String)
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim mysqlstring As String

    Set rs = New ADODB.Recordset
    Set cn = New ADODB.Connection
        cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
        "SERVER=127.0.0.1;" & _
        "DATABASE=progc_db;" & _
        "USER=admin;" & _
        "PASSWORD=admin;" & _
        "Option=3"

    mysqlstring = "INSERT IGNORE INTO mainaddress (EntityID,Address,City,State,Zipcode,Website) VALUES ('" & SubID & "','" & Address & "','" & City & "','" & State & "','" & Zipcode & "','" & Website & "');"
'    Range("A1").Value = mysqlstring
    rs.Open mysqlstring, cn, adOpenStatic

    cn.Close

End Function




Public Function mysqlSearch(SearchString As String, TableName As String, ColumnName As String)
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim ListItem As String
    Dim i As Integer
    Dim ListCount As Integer
    Dim SearchCount As Integer

    SearchString = esc(SearchString)
'    MsgBox (SearchString)

    Set rs = New ADODB.Recordset
    Set cn = New ADODB.Connection
        cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
        "SERVER=127.0.0.1;" & _
        "DATABASE=progc_db;" & _
        "USER=admin;" & _
        "PASSWORD=admin;" & _
        "Option=3"

    ListCount = SubName.ListCount
    mysqlstring = "SELECT " & ColumnName & ",EntityID" & " FROM " & TableName & " WHERE " & ColumnName & " LIKE '" & SearchString & "%';"
'    MsgBox (mysqlString)
    rs.Open mysqlstring, cn, adOpenStatic

' ADD CONTRACTOR ID
' CREATE READ-ONLY FIELDS
' RECORD SELECTION OF ITEM AND POPULATE FIELDS
' ALLOW KEY DOWN LOOP

'    Address.SetFocus
    ' Focus is shifted away and then back to combobox to fix visual bug
'        MsgBox (SubName.ListCount)

    If rs.EOF = True Then
'   Address.SetFocus
    SubName.SetFocus
    SubName.Clear
    ' Focus is shifted away and then back to combobox to fix visual bug

    Else
    SearchResults = rs.GetString(, , ",", Chr(13))
'    MsgBox (SearchResults)
    SearchCount = UBound(Split(SearchResults, Chr(13)))
'    MsgBox SearchCount

'Removes all list items in combobox
        With SubName
             If ListCount > 0 Then
                For i = 0 To ListCount - 1
                   .RemoveItem (0)
                Next i
             End If
        End With
    SubName.Clear

'Adds initial top list item of combobox
    SubName.AddItem "<< ADD A NEW SUBCONTRACTOR >>", 0

'Adds query results as combobox list items
    With SubName
                For j = 0 To SearchCount - 1
                    ListItem = Split(SearchResults, Chr(13))(j)
                    ListItem = Split(ListItem, ",")(0)
 '                  MsgBox (ListItem)
                    .AddItem ListItem, (j + 1)
                Next j
        End With


    'SubName.SetFocus

    End If
rs.Close
End Function