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

  • Thread starter Thread starter Bobbak
  • Start date Start date
B

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
 
You are using ADO (Dim db As ADODB.Connection).
ADO does not have an OpenRecordset method.
You would instead use the open method and pass in the
connection object.
You must first open the connection object, passing in a
connection string.
Alternatively use DAO and then you will have access to
the openrecordset method.

The seek method for ADO is Sub Seek(KeyValues,
[SeekOption As SeekEnum = adSeekFirstEQ])

The Seek method for DAO is Sub Seek(Comparison As String,
Key1, [Key2], [Key3], [Key4], [Key5], [Key6], [Key7],
[Key8], [Key9], [Key10], [Key11], [Key12], [Key13])

Here is a rough DAO example:

Private Sub cmdOK_Click()

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

DoCmd.SetWarnings False

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

sTextDate = Me!TextDay

Set db = CurrentDb()
Set rsDaily_CallVolumes = db.OpenRecordset
("Daily_CallVolumes", dbOpenTable)

If Not rsDaily_CallVolumes.EOF Then
'Start building the Daily Call Volumes Table
Log.Text = "Building Call Volumes" + vbCrLf
Set rsBilling = db.OpenRecordset("Dial_BO",
dbOpenTable)
rsBilling.Index = "Employee ID"

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

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

Set rsSales = db.OpenRecordset("HSE_Tech",
dbOpenTable)
rsSales.Index = "Employee ID"

Set rsOther = db.OpenRecordset("Other",
dbOpenTable)
rsOther.Index = "Employee ID"

Do While Not rsDaily_CallVolumes.EOF
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
.AddNew
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
.AddNew
rsTech("Employee ID") =
sEmployeeID
rsTech(sTextDate) =
dTotalCalls
.Update
Else
.Edit
rsTech(sTextDate) =
dTotalCalls
.Update
End If
End With



Case "Transfer_Business_Office_English", "Transfer_Busines
s_Office_French"
With rsTransfer_BO
.Seek "=", sEmployeeID
If .NoMatch Then
.AddNew
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
.AddNew
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
.AddNew
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
.AddNew
rsOther("Employee ID") =
sEmployeeID
rsOther(sTextDate) =
dTotalCalls
.Update
Else
.Edit
rsOther(sTextDate) =
dTotalCalls
.Update
End If
End With
End Select
rsDaily_CallVolumes.MoveNext
Loop
Log.Text = "Daily Call Volumes Built" + vbCrLf
Else
Log.Text = "No Volumes to be Built" + vbCrLf
End If

DoCmd.SetWarnings True

End Sub

Hope this assists you.
 
Back
Top