User can't move on if a control hasn't changed

[Tony Williams]

I have a subform that is formatted as Continuous Forms. The last control on
the subform is a tick box (txtpaidbymember) which must be ticked before the
user moves to the next record. I need some code to a bring up the message
that they must tick the box and not allow them to move on to the next record
until they have done so. Can anyone help?
Thanks
Tony

 

[Keith Wilby]

I have a subform that is formatted as Continuous Forms. The last control on
the subform is a tick box (txtpaidbymember) which must be ticked before
the
user moves to the next record. I need some code to a bring up the message
that they must tick the box and not allow them to move on to the next
record
until they have done so. Can anyone help?
Thanks
Tony

Something like this in the sub-form's before update event should do the
trick:

If Me.txtpaidbymember = 0 Then
MsgBox "Your custom message"
Cancel = True
End If

I have to ask, why must it be ticked? You could programmatically do this
but what is its purpose?

Incidentally, the prefix "txt" implies a text box control, suggest you
re-name the tick box along the lines of "chkPaidByMember" for clarity in
code (chk = checkbox).

Keith.
www.keithwilby.co.uk

 

[Tony Williams]

Thanks Keith. The reason I want it to be ticked is the records are selected
from a table with all members and I have some queries that select only those
that have paid their annual fee. So if they input a fee and don't tick the
box my query wouldn't pick them up.

Take your point on the prefix!

Thanks again.
Tony

 

[Keith Wilby]

So if they input a fee and don't tick the
box my query wouldn't pick them up.

So by the sound of it, all you need to do is modify your query to test the
"fee" field for being not null.

Keith.
www.keithwilby.com

 

[Tony Williams]

Sorry Keith, I'm not explaining myself. The subform is used to input levies
to a Trade Association and is only completed if the member pays the Levy
themselves and not the business they work for. So because of the way I've
constructed the reports and queries I need to know that the member has paid
the levy themselves (the queries check if the value is not nul and includes
it) and I thought by forcing the user to tick the box I will make sure that
the record is found by the query which only then picks those members who paid
the levy themselves.

I'm not sure that is any clearer. Is it?

Thanks
Tony

 

[Keith Wilby]

Sorry Keith, I'm not explaining myself. The subform is used to input
levies
to a Trade Association and is only completed if the member pays the Levy
themselves and not the business they work for. So because of the way I've
constructed the reports and queries I need to know that the member has
paid
the levy themselves (the queries check if the value is not nul and
includes
it) and I thought by forcing the user to tick the box I will make sure
that
the record is found by the query which only then picks those members who
paid
the levy themselves.

I'm not sure that is any clearer. Is it?

Ah, so the tick indicates that the member has paid the levy rather than
their employer? In that case IMO there is no duplication of effort/data so
the tick box is perfectly valid.

Regards,
Keith.

 

[Tony Williams]

Thanks Keith. I've used the messgae box code you gave me although you did
plant a seed in my mind that if the user was inserting a record then with a
little bit of VBA code I assume I could change the status of the tick box
automatically? For example after they'd input the amount of the levy that
could trigger of the change in the value of the tick box. Hm? I'll have a
look at that. My 64 year old brain doesn't work as fast as it used to!
Cheers
Tony

 

[Keith Wilby]

Thanks Keith. I've used the messgae box code you gave me although you did
plant a seed in my mind that if the user was inserting a record then with
a
little bit of VBA code I assume I could change the status of the tick box
automatically? For example after they'd input the amount of the levy that
could trigger of the change in the value of the tick box. Hm? I'll have a
look at that. My 64 year old brain doesn't work as fast as it used to!
Cheers
Tony

Well Tony that was my original point. If you can hard code the tick box to
be updated based on another field then you don't need the tick box! I
understood that the tick box represented a payment from two different
sources but from your latest posting, that doesn't seem to be the case.

Keith.
www.keithwilby.co.uk

 

[Tony Williams]

Sorry keith this wasn't supposed to be that complicated. The payment can come
from two sources the member or the business, the tick box indicates that it
has come from the member.
maybe I should leave well alone at least what you've helped me with so far
works
Thanks
Tony

 

[Keith Wilby]

Sorry keith this wasn't supposed to be that complicated. The payment can
come
from two sources the member or the business, the tick box indicates that
it
has come from the member.

In that case leave it be If you hard code the tick box then it will
become "ticked" regardless of the payment source ... AIUI ! Glad it's
working and glad I could help.

As an aside, another approach would be to have a combo (drop-down) box to
populate a "payee" field with either "Member" or "Employer" and use that in
your queries. Apples and oranges I suppose ...

Regards,
Keith.

 

[Tony Williams]

Thanks again Keith.
Tony

In that case leave it be If you hard code the tick box then it will
become "ticked" regardless of the payment source ... AIUI ! Glad it's
working and glad I could help.

As an aside, another approach would be to have a combo (drop-down) box to
populate a "payee" field with either "Member" or "Employer" and use that in
your queries. Apples and oranges I suppose ...

Regards,
Keith.