SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Access '10 to a linked SQL table save code broken
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amanalyn
Starting Member

USA
9 Posts

Posted - 11/09/2012 :  10:37:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/10/2012 :  08:02:22  Show Profile  Reply with Quote
This many not necessarily the problem, but you should move the sn.Update and sn.Close to above the Endif
....
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
....
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000