More efficient way of doing this?

[Evan M]

Hello, i just wrote a routine to, if the value of a checkbox is true, set
the value of a text box in a table to a number on another form then set that
checkbox value to false. The problem is that when i click the button to
perform this operation, access freezes. I assume this is because there are
around 3000 records in this table? Does anyone know how to do this a better
way?

'code begins
'Add the the Quote Values to the line items
'Set the db
Set db = CurrentDb
'here rs1 becomes the table
Set rs1 = db.OpenRecordset("tblPartsLookupDoneLineItems", dbOpenTable)
If rs1.RecordCount > 0 Then
rs1.MoveFirst
Do While Not rs1.EOF
If rs1.Fields!QuoteNow = True Then
rs1.Edit
rs1.Fields!QuoteID = Forms![Parts Quote]!QuoteID
rs1.Fields!QuoteNow = binCheck
rs1.Update
rs1.MoveNext
End If
Loop
End If
'End Code

Any help would be greatly appreciated! Thanks, Evan M. evan AT
radiologyonesource DOT com

 

[Dan Artuso]

Hi,
Use an Update statement.

Dim strSql as String

strSql = "Update tblPartsLookupDoneLineItems " & _
"Set QuoteId = " & Forms![Parts Quote]!QuoteID & _
", QuoteNow =" & binCheck & _
" Where QuoteNow = -1"

CurrentDb.Execute strSql, dbFailOnError

I've assumed that QuoteId is numerical, if it's not, add quotes to delimit:
"Set QuoteId = '" & Forms![Parts Quote]!QuoteID & "'" & _

and that binCheck is simply true (-1) or false 0

Please make a backup before trying this.

 

[Evan M]

Thanks, Dan, I'll try that!

Hi,
Use an Update statement.

Dim strSql as String

strSql = "Update tblPartsLookupDoneLineItems " & _
"Set QuoteId = " & Forms![Parts Quote]!QuoteID & _
", QuoteNow =" & binCheck & _
" Where QuoteNow = -1"

CurrentDb.Execute strSql, dbFailOnError

I've assumed that QuoteId is numerical, if it's not, add quotes to
delimit:
"Set QuoteId = '" & Forms![Parts Quote]!QuoteID & "'" & _

and that binCheck is simply true (-1) or false 0

Please make a backup before trying this.

--
HTH
-------
Dan Artuso, MVP

Hello, i just wrote a routine to, if the value of a checkbox is true, set
the value of a text box in a table to a number on another form then set
that
checkbox value to false. The problem is that when i click the button to
perform this operation, access freezes. I assume this is because there
are
around 3000 records in this table? Does anyone know how to do this a
better
way?

'code begins
'Add the the Quote Values to the line items
'Set the db
Set db = CurrentDb
'here rs1 becomes the table
Set rs1 = db.OpenRecordset("tblPartsLookupDoneLineItems",
dbOpenTable)
If rs1.RecordCount > 0 Then
rs1.MoveFirst
Do While Not rs1.EOF
If rs1.Fields!QuoteNow = True Then
rs1.Edit
rs1.Fields!QuoteID = Forms![Parts Quote]!QuoteID
rs1.Fields!QuoteNow = binCheck
rs1.Update
rs1.MoveNext
End If
Loop
End If
'End Code

Any help would be greatly appreciated! Thanks, Evan M. evan AT
radiologyonesource DOT com

 

[Albert D. Kallal]

You have a bug in your code...and thus it freezes.

You have:

If rs1.RecordCount > 0 Then
rs1.MoveFirst
Do While Not rs1.EOF
If rs1.Fields!QuoteNow = True Then
rs1.Edit
rs1.Fields!QuoteID = Forms![Parts Quote]!QuoteID
rs1.Fields!QuoteNow = binCheck
rs1.Update
rs1.MoveNext <---- old place
End If

rs1.MoveNext <--- move to here

Loop
End If
'End Code

Note that you "movenext ONLY runs when you test if the value is true, .you
need to move that out of the IF statement.

3000 records should run very fast....less then one second easy...