By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,928 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,928 IT Pros & Developers. It's quick & easy.

Updating a form form another table

P: n/a
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

Jan 4 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Jan 4 '07 #2

P: n/a
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
Jan 4 '07 #3

P: n/a
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 <best...@NOhotmailSPAM.com.au>
wrote:
On 4 Jan 2007 12:10:58 -0800, "ManningFan" <manning...@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, 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 -
Jan 24 '07 #4

P: n/a
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
Jan 24 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.