BOF Not Working Properly 2

[Sydney1]

Hi,

I'm trying to save a new record, SQL Server table in the backend and Access 97 frontend, by going to the previous record and then back to the present one. That part works, but when I'm on the first record I get the previous record error and the DB crashes. I put in the BOF part in to check if I'm on the first record, but it isn't working properly. MYDB and Mytable are referenced publicly. Not sure if I have to use recordsetclone.

Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("qryMainForm", dbOpenDynaset, dbSeeChanges)

If Not rst.BOF Then

Application.Echo False
DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext
DoCmd.GoToControl "cboLitType"
Application.Echo True

Else
Exit Sub
 End If
If MyDBOpen = True Then MyDB.close: MyDBOpen = False
 If MyTableOpen = True Then MyTable.close: MyTableOpen = False

Any help would be greatly appreciated.

Thanks

Sydney

 

[mp9]

Why not use

DoCmd.RunCommand acCmdSaveRecord

to save your record instead, thereby circumventing this problem.

Hope this helps?

http://www22.brinkster.com/accessory/

 

[Sydney1]

Hi,

Thanks for the reply. Since its a SQL Server Backend table the save command doesn't work. Went through that problem a couple of weeks ago.

Thanks,

Sydney

 

[PaulF]

I've never dealt with SQL server as the backend but you might try using RecordsetClone's AbsolutePosition

Me.RecordsetClone.Bookmark = Me.Bookmark
If Me.RecordSetClone.AbsolutePosition <> 0 Then


PaulF

 

[PHV]

And what about something like Me.Dirty = False ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mp9]

Sorry about the acCmdSaveRecord suggestion, guess I should have read your post more carefully.

Can't see why BOF doesn't work... except that, if your code sample is complete, shouldn't it be

If Not [b]MyTable[/b].BOF Then

???

http://www22.brinkster.com/accessory/

 

[Sydney1]

Hi,

Thanks for the replies. I really appreciate the help.

Using RecordsetClone's AbsolutePosition worked like a charm. Is there any way to use it to see if you're on a new record or not?

Thanks again.

Sydney

 

[PaulF]

if Me.NewRecord Then
msgBox "At New Record"
End if

PaulF

 

[mp9]

If Me.NewRecord Then...

http://www22.brinkster.com/accessory/

 

[Sydney1]

Hi,

That worked too. Thanks for all your help.

Sydney

 

[Sydney1]

Hi, I'm back to needing the EOF to work properly.

I presently have the following code that registers when its not at the EOF, but doesn't work when at the last record.

 Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("qryMainForm", dbOpenDynaset, dbSeeChanges)
If MyTable.EOF Then
 MsgBox "You've reached the end blah blah"
Else
DoCmd.GoToRecord , , acNext
End If

mytable and mydb are publicly named.

Any suggestions would be greatly appreciated.

Sydney

 

[PaulF]

try

Me.RecordsetClone.Bookmark = Me.Bookmark

If Me.RecordSetClone.AbsolutePosition + 1 = MeRecordSetClone.RecordCount 0 Then
MsgBox "You've reached the end blah blah"
End if

PaulF