Where to requery a combobox event

[Johnny]

I have a unbound combobox that takes all dates fronm a table equal to or greater then today and lists them, choosing a date populates a subform on my form.

In the after update event of the combo I have:

Private Sub cboDateEntry_AfterUpdate()
On Error GoTo cboDateEntry_AfterUpdate_Err
    DoCmd.SearchForRecord , "", acFirst, "[idDate] = " & Str(Nz(Screen.ActiveControl, 0))
 
cboDateEntry_AfterUpdate_Exit:
    Exit Sub
cboDateEntry_AfterUpdate_Err:
    MsgBox Error$
    Resume cboDateEntry_AfterUpdate_Exit
End Sub

Since I want users to use the same combobox to add new dates to the table I have the below firing off the not in list event:

Private Sub cboDateEntry_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
    If MsgBox("The Date" & NewData & " is not in the list. Add it?", vbYesNo) = vbYes Then
 
Dim db As Database
Dim rstcboDateEntry As Recordset
Dim sqltblDate As String
    Set db = CurrentDb()
    sqltblDate = "Select * From [tblDate]"
    Set rstcboDateEntry = db.OpenRecordset(sqltblDate, dbOpenDynaset)
 
'Add a new date with the value that is stored in the variable NewData
    rstcboDateEntry.AddNew
    rstcboDateEntry![fldOTDate] = NewData
    rstcboDateEntry.Update
 
 
    Response = acDataErrAdded
 
    rstcboDateEntry.Close 'Close the record set
 
    End If
 
End Sub

In case it matter the rowsource for the combox box in question is:

SELECT [tblDate].[idDate], [tblDate].[fldOTDate] FROM tblDate WHERE tblDate.fldOTDate>=Date();

It works fine as far as adding the new value but I can't get the subform to display the new blank record, I have to click to another existing date then back to the date in question for the sub form to update. As I understood the proces the line Response = acDataErrAdded is supposed to requery automatically but doesn't appear to do so.

My question is where and what do I requery to get the new value to update the subform after the new value is added? I have tried requery to the combobox in the afterupdate event of the combo but doesnt work.

I have tried to requery in the not in list procedure after the update but curiously it fails and says I can't requery until the record is added.

I have tried to requery on dirty of the form itself and still not working. If I requery the entire form then it works but the combobox is reset to the first value which most likely is not the new record.

Any ideas?

 

[boblarson]

The COMBO is what is requeried by acDataErrAdded, not the form. If you want to requery the form you can do that (and that is the only way it will show up in the form) but then that runs into potential bookmark issues, depending on how the recordset is sorted. But you can requery the form and then do a search for the new record.

Can you upload (to the forum here and not some other sites) some screenshots to show what you are doing? I'm not quite getting the whole picture.

 

[Johnny]

Would you prefer the db itself? It's full of jsut sample data nothing important. It's 2007 but I can convert it to earlier if you like.

If not I can get a series of screen shots sure.

The form in question is frmDataEnter

 

[Johnny]

Okay got screenshots now I think it should show in order what happens and how the subform doesnt change to blank as it should after a new record is added from the combobox.

I have to actually close the form and reopen it to get it to display correctly.

For some reason jpeg one didnt work but it just showed the form w/o any combo box selected.