NEW RECORDD NOT SET IN FOCUS

[akasmia]

I have a form called Physical_Exam based on a table also called Physical_Exam. On this form there is a combo box based on the same table. It works fine except when I add a new name (new record) using the NotInlist event. The record I add is not automatically in focus for the user to complete the rest of its elements ( date of birth, phone number... et) since the NotInLilst event routine only adds the last name. I can go back, search for the new added record, and then complete it but that takes time and may confuse the user!.

I want to be abe to add a new record and immediately start editing it using this same form ( physical_exam).

I apprecaite your help!

Adel.


Private Sub Search_By_Last_Name_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Msg As String, Style As Integer, Title As String

Msg = "Name is not in list. Add it?"
Style = vbInformation + vbYesNo
Title = "Not In List Error"

If MsgBox(Msg, Style, Title) = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("Physical_Exam", dbOpenDynaset)
On Error Resume Next

rst.AddNew
rst!LastName = Me!Search_By_Last_Name.Text
rst.Update

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

End Sub

 

[hymn]

you will need to refresh the record set

Hope this helps
Hymn

 

[akasmia]

Thank you hymn !

..... but I am not sure what you mean by "you will need to refresh the record set ". When I use requery it does not work because then I find myself in an indefinite loop adding the same new data to the underlying table. I had to use break to get out of VBA's loop!.

thanks in advance !

Adel

 

[RoyVidar]

Don't know, but
- does it change anything if you close the recordset?

[tt] rst.AddNew
rst!LastName = NewData
rst.Update
rst.close
set rst=nothing
set db=nothing[/tt]

Also try using the NewData arguement, which holds the new data entered in the combo.

You could also try using a

[tt]me!Search_By_Last_Name.requery[/tt]

at the end of the routine, though using acDataErrAdded should perform this.

You could perhaps also use a .FindFirst with the newdata to ensure it isn't alredy in the table prior to adding it, but might this perhaps be some kind of a timing issue? Does it behave correctly if you step thru the code vs running it?

Roy-Vidar