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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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