Access update too fast

[rcsenar]

My app has two forms. The first is a list box full of names. The second is text boxes with the detail for the list (address, etc.). Following is the steps I take to add or edit names.

1. I open an ado connection to a database and populate a list box with names. I then select a name to edit or hit an Add Name button. This takes me to the second form. (I close the ado connection before going to the second form)
2. As the second form loads, I open another ado connection to the database, populate the text boxes, then enter the rest of the data and hit a Save button which updates the database, closes the ado connection, closes the form, and returns me to the first form.
3. As the first form gets back focus, I reopen an ado connection to the database and repopulate the list box of names.

The Problem - When I return to the first screen, the list box does not show my updates. The only way to see my updates is to put a one second delay before returning to the first form.

So the database update is taking place properly. It just looks like the first form is reloading before the database has a chance to finish updating.

Does anyone know what I'm experiencing? Any help would be greatly appreciated.

Rob

 

[LLudden]

This thread probably has the answer:

thread222-298724

 

[rcsenar]

There was a lot of information in that thread. The last piece seemed the most applicable to me. Here is a copy of those statements:

"However, this only flushes the read-cach, and not the lazy write-cache. We have to do that differently. A Connection has a "Jet OLEDB:Transaction Commit Mode" property, which, if set to 1, causes all transaction commits to be written immediately for that Connection. An example:

(assuming than cn is an ADODB.Connection)

OldMode=cn.Properties("Jet OLEDB:Transaction Commit Mode&quot
cn.Properties("Jet OLEDB:Transaction Commit Mode&quot = 1
' Do all your stuff
cn.Properties("Jet OLEDB:Transaction Commit Mode&quot = OldMode 'if you want to set it back to original value"

I tried putting the code

cn.Properties("Jet OLEDB:Transaction Commit Mode&quot = 1

in my app but that gives me an error message...Run Time Error 3265...Item cannot be found in the collection corresponding to the requested name or ordinal.

I've obviously replaced cn with my connection variable, but is there something else I need to do?

 

[strongm]

Sounds like the connection isn't actually open (there are fewer properties when the connection isn't really open). Often you get this issue if you are using the Data Environment. You just need to do a:

cn.Open

before trying to access/modify the Transaction Commit Mode property

 

[rcsenar]

I put the statement after the .open statement and got rid of the error. But the problem is still there. I don't see updates made on form two unless i put in a 1-2 second delay.

Also, and maybe more importantly, I create the record on form one and then go to form two for detail entry. Without the delay before going to form two, I get a BOF EOF type error. With the delay, there is no error and the newly created record is displayed properly in form two.

Thanks for all the help.

Rob

 

[SQLwannabe]

I've ran into this a lot, and I first started doing what you did which was putting in a delay. But this just didn't seem professional to me, especially if you're desiging programs that someone else will use.

What I did was just update the forms manually. Meaning that after I did my database update, I manually put the information into the listview, text boxes, etc. It took a little work to get this to work properly when dealing with listviews, but it would be a breeze if just working with text boxes. This is the way I'd go if I were you.

 

[rcsenar]

I believe I am doing what you are suggesting. I do not use bound controls. I populate the controls through code.

Please advise if this is not what you meant.

I am most interested in what you do with the ado recordset(s) after the update. I close one recordset and open another (tied to the same db table) when I switch forms.

 

[rcsenar]

Here's more information on what I'm doing.

In form one, I use a query of the main table to populate the list box that does not show my updated data without the delay.

In form two, I use the actual table to populate the text boxes.

 

[tedsmith]

I use the SStab control, one LARGE tab for names, another for details, another for sub forms etc.
Any boxes like customer Name and ID text that are common to all tabs, are put on the form behind the SSTab and brought to the front order so they are always visible and never need duplicating.
All boxes that are in the same table or query are all on the same ADODC or dataenvironment.command so you never your problem.
I have variables that remember which adodc is in use.(Eg. CustomerInUse=true)
All text boxes have the same name and are indexed. It the textbox.lostfocus I refresh the active sources.(If CustomerInUse=true then CustomerADODC.update or refresh or whatever)
I also change toggle the background color of the box that has the focus using the one gotfocus and lostfocus subs.