save data

[Marta]

Hi,
I am trying to save data form text640 into my table Odmeny to field Poznamky1. Here is the code I use:

Dim recset As Recordset
Set recset = CurrentDb.OpenRecordset("Odmeny")
Do Until recset.EOF
recset.Edit
recset![Poznamky1].Value = Me.Text640
recset.Update
recset.MoveNext
Loop

Is works but the problem is that it saves the same number from text640 to every field Poznamky1. The text640 is not a part of any table and recordset, I use it to calculate something and it changes for every record in my table.
Thank you very much for help.

 

[boblarson]

1. Which table is used for your form's recordset?

2. Curious as to why you are interating through like this instead of setting up an update query.

3. You should name your text640 something meaningful so it will help others in the future know what it is immediately instead of having to go search the form for it to figure out what it is referring to. All of your controls like text boxes, combo boxes, listboxes, option groups, check boxes, toggle buttons, etc. should be named appropriately. Labels can probably not be named unless you use them in code for some reason.

 

[Marta]

1. I use table Odmeny as a recordset.
2. I don't know how to set up an update query and I find this code in a book so I tried it, but it doesn't work good for me. Can you tell me how to set up an update query, but the field text640 is not a part of any table.
Thank you very much.

 

[boblarson]

It would be something like this:

Dim strSQL As String
 
strSQL = "Update Odmeny Set Poznamky1 = " & Me.Text640
 
CurrentDb.Execute strSQL, dbFailOnError

and if Poznamky1 is text it would need to be changed like:

strSQL = "Update Odmeny Set Poznamky1 = " & Chr(34) & Me.Text640 & Chr(34)

or if it is a date datatype:

strSQL = "Update Odmeny Set Poznamky1 = #" & Me.Text640 & "#"

 

[Marta]

Thank you for answer, but is this code OK? It shows me run-time error 3144, Syntax error in update statement.

 

[boblarson]

What datatype is Poznamky1?

What is the code you have that is causing an error?

 

[Marta]

Poznamky1 is a number field and I want to save there a number from text640.
When I run the code this statement is highlighted: CurrentDb.Execute strSQL, dbFailOnError

 

[boblarson]

Just before that line, put

Debug.Print strSQL

and then try running again. When it fails, go to the IMMEDIATE WINDOW in the VBA area and copy the output and post it here so we can see what it is.

 

[Marta]

Hi,

I run the code and there is the same error message. So I copied the text in immediate window and here is it:;
UPDATE Odmeny SET Poznamky1= 5,98

 

[boblarson]

What language version of Access are you using and what are your region settings set to? I just tried a similar update query and if I used 5,98 it would generate the same error because my regional settings expect a PERIOD instead of a comma. So I would have to use 5.98 and it works.

 

[spikepl]

as a follow up on Bob's post, try
strSQL = "Update Odmeny Set Poznamky1 = " & Str(Me.Text640)
or, if that does not produce a period (instead of comma)
strSQL = "Update Odmeny Set Poznamky1 = " & Str(Csng(Me.Text640))