Trouble adding new recordset

I have an Access database with a table called "Customers", which is linked via a ADODC control to a form in VB.
I have all the textboxes properly linked to the corresponding columns in the DB.  All i want to do is fill in the form, press "Add New", and have the information in the textboxes added to the appropriate fields in the Access DB.  

This code gives me an error stating that "Company Name cannot contain zero-length ...."  but it doesn't... it's filled in and I've double checked the spelling and the links, and they are right... btw, ".Update" is highlighted with this error msg.

With adoCustomers.Recordset
    !firstname = txtFirstName.Text
    !lastname = txtLastName.Text
    !contacttitle = txtTitle.Text
    !CompanyName = txtCompany.Text
    !address = txtAddress.Text
    !city = txtCity.Text
    !State = txtState.Text
    !zipcode = txtZipcode.Text
    !phonenumber = txtPhone.Text
End With

When I do the code without ".Update at the end it just overwrites the first record instead of adding a new one at the end of the db....

Any Thoughts?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
>>Company Name cannot contain zero-length
Try message out the contain of your text save in Company field:


is the text empty?

.AddNew method always follow by a .Update statement.

By the way, always try NOT use the ADODC data control for your data connection, try use the ADODB.Connection and ADODB.Recordset instead:


SQLstr = "Insert into myTable (firstname, lastname, contacttitle, CompanyName, address, city, State, zipcode = txtZipcode, phonenumber ) values ('" & txtFirstName.Text & "','" &  txtLastName.Text & "','" & txtTitle.Text & "',
" & txtCompany.Text & "','" & txtAddress.Text & "','" & txtCity.Text & "','" &  txtState.Text & "','" & txtZipcode.Text & "',
" & txtPhone.Text & "')"

conn.ConnectionString = myConnectionString
        conn.CursorLocation = adUseClient

conn.execute SQLstr
Excuse me if I've misunderstood but this seems to be a rather complex way of coding your program. Have you set the datafield and datasource properties in each of the text boxes. If you have then there is no need for the:

 !firstname = txtFirstName.Text
    !lastname = txtLastName.Text
    !contacttitle = txtTitle.Text
    !CompanyName = txtCompany.Text
    !address = txtAddress.Text
    !city = txtCity.Text
    !State = txtState.Text
    !zipcode = txtZipcode.Text
    !phonenumber = txtPhone.Text

This code is not needed and may be the problem.

I would suggest that you link your code to two seperate command events. i.e. click an add button to add code and an update button to update it. e.g.

Private sub command1_click()
end sub

Private sub command2_click()
end sub

GlattyAuthor Commented:

I do have the textboxes linked to the proper datasource and datafields, so i removed the code (!statements) like you suggested.
I get the same problem that it just adds the records in the first spot instead of appending a new record to the database.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

GlattyAuthor Commented:

I tried working with your suggestion as well, but i can't seem to figure out what's supposed to happen with it.  I think it's more complicated than it needs to be.  I should be able to do this with just the .AddNew and .Update commands....

That said, I think what the trouble is, is the AutoNumber field (CustomerID) in the database....  

What i want this form to do is take all the info from the textboxes, insert that info into the respective fields in the DB, and have the DB field, CustomerID, assign an autonumber.  Obviously i though i could do it this way, but I'm wrong.  How do i incorporate the autonumber function in and have the form add the info as a new record at the end of the DB?

The autonumber field should not be a problem, providing that you have built the datafile correctly it should add a number as needed. Is the table indexed on the autonumber or do you have more than one index field?
GlattyAuthor Commented:
The autonumber is the only (primary) key in the table.
if this form is only for ADDING, not EDITING records, try simply removing the ADODC control and remove all your databindings.  

On the button_click event, create your ADO connection and a recordset object.  Then you just add the new recordset and update the DB (in the exact way you did above).  Basically, just change

With adoCustomers.Recordset


With rsCustomers       'or whatever you decide to name your ADO recordset

Their is some conflict happenning with the databinding.  You probably don't need to databind your text boxes at all.
Glatty could you please repost your code as it is now (since the suggested modifications).
Glatty I have just tried recreating your problem using an ADODC object, two command buttons and one textbox connected to an existing MDB file using the following code.

Private Sub Command1_Click()    'Add button
End Sub

Private Sub Command2_Click()    'Update button
End Sub

It worked perfectly.

If you still hyave a problem this means that either the ADODC object has not been correctly configured or that your data file has a problem. Perhaps you should try with a simpler data file then add fields gradually until you find the one causing the problem.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GlattyAuthor Commented:
I re-did the code opening an ADODB connection...  it's a lot longer, but it works...

here's the code that works for me (for anyone else that's wondering):

Private Sub cmdAddInfo_Click()
Dim cnn1 As ADODB.Connection
Dim rsCustomers As ADODB.Recordset
Dim strCnn As String, strFirstName As String, strLastName As String, strTitle As String
Dim strCompany As String, strAddress As String, strCity As String, strState As String
Dim strCountry As String, strZipcode As String, strPhone As String, booRecordAdded As Boolean
Error = "False"
Call ErrorCheck      'Calls seperate sub to make sure all fields are filled in correctly and give proper error msgs
If Error = "True" Then Exit Sub

'Open Connection
Set cnn1 = New ADODB.Connection
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\BCS.mdb;Persist Security Info=False"
cnn1.Open strCnn
'Set recordset parameters
Set rsCustomers = New ADODB.Recordset
    rsCustomers.CursorType = adOpenKeyset
    rsCustomers.LockType = adLockOptimistic
    rsCustomers.Open "Customers", cnn1, , , adCmdTable

'Get Info from the form
    strFirstName = txtFirstName.Text
    strLastName = txtLastName.Text
    strTitle = txtTitle.Text
    strCompany = txtCompany.Text
    strAddress = txtAddress.Text
    strCity = txtCity.Text
    strState = txtState.Text
    strZipcode = txtZipcode.Text
    strPhone = txtPhone.Text

'Add the info from above to a new record
With rsCustomers
        !firstname = strFirstName
        !lastname = strLastName
        !contacttitle = strTitle
        !CompanyName = strCompany
        !address = strAddress
        !city = strCity
        !State = strState
        !Country = strCountry
        !zipcode = strZipcode
        !phonenumber = strPhone
    End With
booRecordAdded = True
End Sub

Since I solved it on my own, I'll only distribute partial points.  Thanks for your help though!
don't mean to nit-pick here, but it seems like you pretty much used my exact solution above.  you eliminated the databinding and even used the same name for the recordset that I suggested, changing the with statement and all...  I didn't even get 1 point!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.