trying to update a field in a sopecific row in a table using code

[mark_jm via AccessMonster.com]

I and trying to get a value from "specific_field1" in "specific_row1" from
"table_1", and read the value into " specific_field2" in "specific_row2" in
"table2", using the Update command on the on update event of a field on a
form.

The code I have is UPDATE [table2] SET [table2].[field1] = cdttime WHERE (((
[tabel2].[row2])=strfilter1));

strfilter1 is variable with a value from the form inquestion which identifies
the row in table2
cdttime is a value read from table1 using dlookup.

The abve line of code is giving me problems, any ideas?

Ta
Mark

 

[Michel Walsh]

Dim SQLstring = "UPDATE table2 SET field1=FORMS!FormNameHere!cdttime WHERE
row2=" & strFilter1
Debug.Print SQLstring ' easier to debug, if there is problem
DoCmd.RunSQL SQLstring





where I assumed that cdttime is a control on a form and strFilter a string
where the value supply for row2. If that value is also a control on a form
better to use:


Dim SQLstring = "UPDATE table2 SET field1=FORMS!FormNameHere!cdttime WHERE
row2=FORMS!formNameHere!strFilter1"
DoCmd.RunSQL SQLstring


since you won't have to deal with delimiters (if any are required).

In the first case, the Debug.Print is used to print the resulting string
which is about to be interpreted as SQL statement. If there is a problem
with the statement, sometimes, you can try to cut and paste it into the SQL
view of a new query, to help you to see what is the problem (if it is not
already evident).




Vanderghast, Access MVP

 

[mark_jm via AccessMonster.com]

Thank you very much for the advise, but I seem to have just a little syntax
problem.
The code I wrote was

Dim SQLstring = "UPDATE [warranty clamis table] SET [warranty claims table].
[cdttime1] = " & cdttime " WHERE [claim no] = " & strFilter1

Warranty claims tabel is the tabel I am trying to update
cdttime1 is the field in that table I am trying to update.
cdttime is a variable declared in the procedure which stores the number I am
trying to write to the table
claim number is the field which identifies the row in the table
srtfilter1 is the value tht idetifies the row.

The error message is compile error expected end of statement when I try to
enter the line of code?

Dim SQLstring = "UPDATE table2 SET field1=FORMS!FormNameHere!cdttime WHERE
row2=" & strFilter1
Debug.Print SQLstring ' easier to debug, if there is problem
DoCmd.RunSQL SQLstring

where I assumed that cdttime is a control on a form and strFilter a string
where the value supply for row2. If that value is also a control on a form
better to use:

Dim SQLstring = "UPDATE table2 SET field1=FORMS!FormNameHere!cdttime WHERE
row2=FORMS!formNameHere!strFilter1"
DoCmd.RunSQL SQLstring

since you won't have to deal with delimiters (if any are required).

In the first case, the Debug.Print is used to print the resulting string
which is about to be interpreted as SQL statement. If there is a problem
with the statement, sometimes, you can try to cut and paste it into the SQL
view of a new query, to help you to see what is the problem (if it is not
already evident).

Vanderghast, Access MVP

I and trying to get a value from "specific_field1" in "specific_row1"
from

[quoted text clipped - 16 lines]

Ta
Mark

 

[Michel Walsh]

If cdttime1 is a date_time value, you need delimiter (#)
If claim no is a string, you also need delimiter ( single quote, or double
quote)


So, it MAY be:
---------------------------
Dim SQLstring AS string

SQLString = "UPDATE [warranty clamis table] SET [warranty claims table].
[cdttime1] = #" & cdttime "# WHERE [claim no] = '" & strFilter1 & "'"

Debug.Print SQLString

DoCmd.RunSQL SQLString
-----------------------------

(note that we cannot set a variable at the line we declare it, in VBA, my
mistake, in my initial code).



Vanderghast, Access MVP

Thank you very much for the advise, but I seem to have just a little
syntax
problem.
The code I wrote was

Dim SQLstring = "UPDATE [warranty clamis table] SET [warranty claims
table].
[cdttime1] = " & cdttime " WHERE [claim no] = " & strFilter1

Warranty claims tabel is the tabel I am trying to update
cdttime1 is the field in that table I am trying to update.
cdttime is a variable declared in the procedure which stores the number I
am
trying to write to the table
claim number is the field which identifies the row in the table
srtfilter1 is the value tht idetifies the row.

The error message is compile error expected end of statement when I try to
enter the line of code?

Dim SQLstring = "UPDATE table2 SET field1=FORMS!FormNameHere!cdttime
WHERE
row2=" & strFilter1
Debug.Print SQLstring ' easier to debug, if there is problem
DoCmd.RunSQL SQLstring

where I assumed that cdttime is a control on a form and strFilter a string
where the value supply for row2. If that value is also a control on a form
better to use:

Dim SQLstring = "UPDATE table2 SET field1=FORMS!FormNameHere!cdttime
WHERE
row2=FORMS!formNameHere!strFilter1"
DoCmd.RunSQL SQLstring

since you won't have to deal with delimiters (if any are required).

In the first case, the Debug.Print is used to print the resulting string
which is about to be interpreted as SQL statement. If there is a problem
with the statement, sometimes, you can try to cut and paste it into the
SQL
view of a new query, to help you to see what is the problem (if it is not
already evident).

Vanderghast, Access MVP

I and trying to get a value from "specific_field1" in "specific_row1"
from

[quoted text clipped - 16 lines]

Ta
Mark