[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help with VB/Access project

Posted on 2004-11-22
17
Medium Priority
?
221 Views
Last Modified: 2010-05-02
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

0
Comment
Question by:Die-Tech
  • 9
  • 6
  • 2
17 Comments
 
LVL 3

Expert Comment

by:joefm1218
ID: 12645430
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 12645550
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12645743
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:Die-Tech
ID: 12645917
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 12646209
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12646266
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
 
LVL 3

Expert Comment

by:joefm1218
ID: 12647069
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
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 12647918
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12648007
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12648261
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12648834
GrahamSkan,

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


0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 12649020
Yes, it goes in the declarations section of the form.
If you paste everything in the form code, it should sort itself out.
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 12649234
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
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 2000 total points
ID: 12649833
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12660268
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 12663137
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12691340
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question