FindFirst issue - "Operation is not supported etc"

[Malcy]

Hope someone can help as driving me bonkers. I am recycling some text which works elsewhere but is throwing an error here. DAO 3.6 Objects Library is referenced. I have put more code in than usual in case anyone spots anything odd

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim intOp As Integer
    Dim varCompl As Variant
    Dim dbs As DAO.Database
    Dim rstAudit As DAO.Recordset
    Dim rstSwc As DAO.Recordset

' Set the connection
    Set dbs = CurrentDb
    Set rstAudit = dbs.OpenRecordset("tblAuditLog")
    Set rstSwc = dbs.OpenRecordset("tblSixWeekChecks")
    
' Identify the operator
    intOp = DLookup("lngTsOp", "tblTmpSession")
    
' Verify that critical text boxes have been completed
    If IsNull(Me.cboStaff) Or Me.cboStaff = "" Then
        MsgBox "You need to enter the mechanic's name.", vbOKOnly, "Required information"
            Me.cboStaff.SetFocus
        Exit Sub
    End If
    
    If IsNull(Me.txtMileage) Or Me.txtMileage = "" Then
        MsgBox "You need to enter the mileage.", vbOKOnly, "Required information"
            Me.txtMileage.SetFocus
        Exit Sub
    End If

' Populate the mechanic textbox
    Me.txtMechanic = DLookup("Name", "qcboStaff", "[lngStaff]=" & Me.cboStaff)

' Verify that the SWC data has not already been entered
    varCompl = DLookup("lngSwcPlant", "qrySwcCheck")
    
    If varCompl = 0 Then
        MsgBox "The results from the Six Week Check have already been entered.", vbOKOnly, "Duplicate data not allowed"
            
' Add transaction record to tblAuditLog
        rstAudit.AddNew
            rstAudit!dtmAl = Now()
            rstAudit!lngAl = 146
            rstAudit!lngAlOp = intOp
            rstAudit!lngAlPl = Me.txtPlant
        rstAudit.Update
    
        Exit Sub
    End If

' Update record in tblSixWeekChecks
    rstSwc.FindFirst "[lngSwc] =" & Me.txtSwc
       If Not rstSwc.NoMatch Then
            rstSwc.Edit
'                rstSwc!lngSwcMiles = Me.txtMileage

lngSwc is an autonumber and Me.txtSwc is a numeric value where there should always be a match. In my trials its value with mouse hovered over was 7 as expected.
The error message is "Operation is not supported by this type of object"
So far as I can see everything is properly declared as DAO so should be no ambiguity with ADO not liking FindFirst.
Anyone got any bright ideas? If so I would love to hear since I have used this sort of process lots of times without any issue.
Thanks

 

[DCrake]

Simple Software Solutions

Hi

When you type in your rstSwc. Do you get the FindFirst option ?

If not try using the Seek method. I always find this works faster anyway as you can nominate which index to use and use more than one parameter. Type In Seek - Highlight then F1 for more help and examples on this topic.

CodeMaster:

 

[KenHigg]

What line does it zonk on?

 

[Malcy]

What line does it zonk on?

rstSwc.FindFirst "[lngSwc] =" & Me.txtSwc

 

[Malcy]

Hi

When you type in your rstSwc. Do you get the FindFirst option ?

Yes, FindFirst comes up quite happily in the list of options.
Am going to try another db where I use this and I KNOW it was working just in case there is a gremlin developed in my Access configuration

 

[Malcy]

Have just run the rst.findfirst code sequence in another database and it works OK so it isn't my Access configuration. It also has DAO 3.6 referenced.
Hmm ....

 

[gemma-the-husky]

its not just needing a space after the equals sign, is it?
also put an nz in , incase its null, although im sure thats not it

rstSwc.FindFirst "[lngSwc] = " & nz(Me.txtSwc,0)

 

[KenHigg]

What happens if you hard code the Me.txtSwc?

Change
"[lngSwc] =" & Me.txtSwc

to

"[lngSwc] =7"

??

 

[Malcy]

Have tried suggestions from both Ken and Gemma (had already thought of and tried Ken's off my own back)
Neither work.
SOmething really wierd is going on here. I have commented out as much as I can but will see if there is anything more I can comment out in case it is upsetting it
Thanks for the ideas - please try and keep them coming!!

 

[Malcy]

Might be on to something but it doesn't mean to me. When I look in the locals window whilst stepping in once I set rstAudit if I expand the rstAudit part is shows absolute position as 0 but when I step over to rstSwc and do the same then the absolute position shows as <Opertion is not supported by this typw of object>. All the other settings seem to be identical.
Is this significant? I suspect it may be but not sure what it is telling me or how it might have happened.

 

[KenHigg]

As a last resort - Try copying all of the objects into a new mdb.

 

[Malcy]

OK
I have resolved the issue by deleting the original tblSixWeekChecks, closing and restarting Access, and then re-creating the table.
This has put Absolute Position to 0 and it works
What a bugler!

If anyone reading this understands what happened I would still love to know
Thanks for your help and support

 

[KenHigg]

Sounds like something simply became corrupt.

 

[gemma-the-husky]

Set rstSwc = dbs.OpenRecordset("tblSixWeekChecks")
what about moving this down to the position you need it = perhaps doing the dlookup on this table a bit earlier is causing some issue

ie immediately above the rstswc.findfirst

---maybe dbs has lost scope as well for some reason