Updating a form form another table

[Cyberwolf]

I have a form that users enter information into. What I want to do is
to update certain fields from another table once they have entered a
number in a field. The form is already based on a query as this allows
the user to see only the records they entered. What would be the best
approach to this?

TIA,

Cyberwolf

[ManningFan]

I'd probably do something like (and this is just an example that you
will need to adapt to your needs) ...

dim db as database
dim rec as recordset
dim var1, var2, var3 as variant

set db = currentdb
set rec = db.openrecordset ("OtherTable")

var1 = me.field1
var2 = me.field2
var3 = me.field3

LUpdate = "UPDATE rec" & _
LUpdate = LUpdate & " set [field1] = var1," & _
LUpdate = LUpdate & " [field2] = var2 " & _
LUpdate = LUpdate & " WHERE [field3] = var3 "

db.Execute LUpdate, dbFailOnError

Cyberwolf wrote:

I have a form that users enter information into. What I want to do is
to update certain fields from another table once they have entered a
number in a field. The form is already based on a query as this allows
the user to see only the records they entered. What would be the best
approach to this?

TIA,

Cyberwolf

[Wayne Gillespie]

On 4 Jan 2007 12:10:58 -0800, "ManningFan" <ma********@gmail.comwrote:

>I'd probably do something like (and this is just an example that you
will need to adapt to your needs) ...

dim db as database
dim rec as recordset
dim var1, var2, var3 as variant

set db = currentdb
set rec = db.openrecordset ("OtherTable")

var1 = me.field1
var2 = me.field2
var3 = me.field3

LUpdate = "UPDATE rec" & _
LUpdate = LUpdate & " set [field1] = var1," & _
LUpdate = LUpdate & " [field2] = var2 " & _
LUpdate = LUpdate & " WHERE [field3] = var3 "

db.Execute LUpdate, dbFailOnError

There is no need for a recordset. The SQL is executed directly on the table
specified (tblMyTable) and the variables need to be concatenated within the SQL
string ie Set [field1]=var1 will not work, it needs to be SET [field1]=" & var1
etc

strSQL = "UPDATE tblMyTable SET [field1]=" & var1 & ", " _
& "[field2]=" & var2 & ", [field3]=" & var3 & ";"

CurrentDB.Execute strSQL, dbFailOnError

Wayne Gillespie
Gosford NSW Australia

[Cyberwolf]

OK, so how does the SQl know which record to look at? ANd, is my code
below correct?

Dim var1, var2, var3 As Variant
var1 = [Current TB].[Claim Number]
var2 = [Current TB].Amount
var3 = [Current TB].Store

strSQL = "UPDATE Chargebacks SET [Ref#]=" & var1 & ", " _
& "[Amount]=" & var2 & ", [Store]=" & var3 & ";"
On Jan 4, 5:05 pm, Wayne Gillespie <[e-mail]>
wrote:

On 4 Jan 2007 12:10:58 -0800, "ManningFan" <[e-mail]:

I'd probably do something like (and this is just an example that you
will need to adapt to your needs) ...

dim db as database
dim rec as recordset
dim var1, var2, var3 as variant

set db = currentdb
set rec = db.openrecordset ("OtherTable")

var1 = me.field1
var2 = me.field2
var3 = me.field3

LUpdate = "UPDATE rec" & _
LUpdate = LUpdate & " set [field1] = var1," & _
LUpdate = LUpdate & " [field2] = var2 " & _
LUpdate = LUpdate & " WHERE [field3] = var3 "

db.Execute LUpdate, dbFailOnErrorThere is no need for a recordset. The SQL is executed directly on the table

specified (tblMyTable) and the variables need to be concatenated within the SQL
string ie Set [field1]=var1 will not work, it needs to be SET [field1]=" & var1
etc

strSQL = "UPDATE tblMyTable SET [field1]=" & var1 & ", " _
& "[field2]=" & var2 & ", [field3]=" & var3 & ";"

CurrentDB.Execute strSQL, dbFailOnError

Wayne Gillespie
Gosford NSW Australia- Hide quoted text -- Show quoted text -

[Wayne Gillespie]

On 24 Jan 2007 10:40:35 -0800, "Cyberwolf" <cy**********@gmail.comwrote:

>OK, so how does the SQl know which record to look at? ANd, is my code
below correct?

Dim var1, var2, var3 As Variant
var1 = [Current TB].[Claim Number]
var2 = [Current TB].Amount
var3 = [Current TB].Store

strSQL = "UPDATE Chargebacks SET [Ref#]=" & var1 & ", " _
& "[Amount]=" & var2 & ", [Store]=" & var3 & ";"

The sql string looks Ok as long as var1, 2 and 3 are numeric. If any of them are
text they need to be wrapped in quotes. (I use Chr(34) for clarity).
eg [Store]=" & Chr(34) & var3 & Chr(34) & ";"

To have the update performed on specific record(s) you need to add a WHERE
clause to the string to define the record(s).

Assuming you wish to update the record currently displayed in your form, you
would add something like this -

strSQL = "UPDATE Chargebacks SET [Ref#]=" & var1 & ", " _
& "[Amount]=" & var2 & ", [Store]=" & var3 & _
& "WHERE MyIDField = " & Me.MyIDControl & ";"
Wayne Gillespie
Gosford NSW Australia