Solved

Failure to Save new data in first record

Posted on 2004-04-22
6
302 Views
Last Modified: 2010-05-02
Hi......
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:

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

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.
 
0
Comment
Question by:tariqanis
  • 2
  • 2
  • 2
6 Comments
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10889368
try this

rs.UpdateRecord
0
 
LVL 4

Accepted Solution

by:
Javin007 earned 250 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:

ADODB.Connection
ADODB.Recordset

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;" & _
           "Password="

'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:

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

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.

-Javin
0
 
LVL 11

Expert Comment

by:jmwheeler
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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:tariqanis
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  

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

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...
0
 
LVL 4

Expert Comment

by:Javin007
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.

-Javin
0
 

Author Comment

by:tariqanis
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....
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now