BaldyWeb

Open a second form to the record selected on the first form, but still include all records

Sometimes you want to open a second form to the same record displayed on the first, but you still want to have all records available to the user.  Using the wherecondition argument of OpenForm displays a filtered set of records, so isn't appropriate here.  You can use this technique to open the second form to the desired record, but still have the other records available for the user (this presumes a numeric data type) :

Dim rs As Object
Dim lngBookmark As Long

'set a variable to the current record
lngBookmark = Me.txtEmpID
'open the new form
DoCmd.OpenForm "frmEmployeesDetail"

'take it to the selected record
Set rs = Forms!frmEmployeesDetail.RecordsetClone
rs.FindFirst "EmpID = " & lngBookmark

'the lines marked as optional can be included if there's a chance the record won't exist in the form being opened
If rs.NoMatch Then   'optional - if no match, go to a new record
  DoCmd.GoToRecord acForm, "frmEmployeesDetail", acNewRec   'optional
  Forms!frmEmployeesDetail.txtEmpID = Me.txtEmpID  'optional - copy the employee ID from this form
Else   'optional
  Forms!frmEmployeesDetail.Bookmark = rs.Bookmark
End If   'optional

Set rs = Nothing

Home