How do I find and edit record in continuous subform after deleting a different record

[ladyjbean]

I have been searching for a way to solve my problem for the last few days and have yet to find an answer. Hoping someone here can help.

Some background:
I am building a db that tracks various requirements that we must meet. The requirements are stated in the different documents which apply to our specific project. When a document is revised, many of the requirements remain the same.

My form:
The form that I am having issues with is the form that will be used to select which requirements are replicated in the new revision. I have a main form which shows the new document revision in text boxes. The first subform (lets call it "SubForm1") shows the requirements that were in the previous version of the document. There is a button which, when clicked, makes a copy of the requirement and changes the document ID to the new revision. It also changes the "Copied to New Rev" value to 1 (Yes) and enters the source requirement's ID into the appropriate field (SourceReq) for reference.

The second subform (SubForm2) shows the requirements which have been copied. It has a button which is intended to be used if the user realizes that they copied the requirement by mistake. It should delete the new record and change the source requirement’s "Copied to New Rev" value to "2" (No), but I can't get it to work.

What I've tried:
I made following macro to try and accomplish the task:

If Not [Form].[NewRecord] Then

   SetTempVar
      Name [Req]
      Expression = [SourceReq]

   SetTempVar
      Name [NewReq]
      Expression = [Requirement ID]

   GoToControl
      Control Name  [SubForm1]

   FindRecord
      Find Waht  [TemVars]![Req]
      Match  Whole Field
      Match Case  No
      Search  All
      Search As Formatted   No
      Only Current Field   Yes
      Find First  Yes

   SetValue
      Item=[Copied to New Rev]
      Expression=2

   GoToControl
      Control Name  [SubForm2]

   FindRecord
      Find Waht  [TemVars]![NewReq]
      Match  Whole Field
      Match Case  No
      Search  All
      Search As Formatted   No
      Only Current Field   Yes
      Find First  Yes

   RunMenuCommand
      Command  DeleteRecord

End If

When I step through the macro, it gives an error (2162) on the step which sets the value of "Copied to New Rev". I'm not very skilled with VB, but I tried converting it anyway. Here's what I came up with:

Private Sub Command53_Click()

    If (Not Form.NewRecord) Then

        TempVars.Add "Req", Me.SourceReq.Value
        TempVars.Add "NewReq", Me.Requirement_ID.Value
        Forms![Input-Doc-NewRev].SetFocus
        Forms![Input-Doc-NewRev]!SubForm1.SetFocus

        With Forms![Input-Doc-NewRev]!SubForm1![Requirement ID]
            DoCmd.FindRecord ("[TempVar]![Req]")

            If Not NoMatch Then 'we found the record
                .Edit
                [Copied to New Rev].Value = "2"
                .Update
            End If

        End With
 
        Forms![Input-Doc-NewRev].SetFocus
        Forms![Input-Doc-NewRev]!SubForm2.SetFocus

        With Forms![Input-Doc-NewRev]!SubForm2![Requirement ID]
        DoCmd.FindRecord ("[TempVar]![NewReq]")
        DoCmd.RunCommand acCmdDeleteRecord
        End With

    End If

    If (Form.NewRecord And Not Form.Dirty) Then
        Beep
    End If

    If (Form.NewRecord And Form.Dirty) Then
        DoCmd.RunCommand acCmdUndo
    End If

    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If

End Sub

When I run this, I get the same error (2162) and it highlights the first FindRecord line.

The two subforms pull data from two queries which are based off the same table. The only difference between the queries is the document that they are filtered to show.

Any help would be greatly appreciated!

Thanks,
Jill

[NeoPa]

Jill, I'm afraid very little of your explanation means anything to me at all. I suspect that is because you explain things you know about in terms of other things you know about, but which no-one else would.

That's the bad news. The good is that I'm hoping (and suspect) that we won't need to worry too much about how it all fits together as you have made an attempt to identify the line of VBA code that crashes. If it's line #11, as I suspect from your description, then that line certainly has a glaring error in that the value passed is a string value of "[TempVar]![Req]". I'm pretty sure you intended to use the contents of the TempVar called "Req" instead.

I suspect that you'll probably find more errors in the code once that's been cleared away mind you, so I'll post a link (Before Posting (VBA or SQL) Code) to some advice that will help you identify and fix all of your most basic errors without needing to post further. When they are cleared away will be a good time to deal with the rest which may require some experienced assistance.