[slscheer]

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

[Nayana]

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.

[slscheer]

slscheer, 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

[PsychoCoder]

Did his answer solve your problem, all you did was report your original question

[Nayana]

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.

[slscheer]

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!!!