Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: Calculation carried out on click

    I am trying to get my database to carryout a calculation on the click of a button,

    My form is split into a main form and a subform, this works fine and does exactly what is should, however I now need to make an alteration to it.

    How this usually works, the user selects (Picture, section 1) an:

    event code
    a duration

    then clicks the add event button, the data is then transferred to the subform as shown in the (picture, section 2).

    My problem is that I am now requested to do the following:

    If an a specific event code is selected, for example, Code F21 and it has a duration of 20 minutes, instead of this being transferred as :

    F21 20 minutes

    I now need to divide the 20 minutes by 4 so in fact the actual data transferred would be:

    F21 5 mins

    I am thinking that this would be required to be done in code but any ideas please.

    Cheers

    Andy
    Attached Thumbnails Attached Thumbnails mainform.jpg  

  2. #2
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Could you tell me how the data is transferred?
    If it is done with a query, please post the SQL script.
    If it's done with code, post the code.

    Like that, I could give you a quick answer.

  3. #3
    Join Date
    Mar 2003
    Posts
    225
    thanks very much for you help

    this is everything behind the form, hope it's what you are meaning.

    Code:
    Option Compare Database
    Option Explicit
    Dim dbs As Database
    Dim rstNewEvents As Recordset
    Dim rstCodes As Recordset
    Dim rstProdchange As Recordset
    Dim rstCIP As Recordset
    Dim rstMaint As Recordset
    Dim dRunning As Integer
    
    Private Sub AddEvent_Click()
    
    ' This event procedure adds events to tblevents for the daycode and line specified.  The daycode and line are actually
    ' linked to tblproduction, which ensure that the events are correctly allocated.  This procedure determines which of
    ' the event grouping (minor stop, major stops, CIP, maintenance, ProdChange or breakdown) based on whether a part has
    ' been replaced, whether the event code has been defined as a specific type, or failing that on the basis of duration
    
    On Error GoTo Err_AddEvent_Click
    
    Set dbs = DBEngine.Workspaces(0).OpenDatabase("\\Tech02\OeeData\oeedata.mdb")
    
    'Sets the destination for data
    Set rstNewEvents = dbs.OpenRecordset("tblEvents")
    
    ' Sets the tables which contain the event codes that describe event types
    Set rstCodes = dbs.OpenRecordset("tblCodes")
    Set rstProdchange = dbs.OpenRecordset("tblprodchangecodes")
    Set rstCIP = dbs.OpenRecordset("tblCIPcodes")
    Set rstMaint = dbs.OpenRecordset("tblMaintCodes")
    
    'Data collected on the form is stored in these variables
    Dim dDayCode As Integer
    Dim dLine As String
    Dim dEvent As String
    Dim dDuration As Integer
    Dim dPart As Boolean
    
    'The various fields on the events table that the form will write to
    Dim rMin As Integer
    Dim rMaj As Integer
    Dim rCIP As Integer
    Dim rBrk As Integer
    Dim rPrd As Integer
    Dim rMain As Integer
    
    
    
    Dim strMsg As String
    
    'Set to zero so that they have a value when the writing back is done, ie not null
    rMin = 0
    rMaj = 0
    rCIP = 0
    rBrk = 0
    rPrd = 0
    rMain = 0
    
    'Various error checks to ensure that fields have been filled in correctly
    If IsNull([Forms]![frmdataentry]![DayCode]) Then
        Call MsgBox(prompt:="A Day Code is Required." & vbCrLf & "" & vbCrLf & "Please Enter a Day Code.", _
             Buttons:=vbInformation + vbOKOnly + vbDefaultButton1, _
             Title:="No Day Code Entered!")
            GoTo Notallfields
    End If
    
    If IsNull([Forms]![frmdataentry]![Line]) Then
        Call MsgBox(prompt:="A Line is Required." & vbCrLf & "" & vbCrLf & "Please Enter a Line.", _
             Buttons:=vbInformation + vbOKOnly + vbDefaultButton1, _
             Title:="No Line Entered!")
            GoTo Notallfields
    End If
    
    If IsNull([Forms]![frmdataentry]![SelectEvent]) Then
        Call MsgBox(prompt:="An event code is required." & vbCrLf & "" & vbCrLf & "Please enter an event code.", _
             Buttons:=vbInformation + vbOKOnly + vbDefaultButton1, _
             Title:="No Event Code Entered!")
            GoTo Notallfields
    End If
    
    If IsNull([Forms]![frmdataentry]![SelectDuration]) Then
        Call MsgBox(prompt:="An Event Duration is Required." & vbCrLf & "" & vbCrLf & "Please Enter an Event Duration.", _
             Buttons:=vbInformation + vbOKOnly + vbDefaultButton1, _
             Title:="No Duration Entered!")
            GoTo Notallfields
    End If
    
    'Data is gathered from fields on the form and placed in variables
    dDayCode = [Forms]![frmdataentry]![DayCode]
    dLine = [Forms]![frmdataentry]![Line]
    dEvent = [Forms]![frmdataentry]![SelectEvent]
    dDuration = [Forms]![frmdataentry]![SelectDuration]
    dPart = [Forms]![frmdataentry]![PartRepl]
    dRunning = dRunning + dDuration
    
    'Using the event code in 'dEvent', the event is searched for in the tables that contain codes for specific events
    rstProdchange.Index = "PrimaryKey"
    rstProdchange.Seek "=", dEvent
    
    rstCIP.Index = "PrimaryKey"
    rstCIP.Seek "=", dEvent
    
    rstMaint.Index = "PrimaryKey"
    rstMaint.Seek "=", dEvent
    
    'If the 'part replaced' bix is ticked then the event is automatically determined as a breakdown, and allocated as such
    If dPart = True Then
    rBrk = dDuration
    
    'if devent was found in the CIPcode table, CIP field is set to the duration
    ElseIf Not rstCIP.NoMatch Then
        If dDuration > 150 Then
        strMsg = "CIP value set to over 2 and half hours. Are you sure this correct?"
        If MsgBox(strMsg, vbYesNo, "High CIP Time") = vbNo Then Exit Sub
        End If
        
    rCIP = dDuration
    
    'if devent was found in the ProdChgcode table, Prodcng field is set to the duration
    ElseIf Not rstProdchange.NoMatch Then
        rPrd = dDuration
    
    'if devent was found in the maintcode table, maint field is set to the duration
    ElseIf Not rstMaint.NoMatch Then
        rMain = dDuration
    
    'If we have got this far then is either a major or minor stop type
    'If duration is greater or equal to 10 minutes,then it is defined as a major stop
    ElseIf dDuration >= 10 Then
        rMaj = dDuration
        
    'Otherwise it must a minor stop....
    Else
        rMin = dDuration
    
    End If
    
    'New field added to tblEvents
    rstNewEvents.AddNew
    
    'adds daycode, line and eventcodes
    rstNewEvents!DayCode = dDayCode
    rstNewEvents!Line = dLine
    rstNewEvents!EventCode = dEvent
    
    'Allocates the following fields
    'Only one of these should have a number that isn't zero, and that number is the dDuration value
    rstNewEvents!MinorStop = rMin
    rstNewEvents!MajorStop = rMaj
    rstNewEvents!CIP = rCIP
    rstNewEvents!ProductChange = rPrd
    rstNewEvents!Breakdowns = rBrk
    rstNewEvents!Maintenance = rMain
    
    'Data is added newfield in tblevents
    rstNewEvents.Update
    
    'some controls on form are reset, ready for next event
    [Forms]![frmdataentry]![SelectEvent] = Null
    Me!SelectEvent.SetFocus
    [Forms]![frmdataentry]![SelectDuration] = Null
    [Forms]![frmdataentry]![PartRepl] = False
    
    'Form refreshed so that event just added is displayed in the subform
    'NOTE The new event is added at the top.  This is achieved bysort the subform in desc order on the autonumbered
    'eventcodenumberlog.  This field is actually in the subform, but has 0 width so is not visible
    [Forms]![frmdataentry].Refresh
    
    
    Exit_AddEvent_Click:
        Exit Sub
    
    Err_AddEvent_Click:
        MsgBox Err.Description
        Resume Exit_AddEvent_Click
    
    'Not sure what this is for
    Notallfields:
        Exit Sub
    
    End Sub
    
    Private Sub Form_Close()
    DoCmd.OpenForm "fmnuMainMenu", acNormal, "", "", , acNormal
    End Sub
    
    Private Sub NextRecord_Click()
    ' When data entry is finished for one line, the user clicks this button to move onto the next line
    ' FrmDataentry is actually linked straight into tblProduction, so this procedure is about making sure the form stays
    ' nice and ready when the button is pressed
    
    On Error GoTo Err_NextRecord_Click
        Dim Currentday As Integer
        'Takes current daycode and stores it
        Currentday = [Forms]![frmdataentry]![DayCode]
        
        'Sets various fields to empty
        [Forms]![frmdataentry]![SelectEvent] = Null
        [Forms]![frmdataentry]![SelectDuration] = Null
        [Forms]![frmdataentry]![PartRepl] = False
        dRunning = 0
        
        ' Adds new record
        DoCmd.GoToRecord , , acNewRec
    
        'Refreshes form to show changes and clear the subform for new events
        [Forms]![frmdataentry].Refresh
        
        'Puts the daycode back, as this will remain the same
        [Forms]![frmdataentry]![DayCode] = Currentday
    
    Exit_NextRecord_Click:
        Exit Sub
    
    Err_NextRecord_Click:
        MsgBox Err.Description
        Resume Exit_NextRecord_Click
        
    End Sub
    'These calc functions use the mini calculator to update variuos fields
    
    Private Sub outcalc_Click()
    
    If IsNull(morn) Then morn = 0
    If IsNull(after) Then after = 0
    If IsNull(night) Then night = 0
    Me![output(c)] = morn + after + night
    morn = Null
    after = Null
    night = Null
    
    End Sub
    
    Private Sub packcalc_Click()
    If IsNull(morn) Then morn = 0
    If IsNull(after) Then after = 0
    If IsNull(night) Then night = 0
    Me![stdpack(b)] = morn + after + night
    morn = Null
    after = Null
    night = Null
    End Sub
    
    Private Sub plancalc_Click()
    
    If (morn + after + night) > 1440 Then
    MsgBox "planned time can not exceed 1440 mins in a day"
    Exit Sub
    End If
    If IsNull(morn) Then morn = 0
    If IsNull(after) Then after = 0
    If IsNull(night) Then night = 0
    
    Me!PlannedTime = morn + after + night
    morn = Null
    after = Null
    night = Null
    
    End Sub
    
    Private Sub Impcalc_Click()
    If IsNull(morn) Then morn = 0
    If IsNull(after) Then after = 0
    If IsNull(night) Then night = 0
    
    Me![Impressions(A)] = morn + after + night
    morn = Null
    after = Null
    night = Null
    
    End Sub
    
    Private Sub StdPack_b__AfterUpdate()
    'This event procedure is designed to give a visual indication that more packs were produced than pack impressions used
    ' As this can occur if stock from another time is added back, this is allowable, hence a simple colour change
    'data entry is not otherwise affected.
    If [stdpack(b)] > [Impressions(A)] Then
        [stdpack(b)].ForeColor = RGB(255, 0, 0)
    End If
    End Sub
    Private Sub Command33_Click()
    On Error GoTo Err_Command33_Click
    
    
        Screen.PreviousControl.SetFocus
        DoCmd.FindNext
    
    Exit_Command33_Click:
        Exit Sub
    
    Err_Command33_Click:
        MsgBox Err.Description
        Resume Exit_Command33_Click
        
    End Sub
    
    
    Private Sub DNR_Click()
    On Error GoTo Err_DNR_Click
    Dim strMsg As String
    
    strMsg = "Clicking 'Yes' will completely remove this record@Only click 'Yes' if you are sure this line did not run@This action can not be undone@Are you sure you wish to delete this record?@(You will be prompted again for confirmation)"
    If MsgBox(strMsg, vbYesNo, "Did Not Run") = vbNo Then Exit Sub
        
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    Exit_DNR_Click:
        Exit Sub
    
    Err_DNR_Click:
        MsgBox Err.Description
        Resume Exit_DNR_Click
    End Sub
    cheers

    Andy

  4. #4
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    OK Andy, you sure did post a lot of text...

    But here it is :
    Somewhere in the code behind the button AddEvent, you'll find this line :
    dDuration = [Forms]![frmdataentry]![SelectDuration]

    This is the place where the code is reading the duration. If you want the duration to be divided by 4 simply replace that line by :
    dDuration = CInt([Forms]![frmdataentry]![SelectDuration]/4)

    Explanation : The value entered int he textbox is divided by 4 before it is passed to the code. The CInt function is used to make sure that the result of the calculation will be an integer as this is obliged in your code.

    The result of tha calculation will be added as the duration of the newly added event.

    Do keep in mind that this is hardcoded : all durations will be divided by 4 unless you change the code again.

    Give it a try ....

  5. #5
    Join Date
    Mar 2003
    Posts
    225
    hi, thanks again,

    will this not just divide every code by 4???

    i need it to do the following:

    if the following codes appear

    F24 - divide by 4
    F25 - divide by 3
    F26 - Divide by 2

    then i need it to do the calculation, if not i need it to carryout it's normal functions

    sorry if i didn't explain my problem that well.

    Andy

  6. #6
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Right, this is a little more complicated...

    Is the list of possible values complete? Or does it need the possibility to be edited?

    What I suggest :
    Create an extra field in the table where the values F24, F25, etc are stored. Let's call it DurDiv (for "Duration Divider")
    On your form, you have a combobox (i suppose it's called SelectEvent) where the user must choose its event : I suppose this is the F24 or other F-value. Change the Row Source for this combo so that the DurDiv is included as a second but hidden row.

    Where we wrote in the code :
    dDuration = CInt([Forms]![frmdataentry]![SelectDuration]/4)

    we can now use this :
    dDuration = CInt([Forms]![frmdataentry]![SelectDuration]/[Forms]![frmdataentry]![SelectEvent].Column(1))


    Explanation :
    when the user has selected his event (say F24) in the combobox, he also has selected the second (hidden) column with the DurDiv. You can refer to this value as [Forms]![frmdataentry]![SelectEvent].Column(1)

    So each event will be divided by the value it has in the DurDiv field.
    Just make sure this field does never contain 0 or Null. If not applicable to the event (duration must not be divided) simply enter 1.

    This might look all a bit complicated but it should work.

  7. #7
    Join Date
    Mar 2003
    Posts
    225
    ok thanks will give it ago.

    cheers again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •