save value in textbox to field in table

[Freddie81]

How would I go about saving the value of a txtbox to a field in a table without setting the txtbox source to the field in property sheet?
Example:
I have a form calculating totals. I have an unbound textbox to display the value of the Dsum function. Now I need that textbox to save the vale to the field in my table.
Table is InvoiceT
Field is IBalance


Textbox is txtTempInvBalance
I tried:
[InvoiceT.IBalance] = Me.txtTempInvBalance
but It kicks error saying you cant assign a value to this object
Any advice for me ?

[Gicu]

Use update query or SQL:

dim sSQL as string
sSQL="UPDATE InvoiceT SET IBalance = " & Me.txtTempInvBalance
CurrentDB,Execute sSQL,dbFailOnError

[Freddie81]

CurrentDb.Execute sSQL, dbFailOnError givs syntax error when I run it

[Freddie81]

This is what I get nd the debug screen

[isladogs]

What is the datatype for IBalance field you are updating?

Also the Form_Open event isn't appropriate as the form hasn't fully loaded

[Freddie81]

IBalance is number type and format in table is Double
I do not really have a clue what event does what so I assumed it would be the correct one. What in your opinion would be the correct event?

[isladogs]

Ok so the sql should be fine once the form is fully loaded

Try placing at the end of the form load event
Also add the line DoEvents just before the sSQL line to ensure previous code has completed

If it still errors, then I'd place the code in a button click event

[Freddie81]

Moved it to the On Load Event and added the DoEvents line. Still getting the error. "Syntax error in UPDATE statement

[Freddie81]

I have even tried :

DoCmd.RunSQL ("UPDATE InvoiceT SET IBalance= " & Me.txtInvBalance & ";")
Getting the same error

[isladogs]

Ok try adding the line Debug.Print sSQL before the Execute line and post the result you see in the VBE Immediate window
If you get an error, check the DSum and possibly surround with Nz(....,0)
You might also CHANGE to "InvoiceID=" & me.InvoiceID

Also, have you checked the field, table and control names are correct.

[davegri]

Try moving the below lines to the Form_Current event:

Dim sSQL as string
sSQL = "Update  InvoiceT SET IBalence = " & txtInvBalance
debug.print sSQL
Currentdb.execute sSQL, dbFailOnError

As Colin noted, if it still fails, it's probably because
1. InvoiceT is misspelled or is not a table at all, or IBalance is not a field in that table
2. txtInvBalance holds an invalid value.
3. Ibalance is not a numeric field in the table.
The debug.print should show you the actual value of txtInvBalance being used, as well as you being able to see it on the form.

Edit:
Rereading your posts, I see that the source control name for the update is sometimes txtTempInvBalance and sometimes txtInvBalance. Only one is correct.

[Freddie81]

Ridders52, the Immediate window shows UPDATE InvoiceT SET IBalance= -20. This is the same amount my Dsum gives so Dsmu is working like it should

All the names are correct for Field, Table and control...just double checked

"

[Freddie81]

Davegri...the table does exist and is spelled correctly as does IBalance. txtInvBalance displays the correct information as per Dsum. I used MsgBox to display each Variable and they all check out. IBalance is a number field but it is formatted to Double.

[isladogs]

Try this

CurrentDB.Execute "UPDATE InvoiceT SET InvoiceT.IBalance = " & Me.txtInvBalance, dbfailonerror

If it still fails, then despite your checks, something must be spelt wrongly or there is a reference issue or a compile error.
Did you add Nz
You could try form current or a button click event as already suggested but I don't think that's the issue

[davegri]

Well, that exhausts our offhand ideas. We need to see the database. If it holds sensitive data, post a copy with a couple of phony entries to demonstrate the problem.