Go Premium for a chance to win a PS4. Enter to Win


Failure to Save new data in first record

Posted on 2004-04-22
Medium Priority
Last Modified: 2010-05-02
My VB application uses a data control to connect to an Access table.... I use a combo box to navigate between the  table's records without a problem.

When I change the  data in the textboxes which are bound to a record in the table the new data gets saved  and replaces the old data as soon as I move the recordset by selecting another recrord from the combo box... the only exception to this is the top record in the combo box...... when new data is entered in that first record and I move to a different record I find the textboxes revert  back to their origninal value, refusing to update the changes.... every other record upadate  the changes  but not the first recrod.

Here is the combo box click event code:

Do Until Combo1.Text = Me.Text1.Text
If Not rs.EOF Then rs.MoveNext

I noticed the following: When data is changed in the top record and I select another record from the combo box, the combo box doesn't respond the first time.... it doesn't move the recordset until I click a second time.... please help if you can.
Question by:tariqanis
  • 2
  • 2
  • 2
LVL 11

Expert Comment

ID: 10889368
try this


Accepted Solution

Javin007 earned 1000 total points
ID: 10889422
Unfortunately, data bound controls are VERY VERY buggy in Visual Basic.  It's possible you've got a coding problem, but equally as possible that you've found one of the many bugs.

For database work, it's always best to go with ADODB.  It's also incredibly simple, and gives you the most flexibility.

ADODB basically has just two objects that you need:


So you would first go to your project references, and add the "Microsoft ActiveX Data Objects 2.X Library"

Then, you have to build your "connection" to the database, which will require a connection string.  The easiest way to do this is to ALSO add an ADODC control to your form temporarily, use it to make a connection to your database, and then just copy the connection string it creates, and remove the control.  (More on connection strings can be found here: http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJet)

'So now you've got you're connection string, something like this:

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:\somepath\myDb.mdb;" & _
           "User Id=admin;" & _

'You create your connection object:

Set conMyDatabase = New ADODB.Connection

'And a recordset.

Set rcdRecordset = New ADODB.Recordset

'Open the connection to the database.

conMyDatabase.Open strConnect

'Use the connection to open a recordset from the database:

rcdRecordset.Open "SELECT * FROM Table", conMyDatabase

(There are a large number of options here that would require a small amount of reading to understand.  But not too complex.)

Now, just create a REGULAR combobox, and when it's time to update the items in the combobox, you can simply use:

While Not rcdRecordset.EOF
   cmbBox.AddItem rcdRecordset("FieldName).Value

If ya learn to do things a little lower level like this, believe me, it will save you MONTHS worth of bug chasing with data bound controls.

LVL 11

Expert Comment

ID: 10892112
I would agree with Javin.  If your going to make any kind of respectable program accessing a database from VB, ADODB is the way to go.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 10892484
Hi Javin

I am trying it your way.....  the code you gave for filling the combobox is fine but now I am at the stage where I need to use the  combobox to move between  records...... I tried using the following code  but the application hung - here is the code  

Do Until Combo1.Text =  AC_No.text
If Not rsTracking.EOF Then
End If

I used the above code to select the record I want to move to... AC_No is the textbox that contains the ID for each record in the recordset I am browsing...... but like I said the application hangs each time I select a record from the Combobox...

Expert Comment

ID: 10894404
Hrm.  If it's hanging in that loop, you need to check Combo1.Text and make SURE it matches AC_No.text at some point.  Otherwise, once it gets through all the records in the database and doesn't have an exact match, it'll just bounce back and forth between the Do/Loop.


Author Comment

ID: 10896245
Thank you Javin for all your help.... you did get me to switch.... BUT.. the switch is not as easy as you make it out.... I had to learn a lot of other things on the way... like how to Load every field in the database into the textboxes just to geth them displayed  on the form (done automatically by the data control once you bind it to your record source) and when to do it (almost every time you move from record to record), then how commit the updated data back into the database (again some thing the data control makes easy) and other detailes which I struggled since I took up your advise... but I think I'll be well rewarded for the effort... so Thanks again...

The problem in this last case was, like you said, a problem with my recordset.... empty date fields in the table... now it's working fine....

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question