Combine On Load Recordset Code

[Zydeceltico]

Hi All -

Both of the following pieces of code work perfectly for me on a single form.

The only thing is that the first one is on the Load event and I have the second one on the Dirty event because I am unsure how to combine them without screwing them up. I'm sure it would be better to have one single piece of code on the Load event.

Any advice would be most welcome. Thanks!

Load

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    rs.FindFirst "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs
    End If
  End If
End Sub

Dirty

Private Sub Form_Dirty(Cancel As Integer)

Dim QCDB As DAO.Database
Dim rsPart As DAO.Recordset
Dim strSQL As String

Set QCDB = CurrentDb
strSQL = "SELECT Part_ID, PartType, PartDWG " & _
    "FROM tblParts " & _
    "WHERE tblParts.Part_ID = " & intPartID

Set rsPart = QCDB.OpenRecordset(strSQL)

    Me.txtPartType_FK = rsPart.Fields(0).Value
    Me.txtPartName = rsPart.Fields(1).Value
    Me.txtPartDrawing = rsPart.Fields(2).Value
             
    txtInspectionEvent_FK = intInspID

End Sub

or maybe it isn't possible to create a recordset from a recordset?

 

[pbaldy]

Where does intPartID come from? At a glance, I assume the dirty event would only be desired on a new record, so the guts of that event could go after the

Me.InspectionEvent_FK = Me.OpenArgs

line of the load event code.