Invalid Opperation Message

[cneill]

When I run the following code once, it works fine, when I run it for a second time I get an Invalid Opperation message at this line
.FindFirst "[ActivityID] = " & AcIDRow & ""
which appears to suggest the .MoveLast is not working
Prior to this part of the Code a new record is added to the Listbox [SchemesAttached] this code then finds the new record in the listbox which then opens a [FrmSchemesSocialActivity] based on the row selected in the listbox
I am pulling my hair out as I can't find why it works once but on a second time, any thoughts


Dim rs As DAO.Recordset
Dim AcIDRow As String
Dim MyVarBM As Long

Select Case Forms![FrmSchemes].NewSchemeID 'NewSchemeID from the newly created record


Case 46, 48, 54, 71, 81, 69 'Social
Forms![FrmSchemes]![SchemesAttached].Requery

AcIDRow = DMax("[ActivityID]", "tblAccountSchemes")
Forms![FrmSchemes].ActivityID = AcIDRow
If Forms![FrmSchemes]![SchemesAttached].Visible = False Then
Forms![FrmSchemes]![SchemesAttached].Visible = True
Forms![FrmSchemes]![SchemesAttached].RowSource = "QrySearchbyScheme"
End If
Set rs = Forms![FrmSchemes]![SchemesAttached].Recordset
With rs
.MoveLast
.FindFirst "[ActivityID] = " & AcIDRow & ""
MyVarBM = .AbsolutePosition + 1 'returns the row # of the offending record
Forms![FrmSchemes]![SchemesAttached].Selected(MyVarBM) = True 'selects the offending row
If .NoMatch Then
MsgBox "No Match Found"
End If
End With
DoCmd.OpenForm "FrmSchemesSocialActivity", acNormal

End Select

Set rs = Nothing 'Deassign all objects

 

[MajP]

if id is a number
"[ActivityID] = " & AcIDRow
or if string
"[ActivityID] = '" & AcIDRow & "'

 

[TheAceMan1]

Howdy cneill . . .

Although the error rests on [blue]MajP's[/blue] post, I see other problems. Particularly with the logic flow of the following code:

[blue]               With rs
                    .MoveLast
                    .FindFirst "[ActivityID] = " & AcIDRow & ""
                     MyVarBM = .AbsolutePosition + 1 'returns the row # of the offending record
                    Forms![FrmSchemes]![SchemesAttached].Selected(MyVarBM) = True 'selects the offending row
                    If .NoMatch Then
                    MsgBox "No Match Found"
                    End If
                End With[/blue]

Fixing the flow and adding a few objects for easier reading, I have:

[blue]   Dim rs As DAO.Recordset, AcIDRow As String, MyVarBM As Long
   Dim frm As Form, Lbx As ListBox

   Set frm = Forms!FrmSchemes
   Set Lbx = frm!SchemesAttached
   
   Select Case frm.NewSchemeID
      Case 46, 48, 54, 71, 81, 69 'Social
         Lbx.Requery
         AcIDRow = DMax("[ActivityID]", "tblAccountSchemes")
         frm.ActivityID = AcIDRow
         
         If Lbx.Visible = False Then
            Lbx.Visible = True
            Lbx.RowSource = "QrySearchbyScheme"
         End If
         
         Set rs = Lbx.Recordset
         
         With rs
            .FindFirst "[ActivityID] = " & AcIDRow & ""
            
            [purple][b]If Not .NoMatch Then
               MyVarBM = .AbsolutePosition + 1
               Lbx.Selected(MyVarBM) = True
            Else
               MsgBox "No Match Found"
            End If[/b][/purple]
         End With
         
         DoCmd.OpenForm "FrmSchemesSocialActivity", acNormal
         Set rs = Nothing
   End Select
   
   Set Lbx = Nothing
   Set frm = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cneill]

Hi TheAceMan1 and Majp

Thanks for all your help with the flow of the code, agreed looks much better

But at the same point
.FindFirst "[ActivityID] = " & AcIDRow & ""
I can run once, works a dream, but then run it immediately again, I get the Invalid Operation message
It keeps given the same message until I close the Forms!FrmSchemes then I can run it just once again
I have try Debug.print in various place within the code to make sure all the ID numbers are correct and they are.
tried
.FindFirst "[ActivityID] = " & AcIDRow as you suggest Majp as it is a number but am still the same message
Any thoughts?
Thanks
CNEILL

 

[cneill]

Hi,

After extra work, I have found that I don't need
.FindFirst "[ActivityID] = " & AcIDRow & ""
this part of the code, as the rest of the code is finding the correct AcIDRow but what I need it to do is select the row (so that it is highlighted) so that when the form FrmSchemesSocialActivity opens it uses this piece of code in the OnLoad
Me.Caption = Forms![FrmSchemes]![SchemesAttached].Column(3) & " " & Forms![FrmSchemes]![SchemesAttached].Column(4) & " " & Forms![FrmSchemes]![SchemesAttached].Column(5)
to retrive the correct information for the FrmSchemesSocialActivity Caption for the newly added record in the Listbox

Any sugestions

Thanks
CNEILL