How to select a single record from a recordset based on datasheet view

[David Patz]

Hi,

Im currently building a database for users to edit or manipulate train maintanence parts. For one of my forms - I display the inactive parts in stock. I've designed the form to have a datasheet view calling all inactive parts based on the type of part (Cylinder, Axle etc). All of this works however I am having difficulty selecting a record in the datasheet - double clicking and writing THAT record to an underlying table (to set it to 'active'). So far it seems as though the only records being written are the 1st records in the recordset. The recordset is (or should be) an exact replica of the datasheet viewed - as the same query is used to create both objects.

Heres my coding so far:

Option Compare Database

Private Sub Form_DblClick(Cancel As Integer)
'DoCmd.SetWarnings = False
Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM Part WHERE PartActive = true AND PartName = '" & Me.cbxName & "'", dbOpenDynaset)
    If rs.RecordCount <> 0 Then

If MsgBox("Are you sure you want to de-activate this part?", vbYesNo) = vbYes Then
    
    Dim curr As Long
    curr = [Form_qryPartToID1 subform].CurrentRecord
    rs.AbsolutePosition = curr
    
    Dim stat As Integer
    Dim Dyna As Integer
    Dim code As String
    Dim inspect As Integer
    Dim life As Integer
    Dim Com As String
    Dim Din As String
    Dim Dout As String
    Dim serial As String
    Dim act As Boolean
    Dim Coach As Integer
    Dim PartId As Integer
    
    rs.Move (curr)
    stat = rs("PartStaticID").Value
    Dyna = rs("PartDynamicID").Value
        If rs("RVRCode").Value = "" Then
        code = "No RVR Code"
        Else
        code = rs("RVRCode").Value
        End If
    inspect = rs("PartInspectionCycle").Value
    life = rs("PartLifeCycle").Value
    Com = rs("PartComment").Value
    Din = rs("PartDateInService").Value
    Dout = rs("PartDateOutService").Value
    serial = rs("PartSerialNo").Value
    act = False
    Coach = rs("CoachNo").Value
        If Form_frmActivePart.cbxName.Value = "Axle" Then
            PartId = 1
        ElseIf Form_frmActivePart.cbxName.Value = "Cylinder" Then
            PartId = 2
        Else
            PartId = 3
        End If
    
    Dim sSql As String
    sSql = "INSERT INTO Part(PartStaticID,RVRCode,PartInspectionCycle,PartLifeCycle,PartComment,PartDateInService,PartDateOutService,PartSerialNo,PartActive,CoachNo,PartTypeID)" & _
           "VALUES(" & stat & ",'" & code & "'," & inspect & "," & life & ",'" & Com & "',#" & Din & "#,#" & Dout & "#,'" & serial & "'," & act & "," & Coach & "," & PartId & ")"
    
    
    Dim sSql2 As String
    sSql = "INSERT INTO Part(PartStaticID,RVRCode,PartInspectionCycle,PartLifeCycle,PartComment,PartDateInService,PartDateOutService,PartSerialNo,PartActive,CoachNo,PartTypeID,PartDateActive)" & _
           "VALUES(" & stat & ",'" & code & "'," & inspect & "," & life & ",'" & Com & "',#" & Din & "#,#" & Dout & "#,'" & serial & "'," & act & "," & Coach & "," & PartId & ",Date())"
    DoCmd.RunSQL (sSql2)
    DoCmd.RunSQL (sSql)
    MsgBox ("Part has been succesfully activated.")
    Else
   ' DoCmd.SetWarnings = True
   End If
    Exit Sub
End If
End Sub

Im also trying to write the same record to a seperate table - which simply serves as a history of the part.

Any help will be greatly appreciated. Thanks.

[ADezii]

One thing that I immediately see, is that if the Recordset contains Multiple Records (rs.RecordCount > 1), there is no mechanism for looping through the other Records beside the first, as in:

Do While Not rs.EOF
  'some processing here
    .MoveNext
Loop