New Record in a form

[ANDYM]

I open a form with a criteria which selects a record.

When in this form I want to change the current record

Is there a way of changing the record without closing the form and reopening. e.g

In form then

strlinkcriteria = NewEmployee

Docmd.close
Docmd.open "Employee", , , strlinkcriteria
...
...

 

[Hmadyson]

This is what I have used,

write a query that would incorporated all of hte data and the new criteria. Ex.

strQuery = "Select * from [Employee] Where " & strlinkcriteria & ";"

Then set the recordset to that query

form.recordsource=strcriteria

You may need to requery the form, but in the form that I am looking at I don't think that you do.

form.requery

 

[ANDYM]

Thanks

I will give this a try later. Did try setting Me.Filter with new criteria string and then Me.Requery but this did not work.

 

[ANDYM]

Have tried your suggestion as in the following with no success.

Private Function RefreshEmployee(EmployeeID As Long)
On Error GoTo ERROR_HANDLER

Dim stLinkCriteria As String

stLinkCriteria = "SELECT * from Employee WHERE [Employee id]= " & EmployeeID & ";"

' the following causes an immediate execution of form_current
' as does the Me.Requery

Me.RecordSource = stLinkCriteria

' following is how problem was overcome
'
'DoCmd.close acForm, "ER Employee"
'DoCmd.OpenForm "ER Employee", , , stLinkCriteria, acFormEdit

End Function