Solved Object required (2 Viewers)

[chizzy42]

Hi, hope all is well . I was looking to update a date column in a table and was trying to do it by a button with VBA and seem to have got into a fankle. (ive been messing with python and getting confused now with vba). Im trying to update a column called "PM Due" in a table tblDataLog which contains dates in the format dd/mm/yyyy, by x amount of days and getting the error 424 object required at the line set rst=db.OpenRecordSet(sql), could someone see where im going wrong please, ive added the DAo references which i was initially getting problems with.

Private Sub Command0_Click()
Dim sql As String
Dim DQRY As String
Dim data1 As Date
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

sql = "Select * tblDataLog"

Set dbs = CurrentDb
Set rst = db.OpenRecordset(sql)
Do Until rst.EOF

DQRY = "Update " & [PM Due] & " Set ImportDate =  DateAdd('d', 6, [PM Due]);"

db.Execute DQRY
rst.MoveNext
Loop
End Sub

thanks for any help

 

[theDBguy]

Set rst = db.OpenRecordset(sql)

That should probably be more like:

Set rst = dbs.OpenRecordset(sql)

i.e. dbs, instead of just db

 

[GPGeorge]

Hi, hope all is well . I was looking to update a date column in a table and was trying to do it by a button with VBA and seem to have got into a fankle. (ive been messing with python and getting confused now with vba). Im trying to update a column called "PM Due" in a table tblDataLog which contains dates in the format dd/mm/yyyy, by x amount of days and getting the error 424 object required at the line set rst=db.OpenRecordSet(sql), could someone see where im going wrong please, ive added the DAo references which i was initially getting problems with.

Private Sub Command0_Click()
Dim sql As String
Dim DQRY As String
Dim data1 As Date
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

sql = "Select * tblDataLog"

Set dbs = CurrentDb
Set rst = db.OpenRecordset(sql)
Do Until rst.EOF

DQRY = "Update " & [PM Due] & " Set ImportDate =  DateAdd('d', 6, [PM Due]);"

db.Execute DQRY
rst.MoveNext
Loop
End Sub

thanks for any help

Why are you combining a Recordset with an update query, row by agonizing row?

Why not run the Update query and avoid the Recordset?

Try it yourself, on a back up copy of the table, of course.

 

[chizzy42]

HI theDBguy and GP George, thanks for the quick replies.I noticed i had missed the select from also and the table was wrong, im getting the object required now on db.Execute DQRY line

Dim sql As String
Dim DQRY As String
Dim data1 As Date
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

sql = "Select * FROM tblDataLog"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql)
Do Until rst.EOF

DQRY = "Update " & tblDataLog & " Set [PM Due] =  DateAdd('d', 6);"

db.Execute DQRY
rst.MoveNext
Loop

GPGeorge , dp you mean create an update query on its own to carry this out, i wasnt sure if i could use a for loop to take each date and add a number of days to each date.

thanks

ian

 

[theDBguy]

Are you still trying to use "db" instead of "dbs?"

 

[Gasman]

Debug.Print DQRY

What do you get to see?

What is tblDataLog meant to hold?

 

[chizzy42]

I used dbs and the object problem moved to db.execute DQRY

 

[chizzy42]

Hi Gasman tblDataLog holds the column PM DUE which has the dates

 

[Gasman]

Hi Gasman tblDataLog holds the column PM DUE which has the dates

You are using it as a variable?

 

[chizzy42]

hi gas man i think i see what you mean
DQRY = "Update tblDataLog Set [PM Due] = DateAdd('d', 6);"

db.Execute DQRY

i changed it to the above but still getting the same error

 

[Gasman]

You also need to say what you are adding 6 days to?

DateAdd function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[GPGeorge]

HI theDBguy and GP George, thanks for the quick replies.I noticed i had missed the select from also and the table was wrong, im getting the object required now on db.Execute DQRY line

Dim sql As String
Dim DQRY As String
Dim data1 As Date
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

sql = "Select * FROM tblDataLog"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql)
Do Until rst.EOF

DQRY = "Update " & tblDataLog & " Set [PM Due] =  DateAdd('d', 6);"

db.Execute DQRY
rst.MoveNext
Loop

GPGeorge , dp you mean create an update query on its own to carry this out, i wasnt sure if i could use a for loop to take each date and add a number of days to each date.

thanks

ian

My favorite response to theoretical questions is "Try it and see what happens", again on a back up copy.

I see no benefit to running the same unparameterized query against the entire table for each record in that table. I'm assuming, of course, that [PM Due] is a field in that table. If it is, then all you are doing is running a query that updates every record, and then moving forward in the recordset and re-running that same query to update every record again, and again, and again.

Perhaps I'm missing something, though. Try running the query once and see what happens on a backup copy of the table.

 

[chizzy42]

HI GpGeorge just to clarify im trying to update the entire date values in the column by a certain amount by a certain factor of days, its a database i made up years ago for maintenance and was looking to upgrade it to use again. [PM Due] is the date column in the table tbDataLog. i was nt sure if this is the way or try to use the for loop method

 

[chizzy42]

hi gas man i think i see what you mean
DQRY = "Update tblDataLog Set [PM Due] = DateAdd('d', 6);"

db.Execute DQRY

i changed it to the above but still getting the same error

I was a bit fuzzy on this part i was sure whether to add the field into the Dateadd part as i thought it was being added to the field at the Set part???
DQRY = "Update tblDataLog Set [PM Due] = DateAdd('d', 6,[PM Due]);"

 

[Gasman]

I was a bit fuzzy on this part i was sure whether to add the field into the Dateadd part as i thought it was being added to the field at the Set part???
DQRY = "Update tblDataLog Set [PM Due] = DateAdd('d', 6,[PM Due]);"

Two seperate processes.
1. Add an interval to a date
2. Set a date field to that amended value

 

[bastanu]

And as explained by theDBGuy in post #5 you are still getting the error because you use db.Execute instead of dbs.Execute, you do not have a db variable declared and\or set...
But you should use a simple update query instead if the number of days you are adding is the same for all records.

Cheers,

 

[chizzy42]

Two seperate processes.
1. Add an interval to a date
2. Set a date field to that amended value

Thanks for replying gas man im not sure if im getting what you mean..is it like this you mean :

Dim IntervalType As String
Dim Number As Integer
Number = 6
IntervalType = "d"


DQRY = "Update  tblDataLog   Set [PM Due] =  DateAdd(IntervalType, Number, [PM Due]);"

 

[chizzy42]

And as explained by theDBGuy in post #5 you are still getting the error because you use db.Execute instead of dbs.Execute, you do not have a db variable declared and\or set...
But you should use a simple update query instead if the number of days you are adding is the same for all records.

Cheers,

thanks for that (and the dbGuy) couldnt see that one for looking at it,

 

[Gasman]

Would have been easier to use
Dim db AS DAO.Database

 

[GPGeorge]

HI GpGeorge just to clarify im trying to update the entire date values in the column by a certain amount by a certain factor of days, its a database i made up years ago for maintenance and was looking to upgrade it to use again. [PM Due] is the date column in the table tbDataLog. i was nt sure if this is the way or try to use the for loop method

And, I can't say it another time without sounding petulant.... "Try it"