create new record and start editing

[lumpur]

hi,
i'm trying to do a simple thing i thought: a form with a pulldown menue looks up a record based on the menue enrty selected (findfirst). i want to use the "not in list" event to add a record. that works just fine. now after the new record is created how do i get the form to open this record so that i can populate the remaining fields? i have the record id but when i try to do a findfirst on that id access gets into an endless loop.

Thanks

[TheSmileyCoder]

Please show us the code you are using, then we can work of that.

[lumpur]

here is the code for the pulldown (wizzard generated)

Private Sub Combo14_AfterUp date()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Cl one
RS.FindFirst "[REQUEST_ID] = " & Str(Nz(Me![Combo14], 0))
If Not RS.EOF Then Me.Bookmark = RS.Bookmark
Exit Sub


End Sub

This is the "not in list" event

Private Sub Combo14_NotInLi st(newdata As String, Response As Integer)
Dim no As Integer
Dim db As Database
Dim varbkmk As Variant

Set db = CurrentDb
Dim RS As Object
sqlstr = "SELECT RQ.[RQ number], rq.request_id FROM RQ" & ";"
Set RS = db.OpenRecordse t(sqlstr)


RS.AddNew
RS.Fields("RQ number") = newdata
no = RS.Fields("requ est_id")
RS.Update
RS.Close
Response = acDataErrAdded

AND HERE I WOULD LIKE TO OPEN THE RECORD I JUST CREATED FOR EDITING

End Sub


cheers

[TheSmileyCoder]

Hi again.

Been on vacation, sorry for the late reply. Try adding this at the bottom:

Me.Requery
Me.recordset.findfirst "request_id"

I haven't tried using the approach you describe, so I am guessing what should be done, so if its not working, just return here, and we can try a bit more.

You should also remember to unassign your objects. Basicly if you are using a Set command, you should remember to unset it when you exit.

Set Rs=Nothing
Set db=Nothing