Access doing calculations after form is closed.

[Jamet1234]

Hello again

I need help with how to have Access do things in the backround during and after a form has been closed.

I have a form that completes a maintenance work ticket. The form calculates the total amount of downtime of the machine. This works great except that there are times when the machine is down for long periods of time due to part avalability. We call this lost time.

I can make an if than statement that checks the info on that form but I do not know how to write one that opens a query and uses some other ifo that is not on the form that I need to do my calculation of lost time. Then take the answer and put it in the lost time table.

Keep in mind that my boss wants it all done in the backround.
Is this even possible?

Thanks for any help
Anthony

 

[FizzyFish]

You might want to try doing that in a module instead of a form.

 

[petehilljnr]

It is very possible - and there are many many ways in which it can be done.

You can use Domain Aggregate functions or Recordsets to make your calculation, and update a Recordset or use SQL to update the other table.

Pete.

 

[Jamet1234]

I need to open a query without it being seen, make sure there is 1 record, take the info I need. I have the calculation aready done just need the info from the query for VB to plug into the equation. Then take the answer and put it in the right table. If someone could give me some examples of the easiest way do this it would help me tremendously. If you need more info from me to give an example just let me know.

Thanks
Anthony

 

[petehilljnr]

Using Domain Aggregates:

Dim lCount as Long
Dim MyValue
 
lCount = DCount("*","MyQuery")
 
MyValue = DLookup("MyField1","MyQuery","MyID = " & SomeID)
 
MyResult = MyValue1 + .... your calculation

If the query only ever returns 1 result:

MyValue = DFirst("MyField1","MyQuery")

Something along those lines?

Pete

 

[Jamet1234]

Thanks pete

This what I have so far could you take a look at it and let me know if it will work.

'Dim RecordSet1 As New ADODB.RecordSet
'Dim IngCount1 As Integer
'Dim ingTicket As Integer
'Dim stgDown As String
'Dim ingManHours As Integer
'Dim dteOrderDate As Date
'Dim dteComplete As Date
'Dim ingDowntime As Integer
'Dim ingLostTime As Integer
'ingTicket = Me.cmbMatchID
'stgDown = Me.Out_of_Service
DoCmd.Close acForm, "MaintenanceWorkPerformed", acSaveYes
'If stgDown = True Then
    RecordSet1.Open "qryAwaitingParts", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    'With RecordSet1
        'If .EOF = False Then
            'IngCount1 = .RecordCount
        'Else
        'End If
    'End With
'RecordSet1.Close
                'If IngCount1 >= 1 Then
                    'dteOrderDate = DFirst("Date", "qryAwaitingParts", "[TicketID]=" & ingTicket)
                    'dteComplete = DFirst("Date/Time Completed", "qryAwaitingParts", "[TicketID]=" & ingTicket)
                    'ingManHours = InputBox("How many Manhours", "Manhours")
                    'ingDowntime = DateDiff("n", dteOrderDate, dteComplete) / 60
                    'ingLostTime = ingDowntime - ingManhours
        
        
        
        
        
        
        
        
                'Else
                'End If
        
'Else
'End If

I have it remarked out for now that is why the " ' " is there.

Also now I have my value how do I put it into the correct table?

Thanks for all your help

Anthony

 

[petehilljnr]

I'd tend to go for:

Dim lngTicket As Long
Dim stgDown As Boolean
Dim lngManHours As Integer
Dim dteOrderDate As Variant
Dim dteComplete As Variant
Dim lngDowntime As Long
Dim lngLostTime As Long
Dim strSQL As String
 
lngTicket = Me.cmbMatchID
stgDown = Me.Out_of_Service
 
DoCmd.Close acForm, "MaintenanceWorkPerformed", acSaveYes
 
If DCount("*", "qryAwaitingParts") >= 1 And stgDown Then
    dteOrderDate = DLookup("[Date]", "qryAwaitingParts", "[TicketID]=" & lngTicket)
    dteComplete = DLookup("[Date/Time Completed]", "qryAwaitingParts", "[TicketID]=" & lngTicket)
 
    lngManHours = InputBox("How many Man Hours?")
 
    If Not IsNull(dteOrderDate) And Not IsNull(dteComplete) And Not IsNull(lngManHours) And IsNumeric(lngManHours) Then 'just check you got all the values you need
 
        lngDowntime = DateDiff("h", dteOrderDate, dteComplete)
        lngLostTime = lngDowntime - lngManHours
 
        strSQL = "UPDATE MyTable SET MyField = " & lngLostTime & " WHERE MyKeyField = " & lngTicket 'am guessing here
 
        DoCmd.RunSQL strSQL ' OR CurrentDb.Execute strSQL
 
    Else
 
        MsgBox "No order date or no complete data or man hours is invalid"
 
    End If
 
End If

 

[Jamet1234]

Thanks again Pete

It works great except for one thing. I need to put ingLostTime in a new record. If I use your code it trys to change all records. My SQL Strings look like this but UPDATE wont work for the first is there a SQL statement to add to a new record?

strSQL = "UPDATE tblArrival SET ClompleteID = " & ingTicket
strSQL1 = "UPDATE tblArrival SET DowntimeDiff =" & ingLostTime & "WHERE ClompleteID = " & lngTicket

Thanks Again

Anthony

 

[petehilljnr]

Insert into MyTable (Field1, Field2, Field3, etc ...) Values (MyValue1, MyValue2, MyValue3, etc)

 

[Jamet1234]

Thanks Pete

I got it to work. I thank you for all your help.

Thanks

Anthony