Unable to fix a missing operator error

[dhaneshjs]

Hi Friends,

i have a Form which uses unbound textbox to display values from a table.
Since it is textbox it will not be retain its value once i close access
in order to view what i last entered i am trying to copy the values to another table
and then on opening access again textboxes will be populated values from this table.

But i am finding a problem while executing update command on closing

DoCmd.RunSQL "UPDATE Table2 " & _
"SET Table2.ID = " & ref & " " & _
"SET Table2.Date = #" & date1 & "# " & _
"SET Table2.Time = #" & time1 & "# " & _
"SET Table2.VisitorName = '" & visitorname & "' " & _
"SET Table2.POBRFC = '" & pobrfc & "' " & _
"SET Table2.VisitorFrom = '" & visitorfrom & "' " & _
"SET Table2.PR = '" & pr & "' " & _
"SET Table2.RepeatNew = '" & repeatnew & "' " & _
"SET Table2.Status = '" & status & "' " & _
"WHERE Table2.Slno = " & slno & ";"


This statement is returning an error
as

Runtime error '3075'

Syntax error (missing operator)

i am unable to identify the syntax error how much i tried

Please Help me on this



Thanks in advance
Dhanesh

[orange]

You do NOT use a SET for each column to be updated. Only 1 SET is used.

see http://www.w3schools.com/sql/sql_update.asp

[dhaneshjs]

Hi Orange,

It worked. Thanks a lot
Now i have a small problem

How to enter blankspace to a field in a table
which is having data type as 'Number'

My issue is i am
taking values from a text box
to a variable of 'INTEGER' data type

This variable is then used to update a tablefield with 'NUMBER' data type

Dim ref as Integer
Dim intK as Integer

.
.

If (IsNull(Me("Text" & intK))) Or Me("Text" & intK).Value = "" Then
ref = ""
Else
ref = Me("Text" & intK).Value
intK = intK + 1

After this i am updating a table
using DoCmd.RunSQL

While executing
data mis match error is coming on line marked red

Please help me on this

Thanks in advance
Dhaneshjs

[orange]

Why are you putting a sting in a number field? Do you want a 0?
Please describe why?

[dhaneshjs]

Hi Orange,

I don't want 0 appearing in the table field
why i am asking is that i have
a group of text boxes, at times some of these text boxes
will have nothing in it.
i am updating the values in all textboxes to the table at the time of closing the form
so some textboxes may have certain values, some carry null also
i am passing these values to a table and which ever textboxes have null value
i want it to be converted to blankspaces in the table field
so even if its a number data type field , i want blank spaces to come in
the table.
This is in order to avoid a error message popping up like " null value detected"

Please help on this

Thanks in advance,
Dhaneshjs

[orange]

Have you seen the NZ() function? You could display 0, or something else, if the field is null.

[dhaneshjs]

Hi Orange,

Could you also be helpfull
in suggesting a way out for what i am planning
As i have posted earlier
i have several unbound text boxes as matrix(9 columns and 15 rows) in
the form
i am populating values to these textboxes from bound text boxes(to a table)
that works as a place to input values.
unbound text boxes gets value from the input bound textboxes and
this unbound textbox matrix is for "displaying purpose".
some of the text boxes are
1. Status (IN/QUE/REVISIT)
2. DATE
3. TIME

I actually wanted to sort the matrix display as
status (ascending) then date (acsending) then time (ascending)

i thought i could populate the unbound textboxes to an array,
sort it as required
and put it back on the unbound textboxes on the click of a button.

i am finding it difficult to sort things like that using vba

is there a better way of doing it?
may be copying it to another table and then sort it and postback on unbound textboxes.

Please give your advice on this.

Thanks in advance
Dhaneshjs