ADO RecordSet Refresh Problem.

[David Keith]

The problem is that I can add a record into my table, but the only way I can see it is to close my app and re-start it.

Question #1: What am I doing wrong?

Question #2: What is the differance between "rstOwners!fldCount = 0" and "rstOwners.fldCount = 0"?

Working with VB 6.0 and Access 97.

DataGrid DataSource = adoOwners

ADO RecordSource Command Type = adCmdText
ADO RecordSource Command Text = select * from tblOwners order by fldCode

Code follows.
- - - - - - - - - - - - - - - - - - - - - - - - - - - -

Dim rstOwners As Object
Set rstOwners = adoOwners.Recordset

'Add the new record to the Owners Table.
rstOwners.AddNew
rstOwners!fldCode = txtCode.Text
rstOwners!fldName = txtOwner.Text
rstOwners!fldCount = 0
rstOwners.Update

'***** This code has no effect! *****
'***** Can not see the newly added record. *****
adoOwners.RecordSource = _
"select * from tblOwners order by fldCode"
adoOwners.Refresh

'Move the DataGrid Arrow to the found record.
'***** Can not see the newly added record. *****
adoOwners.Recordset.Find ("fldCode = '" & txtCode.Text & "'")

'Inform the user.
lblResult.Caption = "Owner added."

[Jym]

it's been a while since I used a database, but I think after you have done your update you need to reset your recordset to include the new data so try putting the set line in after the Update line.


Set rstOwners = adoOwners.Recordset

that would also explain why when you restart the program the new data is there because you now have the "new" recordset

if I'm wrong oops

[JeffB]

Hello David, don't really understand what is the problem. I have try your code and all works properly. Normally, when you add a record, the recordset position itself to that record, so you don't need to do any Recordset.find, I have try the code that follow and all work well, it looks like there is. You don't seem to do anythin wrong, even using the set rstOwners= adoOwners.Recordset work well, anyway, here is what I've change, but there is nothing really more than what you posted, sorry if it don't help you solve your problem...

Private Sub Command1_Click()

'DataGrid DataSource = adoOwners

adoOwners.CommandType = adCmdText
adoOwners.RecordSource = "select * from tblOwners order by fldCode"
adoOwners.Refresh

'ADO RecordSource Command Type = adCmdText
'ADO RecordSource Command Text = select * from tblOwners order by fldCode

'Code follows.
'- - - - - - - - - - - - - - - - - - - - - - - - - - - -
'

'Dim rstOwners As Object
'Set rstOwners = adoOwners.Recordset

'Add the new record to the Owners Table.


With adoOwners.Recordset

    .AddNew
    .Fields("fldCode").Value = "NEW CODE 11 AUG" 'txtCode.Text
    .Fields("fldName").Value = "NEW VALUE 11 AUG" 'txtOwner.Text
    .Fields("fldCount") = 555
    .Update
    
    MsgBox "Record Added :" & vbCrLf & "fldCode:" & .Fields("fldCode").Value & _
        vbCrLf & "fldName: " & .Fields("fldName") & vbCrLf & _
        "fldCount:" & !fldCount
    
End With
'***** This code has no effect! *****
'***** Can not see the newly added record. *****
'adoOwners.RecordSource = _
'"select * from tblOwners order by fldCode"
'adoOwners.Refresh

'Move the DataGrid Arrow to the found record.
'***** Can not see the newly added record. *****
'adoOwners.Recordset.Find ("fldCode = '" & "NEW CODE 11 AUG" & "'")

MsgBox adoOwners.Recordset.AbsolutePosition

'Inform the user.
'lblResult.Caption = "Owner added."
End Sub

Well, anyway, removed some echo stuff, but the AddRecord work as it should work, after the addNew, the MessageBox will show you the current record values, and for me, it is always what I've just add. Let us know if there is still a problem, and if there is still one, post the project (OR a part of the project that recreate the problem) as an attachment...

Maybe, and I say maybe, you have attribued to the MAxRecords of your Adodc control a value greater than 0, and a newly record bypassing the max record cause the error...

JeffB - hope it helps

[damica]

You may want to check this out.

The Refresh method shows only changes made to records in the current set. Since the Refresh method doesn't actually requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried. Nor will it exclude records that no longer satisfy the criteria of the query or filter. To requery the database, use the Requery method. When the record source for a form is requeried, the current set of records will accurately reflect all data in the record source.

I hope you solve your problem.

David M. Camp

[Patzer]

requery should work.
But just a small addition to that:
After the requery you 'loose' the position in the recordset / on the grid. So before the .requery you should set a bookmark, and after it return it to the rs / grid.

[David Keith]

Okay Jeff, I don't understand. Why does your code work and my code does not?

If I comment out the MsgBox in the WITH section, my SELECT and REFRESH appear to have no effect. Why?

'***** This code has no effect! *****
'***** Can not see the newly added record. *****
adoOwners.RecordSource = _
"select * from tblOwners order by fldCode"
adoOwners.Refresh

'Move the DataGrid Arrow to the found record.
'***** Can not see the newly added record. *****
adoOwners.Recordset.Find ("fldCode = '" & txtCode.Text & "'")

'Inform the user.
lblResult.Caption = "Owner added."

Also, please answer my second question:
Question #2: What is the differance between "rstOwners!fldCount = 0" and "rstOwners.fldCount = 0"?


What I am trying to do is have the user add the new recored, then have the RecordSet sorted again and have the arrow appear at the newly created record.

Sorry for all of the questions, but this is most puzzling.

David C. Keith
davidckeith@yahoo.com

[coolbiz]

Q1: Why do you need to REFRESH? After adding new record, the record will automatically be updated into DATAGRID and the newly added record will be at the current cursor (visible). This is demonstrated in my little project (attached).

Q2: Using <RECORDSET>!<fieldname> is the old way to retrieving/setting value from/to a field. BUT since we have moved to new age, you should use .FIELDS("<fieldname>").Value to do so. <RECORDSET>.<fieldname> is not valid (ie: rstOwners.fldCount = 0) unless I am missing something in learning ADO.

Good Luck,
-Cool Bizs

[JeffB]

Also, please answer my second question:
Question #2: What is the differance between "rstOwners!fldCount = 0" and "rstOwners.fldCount = 0"?

As said, rstOwners!fldCount is the old ways to do it, but it works, but you should think that this functionnality might be loss in further version of ADO (ok, now it's .NET, but 1 year ago, you should have think of that). It works well, it is a shortcut, it is like using txtSomeTextBox.Text instead of txtSomeTextBox, these two return the Text of the textbox, one is a shortcut


Just try coolbiz code, it is eactly as what you said:

What I am trying to do is have the user add the new recored, then have the RecordSet sorted again and have the arrow appear at the newly created record.

Try it, and let us know if it works!
JeffB

[David Keith]

Okay, there is no way that all of you guys could be wrong and I be right. So I went back and carefully re-read everything.

What I found was that damica, a.k.a David Camp, hit the nail on the head.

I was using the Refresh method instead of the Requery. Once I made this change, everything worked just the way I wanted it to. And yes I am using some of Cool Bizs' code.

Thanks a bunch everyone.