Bound Form to ADO recordset but can't select specific records shown

[ChanHY]

Could anyone give me a hand to resolve this issue? I'm just a beginner and I've been stuck for hours.
Here's the problem. I put in the following code behind a OnClick of a button.
User keys in a date in a text field and click this button to retrieve records from a table which match the date that was input. I could get the correct records to be shown on the form but say if there're 10 records, I could only update the very first one I click. It could be the 4th or 5th on the list. I just can't update more than one record there. Instead, I got this error message: Cannot move to specific record or you may reach the end of records.
Can someone kindly help??

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset


With rs
Set .ActiveConnection = cn
.Source = "Select * from UpfrontNominalFee where CreateDate=Convert(Datetime,'" & Me.Text41 & " ',111)"
.LockType = adLockBatchOptimistic
.CursorType = adOpenKeyset
.Open
End With

Set Me.Recordset = rs


UpfrontNominalFeeID.ControlSource = "UpfrontNominalFeeID"
PrimaryAccountNo.ControlSource = "PrimaryAccountNo"
NominalMainAccount.ControlSource = "NominalMainAccount"
NominalSubAccount.ControlSource = "NominalSubAccount"
InputDate.ControlSource = "InputDate"
BookID.ControlSource = "BookID"
CostCentre.ControlSource = "CostCentre"
Amount.ControlSource = "Amount"
IsReconciledMap33.ControlSource = "IsReconciledMap33"
TransDescription.ControlSource = "TransDescription"
CreateDate.ControlSource = "CreateDate"
LastModifiedDate.ControlSource = "LastModifiedDate"
LastModifiedUser.ControlSource = "LastModifiedUser"

Set rs = Nothing
Set cn = Nothing

Exit_Command43_Click:
Exit Sub

 

[cmmrfrds]

Are you planning on doing a Batch Update?

.LockType = adLockBatchOptimistic '-?
.CursorType = adOpenKeyset

Try setting the cursor location to client and static, I don't think Keyset is compatible with a Batch.

.CursorType = adOpenStatic
.CursorLocation = adUseClient

 

[ChanHY]

Thanks...been busy with something else.
Will try it now.

 

[ChanHY]

Tried it....doesn't work....
I tried to put .LockType = adLockOptimistic as well but doesn't work.
Any other suggestion??

Thanks