Refresh/reload a VBA combobox ... [**RESOLVED**]

Hi all.

This one probably has a trick I don't know about to solve it ... I have a ComboBox on a form that with data entry that is not in the list I prompt for that data to be saved ( and then subsequently have shown as selected). I have the add routine going fine. The Requery of the underlying table blows with a 2118 error relating to saving the field before doing a requery action. Any ideas on how to get this to work?

Relevant code:

Code:

---

Function AddNewLocation(ByVal LocName As String) As Long
    On Error GoTo Err_ANL

    Dim NewLocID As Long
    Dim FormsConnect As ADODB.Connection
   
    Set FormsConnect = New ADODB.Connection
   
    FormsConnect.CursorLocation = adUseClient
    FormsConnect.Open "DSN=Billing Forms;"
   
    MyRecSet.CursorType = adOpenDynamic
   
    NewLocID = GetNextID(1)
    If NewLocID <> 0 Then
        SQLString = "SELECT * FROM [GM Locations] WHERE (1=0);"
        MyRecSet.Open SQLString, MyConnect
        MyRecSet.AddNew
        MyRecSet.Fields(0).Value = NewLocID
        MyRecSet.Fields(1).Value = LocName & ""
        MyRecSet.Update
        MyRecSet.Close
        ' Add the location to the internal list
        SQLString = "SELECT * FROM " & LocationsTbl & " WHERE (1=0);"
        MyRecSet.Open SQLString, FormsConnect
        MyRecSet.AddNew
        ' Location ID
        MyRecSet.Fields(0).Value = NewLocID
        ' Location Name
        MyRecSet.Fields(1).Value = LocName & ""
        MyRecSet.Update
        MyRecSet.Close
        LocationComboBox.RowSource = "SELECT * FROM " & LocationsTbl & ";"
        LocationComboBox.Requery
        AddNewLocation = NewLocID
    Else
        AddNewLocation = 0
    End If
    FormsConnect.Close
    Set FormsConnect = Nothing
'    Set TrgRecSet = Nothing
 
Exit_ANL:
    Exit Function
   
Err_ANL:
    MsgBox Err.Number & ": " & Err.Description
    AddNewLocation = 0
    Resume Exit_ANL
End Function

Private Sub LocationComboBox_NotInList(NewData As String, Response As Integer)
    Dim MsgVal As VbMsgBoxResult, LocID As Long
   
    MsgVal = MsgBox(NewData & " is not a recognized location. Do you wish to add it?", vbYesNo, "System Monitor")
    If MsgVal = vbYes Then
        LocID = AddNewLocation(NewData)
        If LocID <> 0 Then
'            LocationComboBox.Requery
            LocationComboBox.Value = LocID
        End If
    Else
        LocationComboBox.SelStart = 0
        LocationComboBox.SelLength = Len(LocationComboBox)
        LocationComboBox.Text = ""
        LocationComboBox.Value = ""
    End If
    Response = DATA_ERRCONTINUE
End Sub

---

Thanks!

- Mike

UPDATE: OK. Solved it!


Here's what I did ...

Code:

---

Function RequeryLocation()
    LocationComboBox.Requery
End Function

---

And

Code:

---

Private Sub LocationComboBox_NotInList(NewData As String, Response As Integer)
    Dim MsgVal As VbMsgBoxResult, LocID As Long
   
    MsgVal = MsgBox(NewData & " is not a recognized location. Do you wish to add it?", vbYesNo, "System Monitor")
    If MsgVal = vbYes Then
        LocID = AddNewLocation(NewData)
        If LocID <> 0 Then
            LocationComboBox.Value = LocID
            Call RequeryLocation
        End If
    Else
        LocationComboBox.SelStart = 0
        LocationComboBox.SelLength = Len(LocationComboBox)
        LocationComboBox.Text = ""
        LocationComboBox.Value = ""
    End If
    Response = DATA_ERRCONTINUE
End Sub

---

I had to use an indirect call to requery the table ...

Quote:

---

Originally posted by M Owen
I had to use an indirect call to requery the table ...

---

How odd anone know why? It's just that I seem to spend half my life running into problems like these.

Matt,

Quote:

---

How odd anone know why? It's just that I seem to spend half my life running into problems like these.

---

I have my suspicions ... Basically I think that my "indirect call" merely allows Access to finish up the NotInList event handling THEN do my Requery...

- Mike