SQL string - Update Query using a variable value

[muirchez]

HI all,

Not sure if this is the right place to post? Hope so.

I am currently trying to create an update query (building a SQL String in VBA for a command button click event) to update a variable's value into a table.

Basically, there are 2 tables, displayed in 2 sub froms within the same main form. (OldTable and NewTable for arguments sake)

The basic method I want to implement is that a user highlights a record in the subform of "NewTable". (This value is stored as a variable "NewJPNUM" This value is then to be inserted into the highlighted row (or rows) of table OldTable on command button click.

So the basic idea is a user highlights a row in one table and this value is stored as a variable "NewJPNUM" . The user then highlights a row or rows in "OldTable" and the value from variable "NewJPNUM" is then written to field "NewJPNUM" in "NewTable" on command button click.

My question is how can this be achieved and is this even the best method to achieve this? I am not experienced with Access but have decent experience in Excel / VBA so not really sure of best practice methods etc. so any advice would be appreciated.

 

[JamesM]

Hi,

Here is an example of what I think you are tring to do.

Main form with 2 subform controls sfc1 and sfc2

It gets a value from a subform sfc1 that has a datasheet list of records and updates sfc2 datasheet records with the field one value of sfc1

Private Sub btnCopyVal_Click()
[INDENT]'This get the selected row value from Field1 in subform control 1
Dim strNewJPNUM As String
strNewJPNUM = Me.sfc1.Form.Recordset.field1
 
'This get the selected row value from ID in subform control 2
Dim iRowID2 As Integer
iRowID2 = Me.sfc2.Form.id
 
Dim strSQL As String
strSQL = "UPDATE Table2 set Field1 = " & Chr$(34) & strNewJPNUM & Chr$(34) & " WHERE id = " & iRowID2
 
DoCmd.RunSQL strSQL
 
Me.sfc2.Form.Requery
[/INDENT]End Sub

Hope this helps

James

 

[Mihail]

Do not treat Access tables as Excel spreadsheets.
There are very rarely the cases when you need to copy something from a table to other table.
In your case, I'm pretty sure that, the need is born from a poorly design of your database.
So, before continue, google and learn about the DATABASES NORMALIZATION .

 

[muirchez]

Cheers JamesM. I did manage to work out almost identical solution yesterday, but many thanks for your help.