Set datefield back to empty

[botci]

Hello everybody,
So I want on opening a form to set some datefields back to null or empty by some criteria :

Private Sub Form_Load() 
Dim db As DAO.Database 
Dim rs As DAO.Recordset 

Set db = CurrentDb 
Set rs = CurrentDb.OpenRecordset("validity") 

If Not (rs.EOF And rs.BOF) Then 
    rs.MoveFirst  
    Do Until rs.EOF = True 

 If rs![Newupp] = DateSerial(2032, 3, 28) Then 
    rs.Edit 
    rs![Upp] = rs![Newupp] 
       With db 
         db.Execute "UPDATE validity" & " SET [Newupp] =  " & "Null" 
       End With 
   rs.Update 
 End If 
  rs.MoveNext 
    Loop 
    rs.Requery 
  Else 
End If 
 
rs.Close 
 Set rs = Nothing 
 Me.Requery

The problem is, every newupp is set to Null, except the one, where the DateSerial(2032, 3, 28) is met.
I searched a lot for answers, but to no avail, please help
thank you

 

[isladogs]

hi

Suggest you do this using an update query instead.
No need for a recorder for this

 

[NauticalGent]

Ridders nailed it. Access performs batch processing (queries) much more efficiently then looping through a record set one record at a time. There are situations where it is necessary, but this doesn't seem to be one of those situations.

Best of luck!

 

[botci]

Thanks, but I don't how how to make this through a query.
I want to make it done automatically when the form is opened.

 

[Minty]

You are 90% of the way there already.

Dim sSql As string

sSql = "UPDATE validity SET [Newupp] = Null WHERE  [Newupp] = DateSerial(2032, 3, 28) ;"
CurrentDb.Execute sSql DbSeeChanges

 

[isladogs]

Hi

I didn't write the code for the update query as its simpler than recordset code.

Either do this the way Minty described or use the following:

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE validity SET [Newupp] = Null WHERE [Newupp] = DateSerial(2032, 3, 28);"
DoCmd.SetWarnings True

Colin

 

[botci]

Thank you guys, but neither solution worked for me, they didn't do anything, the datefield remained the same, didn't disappear.

I used the this code as suggested:

If rs![Newupp] = DateSerial(2032, 3, 28) Then
rs.Edit
rs![Upp] = rs![Newupp]
Dim sSql As string
sSql = "UPDATE validity SET [Newupp] = Null WHERE [Newupp] = DateSerial(2032, 3, 28) ;"
CurrentDb.Execute sSql
rs.Update
End If

I removed DbSeeChanges as it turned red.
And the other code:

If rs![Newupp] = DateSerial(2032, 3, 28) Then
rs.Edit
rs![Upp] = rs![Newupp]
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE validity SET [Newupp] = Null WHERE [Newupp] = DateSerial(2032, 3, 28);"
DoCmd.SetWarnings True
rs.Update
End If

 

[isladogs]

Get rid of all the recordset code - the update query is used instead of this

 

[Minty]

Copy the SQL string into the SQL view of a blank query. Go to the design view. Change the query to a SELECT query and then switch to datasheet view. Do you see any records with the date of 28/3/2032 in them?

 

[botci]

Thank you Minty, I did what you said and it works!
Only one question left: how to get this query work on opening the form?

 

[Minty]

Well the code I provided is the same query changed into an update query, so I have no idea why it wouldn't work.

Can you post a sample of your data in a stripped down database with the form you are opening, or at least your exact new code with the recordset gubbins removed?

 

[botci]

But it works, thank you Minty! No more problem, Thanks for both of you!