Form bound to Recordset issue

[Craig Buchanan]

I have a form that is bound to a recordset during the form's OnOpen event
(see code below). This seems to work correctly in most cases. When I try
to open the form to add a record, it doesn't behave as i would expect. From
another form, I use:

DoCmd.OpenForm "Form1", , , , acFormAdd

When Form1 opens, I see all 1000+ records, when I only want to see one
record (the new record).

What am I missing?

Thanks,

Craig Buchanan

<code>
Private Sub Form_Open(Cancel As Integer)

Dim RS As New ADODB.Recordset
With RS
'connect to SQL Server
.ActiveConnection = g_Application.GetConnection
.Source = "SELECT * FROM MY_TABLE "
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With

Set Me.Recordset = RS
Set RS = Nothing

End Sub
</code>

 

[Craig Buchanan]

I added the following to the Form_OnOpen event, before the other code:

Me.AllowAdditions = True
Me.DataEntry = True

This code will work as expected if the form's Record Source property is set
to the table's name. If I set the recordset property, these setting seem to
be ignored.

Can someone shed some light on this?

thanks.

 

[Dirk Goldgar]

I have a form that is bound to a recordset during the form's OnOpen
event (see code below). This seems to work correctly in most cases.
When I try to open the form to add a record, it doesn't behave as i
would expect. From another form, I use:

DoCmd.OpenForm "Form1", , , , acFormAdd

When Form1 opens, I see all 1000+ records, when I only want to see one
record (the new record).

What am I missing?

Thanks,

Craig Buchanan

<code>
Private Sub Form_Open(Cancel As Integer)

Dim RS As New ADODB.Recordset
With RS
'connect to SQL Server
.ActiveConnection = g_Application.GetConnection
.Source = "SELECT * FROM MY_TABLE "
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With

Set Me.Recordset = RS
Set RS = Nothing

End Sub
</code>

I've played with this a bit, and I can't get the DataEntry property to
work when the form has an ADO recordset. What you could do is set the
source of your recordset to exclude existing records:

.Source = "SELECT * FROM MY_TABLE WHERE False"

Incidentally, I found that it does work if I use a DAO recordset
instead, like this:

Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM Table1"
Set rst = CurrentDb.OpenRecordset(strSQL)

Set Me.Recordset = rst

*provided* that I set the DataEntry property *after* I set the form's
recordset.

 

[Craig Buchanan]

Dirk-

Thanks for the reply.

I was able to reproduce the DAO behavior.

I can't believe how the ADO binding misbehaves. What a pain! I noticed if
you change the where clause as you suggest, then the auto numbering gets
weird (set to fields max value, it seems). I've also tried "WHERE Id=-1",
which also effects the auto numbering field (set to 1). Seems like I'll
have rely on DoCmd.GoToRecord , , acNewRec.

Craig

 

[Craig Buchanan]

If anyone would like to reproduce the error, see the code below. Make sure
that your table has an auto increment column.

<CODE>
Private Const TABLE_NAME="" 'add your table here

Private Sub Form_Open(Cancel As Integer)

'try either ADO or DAO binding
BindAdoRecordset
'BindDaoRecordset

Me.AllowAdditions = True 'ensure this is on
Me.Form.DataEntry = True 'ignored by ADO binding

End Sub

Private Sub BindAdoRecordset()

Dim RS As New ADODB.Recordset
With RS
.ActiveConnection = Application.CurrentProject.AccessConnection
.Source = TABLE_NAME
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

Set Me.Recordset = RS
Set RS = Nothing

End Sub

Private Sub BindDaoRecordset()

Dim db As DAO.Database: Set db = CurrentDb
Dim RS As DAO.Recordset: Set RS = db.OpenRecordset("SELECT * FROM " &
TABLE_NAME)

Set Me.Recordset = RS
Set RS = Nothing
Set db = Nothing

End Sub

</CODE>

Keywords: Microsoft, Access, Form, ADO, DAO, Recordset, DataEntry, Error,
Binding