Error with saving data from a vb6 form to an access database

[eshajoshi]

Hi All,

I have a an error:

"Type Mismatch" on the line that has the stars next to it. Can someone please help? I appreciate all the help.

Option Explicit
Dim cn As ADODB.Connection              ' Establishing a Connection Object that provides a connection to the data
Dim rs As ADODB.Recordset
Dim cd As ADODB.Command

Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cd = New ADODB.Command

    ' Defines the connection object
    ' .ConnectionString passes the connection string as the first argument
    ' .CursorLocation defines the location of the cursor engine if we open a database
    With cn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Hmstore\hmstore\Users\Esha\QST_Job_Request\QSTJOBS.mdb;Persist Security Info=False"
        '.CursorLocation = adUseClient
        .Open
    End With

    ' Defines the command object
    ' .CommandText tells the command how to get the data by the text as an SQL string
    ' .CommandType evaluatew the .CommandText property as an SQL string
    With cd
        .ActiveConnection = cn
        .CommandText = "SELECT * FROM QSTJobRequest"
        .CommandType = adCmdText
    End With

    ' Defines the recordset object
    With rs
        .CursorType = adOpenStatic
        '.CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cd
    End With

    ' Adding new record at the end of the database and updates all the fields
    With rs
        .Requery
        .MoveLast
        .AddNew
        .Fields("RequestorName") = Text3.Text
        .Fields("Phone") = Text4.Text
        .Fields("TestPurpose") = Text7.Text
        .Fields("DateRequested") = Text5.Text
        .Fields("Priority") = Combo3.Text
        .Fields("DateNeeded") = Text6.Text
        .Fields("DateCompleted") = Text8.Text
        .Fields("CompletedBy") = Text10.Text
        .Fields("Program") = Text2(1).Text
        .Fields("HeadManufacturer") = Combo1(1).Text
        .Fields("PreampVendor") = Combo1(2).Text
        .Fields("PreampGeneration") = Combo1(3).Text
        .Fields("Platform") = Combo1(4).Text
        .Fields("HeadsInstalled") = Combo1(5).Text
       ** .Fields("Quantity") = Text2(0).Text**
        .Fields("IniTest") = Combo2(0).Text
        .Fields("IniTemp") = Combo2(1).Text
        .Fields("HeadInit_A") = Combo4(0).Text
        .Fields("HeadInit_B") = Combo4(1).Text
        .Fields("HeadInit_C") = Combo4(2).Text
        .Fields("ReTest") = Combo2(2).Text
        .Fields("ReTemp") = Combo2(3).Text
        .Fields("SpecialRequest") = Text1.Text
        .Update
    End With

    MsgBox ("Request submitted successfully.")

    ' Close the recordset and database
    rs.Close
    cn.Close

    Set cn = Nothing
    Set rs = Nothing
    Set cd = Nothing
End Sub

[eshajoshi]

The error points to line 55.

[rishif2]

The error points to line 55.

here make sure that your form contains Text2(0) control array

other wise replace your line 55 with the following code:-

.Fields("Quantity") = Text2.Text

one more thing , i dont see that in which sub(Procedure) you working and where it starts

hope this helps you . . .

[eshajoshi]

Hi rishif2,

Thanks for your response. I tried replacing line 55 with what you recommended above, but since Text2.Text is not a defined control in my program, the program gave me the error "Method or data member not found."

I made a few changes to my code, but I still have the error "Type Mismatch" on Line 58. Thanks again for all your help.

Option Explicit
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cd As ADODB.Command

Private Sub Command2_Click()
    ' Submitting the request and writing the data to the Microsoft Access Database

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cd = New ADODB.Command

    ' Defines the connection object
    ' .ConnectionString passes the connection string as the first argument
    ' .CursorLocation defines the location of the cursor engine if we open a database
    With cn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Hmstore\hmstore\Users\Esha\QST_Job_Request\QSTJOBS.mdb;Persist Security Info=False"
        '.CursorLocation = adUseClient
        .Open
    End With

    ' Defines the command object
    ' .CommandText tells the command how to get the data by the text as an SQL string
    ' .CommandType evaluatew the .CommandText property as an SQL string
    With cd
        .ActiveConnection = cn
        .CommandText = "SELECT * FROM QSTJobRequest"
        .CommandType = adCmdText
    End With

    ' Defines the recordset object
    With rs
        .CursorType = adOpenStatic
        '.CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cd
    End With

    ' Adding new record at the end of the database and updates all the fields
    With rs
        .Requery
        .MoveLast
        .AddNew
        .Fields("RequestorName") = Text3.Text
        .Fields("Phone") = Text4.Text
        .Fields("TestPurpose") = Text7.Text
        .Fields("DateRequested") = Text5.Text
        .Fields("Priority") = Combo3.Text
        .Fields("DateNeeded") = Text6.Text
        .Fields("DateCompleted") = Text8.Text
        .Fields("CompletedBy") = Text10.Text
        .Fields("Program") = Text2(1).Text
        .Fields("HeadManufacturer") = Combo1(1).Text
        .Fields("PreampVendor") = Combo1(2).Text
        .Fields("PreampGeneration") = Combo1(3).Text
        .Fields("Platform") = Combo1(4).Text
        .Fields("HeadsInstalled") = Combo1(5).Text
        .Fields("Quantity") = Text2(0).Text
        .Fields("IniTest") = Combo2(0).Text
        .Fields("IniTemp") = Combo2(1).Text
        .Fields("HeadInit_A") = Combo4(0).Text
        .Fields("HeadInit_B") = Combo4(1).Text
        .Fields("HeadInit_C") = Combo4(2).Text
        .Fields("ReTest") = Combo2(2).Text
        .Fields("ReTemp") = Combo2(3).Text
        .Fields("SpecialRequest") = Text1.Text
        .Update
    End With

    MsgBox ("Request submitted successfully.")

    ' Close the recordset and database
    rs.Close
    cn.Close

    Set cn = Nothing
    Set rs = Nothing
    Set cd = Nothing

End Sub