Identifying Controls in the Detail of a Continuous Form Sub Form

[Don]

On a form there is a sub form with e-mail addresses. The default view
for the sub form is continuous form. The controls for the detail on
the sub form are a text box for the e-mail address, a check box to
indicate if that address is the primary address for the individual, and
then a tex box for comments. In the case where more than one detail is
displayed, is there a way to tell If there are more than one details
(rows), is there anyway of telling which detail (row) being modified?

For example, if the sub form is showing:

(e-mail address removed) ( ) work
address
(e-mail address removed) (check) home adress
(e-mail address removed) ( ) text only
e-mail

and the user clicked the check box for (e-mail address removed), if I
reference the control txtEmailAddress, which address will it return?
And why? Is there a way to reference the controls for the other
details?

The purpose of all this is to implement code to check how many primary
addresses there are for an employee when an addition or change is made.
If there are more than one, the user is forced to pick. If the change
would result in no primary, the user is prompted to pick another.
The algorithm for a change to the primary check box would be something
like this:
if cleared
check to see if there is an e-mail marked primary
if another primary
don't do anything else
else
warn user and leave checked

if set
check to see if there is another primary
if not
carry on
else
warn user there is another primary
make user chose one or the other as primary

I tried implementing this in the before change event of the check box,
but ran into a problem which I think is caused by changing one of the
other details. It seems like it that change caused the BeforeChange
event to be fired again. (Does a change to the underlying data cause
the change events to fire?)

Any suggestions on this?

Thanks!

Don

 

[Wayne Morgan]

The continuous form is just a display method. Any values returned or edited
will be for the current record, just as they are in single form view. The
current record is the one with the cursor. If you have Record Selectors
turned on in the form's properties, the current record will be the one with
the left pointing triangle in the bar on the left side of the form. This
will change to a pencil icon if the record is "dirty" (i.e. in the process
of being edited).

To reference the controls for the other details, you would either have to
change the current record to the other record or you would have to work with
the form's Recordset, RecordsetClone, or underlying RecordSource.

To keep one and only one checkbox checked, you could do something like this
in the checkbox's AfterUpdate event:

'See if there is already a Primary selected and if the current change was to
True or False
If DCount("*", "FormRecordSourceName", "[PrimaryField] = True") = 0 And Not
Me.chkPrimary Then
strMsg = "You must have at least one address selected as Primary."
strMsg = strMsg & vbCrLf & "Please select another Primary to unselect
this one."
Msgbox strMsg
Me.chkPrimary.Undo
End If

If DCount("*", "FormRecordSourceName", "[PrimaryField] = True") > 0 and
Me.chkPrimary Then
strMsg = "There is already a Primary address. Change it to this one?"
If Msgbox(strMsg, vbYesNo + vbQuestion) = vbYes Then
strSQL = "UPDATE FormRecordSourceName SET [PrimaryField] = False
WHERE [IDField] = " & Me.txtIdField
CurrentDb.Execute strSQL, dbFailOnError
Me.chkPrimary = True
Else
Me.chkPrimary.Undo
End If
End If

This checks the underlying RecordSource to see if there are any addresses
marked as Primary. Since the current change (you just checked or unchecked
the check box) is not yet written to the table, this change won't be seen by
DCount(), so you need to verify what the current change was also (i.e.
whether the user just checked or unchecked the box). The syntax in the SQL
statement assumes that the unique ID field for the record is a number data
type.

 

[stefan hoffmann]

hi Don,

The purpose of all this is to implement code to check how many primary
addresses there are for an employee when an addition or change is made.
If there are more than one, the user is forced to pick. If the change
would result in no primary, the user is prompted to pick another.
Any suggestions on this?

I'm using this simple loop to implement a radio button group behavior in
the after update event of the checkbox control (works only in datasheet
view):

With Me.RecordsetClone
.MoveFirst
Do While Not .EOF
.Edit
![CheckField] = False
.Update
.MoveNext
Loop
End With


mfG
--> stefan <--

 

[Don]

Wayne,

Not having the record selector turned on and using manually placed controls
obscured the idea of a cursor to me. Makes perfect sense when you sit back
and think about it! Put my code in the AfterUpdate event and added a
requery and things are working great now!

Thanks for the help!!

Don

The continuous form is just a display method. Any values returned or edited
will be for the current record, just as they are in single form view. The
current record is the one with the cursor. If you have Record Selectors
turned on in the form's properties, the current record will be the one with
the left pointing triangle in the bar on the left side of the form. This
will change to a pencil icon if the record is "dirty" (i.e. in the process
of being edited).

To reference the controls for the other details, you would either have to
change the current record to the other record or you would have to work with
the form's Recordset, RecordsetClone, or underlying RecordSource.

To keep one and only one checkbox checked, you could do something like this
in the checkbox's AfterUpdate event:

'See if there is already a Primary selected and if the current change was to
True or False
If DCount("*", "FormRecordSourceName", "[PrimaryField] = True") = 0 And Not
Me.chkPrimary Then
strMsg = "You must have at least one address selected as Primary."
strMsg = strMsg & vbCrLf & "Please select another Primary to unselect
this one."
Msgbox strMsg
Me.chkPrimary.Undo
End If

If DCount("*", "FormRecordSourceName", "[PrimaryField] = True") > 0 and
Me.chkPrimary Then
strMsg = "There is already a Primary address. Change it to this one?"
If Msgbox(strMsg, vbYesNo + vbQuestion) = vbYes Then
strSQL = "UPDATE FormRecordSourceName SET [PrimaryField] = False
WHERE [IDField] = " & Me.txtIdField
CurrentDb.Execute strSQL, dbFailOnError
Me.chkPrimary = True
Else
Me.chkPrimary.Undo
End If
End If

This checks the underlying RecordSource to see if there are any addresses
marked as Primary. Since the current change (you just checked or unchecked
the check box) is not yet written to the table, this change won't be seen by
DCount(), so you need to verify what the current change was also (i.e.
whether the user just checked or unchecked the box). The syntax in the SQL
statement assumes that the unique ID field for the record is a number data
type.

--
Wayne Morgan
MS Access MVP

On a form there is a sub form with e-mail addresses. The default view
for the sub form is continuous form. The controls for the detail on
the sub form are a text box for the e-mail address, a check box to
indicate if that address is the primary address for the individual, and
then a tex box for comments. In the case where more than one detail is
displayed, is there a way to tell If there are more than one details
(rows), is there anyway of telling which detail (row) being modified?

For example, if the sub form is showing:

(e-mail address removed) ( ) work
address
(e-mail address removed) (check) home adress
(e-mail address removed) ( ) text only
e-mail

and the user clicked the check box for (e-mail address removed), if I
reference the control txtEmailAddress, which address will it return?
And why? Is there a way to reference the controls for the other
details?

The purpose of all this is to implement code to check how many primary
addresses there are for an employee when an addition or change is made.
If there are more than one, the user is forced to pick. If the change
would result in no primary, the user is prompted to pick another.
The algorithm for a change to the primary check box would be something
like this:
if cleared
check to see if there is an e-mail marked primary
if another primary
don't do anything else
else
warn user and leave checked

if set
check to see if there is another primary
if not
carry on
else
warn user there is another primary
make user chose one or the other as primary

I tried implementing this in the before change event of the check box,
but ran into a problem which I think is caused by changing one of the
other details. It seems like it that change caused the BeforeChange
event to be fired again. (Does a change to the underlying data cause
the change events to fire?)

Any suggestions on this?

Thanks!

Don

 

[Don]

Stefan,

Between your comments and Wayne's regarding the AfterUpdate event, I think I
finally have what I am looking for! I need to review it for a couple of
boundary condition cases (e.g. first e-mail added to an employee, deletions,
etc).

Thanks for the help!!

Don

hi Don,

The purpose of all this is to implement code to check how many primary
addresses there are for an employee when an addition or change is made.
If there are more than one, the user is forced to pick. If the change
would result in no primary, the user is prompted to pick another.
Any suggestions on this?

I'm using this simple loop to implement a radio button group behavior in
the after update event of the checkbox control (works only in datasheet
view):

With Me.RecordsetClone
.MoveFirst
Do While Not .EOF
.Edit
![CheckField] = False
.Update
.MoveNext
Loop
End With


mfG
--> stefan <--