Recordset Updagte Records

  • Thread starter Thread starter Antonio Macias
  • Start date Start date
A

Antonio Macias

Hello group,

This is my first recordset, but something is wrong, and I
don't know what is the problem,

I get error 3021 "No current Record" or something like
that, and I don't know how to fix it

Please Help Me!!


Sub UpdateRecords()
Dim fso, f1
Dim dbs As Database, rst As Recordset, txtNombre As String
MyMoveRecord = 0
Temporal = 1


'Asigno a la variable «dbs» la base de datos activa.
Set dbs = CurrentDb
'Creo un objeto Consulta de Seleccion con la
Tabla «Add Item».
Set rstFirst = dbs.OpenRecordset("select * from [Add
Item] where updated=False", dbReadOnly)
'Lo muevo hasta el ultimo registro para poder calcular
el numero de total de Registros
rstFirst.MoveLast
'Instruccion RecordCount
Count = rstFirst.RecordCount
MsgBox Count
'Mueve el registro a el principio para comenzar el
update
rstFirst.MoveFirst

Do


'Se mueve a travez de todos los registros
rstFirst.Move MyMoveRecord
'Obtengo el numero de registro de la tba Items donde
se almaceno este regitro
MyTransferRegister = rstFirst.Fields
("TransferRegister")
MsgBox MyTransferRegister
'Obtengo el Id del Producto a el cual lo voy a
actualizar
MyTransferItemId = rstFirst.Fields("IdProducto")
MsgBox MyTransferItemId
'Busco el titulo en la tba Items en base al numero
obtenido
MyTitleInItems = DLookup("[Title]", "Items", "[ItemId]
= " & MyTransferRegister & "")
MsgBox MyTitleInItems
'Busco el mismo titulo nuevamente en donde el registro
ademas sea condicion = 2
'Se encuentra entre triple comillas para evitar
problemas con los caracteres especiales
'Esta busqueda nos devuelve el Item Id
MyTitleInItemsCondition2 = DLookup
("[ItemId]", "Items", "[Title]=""" & MyTitleInItems & """
And [Status] = 2")
'MsgBox "El # Registro es " & MyTitleInItemsCondition2
& " de " & MyTitleInItems
'Recupera los campos a Actualizar
'Recupera SiteId
MySiteId = DLookup("[SiteId]", "Items", "[ItemId] = "
& MyTitleInItemsCondition2 & "")
'Recupera Title 'ok
MyFormat = DLookup("[Format]", "Items", "[ItemId] = "
& MyTitleInItemsCondition2 & "")
'Recupera Quantity 'ok
MyQuantity = DLookup("[Quantity]", "Items", "[ItemId]
= " & MyTitleInItemsCondition2 & "")


DoCmd.SetWarnings False

MySQL = "UPDATE [Add Item] " & _
"SET [Add Item].SiteId = " & MySiteId & ",
[Add Item].Format = " & MyFormat & ",[Add Item].Quantity
= " & MyQuantity & ", [Add Item].Updated = " &
MyNewCondition & " " & _
"WHERE ((([Add Item].IdProducto)= " &
MyTransferItemId & " ))"
DoCmd.RunSQL MySQL

DoCmd.SetWarnings True

MyMoveRecord = MyMoveRecord + 1

Loop Until Count = MyMoveRecord

rstFirst.Close


Antonio Macias
 
Check for EOF prior to moving.

Do While Not rstFirst.EOF
debug.print rstFirst.Fields(0).value
rstFirst.MoveNext
Loop


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hello group,

This is my first recordset, but something is wrong, and I
don't know what is the problem,

I get error 3021 "No current Record" or something like
that, and I don't know how to fix it

Please Help Me!!


Sub UpdateRecords()
Dim fso, f1
Dim dbs As Database, rst As Recordset, txtNombre As String
MyMoveRecord = 0
Temporal = 1


'Asigno a la variable «dbs» la base de datos activa.
Set dbs = CurrentDb
'Creo un objeto Consulta de Seleccion con la
Tabla «Add Item».
Set rstFirst = dbs.OpenRecordset("select * from [Add
Item] where updated=False", dbReadOnly)
'Lo muevo hasta el ultimo registro para poder calcular
el numero de total de Registros
rstFirst.MoveLast
'Instruccion RecordCount
Count = rstFirst.RecordCount
MsgBox Count
'Mueve el registro a el principio para comenzar el
update
rstFirst.MoveFirst

Do


'Se mueve a travez de todos los registros
rstFirst.Move MyMoveRecord
'Obtengo el numero de registro de la tba Items donde
se almaceno este regitro
MyTransferRegister = rstFirst.Fields
("TransferRegister")
MsgBox MyTransferRegister
'Obtengo el Id del Producto a el cual lo voy a
actualizar
MyTransferItemId = rstFirst.Fields("IdProducto")
MsgBox MyTransferItemId
'Busco el titulo en la tba Items en base al numero
obtenido
MyTitleInItems = DLookup("[Title]", "Items", "[ItemId]
= " & MyTransferRegister & "")
MsgBox MyTitleInItems
'Busco el mismo titulo nuevamente en donde el registro
ademas sea condicion = 2
'Se encuentra entre triple comillas para evitar
problemas con los caracteres especiales
'Esta busqueda nos devuelve el Item Id
MyTitleInItemsCondition2 = DLookup
("[ItemId]", "Items", "[Title]=""" & MyTitleInItems & """
And [Status] = 2")
'MsgBox "El # Registro es " & MyTitleInItemsCondition2
& " de " & MyTitleInItems
'Recupera los campos a Actualizar
'Recupera SiteId
MySiteId = DLookup("[SiteId]", "Items", "[ItemId] = "
& MyTitleInItemsCondition2 & "")
'Recupera Title 'ok
MyFormat = DLookup("[Format]", "Items", "[ItemId] = "
& MyTitleInItemsCondition2 & "")
'Recupera Quantity 'ok
MyQuantity = DLookup("[Quantity]", "Items", "[ItemId]
= " & MyTitleInItemsCondition2 & "")


DoCmd.SetWarnings False

MySQL = "UPDATE [Add Item] " & _
"SET [Add Item].SiteId = " & MySiteId & ",
[Add Item].Format = " & MyFormat & ",[Add Item].Quantity
= " & MyQuantity & ", [Add Item].Updated = " &
MyNewCondition & " " & _
"WHERE ((([Add Item].IdProducto)= " &
MyTransferItemId & " ))"
DoCmd.RunSQL MySQL

DoCmd.SetWarnings True

MyMoveRecord = MyMoveRecord + 1

Loop Until Count = MyMoveRecord

rstFirst.Close


Antonio Macias
 
Hi,

Seems funny to read comments in Spanish and code in
English. Anyway, the first problem is in your Dim
statement. Dim the recordset object as DAO.Recordset.
Second, your SQL statement is missing a ";" at the end.
You can set your SQL statement to a string variable and
then open the recordset passing in the variable.\

Dim strSQL As String

strSQL = ("select * from [Add Item] where updated=False;"
debug.print strSQL
' print it so that you can copy it and paste it into a new
' query and run it to see if it works.

Set rsFirst = dbs.OpenRecordset(strSQL, dbReadOnly)

Regards,
Jen
-----Original Message-----
Hello group,

This is my first recordset, but something is wrong, and I
don't know what is the problem,

I get error 3021 "No current Record" or something like
that, and I don't know how to fix it

Please Help Me!!


Sub UpdateRecords()
Dim fso, f1
Dim dbs As Database, rst As Recordset, txtNombre As String
MyMoveRecord = 0
Temporal = 1


'Asigno a la variable «dbs» la base de datos activa.
Set dbs = CurrentDb
'Creo un objeto Consulta de Seleccion con la
Tabla «Add Item».
Set rstFirst = dbs.OpenRecordset("select * from [Add
Item] where updated=False", dbReadOnly)
'Lo muevo hasta el ultimo registro para poder calcular
el numero de total de Registros
rstFirst.MoveLast
'Instruccion RecordCount
Count = rstFirst.RecordCount
MsgBox Count
'Mueve el registro a el principio para comenzar el
update
rstFirst.MoveFirst

Do


'Se mueve a travez de todos los registros
rstFirst.Move MyMoveRecord
'Obtengo el numero de registro de la tba Items donde
se almaceno este regitro
MyTransferRegister = rstFirst.Fields
("TransferRegister")
MsgBox MyTransferRegister
'Obtengo el Id del Producto a el cual lo voy a
actualizar
MyTransferItemId = rstFirst.Fields("IdProducto")
MsgBox MyTransferItemId
'Busco el titulo en la tba Items en base al numero
obtenido
MyTitleInItems = DLookup
Click to expand...
("[Title]", "Items", "[ItemId]
= " & MyTransferRegister & "")
MsgBox MyTitleInItems
'Busco el mismo titulo nuevamente en donde el registro
ademas sea condicion = 2
'Se encuentra entre triple comillas para evitar
problemas con los caracteres especiales
'Esta busqueda nos devuelve el Item Id
MyTitleInItemsCondition2 = DLookup
("[ItemId]", "Items", "[Title]=""" & MyTitleInItems & """
And [Status] = 2")
'MsgBox "El # Registro es " & MyTitleInItemsCondition2
& " de " & MyTitleInItems
'Recupera los campos a Actualizar
'Recupera SiteId
MySiteId = DLookup("[SiteId]", "Items", "[ItemId] = "
& MyTitleInItemsCondition2 & "")
'Recupera Title 'ok
MyFormat = DLookup("[Format]", "Items", "[ItemId] = "
& MyTitleInItemsCondition2 & "")
'Recupera Quantity 'ok
MyQuantity = DLookup("[Quantity]", "Items", "[ItemId]
= " & MyTitleInItemsCondition2 & "")


DoCmd.SetWarnings False

MySQL = "UPDATE [Add Item] " & _
"SET [Add Item].SiteId = " & MySiteId & ",
[Add Item].Format = " & MyFormat & ",[Add Item].Quantity
= " & MyQuantity & ", [Add Item].Updated = " &
MyNewCondition & " " & _
"WHERE ((([Add Item].IdProducto)= " &
MyTransferItemId & " ))"
DoCmd.RunSQL MySQL

DoCmd.SetWarnings True

MyMoveRecord = MyMoveRecord + 1

Loop Until Count = MyMoveRecord

rstFirst.Close


Antonio Macias
.
Click to expand...
 
Back
Top