Recordset problems

[helpneeded]

I'm currently writing some code that opens up a recordset tblTitle, finds the last library number, increments it by one and then copies the value back to a new record in that recordset together with some values from a form or adds a copy to another table if the library number exists. When adding a new title it randomly crashes when creating a new record. It seems like it hasn't picked up the last library number value in the recordset. It works fine when there is only one field in the table, but the actual table has 25 fields. I have found one work round, create a new one row table to store the last library number, seems like a cack way round the problem. If anyone can help I will sleep easier at night.

Dim db As DATABASE
Dim rst As Recordset
Dim rstClone As Recordset
Dim rstCopy As Recordset
Dim GetBookNum As Integer
Dim GetCopyNum As Integer
Dim StrLibNum As String

Set db = CurrentDb
Set rst = db.OpenRecordset("tblTitle&quot
Set rstClone = Me.RecordsetClone
rstClone.Bookmark = Me.Bookmark

If Me.strStatus = "new" Then
rst.MoveLast
GetBookNum = rst!lngLibrarynumber + 1
'Debug.Print GetBookNum
rst.AddNew
rst!lngLibrarynumber = GetBookNum
rst!strTitle = Me.strTitle
rst.Update

rst.Close
rstClone.Close
db.Close

Else
StrLibNum = Me.lngLibrarynumber
strSql = "SELECT lngLibraryNumber, lngCopyNumber, strOrderReference FROM tblCopies WHERE [lngLibraryNumber]=" & StrLibNum & " order by lngCopyNumber"

Set rstCopy = db.OpenRecordset(strSql)

rstCopy.MoveLast
GetCopyNum = (rstCopy!lngCopyNumber) + 1
rstCopy.AddNew
rstCopy!lngLibrarynumber = Me.lngLibrarynumber
rstCopy!strOrderReference = Me.strOrderNumber
rstCopy!lngCopyNumber = GetCopyNum
rstCopy.Update
rstCopy.Close
rstClone.Close
db.Close

End If

 

[AlexMidd]

I see that you have "Debug.Print GetBookNum", commented out, in the code. Did this display the correct number in the debug window when you tested it? Have you tried just pulling out that number to see if it works, then add other code one by one, testing all the time to see if the right numbe ris being pulled out?

Also, is lngLibraryNumber the name of a field in the tblTitle table? In which case should you not refer to it as follows:

rst.Fields("lngLibraryNumber&quot.Value Have fun! )

Alex Middleton

 

[damber]

May not be what you are looking for but... from what I can gather the library number increments and you want to add a new record if the form control 'status' field = new and use the next number in the sequence from the values in the table?

Try getting the Max number from the table during the SQL statement.. e.g:

strSQL = "SELECT MAX(lngLibraryNumber) AS MaxNumber FROM tblTitle;"

set rsmyRecordset = dbMyDB.openrecordset(strSQL, dbopensnapshot)

'then add 1 to it if recordset has content else just use 1 , this can then be used as the new number

if rsMyRecordset.recordcount > 0  then
   intMyNewNumber = rsMyRecordset("MaxNumber") + 1
Else
   intMyNewNumber = 1
End if

'then continue as normal..

As this does not open a dynaset recordset you may want to include it ASWELL as your original code. BUT you can redo the whole thing if you want and run SQL statements build prior to execution from the form data..:

strSQL = _
"INSERT INTO tblTitles ( lngLibrarynumber, strTitle ) " & _
"SELECT " & intMyNewNumber & ", " & chr(34) & _
me.strTitle & chr(34) & ";"

docmd.setwarnings false
docmd.runsql(strSQL)
docmd.setwarnings true

The chr(34) bit allows for ' apostrophes to be used in the title, as it represents the double quote.

Up to you, though I am sure the Max function will help in whatever you decide.

Hope that helps.

Damian


damber@damber.net

A smile is worth a thousand kind words. So smile, it's easy!

 

[helpneeded]

Thanks guys for your help, I will give it a go. Did you hve any idea why the code works most of the time. I tried running the code on the timer event, sometimes it would run over 2000 times before failing, other times just 70. It seemed to hold the last value in memory. The only way to clear was by closing down the app or compacting the database.

Chris

 

[AlexMidd]

Could be something to do with memory resources available a the time the crash occurs. Have fun! )

Alex Middleton