help last record in table not avaliable

[calenger]

Hi all

This is a weird one. we have an access 97 database that through a form bound to a query of the main table allows for a user to add a record. now we are unable to add any more records. although it looks like it is saving it is not. Just for debuging i created an openrecordset command before the save routine and have found that the recordset opens minus the recently add record. So I manually added a record to the table and found again that the recordset was only showing me the last record to be 2 records back. I am lost! It almost seems that the recocrds are not there but i can see them in the table.

The only other thing i can think to mention is that some users have both access 97 and 2000. this is happening with only one user right now.

[OBP]

Have you tried a Tools>"Repair & Compact" of the database?

[calenger]

Hi

Yes, sorry about the length but desprete.

More info might help I hope. Standard access 97 flat table design (5000 records) database. 2 joined colums to lookup tables but nothing to unsual. The new file request form is bound to a query (qryfile) and the fields on the form bound to thier various fields in the query. One calculated field. There is a cancel and save button. The save button code is
[VBA]
Private Sub cmdsave_Click()
On Error GoTo Err_cmdsave_Click
Dim intStyle As Boolean
Dim Cancel, RcdCount, Response2 As Integer
Dim stryear, strnum As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
fOKToClose = True

If IsNull(TypeID) Or Me.TypeID = 0 Then
MsgBox ("Please Pick a Category")
Exit Sub
End If

'check it the tblFile is not empty
RcdCount = DCount([FileID], "tblFile")
If RcdCount = 0 Then 'first record
FileNo = Right(str(Year(Now)), 2) + "-0001"
Me![txtFileNo] = FileNo
Me![Opened] = Date
DoCmd.Close
Response2 = MsgBox("File No. is " & FileNo, 64, "New File Request")
Exit Sub
Else ' here down is mine. opens the table to pull the last file no. i changed this 'originally because i thought the old code for the fileno generation to be the prob
Set rs = db.OpenRecordset("tblfile")
rs.MoveLast
strnum = rs!FileNo
Set rs = Nothing
Set db = Nothing
Forms.frmFile!txtOpened = Date
Me![Opened] = Date
Me![txtFileNo] = stryear
stryear = Format(Year(Date), "yy") & "-" & Mid(strnum, 4) + 1 ' creates date 'based on year and last fileno (yy-001)(05-001)
Response2 = MsgBox("File No. is " & stryear, 64, "New File Request")
End If

If IsNull(Me![txtFileNo]) Then ' trying to trap the fileno generation error originaly
MsgBox "File cannot be saved due to File Number Generation Error", vbCritical, Error
Me.Undo
DoCmd.Close
Exit Sub
End If

DoCmd.Close

Exit_cmdsave_Click:
Exit Sub

Err_cmdsave_Click:
'MsgBox (Err.Number)
MsgBox "File cannot be saved due to " & Err.Description & " Error", vbCritical, Error
Me.Undo
DoCmd.Close

Resume Exit_cmdsave_Click[/VBA]


This has been a historic issue and what I thought originally was wrong.
It seems that the record is somehow not being saved correctly with out error but it is there when you go look at the table or even close and reopen the DB. For some unknown reason the last record and ever record after the initial prob is not returned by the openrecordset. Also tryed progamaticly to do an recordset.eof and then a moveprevious but same result.

[OBP]

Why do you have a "Save" button, Access automatically saves the data?

[calenger]

Just for the file no generation. two digit year plus sequnce number (05-???)

Intresting thing i found is that even when I remove the last record and manually enter a new record in the table directly. The same issue occurs.

[OBP]

Have you written a competely new query for this table alone to see if when you run it manually the recordset contains the last record?

[calenger]

Hi

Thanks again for the help. I have not but will definatly try.

Another interesting point that could lead in another whole direction. this morning when I came in, i tested the database that resides on our server and found that it still had the problem. Last night I had copied it down to my local pc so I decided to test that one also. Same problem as the networked one. Then I rebooted my local PC and tested my local copy again and it worked!

[xCav8r]

Cal, I know you inherited this code, but it still needs cleaning in my opinion. My comments are embedded. Take special note around the MoveLast statement. I think that's where your problem lies.

Two tips:

1. To use the VBA tags, either highlight your code and hit the VBA button or hit the VBA button and then insert your code between [ VBA ] and [ / VBA ]. You had [ VBA ] [ /VBA] before and after your code, but it only marks up what's in between those tags. (I had to pad the tags here with spaces to get them to show up in the post.)
2. When you're sharing code, it's a good idea to keep your maximum line length below 81 characters; otherwise, you force lateral scrolling. I modified your comments to prevent that. Learn to use underscore instead.

[VBA]
Private Sub cmdsave_Click()
On Error GoTo Err_cmdsave_Click
' xCav8r: int is the prefix usually used for an integer, but you have intStyle as Boolean
' the prefix for that is usually blnStyle, boolStyle, bolStyle (and sometimes fStyle)
Dim intStyle As Boolean
' xCav8r: only Response2 is typed as an integer in the declaration below;
' declaring them sequentially on the same line does not type all of them--
' only the last. Cancel and RcdCount, therefore, are Variants.
Dim Cancel, RcdCount, Response2 As Integer
' xCav8r: see above re: stryear being a Variant.
' Also, I don't mean to nitpick, but I suggest
' using strYear vs. stryear. If you capitalize distinct words in your variable
' names, then you'll know when you've typed them correctly when you
' intentionally type in in all lower case. If you move to the next line and
' they're still in lower case, then you know you've not spelled them as you've
' declared them.
Dim stryear, strnum As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
' xCav8r: is this a module or global level variable, or are you not using Option Explicit?
fOKToClose = True

If IsNull(TypeID) Or Me.TypeID = 0 Then
MsgBox ("Please Pick a Category")
Exit Sub
End If

'check it the tblFile is not empty
RcdCount = DCount([FileID], "tblFile")
If RcdCount = 0 Then 'first record
FileNo = Right(str(Year(Now)), 2) + "-0001"
Me![txtFileNo] = FileNo
Me![Opened] = Date
' xCav8r: why do you close the form before getting Response2?
DoCmd.Close
' xCav8r: you don't do anything with Response2, but it never gets to
' fire anyway because the form is closed and thus stops the execution
' of the code. Or am I smoking crack?
Response2 = MsgBox("File No. is " & FileNo, 64, "New File Request")
Exit Sub
Else ' here down is mine. opens the table to pull the last file no. i changed this
' originally because i thought the old code for the fileno generation to be
' the prob
' xCav8r: if performance is an issue, then you should only pull the fields
' you need in the recordset. Also, you should really be sorting this
' based on the FileNo so when you MoveLast, you know you're at the
' right value
Set rs = db.OpenRecordset("tblfile")
' xCav8r: I know that tblFile will always have records, so this comment is for
' others that might be looking at my comments. MoveLast can cause an
' error, and you should look up EOF BOF if you're not already familiar with
' that concept.
rs.MoveLast
strnum = rs!FileNo
Set rs = Nothing
Set db = Nothing
' xCav8r: does Me <> frmFile? I assume the answer is yes.
Forms.frmFile!txtOpened = Date
Me![Opened] = Date
Me![txtFileNo] = stryear
' xCav8r: use the + symbol carefully when sticking strings together
stryear = Format(Year(Date), "yy") & "-" & Mid(strnum, 4) + 1
' creates date 'based on year and last fileno (yy-001)(05-001)
' xCav8r: again, you're not doing anything with Response2. Why is this
' being assigned to a variable?
Response2 = MsgBox("File No. is " & stryear, 64, "New File Request")
End If

' xCav8r: why would this value be null?
If IsNull(Me![txtFileNo]) Then ' trying to trap the fileno generation error originaly
MsgBox "File cannot be saved due to File Number Generation Error", vbCritical, Error
Me.Undo
DoCmd.Close
Exit Sub
End If

' xCav8r: as a rule, I like to stick clean up activities beneath my exit labels
' so I can see the clean up activities that need to be done before exiting
' which can sometimes be helpful when troubleshooting
DoCmd.Close

Exit_cmdsave_Click:
Exit Sub

Err_cmdsave_Click:
'MsgBox (Err.Number)
' xCav8r: Is this information of any value to the lawyers? Perhaps you should consider
' logging the error instead for support personnel to deal with. As an
' alternative, you could provide your clients with something less cryptic.
MsgBox "File cannot be saved due to " & Err.Description & " Error", vbCritical, Error
Me.Undo
DoCmd.Close
' xCav8r: There is no reason to resume to your exit label when you're doing cleanup
' in your error handler
Resume Exit_cmdsave_Click[/VBA]

[geekgirlau]

Whenever you use a recordset, close the object in your exit label. This way you can be sure that the object is being closed correctly even if an error occurs.

[VBA] Exit_cmdsave_Click:
rs.close
db.close
set db = Nothing
Exit Sub

[/VBA]

[xCav8r]

I think good programming practices also dictate setting the recordset object variable to nothing after closing.

[VBA] Set rs = nothing[/VBA]

Good catch. I missed that. GeekGirl rox!