HELP! Getting Error: Argument not optional (Error 449)

[Bobbak]

Hello All,
I could really use some help with this bit of code I am working on,
every time I come to execute it I get an error that says "Compile
Error: Argument not optional". Now I am using Access 2002 to run this
code that will allow me to populate a field in several tables at once.
Here is an example of the code.

Private Sub cmdOK_Click()

DoCmd.SetWarnings False

Dim db As ADODB.Connection
Dim rsDaily_CallVolumes, rsBilling, rsTech, rsTransfer, rsSales,
rsOther, As Recordset
Dim sEmployeeID, sQueue, sTextDate As String
Dim dTotalCalls As Double

Log.SetFocus
Log.Text = "Initializing Tables" + vbCrLf


sTextDate = Me!TextDay

Set db = CurrentDb.Connection
Set rsDaily_CallVolumes =
db.OpenRecordset("Daily_CallVolumes")
rsDaily_CallVolumes.MoveFirst

Set rsBilling = db.OpenRecordset("Dial_BO")
rsBilling.Index = "Employee ID"

Set rsTech = db.OpenRecordset("Dial_Tech")
rsTech.Index = "Employee ID"

Set rsTransfer = db.OpenRecordset("HSE_BO")
rsTransfer.Index = "Employee ID"

Set rsSales = db.OpenRecordset("HSE_Tech")
rsSales.Index = "Employee ID"
Set rsOther = db.OpenRecordset("Other")
rsOther.Index = "Employee ID"


'Start building the Daily Call Volumes Table

Log.Text = "Building Call Volumes" + vbCrLf

Do While Not rsDaily_CallVolumes.EOF
With rsDaily_CallVolumes
sEmployeeID = rsDaily_CallVolumes("Employee ID")
sQueue = rsDaily_CallVolumes("Queue")
dTotalCalls = rsDaily_CallVolumes("TotalCalls")

Select Case sQueue
Case "Billing_English", "Billing_French"
With rsBilling.Seek = sEmployeeID
If .NoMatch Then
.Add
rsBilling("Employee ID") = sEmployeeID
rsBilling(sTextDate) = dTotalCalls
.Update
Else
.Edit
rsBilling(sTextDate) = dTotalCalls
.Update
End If
End With

Case "Technical__English", "Technical_French"
With rsTech.Seek = sEmployeeID
If .NoMatch Then
.Add
rsTech("Employee ID") = sEmployeeID
rsTech(sTextDate) = dTotalCalls
.Update
Else
.Edit
rsTech(sTextDate) = dTotalCalls
.Update
End If
End With


Case "Transfer_Business_Office_English",
"Transfer_Business_Office_French"
With rsTransfer_BO.Seek = sEmployeeID
If .NoMatch Then
.Add
rsTransfer_BO("Employee ID") =
sEmployeeID
rsTransfer_BO(sTextDate) = dTotalCalls
.Update
Else
.Edit
rsTransfer_BO(sTextDate) = dTotalCalls
.Update
End If
End With

Case "Transfer_English", "Transfer_French"
With rsTransfer.Seek = sEmployeeID
If .NoMatch Then
.Add
rsTransfer("Employee ID") =
sEmployeeID
rsTransfer(sTextDate) = dTotalCalls
.Update
Else
.Edit
rsTransfer(sTextDate) = dTotalCalls
.Update
End If
End With

Case "Sales_English", "Sales_French"
With rsSales.Seek = sEmployeeID
If .NoMatch Then
.Add
rsSales ("Employee ID") = sEmployeeID
rsSales (sTextDate) = dTotalCalls
.Update
Else
.Edit
rsSales (sTextDate) = dTotalCalls
.Update
End If
End With

Case Else
With rsOther.Seek = sEmployeeID
If .NoMatch Then
.Add
rsOther("Employee ID") = sEmployeeID
rsOther(sTextDate) = dTotalCalls
.Update
Else
.Edit
rsOther(sTextDate) = dTotalCalls
.Update
End If
End With
End Select
With rsDaily_CallVolumes
Move.Next
End With
End With
Loop
Log.Text = "Daily Call Volumes Built" + vbCrLf

DoCmd.SetWarnings True

End Sub

 

[Gary Miller]

Bobbak,

I see a couple of potential problems, but it would help if
you would tell us which line is throwing this particular
error. Is it possibly this one which is the first problem
that I see?

Dim rsDaily_CallVolumes, rsBilling, rsTech, rsTransfer,
rsSales,
rsOther, As Recordset

The comma between 'rsOther' and 'As Recordset' should make
it bomb. The correct way to declare them all as Recordsets
would be

Dim rsDaily_CallVolumes as Recordset, rsBilling as
Recordset, rsTech as Recordset
Dim rsTransfer As Recordeset, etc...

Also beware of some real problems with 'DoCmd.SetWarnings
False' if you don't have error handling in place to turn
them back on if you do throw an error somewhere as Access
will not turn them back on until you tell it to.

Gary Miller
Sisters, OR