Not in list requery issue

[CJ]

Hi Groupies:

I have the following code in a combo box on a subform.

Private Sub CreatedBy_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = strMsg & "Add " & NewData & " to the list? "
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to re-type."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblEmployees", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!EmpLast = NewData
rs.Update
DoCmd.OpenForm "frmEmployees", acNormal, , , acFormAdd

If Err Then
' MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If
Me.CreatedBy.Value = Null
Me.dtmDate.SetFocus
Me.CreatedBy.Requery

End Sub


The problem is that I can not get the new item to show up in the combo
unless the user pushes F9 or changes records.

Can anybody provide some insight into what I am missing?

 

[Douglas J. Steele]

That doesn't sound right.

Setting the Response variable to acDataErrAdded should automatically cause a
requery of the control. Not only that, but you've got an explicit Requery in
there!

See whether removing the

Me.dtmDate.SetFocus

makes a difference.

 

[CJ]

Hi Doug, thanks for popping in.

Removing Me.dtmDate.SetFocus did not make a difference.

What I did forget to mention is that this is A2K7......it has it's "issues".

What is happening is this:
1. If I enter Player as a last name, I get prompted for the new record
2. frmEmployees opens but Player is not entered in the Last Name field which
I thought: rs!EmpLast = NewData, was supposed to do.
3. So, I enter Player in LastName and Hockey in FirstName and close
frmEmployees.
4. If I push the combo box down arrow, Player is listed but not with the
First Name
5. After I push F9, I have 2 entries, Player and then Player Hockey

Thanks for your assistance on this Doug.

 

[Douglas J. Steele]

Get rid of the On Error Resume Next, and put error trapping in to see
whether there's a problem with what your code is doing.

Resume Next gives a false sense of security: the code appears to work, but
that's because it just ignored any errors that may have occurred.

If you're not familiar with error trapping, see what Allen Browne has at
http://www.allenbrowne.com/ser-23a.html

 

[Guest]

I can't get my head around the error trapping and I think it will take more
time than I have, to solve it.

Instead, I am just going to add a button to open the frmEmployees and have
them manually enter a new employee as necessary.

Thanks again.
--
Thanks for the brainwaves!

CJ
I blame the parents........

Get rid of the On Error Resume Next, and put error trapping in to see
whether there's a problem with what your code is doing.

Resume Next gives a false sense of security: the code appears to work, but
that's because it just ignored any errors that may have occurred.

If you're not familiar with error trapping, see what Allen Browne has at
http://www.allenbrowne.com/ser-23a.html