Adding New Agent in Same Form but diffenrent record

[Dina01]

I have a form that contains 7 combo box that I can assign to 7 different agents, but when I assign the 2nd agent I would like it to have it's own record in the table, therefore it will increment the AUTONUMBER of my table named Urg_MedicaleDescrip. take all the data but save it on the other record, is this possible?

The name sof the agent come from a table named Effectif_tb and the combo box on the form have the following for the origin source

SELECT Effectif_tb.Agent, Effectif_tb.Poste, Effectif_tb.Code, Effectif_tb.Groupe FROM Effectif_tb GROUP BY Effectif_tb.Agent, Effectif_tb.Poste, Effectif_tb.Code, Effectif_tb.Groupe HAVING (((Effectif_tb.Code)<>1)) ORDER BY Effectif_tb.Groupe;

Here is my code for the OK button on my form. But right now all the data saves on the same record.


Private Sub Btn_OK_Click()

Dim rs 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 IsNull(Categorie) Then
MsgBox "Vous ne pouvez assigné un appel ne contenant pas de donnée !!!"
DoCmd.GoToControl "categorie"
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
'Assign code level 1 to agent since he is getting assigned to the call
Code.Value = "1"




' Set "Urg_MedicaleDescrip" to be the current recordset
Set rs = CurrentDb.OpenRecordset("Urg_MedicaleDescrip")

' Add all the following fields to the current recordset
With rs
.AddNew
!Categorie = Me!Categorie
!Priorite = Me!Priorite
!Descriptions = Me!description
!Lieux = Me!Lieux
!Niveau = Me!Niveau
!Emplacement = Me!Emplacement
!Precision = Me!précisions
!Telephone = Me!Telephone
!NumEmployé = Me!NoEmpl
!Commentaires = Me!Commentaires
!Sexe = Me!Sexe
!Age = Me!Age
!ConscienceDescription = Me!EtatCons
!Respiration = Me!respiratoire
!Pouls = Me!Pouls
!Informations = Me!Informations
!Agt1 = Me!Agt1
!Agt2 = Me!Agt2
!Agt3 = Me!Agt3
!Agt4 = Me!Agt4
!Agt5 = Me!Agt5
!Agt6 = Me!Agt6
!Agt7 = Me!Agt7
!Ambu = Me!Ambu
!Patrouille = Me!Patrouille
!HrsSupSecAvise = Me!HrsSupSecAvise
!HrsSurvAvise = Me!HrsSurvAvise
!HrsEntMenAvise = Me!HrsEntMenAvise
!HrsAviseInfirm = Me!HrsAviseInfirm
!HrsClientDirect = Me!HrsClientDirect
!HrsRendrePlace = Me!HrsRendrePlace
!HrsRegul = Me!HrsRegul
!HrsUrgSante = Me!HrsUrgSante
!HrsPolice = Me!HrsPolice
!HrsPompier = Me!HrsPompier
!HrsRecu = Me!HrsRecu
!HrsDebut = Me!HrsDebut
!HrsFin = Me!HrsFin
!IncidentIdicatif = Me!IncidentIdicatif
!IncidentSeq = Me!IncidentSeq
!HospitalierDescription = Me!CentreHosp
!Evenement = Me!urgencemedicale
!Code = Me!Code


.Update
.Close
End With
' Set the recordset to null
Set rs = Nothing


' Close the form
DoCmd.Close

Else

' 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) & " Les Information ne seron pas sauver."

'If the users response is no then it display the message box
If vbNo = MsgBox(strMessage, vbYesNo + vbQuestion, _
"URGENCE MEDICALE - Assignée agent") Then

' Go to The first Agent combo Box refresh the combo box and open the drop down list.
Me.Agt1 = ""
Me.Agt1.Requery
DoCmd.GoToControl "Agt1"
Me.ActiveControl.Dropdown
Else
' If you click on YES

Me.HrsDebut.Visible = False
Me.HrsRecu.Visible = False
Me.HrsEnAttente.Visible = False
Me.HrsFin.Visible = False
DoCmd.Close


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




[This message has been edited by Dina01 (edited 04-24-2002).]

 

[David R]

It never rains but it pours. If you have Agent1, Agent2, Agent3, etc fields, your data is not normalized.

You need ONE record in your main table for the data on your form. You need (up to) 7 individual records in a linked table that connects the PK of your main table to the AgentIDs for your 7 agents.

If I am misunderstanding what you're asking I apologize. However when you say you want to copy the existing data into a new record and increment the autonumber by one, that is a red flag for normalization problems. Data should be stored once and referred to as necessary.

Post back if I have missed the point.

David R

 

[Dina01]

Hey David,

I made a few modification, it's kind of hard to explain unless I would e-mail you my db, but let me know if you don't mind.

I have changed the table so know it only accept one agentNo but how do I choose 7 agents and save them to that table if I can only see one agent? I am pretty confused, it's a few days I am working on this....Can you please help

 

[David R]

Read up on database normalization. What you need is an additional table whose only role is to connect your main data (we'll call it tableData) to your agents data (we'll call it tableAgents). So if we have:

tableData
DID DataPiece AnotherDataPiece MoreData  SomeMoreData
 12  "Black"   "47th Cavalry"   "Ansel"   34-45-1245


AND


tableAgents
AID TheirName TheirRank TheirLocation
 47  "Frank"    "Sgt."    "Amsterdam"
 56  "Marie"    "Cpt."    "Hong Kong"
 87  "Lou"      "Pvt."    "Dallas"

Then the joining table will look like this:

tableDataAgents
DID AID
 12  47
 12  56
 12  87
  etc.

Then with queries you can pull together the data from any side you want to, whenever you want to, but you've only stored it once in your database, making it smaller and more efficient, not to mention a lot easier to maintain in case your information changes.

[This message has been edited by David R (edited 04-24-2002).]

 

[Dina01]

Hey David,

Thanks for you info, I am trying it right now...

Thanks Again

 

[Dina01]

Hey David,

I created a query named Data that looks like this

QryData
Urg_MedicaleDescripID NoAgent
Urg_MedicaleDescrip Effectif_tb

and my tables are both linked,

but I am confused when it come to the form I changed the code to

Private Sub Btn_OK_Click()

Dim rs 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 IsNull(Categorie) Then
MsgBox "Vous ne pouvez assigné un appel ne contenant pas de donnée !!!"
DoCmd.GoToControl "categorie"
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
'Assign code level 1 to agent since he is getting assigned to the call
Code.Value = "1"

' Set "Urg_MedicaleDescrip" to be the current recordset
Set rs = CurrentDb.OpenRecordset("Urg_MedicaleDescrip")

' Add all the following fields to the current recordset
With rs
.AddNew
!Categorie = Me!Categorie
!Priorite = Me!Priorite
!Descriptions = Me!description
!Lieux = Me!Lieux
!Niveau = Me!Niveau
!Emplacement = Me!Emplacement
!Precision = Me!précisions
!Telephone = Me!Telephone
!NumEmployé = Me!NoEmpl
!Commentaires = Me!Commentaires
!Sexe = Me!Sexe
!Age = Me!Age
!ConscienceDescription = Me!EtatCons
!Respiration = Me!respiratoire
!Pouls = Me!Pouls
!Informations = Me!Informations
!NoAgent = Me!Agt1
'!NoAgent = Me!Agt2
'!NoAgent = Me!Agt3
'!NoAgent = Me!Agt4
'!NoAgent = Me!Agt5
'!NoAgent = Me!Agt6
'!NoAgent = Me!Agt7
'!NoAgent = Me!Ambu
'!NoAgent = Me!Patrouille
!HrsSupSecAvise = Me!HrsSupSecAvise
!HrsSurvAvise = Me!HrsSurvAvise
!HrsEntMenAvise = Me!HrsEntMenAvise
!HrsAviseInfirm = Me!HrsAviseInfirm
!HrsClientDirect = Me!HrsClientDirect
!HrsRendrePlace = Me!HrsRendrePlace
!HrsRegul = Me!HrsRegul
!HrsUrgSante = Me!HrsUrgSante
!HrsPolice = Me!HrsPolice
!HrsPompier = Me!HrsPompier
!HrsRecu = Me!HrsRecu
!HrsDebut = Me!HrsDebut
!HrsFin = Me!HrsFin
!IncidentIdicatif = Me!IncidentIdicatif
!IncidentSeq = Me!IncidentSeq
!HospitalierDescription = Me!CentreHosp
!Evenement = Me!urgencemedicale
!Code = Me!Code


.Update
.Close
End With
' Set the recordset to null
Set rs = Nothing


' Close the form
DoCmd.Close

Else

' 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) & " Les Information ne seron pas sauver."

'If the users response is no then it display the message box
If vbNo = MsgBox(strMessage, vbYesNo + vbQuestion, _
"URGENCE MEDICALE - Assignée agent") Then

' Go to The first Agent combo Box refresh the combo box and open the drop down list.
Me.Agt1 = ""
Me.Agt1.Requery
DoCmd.GoToControl "Agt1"
Me.ActiveControl.Dropdown
Else
' If you click on YES

Me.HrsDebut.Visible = False
Me.HrsRecu.Visible = False
Me.HrsEnAttente.Visible = False
Me.HrsFin.Visible = False
DoCmd.Close


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


But I this code saves all the data to my table named Urg_MedicaleDescrip but only one agent gets saved to the call, I need it to save 2 agent if I enter 2 names and so on...

But it has to save ona different record with a different ID..

I am stuck and don't know what else to do....Do you think it's possible to look at my db..

 

[David R]

I am confused why you still have all of this code. Is your form bound to the recordsource? Is your subform linked via Master/Child fields correctly?

Please detail your new table structures. Posting acres of code doesn't help if your structure is faulty underneath.

 

[Dina01]

Hey David,

I am restructuring all me tables. I will let you know if I stil need help, Thanks for allyour help