Create a query

[Dina01]

Hello,
I have an independant form that I enter data and then after I click on the OK button all the fields are saved into my table named Data_tb and Agent_tb.

When the form is closed I have an updated query that runs so we can update the code of the agent from 0 to 2.

This is what the code stand for...


'********************************
'* CODES *
'* *
'* 0 = AVAILABLE *
'* 1 = ASSIGNED *
'* 2 = PAUSE *
'* 3 = WAITING *
'* 4 = FINISH *
'* 5 = INCONNU *
'* 6 = PAUSE EN ATTENTE *
'********************************

Therefore the agent goes form being AVAILABLE TO BEING on PAUSE...Therefore codes 0 to 2...

The agent only gets updated again when the call is terminated from another form,and then another query is excuted to update the codes from 2 to 0.

This is my problem, I have a combo box named "description" and I only have 2 choices 15minutes or 30 minutes. And I also have a textbox named "HrsDebut" What I need it to do is that when I click on the OK button everything saves into my table but I need to start a count down of the minutes depending onthe choice I choose.......

Example if I choose 30 minutes, after the elapstiem the agent automatically gets update to code 0.


The following is the code I have in the form to save the data..
***********

'The following procedure will update the field code in the table named "Effetic_tb" when the form is closed.
Private Sub Form_Close()

Dim strSQL As String 'Declares strSQL as string
Dim dbs As Database 'Declarest dbs as a database

Set dbs = CurrentDb 'Set dbs as the current database

'SQL statement that willupdate the Effecti_tb table.
strSQL = "UPDATE Effectif_tb INNER JOIN Agent_tb "
strSQL = strSQL & "ON Effectif_tb.Agent = Agent_tb.Agent SET Effectif_tb.code = Agent_tb.code;"

' Procedure that will execute the SQL statement
dbs.Execute strSQL

End Sub


Private Sub Btn_OK_Click()

Dim rsData As Recordset ' Set rs as recordset
Dim rsAgent As Recordset ' Set rs as recordset

'Enable errors-handling routine
On Error GoTo Err_Btn_OK_Click

Dim strMessage As String 'Declares strMessage as string
Dim message As String 'Declares message as string

' If the categorie field is empty prompt user that he can't save data
If IsNull(Categorie) Then

MsgBox "Vous devez remplir le champs CATEGORIE pour pouvoir continuer !!!", vbInformation
Else
If IsNull(description) Then

MsgBox "Vous devez remplir le champs DESCRIPTION pour pouvoir continuer !!!", vbInformation

Else
' If an agent is chosen and you click on the OK button
If Not IsNull(Me.Agt1) Then

' Set the Start time of the call to be visible
Me.HrsDebut.Visible = True
' Automatically assign the current time
Me.HrsDebut = Time()
' Do not show the Finish time, since call is not finish
Me.HrsFin.Visible = False
' Do not show the Waiting Time, since call is not waiting status
Me.HrsEnAttente.Visible = False

' Set "Data_tb" to be the current recordset
Set rsData = CurrentDb.OpenRecordset("Data_tb")

' Add all the following fields to the current recordset
With rsData

.AddNew
!DataID = ObtenirSequence
!Date = Me!Date
!Categorie = Me!Categorie
!Priorite = "4"
!Descriptions = Me!description
!Lieux = Me!Lieux
!Niveau = Me!Niveau
!Emplacement = Me!Emplacement
!Precision = Me!précisions
!Commentaires = Me!Commentaires
!HrsRecuCall = Me!HrsRecu
!HrsDebutCall = Me!HrsDebut
!Evenement = "PAUSE"
!Code = "2"
!DateLog = Me!DateLog
!userId = Me!userId
!TimeLog = Me!TimeLog

.Update
.Close
End With

' Set "Agent_tb" to be the current recordset
Set rsAgent = CurrentDb.OpenRecordset("Agent_tb")

With rsAgent
.AddNew
!DataID = ObtenirSequenceAGT
!Date = Date
!HrsRecu = Me!HrsRecu
!HrsDebut = Time()
!HrsFin = Me!HrsFin
!Agent = Me.Agt1
!Code = "2"

.Update
.Close
End With

' Set the recordset to null
Set rsData = Nothing
Set rsAgent = Nothing

' Close the current form
DoCmd.Close
' Open the Main Form
DoCmd.OpenForm ("PCV_Dispatch_form")
Else

'If all the agents fields are empty then do the following
If IsNull(Agt1) Then

Me![Btn_OK1].Visible = True
Me![Btn_OK].Visible = False
Me![Btn_Menu1].Visible = True
Me![Btn_Menu].Visible = False
Me![Btn_retour1].Visible = True
Me![Btn_retour].Visible = False

' If no agent was chosen and you click on the OK button, You will be prompt
' to save the data or disgard all changes

' Prompt message
strMessage = message & "Voulez-vous vraiment QUITTER ce formulaire, sans assigné un agent.?" _
& Chr(13) & " "

'If the users response is no then it display the message box
If vbNo = MsgBox(strMessage, vbYesNo + vbQuestion, _
"PAUSE - Metter Agent En Pause") Then

Me![Btn_OK1].Visible = False
Me![Btn_OK].Visible = True
Me![Btn_Menu1].Visible = False
Me![Btn_Menu].Visible = True
Me![Btn_retour1].Visible = False
Me![Btn_retour].Visible = True

' Go to The first Agent combo Box refresh the combo box and open the drop down list.

'DoCmd.GoToControl "Agt1"
'Me.ActiveControl.Dropdown

Else

' Close the current form
DoCmd.Close
' Open the Main Form
DoCmd.OpenForm ("PCV_Dispatch_form")

' Set the recordset to null
Set rsData = Nothing
Set rsAgent = Nothing

End If
End If
End If
End If
End If

' Exit to avoid handler
Exit_Btn_OK_Click:
Exit Sub

'Error handling routine
Err_Btn_OK_Click:

MsgBox Err.description
' Resume execution at same line that caused the error.
Resume Exit_Btn_OK_Click:

End Sub



I am not too sure where to start, Can anyone please help.

 

[Jon K]

I think you can put your code in the form's Timer event procedure (On Timer event) to check the time elapsed and update the agent code to 0 when the time is up.

The frequency of running the code in the Timer event procedure is controlled by the form's TimerInterval property. By default, this proper is set as 0 (milliseconds), i.e. the Timer event is disabled.

From the OK button, you can activate the Timer event by setting the form's TimerInterval property to 1000 (milliseconds):-

Me.TimerInterval=1000

Thereafter the code in the Timer event procedure will be run once every second.