CODEWITHSUNDEEP
sqlms-accessms-office
HoxDef
HoxDef

Reputation: 15

Copying Primary key to another field in Access

Hey, I'm struggling to copy the Primary Key to another field in Access. This is irrelevant , but clarifying on what I'm comparing.

... WHERE Tunniste=" & [Tarkiste] & ""

Tunniste = Primary Key , Autonumber , ID (Generated by Access.)

Tarkiste = This is the field I want to copy it to compare it.

I'm open to suggestions, I've already try'ed with Form_Load, using the following code.

Private Sub Form_Load()

DoCmd.RunSQL "UPDATE Korut SET [Tarkiste]=('" & Tunniste & "');"

End Sub

But this copied the same key to all the entries in "Tarkiste" field.

In simplicity I want 1:1 copy of field "Tunniste" to "Tarkiste" , whichever method it takes.

Started from this question. File Picker Replaces All Rows With The Same Choice.

Upvotes: 0

Views: 898

Answers (2)

Toni
Toni

Reputation: 26

If the two fields are in the same table, you can do this:

DoCmd.RunSQL "UPDATE Korut SET [Tarkiste]=[Tunniste]"

And its sure that it runs!

Upvotes: 1

Unreason
Unreason

Reputation: 12704

To better grasp what is happening is

The single value gets updated in every row because your query's string is calculated when the form loads. At that time the variable Tunniste gets a value from the control it is bound to (in your case probably the control that points to the field in the recordset of the same table).

So assuming Tunniste is 1 for the current record on Form_Load the database ends up running a query

UPDATE Korut SET [Tarkiste]=('1');

where

UPDATE Korut SET [Tarkiste]=[Tunniste];

will do a proper thing.

Things to notice:

  • you can (should) test and construct your SQL command independently of form events (especially if non of the query parameters depend on the values in the form, bound or unbound); running SQL happens in its own space
  • use visual query builder to practice your SQL skills (switch to SQL mode and back to understand what happens)
  • read up on how the data is bound to the form
  • you are updating all records in the table every time a form loads and (if this is a normal edit form) this is certainly not optimal (you could at least add a WHERE condition to SET the [Tarkiste] only in case the values are different), this will scale poorly
  • you seem to be using different data types for the same field and silent conversion; this can backfire

Maybe explaining what is it that you are trying to achieve will bring better advice.

Upvotes: 0

Related Questions