ADO new record #Error why??

[GAVO-UK]

Hello Everyone

I have a main form (frmNewOrder) and a sub form (SubOrderBeneficiary).
On the main form I have a button which sends the Subform
(SubOrderBeneficiary) 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_Click()
On Error GoTo Err_cmdNewBene_Click

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

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
Set SubBen = Me.SubOrderBeneficiary.Form

With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM tblBeneficiary WHERE BeneficiaryID =
''"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

Set SubBen.Recordset = rs
Set rs = Nothing
Set cn = Nothing
Me.SubOrderBeneficiary.SetFocus

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 (SubOrderBeneficiary).
On the main form I have a button which sends the Subform
(SubOrderBeneficiary) 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_Click()
On Error GoTo Err_cmdNewBene_Click

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

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
Set SubBen = Me.SubOrderBeneficiary.Form

With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM tblBeneficiary WHERE BeneficiaryID =
''"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

Set SubBen.Recordset = rs
Set rs = Nothing
Set cn = Nothing
Me.SubOrderBeneficiary.SetFocus

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.GoToRecord acActiveDataObject, , 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.Echo = 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****@peopleex.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

[Lyle Fairfield]

No, no one has actually bought anything from me on this news group.
So your doubt is well-justified.
Of course, I have nothing for sale, so this may not be so surprising.

[Tim Marshall]

GAVO-UK wrote:

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

Many, many of us have benefitted from Lyle's posts here over the years.

Basically, what he's saying is you have written code needlessly and what
you describe in your post as what you're trying to do is already done by
Access. So you're struggling (needlessly) to code something that Access
already does for you.

Just make the recordsource of the sub form SubOrderBeneficiary:

SELECT * FROM tblBeneficiary WHERE BeneficiaryID =
forms!frmNewOrder!SomeField

When you move from record to record in frmNewOrder, the display in
SubOrderBeneficiary will change.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[rkc]

GAVO-UK wrote:

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

I've stolen a thing or two from him.

[GAVO-UK]

If my post offended anyone, Im sorry about that, its just that this
other day this guy offered my to pay him to solve my problem in this
news group and this other time some one else offered to sell me a pice
of code/App that did what i was looking for.

I read you post in hurry and missed the point completely.
Thanks anyway for you trying to help

GAVO

[GAVO-UK]

If my post offended anyone, Im sorry about that, its just that this
other day this guy offered my to pay him to solve my problem in this
news group and this other time some one else offered to sell me a pice
of code/App that did what i was looking for.
I read you post in hurry and missed the point completely.
Thanks anyway for you trying to help

-----------------------------

Tim

I know that Access does something like that, my problem is that the
subform which I'm talking about is not linked to the main form but to
another sub form, let me explain.

This is an ADP application with an SQL back end, which will be accessed
using the internet (ADSL or Dial Up). I am using unbound forms and
subforms and setting their recordset from code.

The main form (frmNewOrder - Unbound) has two subforms SubForm1
(subNewOrderCustomer - being the main subform) and SubForm2
(SubNewOrderBeneficary - Linked to Subform 1). What I'm trying to do
is; call a code using a button from the Main form which would send the
just the subform 2 to a new record and the SubForm 1 remain in its
current record.

Maybe I'm struggling in something which is done easily, but then as I
already said, my code is working its just that it display an #Error for
a fraction of a second in the controls of SubForm 2 before sending it
to a new record.

Jaime.

[TC]

Maybe try this:

1. Unbind all of the form controls from the datasource;
2. Do whatever it is that causes the controls to say #Error;
3. Rebind the controls.

This would only take a few lines of code & could work quite well IMO.

HTH,
TC

[Darryl Kerkeslager]

"TC" <aa**********@yahoo.com> wrote

1. Unbind all of the form controls from the datasource;
2. Do whatever it is that causes the controls to say #Error;
3. Rebind the controls.

Dim c As Boolean
c = (Me.RecordsetClone.RecordCount > 0)

Me!btnSortByName.Enabled = c
Me!btnSortByLevel.Enabled = c
If c Then
Me!txtName.ControlSource = "off_name"
Me!txtLevel.ControlSource = "cs_level"
Else
Me!txtName.ControlSource = vbNullString
Me!txtLevel.ControlSource = vbNullString
End If
--
Darryl Kerkeslager

[TC]

Um, sorta. I had more like this in mind:

' unbind the controls.
dim ctl as control
for each ctl in me.controls
with ctl
if .controlsource<>"" then
.tag = .controlsource
.controlsource=""
endif
end with
next
' now do the thing which causes #error to occur.
' ( do thing ... do thing )
' rebind the controls.
for each ctl in me.controls
with ctl
if .tag<>"" then
.contrtolsource = .tag
.tag=""
endif
end with
next

That code is a bit rough because it assumes that the tag properties are
blank to begin with, and that they are not already used for other
purposes. But it would be easy to store the controlsource values
somehow else, eg. in a collection.

HTH,
TC

[GAVO-UK]

TC.

I use a similar code to check for nulls, therefore my tags are already
in use. What other way can I implement you code and use an alternative
to tags, I know you mentioned a possible solution but ! have no idea
how to do it! any guidelines?

Thanks
GAVO

[TC]

Try this (untested):

dim cs as collection
set cs = new collection

' unbind the controls.
dim ctl as control
for each ctl in me.controls
with ctl
if .controlsource<>"" then
cs.add .name & "|" & .controlsource, .name ' item, key.
.controlsource=""
endif
end with
next

' now do the thing which causes #error to occur.
' ( do thing ... do thing )

' rebind the controls.
dim v as variant, n as integer
for each v in cs
n = instr(v, "|")
me.controls(left$(v,n-1)).controlsource = mid$(v, n+1)
next
set cs = nothing

HTH,
TC

[Larry Linson]

"GAVO-UK" wrote

If my post offended anyone, Im sorry about that, its just that this
other day this guy offered my to pay him to solve my problem in this
news group and this other time some one else offered to sell me a pice
of code/App that did what i was looking for.

I'd wager that the "this guy" and the "some one else" to whom you refer are
the same person, someone who uses the newsgroup primarily to market his
services and a code CD with code from "unknown sources" and who refuses to
observe USENET rules and the newsgroup's charter's prohibition against
advertising. In recent years, only one "this guy" has done so, and it
definitely was not Lyle. You need to carefully differentiate between the one
person who offended you and the rest of us who are in the newsgroup to
assist others.

I have been around this newsgroup ever since it was created, and I've read a
lot of Lyle's posts. We have had our disagreements from time to time, but I
have NEVER, EVER seen him advertise in this newsgroup. I do not believe he
would violate that provision of the charter, even if his work was suitable
for "newsgroup marketing", which I think it is not.

Larry Linson
Microsoft Access MVP