Combo box not updating when adding new not in list

[GinaStar]

Combo box is updating table properly when has new city to add on 'not in list' function. Problem is, when return to the box after the addition of the city, the rowsource is not showing the new entry (it's in the table, but the combo box does not show the new listing). I have no idea why the combo box will not update after the new city is added and it is driving me totally nuts. I have been trying for two weeks to get it to work. Someone please call or email me before I totally lose my mind. I know it is something simple I am overlooking, but I can't figure it out. email is ginastar@sonet.net or phone is 1-888-715-3622. <begging> please!! </begging> The query that the combo box is based on is very simple:

SELECT tblCity.strPerdiemCity FROM tblCity;

Actually, the table is very simple too, strPerdiemCity is the only field in it

Private Sub strPerdiemCity_NotInList(NewData As String, UserResponse As Integer)
'********************************************************************
' Name: strPerdiemCity_NotInList
' Author: Regina Parker
' Date: March 27, 2003
' Comment: If City not in list, add to list
'********************************************************************
Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = &quot;'&quot; & NewData & &quot;' is not an available City&quot;
strMsg = strMsg & &quot;@Do you want to quickly add the new City to the current list?&quot;
strMsg = strMsg & &quot;@Click Yes to add or No to re-type it.&quot;
If msgbox(strMsg, vbQuestion + vbYesNo, &quot;Add new City?&quot = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset(&quot;tblCity&quot;, dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![strPerdiemCity] = NewData
rs.Update

If Err Then
msgbox &quot;An error occurred. Please try again.&quot;
Response = acDataErrContinue
Me![strPerdiemCity].Undo
Else
Response = acDataErrAdded
End If
End If

Exit_strPerdiemCity_NotInList:
Exit Sub

Err_strPerdiemCity_NotInList:
Select Case Err
Case 0
Case Else
msgbox Err.DESCRIPTION
Resume Exit_strPerdiemCity_NotInList
End Select

End Sub

 

[skittle123]

hi I am guessing you are trying to add new entries to a combo box and want them to show up in the drop down list.

in the after_update event of your combo box add this

Call FunctiontoInsertNewFeildInDB
Me.<comboboxname>.requery

 

[HomeALone]

you need to requery the combo box afterupdate.

Add the following code to requery.

Me!ComboBoxName.Requery

 

[judgehopkins]

thread702-527740

GinaStar, look at the thread above. I am doing what you are trying without a problem (except for some string manipulation).

After my rst.update (your rs.update) I have the following lines which you do not:

...
Response = acDataErrAdded
rst.Close
Else
...

That would lead me to believe you need to close the recordset after you have updated it. It works for me!

Judge Hopkins

&quot;...like hunting skunks underwater....&quot;
John Steinbeck, The Grapes of Wrath

 

[GinaStar]

Ok, I must be stupid. I've added the requery

Private Sub strPerdiemCity_AfterUpdate()
Me.strPerdiemCity.Requery
End Sub

and the close but it is still not updating. If someone doesn't mind, can I zip it and email it? I'm about ready to chuck my computer out the window. I've also tried sticking the close after the rs.update in case I was putting it in the wrong place (i've had a bad day at work, got called out at 4:00 am this morning!)

Private Sub strPerdiemCity_NotInList(NewData As String, UserResponse As Integer)

Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = &quot;'&quot; & NewData & &quot;' is not an available City&quot;
strMsg = strMsg & &quot;@Do you want to quickly add the new City to the current list?&quot;
strMsg = strMsg & &quot;@Click Yes to add or No to re-type it.&quot;
If msgbox(strMsg, vbQuestion + vbYesNo, &quot;Add new City?&quot = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset(&quot;tblCity&quot;, dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![strPerdiemCity] = NewData
rs.Update

If Err Then
msgbox &quot;An error occurred. Please try again.&quot;
Response = acDataErrContinue
Me![strPerdiemCity].Undo
Else
Response = acDataErrAdded
rs.Close
End If
End If

Exit_strPerdiemCity_NotInList:
Exit Sub

Err_strPerdiemCity_NotInList:
Select Case Err

Case 0
Case Else
msgbox Err.DESCRIPTION
Resume Exit_strPerdiemCity_NotInList
End Select

End Sub

 

[hymn]

To display it you first need to save it
DoCmd.RunCommand acCmdSaveRecord

Hope this helps
Hymn