Hoping some one can help me

[Tastic]

Hi,

I am currently working on a database of call centre statistics,

I have a table called Agents which contains the following fields:

[AgentKey] [Agent Name] [Active] [TeamID] plus a couple of others

I have a table called Stats which contains the following fields:

[Date] [AgentKey] and my Stats fields

I am trying to write some code that will create a new record in "Stats"
for each Agent in "Agents" usint [AgentKey] with the conditions that
[TeamID] must be equal to [TeamID] on the form, also [Active] must =
True

I have tried doing this using Do loop with the following code

Let StatsAgent = 0

Do Until StatsAgent = 80
StatsAgent=StatsAgent+1

Me.AgentKey.Value = StatsAgent
DoCmd.GotoRecord,,acNewRec

Loop


I can create a query of active agents by team, but the problem I have
here is that the Agent Keys will not be sequential and so the question
is how do I get the code to create a record for every key listed in the
query

If you can help me with this I will be incredibly grateful

Cheers

 

[Guest]

Hi Tastic,

I have a table called Stats which contains the following fields:

[Date] [AgentKey] and my Stats fields

I recommend renaming the Date field. You will save yourself future headaches
if you learn to avoid using reserved words in Access / JET. Here is a listing
of all of the words you should avoid using, along with a very handy tool that
you can download for free to check your existing databases:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

I am trying to write some code that will create a new record in "Stats"
for each Agent in "Agents" usint [AgentKey] with the conditions that
[TeamID] must be equal to [TeamID] on the form, also [Active] must =
True

Unless you are also storing [TeamID] in your Stats table, I don't see how
this comes into play. Here is a procedure that you might find helpful as a
starting point.

Option Compare Database
Option Explicit

Private Sub cmdAddStatRecords_Click()
On Error GoTo ProcError

Dim strSQL As String

strSQL = "INSERT INTO Stats (StatDate, AgentKey, blnActive) " _
& "VALUES (Date(), " & Me.AgentKey & ", True );"

' Debug.Print strSQL

CurrentDb.Execute strSQL, dbFailOnError

Me.SubFormContainer.Form.Requery

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdAddStatRecords_Click..."
Resume ExitProc
End Sub


where SubFormContainer is the name of the control on your main form that
holds or contains the subform.
Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi,

I am currently working on a database of call centre statistics,

I have a table called Agents which contains the following fields:

[AgentKey] [Agent Name] [Active] [TeamID] plus a couple of others

I have a table called Stats which contains the following fields:

[Date] [AgentKey] and my Stats fields

I am trying to write some code that will create a new record in "Stats"
for each Agent in "Agents" usint [AgentKey] with the conditions that
[TeamID] must be equal to [TeamID] on the form, also [Active] must =
True

I have tried doing this using Do loop with the following code

Let StatsAgent = 0

Do Until StatsAgent = 80
StatsAgent=StatsAgent+1

Me.AgentKey.Value = StatsAgent
DoCmd.GotoRecord,,acNewRec

Loop


I can create a query of active agents by team, but the problem I have
here is that the Agent Keys will not be sequential and so the question
is how do I get the code to create a record for every key listed in the
query

If you can help me with this I will be incredibly grateful

Cheers