Could not see the changes after updating.

[redshadow]

Hi all,

I am using VB6 with ado data control and ado code and MS Access as the database.

I used ado control to hold selected records such as specific journal number.

I used ado code to insert and delete records off ado control data.

My objective here is to select specific journal group, and calculated their total amount.

After issuing insert, I noticed the database was updated and the new record was inserted. But the display in my form does not change. But the changes can be shown only after closing the form and running it again.(without issuing insert)

The code works fine and displays the values properly if I step through it; that is, executing the code line by line using the debugger.

I am thinking that maybe the execution of the code is too fast for before MS Access can update the table? (Just a guess.)

Any one of you has had an encounter of this situation?

Any help is highly appreciated. Thanks in advance.

 

[zemp]

You need to refresh the data control and what it uses to display the data. Look into the refresh method.

zemp

 

[redshadow]

Hi zemp,

I already did that. I even tried using the requery method. after the use of refresh didn't work, thinking that I should execute the same query again.

 

[zemp]

Post your code, maybe we can see something you missed. A common error is to refresh the Data control but not the Grid. Remember to do both.

Reexecuting the query is also a valid option.

zemp

 

[redshadow]

Here's my code, it is not the whole code but it shows the portion of executing the insert and its called modules.

'on the form activate
adodc1.recordsource = "SELECT * FROM JEntry WHERE JournalNo = " & txtJournalno.text
'cursor type is set to dynamic and locktype is pessimistic
adodc1.refresh

'insert code
Private sub InsertData()
dim rsNewEntry as ADODB.Recordset
set rsNewentry = New ADODB.Recordset
rsNewentry.open "SELECT Max(EntryNo) as lastEntry FROM JEntry WHERE JournalNo = " & txtJournalno.text, cn,adOpenDynamic, adLockOptimistic
if not (rsNewentry.EOF or rsNewentry.BOF) then
txtEntryNo.text = rsNewEntry!lastEntry + 1
endif
adodc1.recordset.addnew
SaveData
End Sub

'SaveData code
Private Sub SaveData()
adodc1.recordset!JournalNo = val(txtJournalNo.text)
adodc1.recordset!Amount = val(txtamount.text)
adodc1.recordset!EntryNo = val(txtEntryno.text)
adodc1.recordset!Description = trim(txtDescription.text)
adodc1.update
adodc1.refresh
txtTotalAmount.text = GetTotalAmount
'After trying the above and did not work I tried using
'adodc1.update
'adodc1.refresh
'adodc1.requery
'adodc1.refresh
' as well as ommiting the first refresh
End Sub

'GetTotalAmount Function
Function GetTotalAmount() as Double
dim rsTotal as ADODB.Recordset
set rsTotal = new ADODB.Recordset
rsTotal.open "SELECT SUM(Amount) as Total FROM JEntry WHERE JournalNo = " txtJournalno.text, cn,adOpenDynamic, adLockOptimistic
if not (rsTotal.EOF or rsTotal.BOF) then
GetTotalAmount = 0
else
GetTotalAmount = rsTotal!Total
End if

End Function

 

[redshadow]

Additionally, I am not using Grid here. I only use text box and maskedit box controls.

 

[redshadow]

Sorry, I mentioned in the ado data control that the lock type is pessimistic, it is optimistic rather.

 

[jebenson]

I think I see the problem. In your sub SaveData, after setting the values of the recordset fields you call adodc1.update. You need to call adodc1.recordset.update to commit the changes to the database, then call the adodc1.refresh.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[redshadow]

Hi jebenson,

Sorry. It was my mistake in typing. It is adodc1.recordset.update really. It won't run if it isn't, right?