Added Record in table not seen on Form

[bob000]

Below is the code in the NotInList Event of a combo box. This will add the value not in the list to the table and refresh the list (that works fine and the combo box is correct). Next I open the form ReasonCode so the user can update other fields for this record. ReasonForm does not have the record I added, but when I go to the table, it is there. Why? Is there a better way?

Dim ctl As Control
Dim strLinkCriteria As String

' Return Control object that points to combo box.
Set ctl = Me!cboReasonCode

' Prompt user to verify they wish to add reason code.
If MsgBox("Not in list. Add it?", vbOKCancel) = vbOK Then

Set dbADR = OpenDatabase("c:\wrk\devel adr.mdb&quot
Set recADR = dbADR.OpenRecordset("reason&quot

recADR.MoveLast
recADR.AddNew
recADR!ReasonCode = NewData
recADR.Update

recADR.Close
dbADR.Close

'Save Change on form
Me![cboReasonCode].Value = NewData

'Don't display error since we are handeling it
Response = acDataErrContinue

'Requery the combo box on the form
ctl.Requery

'Open ReasonForm for the new record to update
DoCmd.OpenForm "ReasonForm"

End If

 

[Databaseguy]

You're code looks almost identical to mine(below) which works. The only thiong you do that I don't is .movelast. Also, I put mine in the click event, but that shouldn't make a difference. Also, I refresh the form. Try that...


Private Sub cboAdd_Click()
On Error GoTo Err_Hand
'Dim Variables for recordset
Dim DBS As Database
Dim RST As Recordset
'Dim confirmation message box stuff
Dim msg, resp, sty
sty = vbYesNo
msg = "Do you really want to add this certification for " & Me.txtEmployeeName & " ?"
'Fire off confirmation
resp = MsgBox(msg, sty)
'If user responds yes
If resp = vbYes Then
'Instancate DBS & Recordset objects
Set DBS = CurrentDb
Set RST = DBS.OpenRecordset("tblCert&quot
'Add new record & update recordset
With RST
.AddNew
!TestID = cboAdd.Value
!EmployeeID = txtEmployeeIDHidden.Value
.Update
End With
'Clear reference to objects
Set DBS = Nothing
Set RST = Nothing
'Refresh the form
Me.Refresh
Else
'Bail out of procedure. OK is only response allowed
MsgBox "Add Canceled", vbOKOnly
End If
'Error trapping
Err_Hand:
Select Case Err.Number
'Ignore 0, which is always generated for no error
Case 0
'3022 is Access key violation. If it occurs, record is already there
Case 3022
MsgBox "You have already certified this person for this test"
Resume Next
Case Else
'Diplay error desciption
MsgBox Err.Description
End Select
End Sub Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy