Code to run macro at a certain time not working, any suggestions?

[SpectacledBear]

Hi there, I have a form which is supposed to allow the user to select either ‘Deliveries’, ‘Sales’ or ‘Orders’ from a list box. There is also a text box called txtTime. In this text box, the user should type the time they want to archive the tables (done by a macro). I have done the macro, and this works fine when run. However, the coding on the form’s cmdTime button does not work. This is probably because I am not too confident with VB, and have probably made a lot of mistakes. The code I have used to try and run the macro if the time equals the one the user has typed in is as follows:

Private Sub cmdTime_Click()
On Error GoTo Err_cmdTime_Click

Dim db As DAO.Database

Set db = CurrentDb

If Not IsNull(Me!txtTime) Or Not Me!txtTime = "" Then
rs.MoveFirst

rs("TimetoArchive") = CDate(Me!txtTime)
rs.Update

Dim stDocName As String

IIF(lst2="Deliveries" and txtTime =Time(), DoCmd.RunMacro mcrDeliveryArchive, "")
IIF(lst2="Orders" and txtTime =Time(), DoCmd.RunMacro mcrOrderArchive, "")
IIF(lst2="Sales" and txtTime =Time(), DoCmd.RunMacro mcrSalesArchive, "")


Else
MsgBox "You have not selected a correct time to complete archives", vbCritical + vbOKOnly, "Error"
Me!txtTime.SetFocus
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

MsgBox "Table(s) archived successfully.", vbInformation
Me!txtTime.Requery

Exit_cmdTime_Click:
Exit Sub

Err_cmdTime_Click:
If Err = 3021 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_cmdTime_Click
End If
End Sub

I would appreciate any corrections or alternative codes. Thanks!

(P.S. This needs to be done in access alone)

 

[PHV]

What is rs in your code, a non defined non instantiated and non open DAO.Recordset ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SpectacledBear]

I am confused. It is a record set but I don't know about all those non's. What should it be?

 

[PHV]

I just said that in your code you use [tt]rs.MoveFirst[/tt]
without previous [tt]Dim rs As DAO.Recordset[/tt] nor [tt]Set rs = ...[/tt] nor [tt]rs.Open[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SpectacledBear]

oh i see. maybe the rs.move shouldn't be there? Most of this is cut from books, I am not good with VB

 

[PHV]

Again, what are you thinking rs is ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SpectacledBear]

I think it might refer to a module I have. I'm not sure if I should take it out.

 

[SpectacledBear]

When I edit the code, the three lines beginning IIF are in red