Recordset update query

  • Thread starter Thread starter Zikar
  • Start date Start date
Z

Zikar

Hi all Group members,
I am in the process of building a code to update a table field I call
[Transitional_Temp_ITP_ID] and is a Long Integer. The value of this filed has
to be updated to become equal to the Primary Key field which is an autonumber
and I call it [ITP_Master_ID]. I have set a criterion where the only records
in which a third field within the same table I called [ProjectID], which is a
Long Integer, is equal to zero. The code I wrote is as follows:

Dim rst As Recordset
Dim db As Database
Set db = CurrentDb()

Set rst = CurrentDb.OpenRecordset("SELECT * FROM
Tbl_QA_ITP_ProjectList_Parent WHERE ProjectID = " & 0)

rst.MoveFirst

If Not rst.NoMatch Then
rst.Edit
rst![Transitional_Temp_ITP_ID] = rst![ITP_Master_ID]
rst.Update
End If

Now, this code works but it only updates the first record while I want it to
update all the records that meet my set criterion; that is where the third
field called ProjectID = 0. I know that this could be because I did not set
the " For each" and "Next" iteration code within this event and that could be
the reason it is not doing the update for the rest of the records. Can
someone help me with this desperate situation? Great thanks in advance to all
of you who make this workgroup site a great place to share information and
make others achieve many wonderful things and great work with Microsoft
Access!
 
You could create an Update query statement (Update on Query menu, in query
design) to do this for you.

Before you go to the trouble, though, I'm not sure that this is a good idea.
If the [Transitional_Temp_ITP_ID] field should always match the
![ITP_Master_ID], then it should not be stored in the table.
 
Thanks Allen for your advice. I made it work with several SQL queries and
still kept the [Transitional_Temp_ITP_ID] field in the table. However this
was a very tedious process since I had to use what I call "auxiliary fields"
in the same table which saves temporary virtual data for creating links then
I delete this data automaticaly after the procedure would have been executed.
I thought I 'd let you know of the result and thanks again.

Allen Browne said:
You could create an Update query statement (Update on Query menu, in query
design) to do this for you.

Before you go to the trouble, though, I'm not sure that this is a good idea.
If the [Transitional_Temp_ITP_ID] field should always match the
![ITP_Master_ID], then it should not be stored in the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Zikar said:
Hi all Group members,
I am in the process of building a code to update a table field I call
[Transitional_Temp_ITP_ID] and is a Long Integer. The value of this filed
has
to be updated to become equal to the Primary Key field which is an
autonumber
and I call it [ITP_Master_ID]. I have set a criterion where the only
records
in which a third field within the same table I called [ProjectID], which
is a
Long Integer, is equal to zero. The code I wrote is as follows:

Dim rst As Recordset
Dim db As Database
Set db = CurrentDb()

Set rst = CurrentDb.OpenRecordset("SELECT * FROM
Tbl_QA_ITP_ProjectList_Parent WHERE ProjectID = " & 0)

rst.MoveFirst

If Not rst.NoMatch Then
rst.Edit
rst![Transitional_Temp_ITP_ID] = rst![ITP_Master_ID]
rst.Update
End If

Now, this code works but it only updates the first record while I want it
to
update all the records that meet my set criterion; that is where the third
field called ProjectID = 0. I know that this could be because I did not
set
the " For each" and "Next" iteration code within this event and that could
be
the reason it is not doing the update for the rest of the records. Can
someone help me with this desperate situation? Great thanks in advance to
all
of you who make this workgroup site a great place to share information and
make others achieve many wonderful things and great work with Microsoft
Access!
Click to expand...
Click to expand...
 
Back
Top