Run SQL after saving record

[Guest]

I have a form, Form1, based on a Table, TableA, which contains only one
field, FieldA (TextBox). When FieldA is updated, on Save, Exit, ChangeRecord
etc, I would like to execute an SQL feature both the OldValue and the Current
Value of the field.
The SQL goes like this:
Update TABLEB
SET FieldB = CurrentValue
Where FieldB = Old Value;

The problem is that on the Form's 'OnUpdate' Event that I am using, I can't
get Values from the TextBox.

Any Suggestions?

Thanks a lot,

 

[Guest]

Try something like:

Dim MySQL As String
MySQL = "Update TABLEB SET FieldB = " & Me.[FieldA] & " Where FieldB = " &
Me.[FieldA].OldValue
CurrentDb.Execute(MySQL), dbFailOnError

*******
If the field are text you need to add single quote
MySQL = "Update TABLEB SET FieldB = '" & Me.[FieldA] & "' Where FieldB = '"
& Me.[FieldA].OldValue & "'"

*******
Can you please explain why you need to update another table, you might be
able to get the same resault using a query

 

[Guest]

Value in FieldB in TableB is tha same as FieldA in TableA. And because I run
a report where I join the two tables on these two fields, I need them to
have the same value

Ο χÏήστης "Ofer Cohen" έγγÏαψε:

Try something like:

Dim MySQL As String
MySQL = "Update TABLEB SET FieldB = " & Me.[FieldA] & " Where FieldB = " &
Me.[FieldA].OldValue
CurrentDb.Execute(MySQL), dbFailOnError

*******
If the field are text you need to add single quote
MySQL = "Update TABLEB SET FieldB = '" & Me.[FieldA] & "' Where FieldB = '"
& Me.[FieldA].OldValue & "'"

*******
Can you please explain why you need to update another table, you might be
able to get the same resault using a query


--
Good Luck
BS"D

I have a form, Form1, based on a Table, TableA, which contains only one
field, FieldA (TextBox). When FieldA is updated, on Save, Exit, ChangeRecord
etc, I would like to execute an SQL feature both the OldValue and the Current
Value of the field.
The SQL goes like this:
Update TABLEB
SET FieldB = CurrentValue
Where FieldB = Old Value;

The problem is that on the Form's 'OnUpdate' Event that I am using, I can't
get Values from the TextBox.

Any Suggestions?

Thanks a lot,

 

[Guest]

Another one,

I tried the SQL, but Me.[FieldA] and Me.[FieldA].OldValue return the same
result. How can I get the previous Value of the field?


Ο χÏήστης "Ofer Cohen" έγγÏαψε:

Try something like:

Dim MySQL As String
MySQL = "Update TABLEB SET FieldB = " & Me.[FieldA] & " Where FieldB = " &
Me.[FieldA].OldValue
CurrentDb.Execute(MySQL), dbFailOnError

*******
If the field are text you need to add single quote
MySQL = "Update TABLEB SET FieldB = '" & Me.[FieldA] & "' Where FieldB = '"
& Me.[FieldA].OldValue & "'"

*******
Can you please explain why you need to update another table, you might be
able to get the same resault using a query


--
Good Luck
BS"D

I have a form, Form1, based on a Table, TableA, which contains only one
field, FieldA (TextBox). When FieldA is updated, on Save, Exit, ChangeRecord
etc, I would like to execute an SQL feature both the OldValue and the Current
Value of the field.
The SQL goes like this:
Update TABLEB
SET FieldB = CurrentValue
Where FieldB = Old Value;

The problem is that on the Form's 'OnUpdate' Event that I am using, I can't
get Values from the TextBox.

Any Suggestions?

Thanks a lot,

 

[Guest]

In which event did you tried this code?
Try it on the before update event of the form and not on the AfterUpdate
event (if that the case).

Also, I assume that this field is bound to a field in the table.

--
Good Luck
BS"D

Another one,

I tried the SQL, but Me.[FieldA] and Me.[FieldA].OldValue return the same
result. How can I get the previous Value of the field?


Ο χÏήστης "Ofer Cohen" έγγÏαψε:

Try something like:

Dim MySQL As String
MySQL = "Update TABLEB SET FieldB = " & Me.[FieldA] & " Where FieldB = " &
Me.[FieldA].OldValue
CurrentDb.Execute(MySQL), dbFailOnError

*******
If the field are text you need to add single quote
MySQL = "Update TABLEB SET FieldB = '" & Me.[FieldA] & "' Where FieldB = '"
& Me.[FieldA].OldValue & "'"

*******
Can you please explain why you need to update another table, you might be
able to get the same resault using a query


--
Good Luck
BS"D

I have a form, Form1, based on a Table, TableA, which contains only one
field, FieldA (TextBox). When FieldA is updated, on Save, Exit, ChangeRecord
etc, I would like to execute an SQL feature both the OldValue and the Current
Value of the field.
The SQL goes like this:
Update TABLEB
SET FieldB = CurrentValue
Where FieldB = Old Value;

The problem is that on the Form's 'OnUpdate' Event that I am using, I can't
get Values from the TextBox.

Any Suggestions?

Thanks a lot,

 

[Guest]

Thanks, it worked

Ο χÏήστης "Ofer Cohen" έγγÏαψε:

In which event did you tried this code?
Try it on the before update event of the form and not on the AfterUpdate
event (if that the case).

Also, I assume that this field is bound to a field in the table.

--
Good Luck
BS"D

Another one,

I tried the SQL, but Me.[FieldA] and Me.[FieldA].OldValue return the same
result. How can I get the previous Value of the field?


Ο χÏήστης "Ofer Cohen" έγγÏαψε:

Try something like:

Dim MySQL As String
MySQL = "Update TABLEB SET FieldB = " & Me.[FieldA] & " Where FieldB = " &
Me.[FieldA].OldValue
CurrentDb.Execute(MySQL), dbFailOnError

*******
If the field are text you need to add single quote
MySQL = "Update TABLEB SET FieldB = '" & Me.[FieldA] & "' Where FieldB = '"
& Me.[FieldA].OldValue & "'"

*******
Can you please explain why you need to update another table, you might be
able to get the same resault using a query


--
Good Luck
BS"D


:

I have a form, Form1, based on a Table, TableA, which contains only one
field, FieldA (TextBox). When FieldA is updated, on Save, Exit, ChangeRecord
etc, I would like to execute an SQL feature both the OldValue and the Current
Value of the field.
The SQL goes like this:
Update TABLEB
SET FieldB = CurrentValue
Where FieldB = Old Value;

The problem is that on the Form's 'OnUpdate' Event that I am using, I can't
get Values from the TextBox.

Any Suggestions?

Thanks a lot,