Need help with VB/Access project

Hello All,

I have been given a project at work and I'd like to get some input from the experts!

Project:  Write VB6 code to close and open contacts on a PAZ-2503 I/O board at predetermined times and for predetermined duration(s).
The closing and opening of the contact on the PAZ-2503 will make and break a circuit that will ring a factory bell (shift start, break, lunch, etc)

I have a server that has a PAZ-2503 "Relay" board.  The board has 8 normally open/normally closed relays on it that can be programmed to close or open.

What needs to happen is this....

The code should look up in the Access database to get the time(s) the relay boards contacts should close along with the number of times and the duration.

The code waits until the time found in the database is reached.  Then the contact should close for the predetermined duration, then open the contact.
If the bell is suppose to ring more than once, the code would close the contact again for the predetermined duration and then open the contact.

e.g.

  6:59am - Close contact for 500ms, open contact for 500ms, close contact for 500ms, open contact
  7:00am - Close contact for 1000ms, open contact

  9:00am - Close contact for 250ms, open contact for 250ms, close contact for 250ms, open contact
  9:10am - Close contact for 1000ms, open contact

11:44am - Close contact for 500ms, open contact for 500ms, close contact for 500ms, open contact
11:45am - Close contact for 1000ms, open contact

12:14am - Close contact for 500ms, open contact for 500ms, close contact for 500ms, open contact
12:15am - Close contact for 1000ms, open contact

  2:00pm - Close contact for 250ms, open contact for 250ms, close contact for 250ms, open contact
  2:10pm - Close contact for 1000ms, open contact

  3:29pm - Close contact for 500ms, open contact for 500ms, close contact for 500ms, open contact
  3:30pm - Close contact for 1000ms, open contact


I have the code needed to close and open the relay on the board.

I'm guessing the code will do a database lookup, need to pause somehow or wait until the looked up time is reached, react to the event, wait until the next time.

I have a little bit of Visual Basic 6 experience... more Access experience.

Any ideas on this?  How would you do something like this?  Should the "timed event" records be processed in some kind of array?
Should the code keep looking at the current time and loop if current time doesn't equal looked up time?

I'm giving 500 points to the what sounds like the best approach to handle this project.
I'm sure there will be more questions posted once I decide how to proceed.... all worth 500 points each.

TIA,
Die-Tech

LVL 4
Die-TechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

joefm1218Commented:
I'm not sure I follow exactly what the requirements are, but here is a possible solution. One follow up question is, "Does the timely need to be precise? And if so, how precise?"

I ask, because in VB, you can use timers to schedule events to fire based off of preset times. For example, I can add a timer to a form, and set the time to fire at 500ms. At approximately 500ms, you will receive a callback in a method which you can then act upon. Notice, I say approximately. This is because timers use Window's messaging which will queue the event on the window message queue for the running thread. Therefore, you might not get the event at precisely 500ms. It depends on when that queued event get's processed by the the correct thread (ie. the thread could get starved by a higher priority thread).

If this sounds acceptable, then let me know. I could russle up some code as an example.
0
GrahamSkanRetiredCommented:
You'll need a table with at least four fields:
Event Start Time, Close Interval 1, Open Interval 1, Close Interval 2

1. Find the time now and look for the time of the next event time

2. Set the timer to 1 minute, and in the Timer event, check the time-of-day until it is within 1 minute. Then set the to the time until the next event

3 Now close & set timer to close 1 interval.
On timer event open. If the value  for Open Interval 1 is not null, repeat step 3

4 go back to step 1

0
Die-TechAuthor Commented:
joefm1218,

I think this needs to be pretty precise.

The event needs to trigger at the specified time and if the durations are not exact the bell will sound a little differently when the durations are out of range.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Die-TechAuthor Commented:
GrahamSkan,

What if I want the bell to sound 3 times instead of 2?

I was thinking the table would have something like:
 EventStartTime     NumberOfRings       DurationOfRingClose        DurationOfRingOpen
        6:50am                  2                               500                                500
        7:00am                  1                             1000                               1000
        9:00am                  3                               250                                250
        9:10am                  1                             1000                               1000
      11:44am                  2                               500                                500
      12:45pm                  1                             1000                               1000
        1:59pm                  2                               500                                500
        2:00pm                  1                             1000                               1000
        3:29pm                  2                               500                                500
        3:30pm                  1                             1000                               1000

But I'm no expert... so mine may not be good table design.

Another thing to keep in mind is.... the contact relay on the board is normally open.  The event will close the contact relay (making the circuit) for the "DurationOfRingClose" and then open the contact relay for "DurationOfRingOpen".... if the "NumberOfRings" is >1 close the contact relay again for the "DurationOfRingClose" and then open the contact relay for "DurationOfRingOpen".  The "DurationOfRingOpen" is only for the time between multiple contact relay closes.  After the event, the contact relay stays open anyway.

What are your thoughts on this?

Thanks,
Die-Tech

0
GrahamSkanRetiredCommented:
I don't see why that wouldn't work.

1. Get Next event time
2. EventTime reached?
3 Close contact
4. Increment Counter
5 Closed time elapsed?
6 Open Contact
7 Counter < No required, repeat from 2
8 Repeat from 1  
0
Die-TechAuthor Commented:
GrahamSkan,

It sounds like you have the concept down.

What gets the next event time?  Some kind of Access db lookup?
What checks to see if the event time has been reached?  A timer? a counter loop?

Could you write an example of the above code?
I'm not sure how to even proceed on this.

Thank You,
Die-Tech
0
joefm1218Commented:
My experience with timers have been that they are accurate up to about +/- 100ms. If this is not acceptable, then you next alternative would be to use a hard loop. Remember, this loop is running within a thread that will be context switched by the OS. If the context is switched close to the 500ms interval, then you're out of luck.

The next alternative is a bit more involved. You will have to use the hardware timer. This timer will trigger a hard interrupt which will be very precise. The problem is that interfacing with the hardware timer is much more involved.
0
GrahamSkanRetiredCommented:
Here is an outline for the code.
You need to set a reference to the Microsoft Active Data Objects Library

Option Explicit

Dim Phase As Integer
Dim RingCounter As Integer
Const GetNewEvent = 0
Const Waiting = 1
Const EventImminent = 2
Const ContactsClosed = 3
Dim MyConn As New ADODB.Connection
Dim rs As New ADODB.Recordset


Private Sub Form_Load()
With MyConn
   .Provider = "Microsoft.Jet.OLEDB.4.0"
   .Open "C:\Documents and Settings\User Name\My Documents\RingTimes.mdb"
End With
Timer1.Enabled = True
Timer1.Interval = 100
End Sub

Private Sub Timer1_Timer()
Dim strSQL As String
Dim Secs As Long
Select Case Phase
    Case GetNewEvent
        RingCounter = 0
        strSQL = "SELECT RingContact.EventStartTime, RingContact.NumberOfRings, RingContact.DurationOfRingClose, RingContact.DurationOfRingOpen " & _
               "From RingContact " & _
                "WHERE RingContact.EventStartTime > #" & Time() & "# " & _
                "ORDER BY RingContact.EventStartTime;"
        rs.Open strSQL, MyConn
        Timer1.Interval = 100
        Phase = Waiting
    Case Waiting
        Secs = DateDiff("s", Time, rs.Fields("EventStartTime"))
        If Secs < 60 Then
            Phase = EventImminent
            Timer1.Interval = 1000 * Secs
        Else
            Timer1.Interval = 60000
        End If
    Case EventImminent
        Call CloseContacts
        Timer1.Interval = rs.Fields("DurationOfRingClose").Value
        RingCounter = RingCounter + 1
        Phase = ContactsClosed
    Case ContactsClosed
        Call OpenContacts
        If RingCounter < rs.Fields("NumberOfRings").Value Then
            Timer1.Interval = rs.Fields("DurationOfRingClose").Value
            Phase = EventImminent
        Else
            Phase = GetNewEvent
            rs.Close
        End If
End Select
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Die-TechAuthor Commented:
GrahamSkan,

Thanks for the hard work.

I'm going to try and see if I can get your code to work this afternoon.
I'll let you know how it's going.

Thanks,
Die-Tech
0
Die-TechAuthor Commented:
GrahamSkan,

Where does this part of the code go?

Option Explicit

Dim Phase As Integer
Dim RingCounter As Integer
Const GetNewEvent = 0
Const Waiting = 1
Const EventImminent = 2
Const ContactsClosed = 3
Dim MyConn As New ADODB.Connection
Dim rs As New ADODB.Recordset



Thanks,
Die-Tech
0
Die-TechAuthor Commented:
GrahamSkan,

Ok... I figured out where the above code goes.... it just goes at the top of the code page..... right?


0
GrahamSkanRetiredCommented:
Yes, it goes in the declarations section of the form.
If you paste everything in the form code, it should sort itself out.
0
Die-TechAuthor Commented:
GrahamSkan,

So far it seems to work great!!

I am running into an error after all the EventStartTimes have been handled though.

I get the following error message:

    Run-time error '3021'
    Either BOF or EOF is true, or the current record has been deleted.
    Requestion operation requires a current record.


So I'm guessing I need some kind of:    If rs.EOF And rs.BOF Then ?????


Thanks,
Die-Tech



0
GrahamSkanRetiredCommented:
Yes, I haven't handled that in the code. You could simply exit the program if eof or bof.
If the program needs to keep running overnight, You'll need to modify it a bit.

    Case GetNewEvent
        RingCounter = 0
       strSQL = "SELECT RingContact.EventStartTime, RingContact.NumberOfRings, RingContact.DurationOfRingClose, RingContact.DurationOfRingOpen " & _
               "From RingContact " & _
                "WHERE RingContact.EventStartTime > #" & Time() & "# " & _
                "ORDER BY RingContact.EventStartTime;"
        rs.Open strSQL, MyConn
       If rs.eof then
              Timer1.Interval = 60000
               rs.Close
       else
        Timer1.Interval = 100
        Phase = Waiting
       endif
0
Die-TechAuthor Commented:
GrahamSkan,

Excellent Job!!

I've got it working with the "CloseContacts" and "OpenContacts" sub routines.

I'm still having a bit of a problem once all the EventStartTimes have been processed.
The last bit of code you wrote is in place.  I see where if the recordset is at the EOF, the timer is set to 60000 and the recordset is closed.

What would be needed to restart the process?  I'm still trying to understand what exactly is happening at this point.

I have EventStartTimes from 7:00am to past midnight.

When the recordset is selected it gets all EventStartTime greater than the current time, right?

So if it's noon when I start the program, the recordset will only consist of the EventStartTimes after noon? or does it get all EventStartTimes after noon and until noon the next day?

Should I close the application and then reopen it to force it to reload the EventStartTimes again?

Thanks,
Die-Tech
0
GrahamSkanRetiredCommented:
The idea is (though it may not be working properly) that the recordset is closed after using the first record or because it is empty.
It should then recreate the recordset after an interval.
Between the last entry for the day and midnight, the recordset will be empty.
After midnight, it should pick up the first event for the day.

"What would be needed to restart the process?  I'm still trying to understand what exactly is happening at this point."
Should not be necessary.

"When the recordset is selected it gets all EventStartTime greater than the current time, right?"
Yes, but it only uses the first one.

"So if it's noon when I start the program, the recordset will only consist of the EventStartTimes after noon? or does it get all EventStartTimes after noon and until noon the next day?"
The former is true.

"Should I close the application and then reopen it to force it to reload the EventStartTimes again?"
No. At the first creation after midnight, the recordset will contain all the records.
0
Die-TechAuthor Commented:
GrahamSkan,

You did an excellent job!
I've got the program tweaked and compiled and running.


How are you with "TimeClock Design"?

I'm working on a time clock application in Access.
Once it's finished and working properly, I'd like to convert the program over to VB and compile it.
Is there an easy way to do this?

Thanks,
Die-Tech
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.