Access '10 to a linked SQL table save code broken

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Access '10 to a linked SQL table save code broken

Author

Topic

amanalyn
Starting Member

9 Posts

Posted - 2012-11-09 : 10:37:25

Hey, I recently switched over to using SQL for the backend of my Access databases. The codes I have use to work, all I have just modified them a little to be able to use the SSRS easier.
However since I made the changes I keep getting an error message stating:
Quote:
Object variable or With block not set
My code is basically the following

Code:

Private Sub Save_Click()
On Error GoTo ErrorHandler
Dim db As Database
Dim sn As Recordset

strCount = 0

If Not IsNull(Me.dp1) Or Me.dp1 <> "" Then
'If you are adding a new record, then this code will work, if you finding an existing record you will need a where clause and an edit instead of addnew
Set db = CurrentDb
Set sn = db.OpenRecordset("SELECT * FROM dbo_FIDataEntry ORDER BY ID DESC", dbOpenDynaset, dbSeeChanges)

sn.AddNew
sn!DateTime = Now()
sn![N/S] = "N"
sn!datapoint1 = Me.dp1
sn!datapoint2 = Me.dp2
sn!Name = Me.name
sn!Shift = Me.Shift

End If

'Updates and closes the record
sn.Update
sn.Close

'Clears the fields for the next entry
Me.dp1 = Null
Me.dp2 = Null
Me.Shift = Null
Me.operator = Null

Exit_ErrorHandler:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume Exit_ErrorHandler

End Sub

I get the error message but it is still saving the data to SQL, but not clearing the fields. I have not encountered this issue before and I am not having any luck finding out how to fix it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-10 : 08:02:22

This many not necessarily the problem, but you should move the sn.Update and sn.Close to above the Endif

[code]....
sn!Name = Me.name
sn!Shift = Me.Shift

'Updates and closes the record
sn.Update
sn.Close

End If

'Clears the fields for the next entry
Me.dp1 = Null
....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources