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

[M Owen]

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:

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

[M Owen]

UPDATE: OK. Solved it!


Here's what I did ...

Function RequeryLocation()
    LocationComboBox.Requery
End Function

And

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 ...

[Matt_T_hat]

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.

[M Owen]

Matt,

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