Requerying the RecordSource for SubForm

D

Don

On a form there is a sub-form which lists the e-mail addresses of a member,
one of which is flagged as the primary address with a boolean check box. On
the main form there is a hidden text box whose data source is a member ID
number. On the sub form, me.RecordSource is used with a SQL statement to
load the e-mail addresses. It works fine when the main form is opened, but
when the user moves to another member, the e-mail addresses don't change;
they are the ones from the first displayed member. In the code that changes
members, I added the following:

me!subFormEMail.Requery

but the sub-form does not update.

Am I off base here with this design? Part of the reason for going this way
was that I have code on the sub-form to ensure the user only has one e-mail
address picked as the primary. To make the display work it appears the form
needs to requery the database to get changes that are made.

For example if a member has the address (e-mail address removed) in the database and
it is marked primary, when the user adds the address (e-mail address removed) and
marks it as primary, the sub-form shows both as primary, but the underlying
database correctly reflects only (e-mail address removed) as the primary.

Any ideas?

Thanks!

Don
 
M

Marshall Barton

Don said:
On a form there is a sub-form which lists the e-mail addresses of a member,
one of which is flagged as the primary address with a boolean check box. On
the main form there is a hidden text box whose data source is a member ID
number. On the sub form, me.RecordSource is used with a SQL statement to
load the e-mail addresses. It works fine when the main form is opened, but
when the user moves to another member, the e-mail addresses don't change;
they are the ones from the first displayed member. In the code that changes
members, I added the following:

me!subFormEMail.Requery

but the sub-form does not update.

Am I off base here with this design? Part of the reason for going this way
was that I have code on the sub-form to ensure the user only has one e-mail
address picked as the primary. To make the display work it appears the form
needs to requery the database to get changes that are made.

For example if a member has the address (e-mail address removed) in the database and
it is marked primary, when the user adds the address (e-mail address removed) and
marks it as primary, the sub-form shows both as primary, but the underlying
database correctly reflects only (e-mail address removed) as the primary.


I don't think that abbreviated syntax works. Try using the
more complete:
me!subFormEMail.FORM.Requery
 
D

Don

Marshall,

I tried the more complete form (), but no luck. Any chance I am
setting the RecordSource property in the wrong place?

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
Dim intDEBUG As VbMsgBoxResult
intDEBUG = MsgBox("Form_Open - fsubEmail", vbCritical, "DEBUG")
strSQL = "SELECT * FROM tblEmail " _
& "WHERE tblEmail.LinkID = " & Me.Parent!txtLinkID
Me.RecordSource = strSQL
End Sub

I have put MsgBox pop-ups in all the sub form events that seem
connected, but other than Open and Load when the main form is opened,
no events for the sub form ever seem to get triggered. Should the
Requery method trigger the sub form's query event?

The Requery statement seems to be in the correct place to detect the
name change in the parent form. It is set to change the background of
the e-mail field in the sub form red when a name is changed. When I
change the name, the e-mail address bacground changes to red.

Any suggestions would be greatly appreciated!

Thanks!

Don
 
M

Marshall Barton

Requery will not run the (sub)form's Open event and, because
you are concatenating the value of txtLinkID into the record
source SQL statement, it would have to do that. To make
this approach you wiould have to (re)set the record source
instead of using Requery.

A better way is to use the subform's design view to set the
record source to:

SELECT * FROM tblEmail WHERE tblEmail.LinkID =
Forms!nameofmainform.txtLinkID

amd, since that doesn't change, there's no need for the
subform's Open event to get involved. The requery should
work now.

Actually I would expect all this to be unnecessary if you
set the subform control's Link Master property to txtLinkID
and the Link Child property to LinkID. This way you can set
the subform's record source to just tblEmail and you
wouldn't even need the requery.
 
D

Don

Marshall,

Actually I started out as you suggested in terms of using the Link Child
property. The problem I ran into was that I was running into a conflict
trying to change the data.

Basically, in the before change event of the a check box which indicates the
primary e-mail on the sub form the user is asked if they want to keep the
existing primary e-mail, or use the new one. When value of the existing
record was changed, a critical error is thrown saying something the data
cannot be changed because the code or macro would create a conflict.

Unfortunately, I do not have the code with me right now or I would post it.
I will do that tomorrow. The more I am think about it, I might have a sort
of circular reference going since if I am changing the value in the
recordset it is changing the form which is probably throwing another
BeforeChange event for the check box that indicates the primary e-mail. I
will look at this on Monday and post conclusions.

Thanks for the help!!

Don




Marshall Barton said:
Requery will not run the (sub)form's Open event and, because
you are concatenating the value of txtLinkID into the record
source SQL statement, it would have to do that. To make
this approach you wiould have to (re)set the record source
instead of using Requery.

A better way is to use the subform's design view to set the
record source to:

SELECT * FROM tblEmail WHERE tblEmail.LinkID =
Forms!nameofmainform.txtLinkID

amd, since that doesn't change, there's no need for the
subform's Open event to get involved. The requery should
work now.

Actually I would expect all this to be unnecessary if you
set the subform control's Link Master property to txtLinkID
and the Link Child property to LinkID. This way you can set
the subform's record source to just tblEmail and you
wouldn't even need the requery.
--
Marsh
MVP [MS Access]

I tried the more complete form (), but no luck. Any chance I am
setting the RecordSource property in the wrong place?

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
Dim intDEBUG As VbMsgBoxResult
intDEBUG = MsgBox("Form_Open - fsubEmail", vbCritical, "DEBUG")
strSQL = "SELECT * FROM tblEmail " _
& "WHERE tblEmail.LinkID = " & Me.Parent!txtLinkID
Me.RecordSource = strSQL
End Sub

I have put MsgBox pop-ups in all the sub form events that seem
connected, but other than Open and Load when the main form is opened,
no events for the sub form ever seem to get triggered. Should the
Requery method trigger the sub form's query event?

The Requery statement seems to be in the correct place to detect the
name change in the parent form. It is set to change the background of
the e-mail field in the sub form red when a name is changed. When I
change the name, the e-mail address bacground changes to red.
 
D

Don

Marshall,

putting my code in the AfterUpdate event was the key! Put things with the
Link Child property back the way I had them, and away I went!

Thanks!

Don



Marshall Barton said:
Requery will not run the (sub)form's Open event and, because
you are concatenating the value of txtLinkID into the record
source SQL statement, it would have to do that. To make
this approach you wiould have to (re)set the record source
instead of using Requery.

A better way is to use the subform's design view to set the
record source to:

SELECT * FROM tblEmail WHERE tblEmail.LinkID =
Forms!nameofmainform.txtLinkID

amd, since that doesn't change, there's no need for the
subform's Open event to get involved. The requery should
work now.

Actually I would expect all this to be unnecessary if you
set the subform control's Link Master property to txtLinkID
and the Link Child property to LinkID. This way you can set
the subform's record source to just tblEmail and you
wouldn't even need the requery.
--
Marsh
MVP [MS Access]

I tried the more complete form (), but no luck. Any chance I am
setting the RecordSource property in the wrong place?

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
Dim intDEBUG As VbMsgBoxResult
intDEBUG = MsgBox("Form_Open - fsubEmail", vbCritical, "DEBUG")
strSQL = "SELECT * FROM tblEmail " _
& "WHERE tblEmail.LinkID = " & Me.Parent!txtLinkID
Me.RecordSource = strSQL
End Sub

I have put MsgBox pop-ups in all the sub form events that seem
connected, but other than Open and Load when the main form is opened,
no events for the sub form ever seem to get triggered. Should the
Requery method trigger the sub form's query event?

The Requery statement seems to be in the correct place to detect the
name change in the parent form. It is set to change the background of
the e-mail field in the sub form red when a name is changed. When I
change the name, the e-mail address bacground changes to red.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top