Type Mismatch

[ParyGoal]

Hello

I am getting an type Mistmach error every time I try adding a new record by calling a function. I am really clueless
THe code break on the calling function with all the parameters.
If ADD_ROLLNUMBER_RECORD( _
txtRollNumber.Text, _
txtRollDescription.Text, _
txtDrawingNumber.Text, _
txtCoverTopStockThick.Text, _
txtCoverThickness.Text, _
txtInitialCost.Text, _
txtRollFaceLenght.Text, _
txtRollOveralLength.Text, _
txtRollBearingCenterToCenterLenght.Text, _
txtRollWeight.Text, _
txtRollShellOD.Text, _
txtRollCoverMaxOD.Text, _
txtRollCoverMinOD.Text, _
txtRollBearingNumber.Text, _
txtRollBearingApaptNumber.Text, _
txtMaxSpeed.Text, _
txtRollCoverHardness.Text, _
txtHardnessTolerance.Text, _
txtSurfaceFinishTolerance.Text, txtMemo.Text) = True Then

Record Added successfully, else there was an error.

Here is the Function that adds new records to database

Private Function ADD_ROLLNUMBER_RECORD(ByVal RollNumber As String, _
ByVal RollDescription As String, _
ByVal DrawingNumber As String, _
ByVal CoverTopStockThick As String, _
ByVal CoverThickness As String, _
ByVal InitialCost As Currency, _
ByVal RollFaceLength As String, _
ByVal RollOveralLength As String, _
ByVal RollBearingCenterToCenterLenght As String, _
ByVal RollWeight As String, _
ByVal RollShellOD As String, _
ByVal RollCoverMaxOD As String, _
ByVal RollCoverMinOD As String, _
ByVal RollBearingNumber As String, _
ByVal RollBearingApaptNumber As String, _
ByVal MaxSpeed As String, _
ByVal RollCoverHardness As String, _
ByVal HardnessTolerance As String, _
ByVal SurfaceFinishTolerance As String, _
ByVal strMemo As String) As Boolean






Dim tmpSQL As String
On Error GoTo ADD_RECORD_ERROR

tmpSQL = ""
tmpSQL = "Select * from tblRollSpecification"
Set moRS = New ADODB.Recordset
moRS.Open tmpSQL, goConn, adOpenKeyset, adLockOptimistic

moRS.AddNew



'RollNumbe
moRS.Fields("RollNumber") = Trim$(txtRollNumber)
'Description
moRS.Fields("txtRollDescription") = Trim$(txtRollDescription)

moRS.Fields("cboMachinePrimary") = cboMachinePrimary.ItemData(cboMachinePrimary.ListIndex)
moRS.Fields("cboRollType") = cboRollType.ItemData(cboRollType.ListIndex)
moRS.Fields("txtDrawingNumber") = Trim$(txtDrawingNumber)
moRS.Fields("cboRollManufacturer") = cboRollManufacturer.ItemData(cboRollManufacturer.ListIndex)
moRS.Fields("cboPhysicalLocation") = cboPhysicalLocation.ItemData(cboPhysicalLocation.ListIndex)
moRS.Fields("txtCoverTopStockThick") = Trim$(txtCoverTopStockThick)
moRS.Fields("txtCoverThickness") = Trim$(txtCoverThickness)
moRS.Fields("txtInitialCost") = Trim$(txtInitialCost)
moRS.Fields("txtRollFaceLenght") = Trim$(txtRollFaceLenght)
moRS.Fields("txtRollOveralLength") = Trim$(txtRollOveralLength)
moRS.Fields("txtRollBearingCenterToCenterLenght") = Trim$(txtRollBearingCenterToCenterLenght)
moRS.Fields("txtRollWeight") = Trim$(txtRollWeight)
moRS.Fields("txtRollShellOD") = Trim$(txtRollShellOD)
moRS.Fields("txtRollCoverMaxOD") = Trim$(txtRollCoverMaxOD)
moRS.Fields("txtRollCoverMinOD") = Trim$(txtRollCoverMinOD)
moRS.Fields("txtRollBearingNumber") = Trim$(txtRollBearingNumber)
moRS.Fields("txtRollBearingApaptNumber") = Trim$(txtRollBearingApaptNumber)
moRS.Fields("txtMaxSpeed") = Trim$(txtMaxSpeed)
moRS.Fields("cboRollCoverMaterialID") = cboRollCoverMaterialID.ItemData(cboRollCoverMaterialID.ListIndex)
moRS.Fields("txtRollCoverHardness") = Trim$(txtRollCoverHardness)
moRS.Fields("txtHardnessTolerance") = Trim$(txtHardnessTolerance)
moRS.Fields("txtSurfaceFinishTolerance") = Trim$(txtSurfaceFinishTolerance)
moRS.Fields("txtMemo") = Trim$(txtMemo)



'Update Database
moRS.Update

DoEvents
ADD_RECORD = True
Debug.Print "frmRollSpec.ADD_RECORD - Record Added"
Exit Function

ADD_RECORD_ERROR:
If err.Number <> 0 Then
MsgBox "ERROR : frmRollSpec.ADD_RECORD" & vbNewLine & _
"ERROR # " & Str$(err.Number) & _
"DESCRIPTION - " & err.Description & vbNewLine, vbCritical + vbOKOnly
err.Clear
ADD_RECORD = False
End If
End Function

I will appreciate any help

Thank you

ParyGoal

 

[johnwm]

Unless you REALLY need the recordset open then just use an INSERT sql statement

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[Golom]

That particular error is difficult to diagnose from nothing but code because it depends on specific data values.

I do notice that your calling arguments are mostly string values with only one Currency and one Boolean value. What you are passing seems to be the contents of text boxes which are of course Strings.

Try converting the currency and boolean to those data types in the call to the function. For example

If ADD_ROLLNUMBER_RECORD( _
               txtRollNumber.Text, _
               txtRollDescription.Text, _
               txtDrawingNumber.Text, _
               txtCoverTopStockThick.Text, _
               txtCoverThickness.Text, _
               [red]CCur(txtInitialCost.Text)[/red], _
               txtRollFaceLenght.Text, _
               txtRollOveralLength.Text, _
               txtRollBearingCenterToCenterLenght.Text, _
               txtRollWeight.Text, _
               txtRollShellOD.Text, _
               txtRollCoverMaxOD.Text, _
               txtRollCoverMinOD.Text, _
               txtRollBearingNumber.Text, _
               txtRollBearingApaptNumber.Text, _
               txtMaxSpeed.Text, _
               txtRollCoverHardness.Text, _
               txtHardnessTolerance.Text, _
               txtSurfaceFinishTolerance.Text, _
               [red]CBool(txtMemo.Text)[/red]) = True Then

Also ... in the function code ... you are using "Trim$" on every field from the calling arguments. "Trim$" returns a String and you don't want strings for the currency and boolean fields.

 

[ParyGoal]

thank you both Golom and johnwm for you reply.
I will give it a try, and see what happens.
ParyGoal

 

[ParyGoal]

I did some revision to my function. I have five combo boxes that I am using as lookup value. If I specify a value for all the five combos while adding a record, everything goes smoothly. But I don't specifiy a value, which in sometimes I might, an error is returned saying: Run-time error 381. Invalid property array index. Here is the function below.


If ADD_ROLLNUMBER_RECORD( _
txtRollNumber.Text, _
txtRollDescription.Text, _
cboMachinePrimary.ItemData(cboMachinePrimary.ListIndex), _
cboRollType.ItemData(cboRollType.ListIndex), _
txtDrawingNumber.Text, _
cboRollManufacturer.ItemData(cboRollManufacturer.ListIndex), _
cboPhysicalLocation.ItemData(cboPhysicalLocation.ListIndex), _
txtCoverTopStockThick.Text, _
txtCoverThickness.Text, _
txtInitialCost.Text, _
txtRollFaceLenght.Text, _
txtRollOveralLength.Text, _
txtRollBearingCenterToCenterLenght.Text, _
txtRollWeight.Text, _
txtRollShellOD.Text, _
txtRollCoverMaxOD.Text, _
txtRollCoverMinOD.Text, _
txtRollBearingNumber.Text, _
txtRollBearingApaptNumber.Text, _
txtMaxSpeed.Text, _
cboRollCoverMaterialID.ItemData(cboRollCoverMaterialID.ListIndex), _
txtRollCoverHardness.Text, _
txtHardnessTolerance.Text, _
txtSurfaceFinishTolerance.Text, txtMemo.Text) = True Then

 

[Golom]

ListIndex returns zero if nothing is selected, You will need to resolve those parameters before you make the call to the function.

Dim MachinePrimary As String
If cboMachinePrimary.ListIndex = 0 Then
   MachinePrimary = ""
Else
   MachinePrimary = cboMachinePrimary.ItemData(cboMachinePrimary.ListIndex)
End If
[COLOR=black cyan] ' Etc for other Combo Box sourced fields.[/color]
If ADD_ROLLNUMBER_RECORD( _
               txtRollNumber.Text, _
               txtRollDescription.Text, _
               [red]MachinePrimary[/red], _
               etc.

You can't use IIF for this because IIF evaluates BOTH the TRUE and FALSE conditions and will still raise the error.