need help with code for form to launch archives

[SpectacledBear]

I currently have this code on my archive form:


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

Dim stDocName As String

If lst2 = "Deliveries" And txtTime = Time() Then
DoCmd.RunMacro mcrDeliveryArchive

Else

If lst2 = "Orders" And txtTime = Time() Then
DoCmd.RunMacro mcrOrderArchive

Else

If lst2 = "Sales" And txtTime = Time() Then
DoCmd.RunMacro mcrSalesArchive

End If
End If
End If


Else
MsgBox "You have not selected a correct time to complete archives", vbCritical + vbOKOnly, "Error"
Me!txtTime.SetFocus
db.Closee
Set db = Nothing
Exit Sub
End If
db.Close
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 want the code to check if the calue of txtTime is now, and the value of the listbox is deliveries, to run the delivery archive macro etc. I typed in this code, and it is not working. Please can anyone offer any suggestioons as to how I can make this happen?

 

[traingamer]

Try:

And txtTime [COLOR=red]<=[/color] Time()

instead of

And txtTime = Time()

They're only going to be equal for a brief time...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SpectacledBear]

Hi there traingamer, wont this run the arhive macro at any time then? Like if It's set to run at 00:00:00, wil adding the < mean that it will run any time between 00:00:01 and 11:59:59?

 

[traingamer]

Yes. It'll run at any time less than 23:59:59. You may want to include a Date/time check (that is then updated) for kickoff rather than just time.
I was just trying to point out the apparent flaw that would require the button to be clicked at EXACTLY the time specified or it would never rune. One second too soon - sorry. One second too late - sorry.

 

[SpectacledBear]

hi traingamer

I see where you're coming from. Perhaps I should specify the time in hh:mm instead of hh:mm:ss?

How do I include a Date/time check (that is then updated) for kickoff rather than just time?

Thanks!

 

[traingamer]

One way: Have your time be a 'General Time' format like '4/3/05 05:34:00 PM' and compare to that. Then when it meets your criteria, run your DoCmd, then add one day or one week or whatever to your check time for that report.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SpectacledBear]

HI there,

What changes would this mean to my original code? and if the time is still hh:mm:ss how does this get rid of the problem?

Sorry to be annoying by the way, am still famillairising myself with access.

 

[SpectacledBear]

What if I changed the code to read And txtTime = Between Time()-1 and Time()+1 instead of =Time()

would this work?

 

[traingamer]

Let's back up a step. I sometimes get bogged down answering questions instead of figuring out the big picture.

Are you just trying to click a button to run an archive command (or any command)? If so, why bother with all of the time checks? Just run the command. Or am I missing something here?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SpectacledBear]

No, I want a macro to be run at a set time.

I have a form which allows the user to select a table to archive, and to set a time to archive it, and I have some code to make it all happen, but the code doesnt work properly. so like, if the value of the list box is Order, and the time is 11:00:00 (or whatever time the user enters), then run mcrOrderArchive.

Would it be easier to specify a time and have the macros run at that time automatically?

 

[traingamer]

So you are using an OnTimer event? I guess I was thrown by the click event above.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SpectacledBear]

No I was using a click event. However, if you think a on timer eent is easier/better I will change it. The click event is when the user selects the table they wish to archive, and types a time which they want to archive it, they click a button called 'set' (cmdTimer). This seemed logical to me at the time, but as I said my knowledge of access is limited and i'm beginning to ownder if i ve took the long way around?

 

[SpectacledBear]

When I run the code, it tells me there is an error and I need to debug. When I debug, the following line is highlighted:

Dim db As DAO.Database. If i delete it and run the code again, the following line is highlighted

Set db = CurrentDb

Any ideas?

 

[SpectacledBear]

Would using an on timer event be easier?

 

[AppStaff]

You may want to check to make sure that you have dao selected in your reference library. That is a common mistake that I make myself on occasion.

Also, I noticed your code has a typo (I think) for the word "closee"

In addition, not sure why you declare this variable

"Dim stDocName As String"

I don't see it used anywhere.

 

[SpectacledBear]

Appstaff. How do I reference the dao in the reference library?

Ah yes, I see the closee. This will be changed. Silly Me.

As for the dim line, I never noticed that before, it does seem out of place. I will delete it.

Thanks for your help!

 

[AppStaff]

To reference a library object:

1. Open up a module or access the vb window as if you were writing code.
2. Select from the top menu bar, Tools, References.
3. Click on the Microsoft DAO X.X Object Library.

X.X = latest version. mine is 3.6

 

[SpectacledBear]

Thanks for that,

have done it. Button is working, but runs macro stragith away, regardless of specified time. Is there any way to combat this?

 

[SpectacledBear]

I've tried everything on it, have spent four days now trying to sort this out, and would really really appreciate it if anyone could offer any suggestions

 

[AppStaff]

if the macro is running not to the logic you'd expect then you must have a logical flaw. sometimes it helps to map out the logic usign all possible options even if you will only be qualifying a few. We cannot really help you further without seeing your exact code with the logical problem.