ADO new record #Error why??

[GAVO-UK]

Hello Everyone

I have a main form (frmNewOrder) and a sub form (SubOrderBenefi ciary).
On the main form I have a button which sends the Subform
(SubOrderBenefi ciary) to a newrecord. Everything works fine expect that
when I press the button, the controls on the subform display an #Error
for a fraction of a second when changing from the current record on the
subform to the new record and theneverything works fine.

Does anyone knows how can I prevent this as it does not look
professional?

The code Im using is as follows:

Private Sub cmdNewBene_Clic k()
On Error GoTo Err_cmdNewBene_ Click

Dim cn As ADODB.Connectio n
Dim rs As ADODB.Recordset

Set cn = CurrentProject. AccessConnectio n
Set rs = New ADODB.Recordset
Set SubBen = Me.SubOrderBene ficiary.Form

With rs
Set .ActiveConnecti on = cn
.Source = "SELECT * FROM tblBeneficiary WHERE BeneficiaryID =
''"
.LockType = adLockOptimisti c
.CursorType = adOpenKeyset
.Open
End With

Set SubBen.Recordse t = rs
Set rs = Nothing
Set cn = Nothing
Me.SubOrderBene ficiary.SetFocu s

Exit_cmdNewBene _Click:
Exit Sub

Err_cmdNewBene_ Click:
MsgBox "Your request could not be processed. Err 14 - " &
Err.Description & Err.Number, vbCritical, "Error"
Resume Exit_cmdNewBene _Click
End Sub
Any comments and possible solution would help me a lot.

Thanks in advanced.
GAVO.

[salad]

GAVO-UK wrote:

Hello Everyone

I have a main form (frmNewOrder) and a sub form (SubOrderBenefi ciary).
On the main form I have a button which sends the Subform
(SubOrderBenefi ciary) to a newrecord. Everything works fine expect that
when I press the button, the controls on the subform display an #Error
for a fraction of a second when changing from the current record on the
subform to the new record and theneverything works fine.

Does anyone knows how can I prevent this as it does not look
professional?

The code Im using is as follows:

Private Sub cmdNewBene_Clic k()
On Error GoTo Err_cmdNewBene_ Click

Dim cn As ADODB.Connectio n
Dim rs As ADODB.Recordset

Set cn = CurrentProject. AccessConnectio n
Set rs = New ADODB.Recordset
Set SubBen = Me.SubOrderBene ficiary.Form

With rs
Set .ActiveConnecti on = cn
.Source = "SELECT * FROM tblBeneficiary WHERE BeneficiaryID =
''"
.LockType = adLockOptimisti c
.CursorType = adOpenKeyset
.Open
End With

Set SubBen.Recordse t = rs
Set rs = Nothing
Set cn = Nothing
Me.SubOrderBene ficiary.SetFocu s

Exit_cmdNewBene _Click:
Exit Sub

Err_cmdNewBene_ Click:
MsgBox "Your request could not be processed. Err 14 - " &
Err.Description & Err.Number, vbCritical, "Error"
Resume Exit_cmdNewBene _Click
End Sub
Any comments and possible solution would help me a lot.

Thanks in advanced.
GAVO.

Can you do something like this? Create a button in the main form with
code similar to
Me.SubFormName. SetFocus
DoCmd.GoToRecor d acActiveDataObj ect, , acNewRec

[GAVO-UK]

Your code does send the subform to a new record and does not display
the #Error, but it does not allow me to EDIT the new record.

Any other possible solutions?

Thanks
GAVO.

[salad]

GAVO-UK wrote:

Your code does send the subform to a new record and does not display
the #Error, but it does not allow me to EDIT the new record.

Any other possible solutions?

Thanks
GAVO.

Do you have AllowEdits set to true? Is your recordsource for the form
updateable?

[GAVO-UK]

My Form is unbound and yes i can edit records?? what about my code,
would you think of anything that I could do to prevent the #Error?

Thanks

[salad]

GAVO-UK wrote:

My Form is unbound and yes i can edit records?? what about my code,
would you think of anything that I could do to prevent the #Error?

Thanks

Haven't a clue. Maybe set Painting or Repainting to False until the
code executes. Maybe Application.Ech o = False too.

[Lyle Fairfield]

GAVO-UK wrote:

Any comments and possible solution would help me a lot.

I'm glad you said that. My answer for almost all record selection,
creation and navigation problems on forms and subforms is this:

Access 2003 costs $130 CAD (Staples Business Depot 2005-11-30). It does
a few things superbly. One of these is to handle record selection,
creation and navigation on forms and subforms. Remove your code and
buttons and let Access do its job. That's what you paid the $130 CAD or
its equivalent for.

I've programmed Database User Interfaces for more than twenty years;
that's what I do.

[GAVO-UK]

Has anyone actually bought anything from you on this news group? I
really doubt it.

Still, any comments about my problem, highly appreciated.
GAVO.

[Lyle Fairfield]

You're welcome.

[Darryl Kerkeslager]

"GAVO-UK" <an****@peoplee x.com> wrote

Has anyone actually bought anything from you on this news group? I
really doubt it.

I suggest you re-read Lyle's post. You obviously missed his point.

--
Darryl Kerkeslager