[AccessD] Re: File Sharing lock count exceeded

Rocky,

Break your process into steps.

The following should be close but I think the term is air code.

dim db as database, rs as recordset, rsTax as recordset
dim strSQL as string
dim curSalesTax as currency
strSQL = "Select * "
strsql = strsql & " FROM tblPrintRecoveryForm"
strsql = strsql & " WHERE Nz(fldInvoiceDetailPriceShouldBe) = 0;)
Set rs = db.OpenRecordset(strsql, dbopendynaset)
if not(rs.eof and rs.bof) then  ' records were returned
      rs.close
      strsql = "UPDATE tblPrintRecoveryForm SET PriceError = 0,"
      strsql = strsql & " TotalDue = 0"
      strsql = strsql & " WHERE Nz(fldInvoiceDetailPriceShouldBe) = 0;)
      docmd.runsql strsql
end if
strSQL = "Select * "
strsql = strsql & " FROM tblPrintRecoveryForm"
strsql = strsql & " WHERE fldInvoiceDetailPricePaid < 0;)"
Set rs = db.OpenRecordset(strsql, dbopendynaset)
if not(rs.eof and rs.bof) then  ' records were returned
      rs.close
      strsql = "UPDATE tblPrintRecoveryForm"
      strsql = strsql & " SET PriceError = fldInvoiceDetailPricePaid,"
      strsql = strsql & " TotalDue = PriceError * fldInvoiceDetailQuantity"
      strsql = strsql & " WHERE fldInvoiceDetailPricePaid < 0;)"
      docmd.runsql strsql
end if
strSQL = "Select * "
strsql = strsql & " FROM tblPrintRecoveryForm"
strsql = strsql & " WHERE fldInvoiceDetailSalesTax = 'Y';)"
Set rs = db.OpenRecordset(strsql, dbopendynaset)
if not(rs.eof and rs.bof) then  ' records were returned
      with rs
           .movefirst
           do until .eof
                strsql = SELECT *"
                strsql = strsql & " FROM tblTaxRates
                strsql = strsql & " WEHRE fldTaxRateStartDate <= #"
                strsql = strsql &  !fldInvoiceDate & "# AND 
fldTaxRateEndDate >= #"
                strsql = strsql & !fldInvoiceDate & "#;"
                set rstax = db.openrecordset(strsql, dbopensnapshot)
                if not(rstax.eof and rstax.bof) then  ' a tax rate was found
                     .edit
                          curSalesTax = !PriceError * rsTax!fldTaxRate
                          !SalesTax = curSalesTax
                          !TotalDue = (!PriceError + curSalesTax) * 
!fldInvoiceDetailQuantity
                     .update
                end if
                .movenext
           loop
      end with
end if
rs.close
rstax.close
set db=nothing


Watch for line wraps
HTH

Robert

At 11:45 AM 10/9/2003 -0500, you wrote:
>Date: Thu, 9 Oct 2003 09:44:40 -0700
>From: "Rocky Smolin - Beach Access Software" <bchacc at san.rr.com>
>Subject: [AccessD] File Sharing lock count exceeded
>To: <AccessD at databaseadvisors.com>
>Message-ID: <015f01c38e84$a2551ca0$6701a8c0 at HAL9002>
>Content-Type: text/plain; charset="iso-8859-1"
>
>Dear List:
>
>I am getting an error in a loop which says:
>
>File sharing lock count exceeded.  Increase MaxLocksPerFile registry entry.
>
>I don't think that's the solution.  The lock limit should not be 
>exceeded.  Somehow I'm not releasing the locks as they're being 
>created.  Is there a trick to doing that? It blows at different counts, 
>too.  Sometimes over 70,000 records get processed, sometimes 40,000.
>
>Here's the code:
>
>'********************************************************************************
>Set rstTaxRate = db.OpenRecordset("Select * FROM tblTaxRates")
>Set rstRecoveryTable = db.OpenRecordset("Select * FROM tblPrintRecoveryForm")
>
>With rstRecoveryTable
>
>If .BOF = True And .EOF = True Then GoTo GoodBye:
>
>.MoveLast
>txtTotalRecords = .RecordCount
>.MoveFirst
>lngCount = 0
>
>Do While Not .EOF
>
>     lngCount = lngCount + 1
>     If lngCount Mod 1000 = 0 Then
>         txtRecordNumber = lngCount
>         Me.Repaint
>     End If
>
>     .Edit
>
>     If Nz(!fldInvoiceDetailPriceShouldBe) = 0 Then
>         !PriceError = 0
>         !TotalDue = 0
>     End If
>
>     If Nz(!fldInvoiceDetailPricePaid) < 0 Then
>         !PriceError = !fldInvoiceDetailPricePaid
>         !TotalDue = !PriceError * !fldInvoiceDetailQuantity
>     End If
>
>     If !fldInvoiceDetailSalesTax = "Y" Then
>
>         ' find tax rate for this invoice date; if not found, tax is zero
>         rstTaxRate.FindFirst "fldTaxRateStartDate <= #" & !fldInvoiceDate _
>             & "# AND fldTaxRateEndDate >= #" & !fldInvoiceDate & "#"
>
>         If rstTaxRate.NoMatch = False Then
>             !SalesTax = !PriceError * rstTaxRate!fldTaxRate
>             !TotalDue = (!PriceError + !SalesTax) * !fldInvoiceDetailQuantity
>         End If
>
>     End If
>
>     .Update
>
>     .MoveNext
>Loop
>
>End With
>'*************************************************************************
>
>Any hints?
>
>MTIA,
>
>Rocky Smolin
>Beach Access Software