Why doesn't my table update the vaule when I use Recordset?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Help! I am using a recordset to look up a value in a table and change it. I
have used recordsets before, but for some reason, it appears that the Update
isn't working. What am I doing wrong?

Dim rstCovers As Recordset
Dim dbsCovers As Database
Dim strSQL1 As String

TempCoverID = Forms!Covers_Main_Form!CoverID
Set dbsCovers = CurrentDb()
Set rstCovers = dbsCovers.OpenRecordset("Covers", dbOpenTable)
strSQL1 = "SELECT * FROM [Covers]"
strSQL1 = strSQL1 & " WHERE ([CoverID] = SetTempCoverIDValue());"
Set rstCovers = dbsCovers.OpenRecordset(strSQL1)

rstCovers.MoveFirst
rstCovers.Index = "CoverID"
rstCovers.Seek "=", SaveCoverID

' Do While Not rstCovers.EOF()
' If rstCovers!CoverID = SaveCoverID Then
NewNum = rstCovers!CaseCode - NumOfConstructorsLeft
rstCovers!CaseCode = NewNum
rstCovers.Update
' End If
' If Not rstCovers.EOF() Then
' rstCovers.MoveNext
' End If
' Loop
rstConstructors.Close
rstCovers.Close
 
It's me again. As I was trying to cut and paste my code, I managed to hit a
key
that sent my posting too early. I must be getting tired. Anyway....

Let me fix my posting here:

Help! I am using a recordset to look up a value in a table and change it.
I
have used recordsets before, but for some reason, it appears that the Update
isn't working. What am I doing wrong?

I have used this code to set up my recordsets before with no problems.

TempCoverID is a Global Variable.

SetTempCoverIDValue() is a Global Function which returns my TempCoverID
value to the SQL statement since you can't put variables in directly.

It appears to find the correct record and do the calculation properly, but
it won't
update.

Dim rstCovers As Recordset
Dim dbsCovers As Database
Dim strSQL1 As String

TempCoverID = Forms!Covers_Main_Form!CoverID

Set dbsCovers = CurrentDb()
Set rstCovers = dbsCovers.OpenRecordset("Covers", dbOpenTable)
strSQL1 = "SELECT * FROM [Covers]"
strSQL1 = strSQL1 & " WHERE ([CoverID] = SetTempCoverIDValue());"
Set rstCovers = dbsCovers.OpenRecordset(strSQL1)

rstCovers.MoveFirst
rstCovers.Index = "CoverID"
rstCovers.Seek "=", TempCoverID

NewNum = rstCovers!CaseCode - NumOfConstructorsLeft
rstCovers!CaseCode = NewNum
rstCovers.Update

rstConstructors.Close
rstCovers.Close
 
Try:
Dim rstCovers As DAO.Recordset
Dim dbsCovers As DAO.Database
Dim strSQL1 As String

TempCoverID = Forms!Covers_Main_Form!CoverID

Set dbsCovers = CurrentDb()
'Set rstCovers = dbsCovers.OpenRecordset("Covers", dbOpenTable)
strSQL1 = "SELECT * FROM [Covers]"
strSQL1 = strSQL1 & " WHERE ([CoverID] = SetTempCoverIDValue());"
Set rstCovers = dbsCovers.OpenRecordset(strSQL1)

rstCovers.MoveFirst
rstCovers.Index = "CoverID"
rstCovers.Seek "=", TempCoverID
If Not rstCovers.NoMatch Then
NewNum = rstCovers!CaseCode - NumOfConstructorsLeft
rstCovers.Edit
rstCovers!CaseCode = NewNum
rstCovers.Update
End If
rstConstructors.Close
rstCovers.Close
 
KmhComputer said:
It's me again. As I was trying to cut and paste my code, I managed to hit a
key
that sent my posting too early. I must be getting tired. Anyway....

Let me fix my posting here:

Help! I am using a recordset to look up a value in a table and change it.
I
have used recordsets before, but for some reason, it appears that the Update
isn't working. What am I doing wrong?

I have used this code to set up my recordsets before with no problems.

TempCoverID is a Global Variable.

SetTempCoverIDValue() is a Global Function which returns my TempCoverID
value to the SQL statement since you can't put variables in directly.

It appears to find the correct record and do the calculation properly, but
it won't
update.

Dim rstCovers As Recordset
Dim dbsCovers As Database
Dim strSQL1 As String

TempCoverID = Forms!Covers_Main_Form!CoverID

Set dbsCovers = CurrentDb()
Set rstCovers = dbsCovers.OpenRecordset("Covers", dbOpenTable)
strSQL1 = "SELECT * FROM [Covers]"
strSQL1 = strSQL1 & " WHERE ([CoverID] = SetTempCoverIDValue());"
Set rstCovers = dbsCovers.OpenRecordset(strSQL1)

rstCovers.MoveFirst
rstCovers.Index = "CoverID"
rstCovers.Seek "=", TempCoverID

NewNum = rstCovers!CaseCode - NumOfConstructorsLeft
rstCovers!CaseCode = NewNum
rstCovers.Update
Click to expand...


I see a couple of errors here. First, (ignoring the extra
(old?) open recordset), the open recordset with the SQL
statement will be a Dynaset type recordset, but Seek only
works on Table type recordsets. With Dynaset type
recordsets, you need to use FindFirst instead of Seek.

Second, the statement where you assign a value to NewNum
contains syntax errors. This code should never have gotten
to the point of trying to run it because VBA's automatic
syntax checking should have produced an error message as
soon as you hit the enter key. Even if you have disabled
automatic syntax checking, you should have received the
error when you compiled the code. I can't tell what you
intended here, but if that whole thing is a field name, then
it must be enclosed in [ ]

Third, the function, SetTempCoverIDValue, you are calling in
the SQL statement may (or may not?) be part of the problem,
but without seeing its code we can't tell what it's doing.
 
Back
Top