VB Forum / Database Access / October 2003

Bookmarks between the two recordsets will not be coherent.  IMHO the most
effecient way to do what you want to do is:

1. Don't create a data control
2. Create a recordset, using a keyset cursor, based on the entire table
3. Clone the recordset  (filter the clone using the WHERE clause for adodb2,
if any)
4. Bind the recordsets to your form controls in code
   Textbox:
       Set text1.DataSource = rs
       text1.DataField = "fieldname"
   Grid
       Set Grid1.DataSource = rsClone
       Grid1.Rebind

That way, when you add records to one recordset, they'll automatically
show-up in all clones without needing to requery.  Also, bookmarks across
all clones will be coherent.

But I don't think it'll fly unless your data provider supports keysets.

Good Luck,
Mark

Mark,

Thanks for your help. It works. I am wondering about a couple of things
though:
 
 1) Why you suggested not using a data control.

 2) When I update one recordset based on a table and the data persists
then do a requery of another recordset that has an sql statement
selecting data fields from the same table, why the changed data does not
show in the second recordset. It seems to me by requerying the recordset
I should be able to see the value of the changed field.

Also as an aside, here's another question I hope you could answer for
me.

Validating a textbox to make sure that only numerics are input, I have
the following code:

Private Sub txtQuantity_KeyPress(KeyAscii As Integer)
If KeyAscii = 8 Then Exit Sub 'Backspace
If KeyAscii = 27 Then 'Escape
   txtQuantity.Text = Adodc1.Recordset.Fields (Quantity").OriginalValue
   txtQuantity.SetFocus
   txtQuantity.SelStart = 0
   txtQuantity.SelLength = Len(txtQuantity.Text)
   Exit Sub
End If
If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
     KeyAscii = 0   ' Cancel the character.
     Beep
End If
End Sub

If the user presses the escape key, the original text is supposed to
reappear so the user can reedit.
When I hit the escape key, the code executes properly but the system
beeps at me. I'm not sure why it beeps.

Again, thank you very much for your help. I'd been racking my brain over
this for a few days and contemplating either using my second free
support from Microsoft for VB6 or going back to MS Access.

John