error 2105 with gotorecord,, acnext (1 Viewer)

Hello everybody,

i am an access beginner and was surfing the hole day for a solution for my problem, but i can't get it..

With this vb-code i want to check wheter an i have an entry in a query (qryConnSampleTS) or not. if yes --> checkbox = true...
i dont want to check for each recordset manually therefore i put it under a current_event and at the end of the code a docmd.gotorecord acnext...
put it gives me an error 2105 (after 125-128 entries). i can stop the error msg with "on error resume next" but the acnext stops again after the same amount of entries...

here my code...

Thanks a lot!
David



Private Sub Form_Current()
Me.thinSectionC = False
Me.thinSectionUC = False

Dim db As DAO.Database
Dim rsTS As DAO.Recordset
Dim strSQLTS As String
Dim var
Set db = CurrentDb

strSQLTS = "SELECT sampleNr,covered FROM qryConnSampleTS"
Set rsTS = db.OpenRecordset(strSQLTS)
If Not rsTS.EOF Then rsTS.MoveFirst
Do While Not rsTS.EOF
var = rsTS!sampleNr
varString = CStr(var)
If Me.sampleNr = varString Then
If rsTS!covered = True Then
Me.thinSectionC = True
Else
Me.thinSectionUC = True
End If

End If
rsTS.MoveNext
Loop

rsTS.Close
Set rsTS = Nothing
strSQLTS = ""
'Set db = Nothing

If Me.CurrentRecord < Me.Recordset.RecordCount Then

On Error Resume Next
DoCmd.GoToRecord , , acNext

End If
Set db = Nothing
End Sub

Just use an error handler as that error just basically means you're already at one end of the recordset and can't move further.

thanks for the fast answer - i'll try that.
but i am quite sure that i am in the middle of my datasets (it stops at 127/220 if i start at the first, 128/220 if i start at the second and so on..)
i dont know if access has another order than displayed in the query?!

thanks
david

hi SOS

i put the error handler into the code but it doesn't help... i just got an error-msgbox at each entry...

Maybe all you would need is the DLookup() function for this. Have a look in the help files and online and see if that helps.

hi vbaInet,

tried that, it makes the same as all the DAO recordset stuff with exception that it checks only the first entry (only the first sampleNr in the table tblBlockSlab):

code1

test = DLookup("[sampleNr]", "tblBlockSlab")
If Me.sampleNr = test Then
Me.blockSlab = True
End If

therefore i modified it to:

code 2

If not isnull(DLookup("[sampleNr]", "tblBlockSlab", "[sampleNr]=" & Me.sampleNr) Then
Me.blockSlab = True
End If

this should (according to several forums) check ALL entries... but i get an error 2001...

anyway - code 1 goes also only a 127 entries and stops then! is the problem related to using the onCurrent event for that kind of code???

thanks for your help

Don't think that's the right event to be putting this kind of code. Why are you looking up that value on current? Should that not be on the Before Update event?

hi
i think i found the error (on another forum)...
i put the gotorecord command at the end of the on current code. doing so i am starting another instance of the code (i do not exit the code). at each record i am starting a new instance without stopping the code each time. so i get some 125 instances and memory is full?!
now i am trying to put an update button in another subform (not possible in the actual data sheet form) which is performing the gotorecord acnext until it is at the end of the recordset....
hope this works

david

Again, I will be weary putting such a code in the Current event, not good on performance and seems to be a waste of resources. What is the purpose of this?

Why are you looking to put an update button if your form is bound? Records are updated when you move to another record.

hi,

ok i'll try to explain myself better: i collect geological samples in the field and produce different types of samples (blocks, thin sections, polished sections etc) out of one sample. the form should just be an overview (field can not be edited manually) which sampletypes of which samplenr i actually have in my stockage... everytime i add or change something in my database (adding a sample type to a sample) then i want to see that in my overview. and of course i dont want to access each sample manually to get an update for all the different sample types i have made...