Error 2105 from "Add New" Command Button

[aadebayo]

I have created a form, when I add the first record, it works perfectly. After saving the first record, when I attempt to add the second record, it complains and gives me the error 2105. Can this be because the saving of the record has not been commited to the SQL server database? The code that I use to save the record is below

Dim cnn As New ADODB.Connection
Dim strSQL as string


strSQL = "INSERT INTO tblCompany (companyid, CompanyName,Address1,Address2,Address3,Address4,Postcode,Telephone,Email,Contact)" & _
    "values ('" & company_id & "', '" & lvCompanyName & "' ,'" & Me.Address1 & "' ,'" & Me.Address2 & "' , '" & Me.Address3 & "' ," & _
    "'" & Me.Address4 & "', '" & Me.Postcode & "', '" & Me.Telephone & "', '" & Me.Email & "'," & _
    "'" & Me.Contact & "')"

    Set cnn = CurrentProject.Connection

    cnn.Execute strSQL, , adExecuteNoRecords

Please can anybody help me?

 

[boblarson]

2 things -

Just curious why you are using ADO here if you are connecting to the current database. It is less overhead and all just to use DAO in this case.

Dim strSQL as string


strSQL = "INSERT INTO tblCompany (companyid, CompanyName,Address1,Address2,Address3,Address4,Postcode,Telephone,Email,Contact)" & _
    "values ('" & company_id & "', '" & lvCompanyName & "' ,'" & Me.Address1 & "' ,'" & Me.Address2 & "' , '" & Me.Address3 & "' ," & _
    "'" & Me.Address4 & "', '" & Me.Postcode & "', '" & Me.Telephone & "', '" & Me.Email & "'," & _
    "'" & Me.Contact & "')"


    CurrentDb.Execute strSQL, dbFailOnError

So, there's no translation layer needed which ADO is, and you don't have to open a connection, etc.

Second thing - is that the entire code from your procedure? It doesn't look like it and so seeing the whole event in which this is located could help. If it is the whole thing then you have a problem since you're not closing your connection.

 

[aadebayo]

Thanks boblarson. I have decided to change the way I add records to the table, by making use of the ADODB.recordset, but I am still having the same 2105 problem. Below is the code that I am currently using

 Dim company_id As Integer
   Dim cnn As New ADODB.Connection
   Dim rst As New ADODB.Recordset
        
        company_id = DMax("[companyid]", "tblCompany") + 1
        Set cnn = CurrentProject.Connection
        rst.Open "tblCompany", cnn, adOpenDynamic, adLockPessimistic
            rst.AddNew
            rst!CompanyID = company_id
            rst!CompanyName = txtCompanyName
            rst!Address1 = Me.txtAddress1
            rst!Address2 = Me.txtAddress2
            rst!Address3 = Me.txtAddress3
            rst!Address4 = Me.Address4
            rst!Postcode = Me.txtPostcode
            rst!Telephone = Me.txtTelephone
            rst!Email = Me.txtEmail
            rst!Contact = Me.txtContact
            rst.Update
            rst.Close

Please I need some help

 

[boblarson]

If you feel you must use ADO instead of the native, built-in DAO, then you need to close your connection object and set it to nothing as well as the recordset object if you are going to instantiate them each time. You need to add this at the bottom of your procedure:

cnn.Close
Set rst = Nothing
Set cnn = Nothing