5 Replies - 6484 Views - Last Post: 24 January 2008 - 11:49 AM Rate Topic: -----

#1 slscheer  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 24
  • Joined: 23-January 08

Object Variable or WIth Block Not Set

Post icon  Posted 23 January 2008 - 06:49 PM

Ok SO i am new to this but I have written a program in which i can add data, with no problems, but when i t comes to a simple update i am just not getting it. FOr this i have created a simple form with two text boxes on it and one command button. The database holds a table called Invoices and the idea is to simply be able to change the amount of the balnce due as money is applied to it. the table has two fields that are important, 1 OrdrNum and 2 Balance I want to be able to update the balance on the record which matchs the orderNum (txtOrderID) with the number entered in the txtRemaining. I keep getting the above error and cant figure it out also i am not sure of the rest of the code Please Help!!

Thanks
Shannon

 Dim intOrderID As Integer
Dim IntBalance As Currency
Dim Myconn As ADODB.Connection
Dim statement As String
Dim conn As New ADODB.Connection

Private Sub cmdSave_Click()

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\TerrysCustomData\TerrysData.mdb'"

conn.Open
statement = " UPDATE Invoices " & _
" SET Balance = '%" & txtRemaining & "%' "
'" WHERE OrdrNum = '%" & txtOrderID & "%'"

Myconn.Execute statement, , adCmdText
Myconn.Close

End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: Object Variable or WIth Block Not Set

#2 Nayana  Icon User is offline

  • DIC Hawk - 나야나 नयन:
  • member icon

Reputation: 31
  • View blog
  • Posts: 824
  • Joined: 14-November 07

Re: Object Variable or WIth Block Not Set

Posted 23 January 2008 - 09:51 PM

On which line of code do you get the error?

I'm guessing it is on Myconn.Execute....

I think the reason is that you have created the variable, but you haven't created the object (with the 'new' keywork).

Why are you using two connections anyway? Are they to the same database? If they are, you should only use the one (conn) connection.
Was This Post Helpful? 0
  • +
  • -

#3 slscheer  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 24
  • Joined: 23-January 08

Re: Object Variable or WIth Block Not Set

Posted 23 January 2008 - 11:28 PM

View Postslscheer, on 23 Jan, 2008 - 06:49 PM, said:

I made an error when i pasted the code the code had an additional comment ' in front of the WHer statement so i am reposting the attempted code i am using. Also Yes i am getting the error on the Execute line. I am not sure of the actual syntax or placment to SET the NEW object an example would be awesome.

Thanks again !!!

 Dim objserver As clsTerrysDat
Dim rsOrderNum As New ADODB.Recordset
Dim intOrderID As Integer
Dim IntBalance As Currency
Dim Myconn As ADODB.Connection
Dim statement As String
Dim conn As New ADODB.Connection


Private Sub cmdSave_Click()

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\TerrysCustomData\TerrysData.mdb'"

conn.Open
statement = " UPDATE Invoices " & _
" SET Balance = '%" & txtRemaining & "%' " & _
" WHERE OrdrNum = '%" & txtOrderID & "%'"

Myconn.Execute statement, , adCmdText
Myconn.Close

End Sub







Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Object Variable or WIth Block Not Set

Posted 23 January 2008 - 11:47 PM

Did his answer solve your problem, all you did was report your original question
Was This Post Helpful? 0
  • +
  • -

#5 Nayana  Icon User is offline

  • DIC Hawk - 나야나 नयन:
  • member icon

Reputation: 31
  • View blog
  • Posts: 824
  • Joined: 14-November 07

Re: Object Variable or WIth Block Not Set

Posted 24 January 2008 - 03:00 AM

slscheer:

My advice to you is to simply
Don't declare the myconn (get rid of it)

Change the reference from myconn, to just conn.
Was This Post Helpful? 0
  • +
  • -

#6 slscheer  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 24
  • Joined: 23-January 08

Re: Object Variable or WIth Block Not Set

Posted 24 January 2008 - 11:49 AM

Hi Everyone who tried to help me with this. It was driving me crazy but I finally figured it out the suggestions help greatly but here is the code that worked

Option Explicit


'Dim intOrderID As Integer
'Dim intBalance As Currency
'Dim conn As ADODB.Connection
Dim statement As String
Dim conn As New ADODB.Connection


Private Sub cmdSave_Click()

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\TerrysCustomData\TerrysData.mdb'"

conn.Open
statement = " UPDATE Invoices " & _
" SET Balance = " & txtRemaining & " " & _
" WHERE OrdrNum = " & txtOrderID & ""

conn.Execute statement, , adCmdText
conn.Close

End Sub



 


As you can see i got rid of the myconn references and changed the execute and close staements to reflect this (Thanks Nayana) I also had to remove the single quotes from around the txt(statements) it now works likea charm.

Once again Thank you everyone!!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1