Variables for Updating records

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I am trying to UPDATE records in a table using variables
that get their values as the code is executed. I have
never been able to do this successfully, though it seems
it should be easy to do. I've always found a way to get
the values I need by either getting the values from
existing forms, or by creating forms and then getting the
values from them. Sometimes it is a cumbersome way to do
it and I am now at a point where I don't see a way to work
around it. Can you help me write code that will do what I
am trying to do?

Private Sub UpdateRecords_Click()
On Error GoTo Err_UpdateRecords_Click

Dim Entryidtemp As Integer
Dim relationidtemp As Integer

DoCmd.RunSQL "INSERT INTO Relationstemp (Entryid)
SELECT DISTINCT Relations.entryid FROM Relations WHERE
(((Relations.relationid)=6) AND ((Relations.category)=4))
OR (((Relations.relationid)=7) AND ((Relations.category)
=5));"

Set dbCurrent = CurrentDb
strSQL = "select * from relationstemp;"
Set rsrelationstemp = dbCurrent.OpenRecordset(strSQL)

rsrelationstemp.MoveFirst
Entryidtemp = rsrelationstemp!entryid
Do Until rsrelationstemp.EOF
rsrelationstemp.MoveNext
relationidtemp = rsrelationstemp!entryid
*** DoCmd.RunSQL "Update relationstemp set relationid =
relationidtemp where entryid = entryidtemp;" ***
Loop

Exit_UpdateRecords_Click:
Exit Sub

Err_UpdateRecords_Click:
MsgBox Err.Description
Resume Exit_UpdateRecords_Click

End Sub

This is the problem line:

DoCmd.RunSQL "Update relationstemp set relationid
= relationidtemp where entryid = entryidtemp;"

I want the code to pick up the variables set in the
previous lines, but when I execute the code (click the
button), I get a message asking for the parameters
relationidtemp and entryid temp. Any help you can give is
very much appreciated.
 
Something like this, perhaps:

DoCmd.RunSQL "INSERT INTO Relationstemp (Entryid) " & _
"SELECT DISTINCT Relations.entryid FROM Relations " & _
"WHERE (Relations.relationid=" & entryidtemp & " AND " & _
"Relations.category=" & categorytemp & ") OR " & _
"(Relations.relationid)=" & relationidtemp & " AND " & _
"Relations.category=" & categorytemp & ");"
 
The problem line is the line lower in the code that is
supposed to update the records in the relationshiptemp
table. I've tried using similar code (putting the SQL
text in quotes and the variables between ampersands), but
I can't seem to get it right. If you can take a shot at
the line I'm having trouble with, I'll try it. Thx.
 
Scott said:
I am trying to UPDATE records in a table using variables
that get their values as the code is executed. I have
never been able to do this successfully, though it seems
it should be easy to do. I've always found a way to get
the values I need by either getting the values from
existing forms, or by creating forms and then getting the
values from them. Sometimes it is a cumbersome way to do
it and I am now at a point where I don't see a way to work
around it. Can you help me write code that will do what I
am trying to do?

Private Sub UpdateRecords_Click()
On Error GoTo Err_UpdateRecords_Click

Dim Entryidtemp As Integer
Dim relationidtemp As Integer

DoCmd.RunSQL "INSERT INTO Relationstemp (Entryid)
SELECT DISTINCT Relations.entryid FROM Relations WHERE
(((Relations.relationid)=6) AND ((Relations.category)=4))
OR (((Relations.relationid)=7) AND ((Relations.category)
=5));"

Set dbCurrent = CurrentDb
strSQL = "select * from relationstemp;"
Set rsrelationstemp = dbCurrent.OpenRecordset(strSQL)

rsrelationstemp.MoveFirst
Entryidtemp = rsrelationstemp!entryid
Do Until rsrelationstemp.EOF
rsrelationstemp.MoveNext
relationidtemp = rsrelationstemp!entryid
*** DoCmd.RunSQL "Update relationstemp set relationid =
relationidtemp where entryid = entryidtemp;" ***
Loop

Exit_UpdateRecords_Click:
Exit Sub

Err_UpdateRecords_Click:
MsgBox Err.Description
Resume Exit_UpdateRecords_Click

End Sub

This is the problem line:

DoCmd.RunSQL "Update relationstemp set relationid
= relationidtemp where entryid = entryidtemp;"

I want the code to pick up the variables set in the
previous lines, but when I execute the code (click the
button), I get a message asking for the parameters
relationidtemp and entryid temp. Any help you can give is
very much appreciated.
Click to expand...

The problem line should be fixed by writing it as

DoCmd.RunSQL "Update relationstemp set relationid = " &_
relationidtemp & " where entryid = " & entryidtemp & ";"

That is, embedding the values of the variables into the string literal
to be executed.

I must warn you, there's a bug in your code that will hit you once you
get past this line. Your code in the loop refers to the recordset field
"rsrelationstemp!entryid" immediately after executing a .MoveNext. That
means you will inevitably attempt to access the recordset's current
record after the recordset has reached EOF. That will raise a "no
current record" error. You also don't check at the very beginning to
see if there are any records at all in the recordset.

Although I'm not sure what you're trying to do, I think you *probably*
intend something like this:

'----- start of revised code -----
strSQL = "select * from relationstemp;"
Set rsrelationstemp = dbCurrent.OpenRecordset(strSQL)

With rsrelationship
If Not .EOF Then
' already at first record.
Entryidtemp = !entryid
Do Until .EOF
.MoveNext
If Not .EOF Then
relationidtemp = rsrelationstemp!entryid

DoCmd.RunSQL _
"Update relationstemp set relationid = " &_
relationidtemp & " where entryid = " &
entryidtemp & ";"
End If
Loop
End If
End With
'----- end of revised code -----

I'm not at all sure I understand the purpose of this code, though,
especially since you seem to be updating the same table your recordset
is open on. I fear this may cause problems, but as I said I don't
really understand what you're trying to do.
 
Sorry....it wasn't obvious to me which line when I read the post. But I
think Dirk's answer covers all the bases.
 
Thanks for the help. I used the principles that Ken gave
in the earlier message and it worked. As a novice, I run
into problems as I go along and try to address them as
they happen. I appreciate the heads up on the other
issues (the results I got were exactly as you describe)
and I've saved what you gave me so I can apply it as
needed. There is other code that I will put in to make it
do what I need, but, as I said, I was never able to get
this part of it to work. Thanks lot for getting me past
this.
 
Back
Top