Cannot refresh combobox

[Lhuffst]

I have a combo box that is linked to a table

rowsourcetype = table/query
rowsource = SELECT TblReturntoList.fldReturnToList FROM TblReturntoList;
limitToList = Yes

I (thanks to code I found here) am able to add new items via the notinlist event but after the users add the new item, it does not refresh in the combo box. It does add it to the table.

I have tried
me.requery
on the onfocus,
onclick and
even as the last step in the notinlist.

I do not want to create a new form for to simply add a new item and will appreciate any help/guidance given.
Thanks
lhuffst

 

[PHV]

What is your actual code in the NotInList event procedure ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lhuffst]

Dim db As Database
Dim rst As Recordset
Dim strMsg As String
Dim ssql As String



strMsg = "'" & NewData & "' is not an available item " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this item to the current list? "
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes To Add " & NewData & " to the list or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New Item?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("tblReturnToList", dbOpenDynaset)
On Error Resume Next
rst.AddNew
rst!fldreturntolist = NewData
rst.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
Response = acDataErrContinue 'gets around default error message of the value you entered isnt in valid for this field


rst.Close
Set rst = Nothing
Set db = Nothing

Exit Sub
End If

End If

Exit Sub


End Sub


When I went back to retest, it adds the data but doesnt' refresh and also continually trys to execute the noinlist code regardless of which choice you make (yes/no)

 

[Lhuffst]

I did end up taking the requery statment out.

 

[missinglinq]

Have you tried requerying the combobox itself?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

How are ya Lhuffst . . .

. . . and this:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   Msg = "'" & NewData & "' is not an available item " & DL & _
         "Do you want to add this item to the current list? " & DL & _
         "Click Yes To Add " & NewData & " to the list" & DL & _
         "Click No to re-type it."
   Style = vbQuestion + vbYesNo
   Title = "Add New Item?"
   
   If MsgBox(Msg, Style, Title) = vbNo Then
       Me![purple][b]ComboboxName[/b][/purple].Undo
       Response = acDataErrContinue
   Else
      Set db = CurrentDb
      Set rst = db.OpenRecordset("tblReturnToList", dbOpenDynaset)
      rst.AddNew
      rst!fldreturntolist = NewData
      rst.Update
      Response = acDataErrAdded
   End If
   
   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997