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.

[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 SubOrderBenefic iary:

SELECT * FROM tblBeneficiary WHERE BeneficiaryID =
forms!frmNewOrd er!SomeField

When you move from record to record in frmNewOrder, the display in
SubOrderBenefic iary 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
(subNewOrderCus tomer - being the main subform) and SubForm2
(SubNewOrderBen eficary - 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**********@y ahoo.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.RecordsetCl one.RecordCount > 0)

Me!btnSortByNam e.Enabled = c
Me!btnSortByLev el.Enabled = c
If c Then
Me!txtName.Cont rolSource = "off_name"
Me!txtLevel.Con trolSource = "cs_level"
Else
Me!txtName.Cont rolSource = vbNullString
Me!txtLevel.Con trolSource = 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(lef t$(v,n-1)).controlsour ce = mid$(v, n+1)
next
set cs = nothing

HTH,
TC