Repost about Event Code help

[JohnB]

Hi. I posted about this a few days ago in the Forms
section and received a reply that I queried. Since then
the Forms section has not been available. I thought Id
take another shot here, which is where I should have
posted in the first place. Sorry to be so impatient.

I want to be able to force the users to complete two
fields on a form if they complete one or two other fields.

Lets say the form contains txtStatus1 and txtStatus2 and
users may complete one or the other or both of these. If
they do they should also complete both the txtSubject1 and
txtSubject2 fields. If they forget, they must be stopped
from moving to the next record and a message must be
displayed reminding them to complete them.

What code could I use and where please?

Thanks, JohnB

 

[Dirk Goldgar]

Hi. I posted about this a few days ago in the Forms
section and received a reply that I queried. Since then
the Forms section has not been available.

Not available? How so? I see a reply from Klatuu in that thread today.

I thought Id
take another shot here, which is where I should have
posted in the first place. Sorry to be so impatient.

I want to be able to force the users to complete two
fields on a form if they complete one or two other fields.

Lets say the form contains txtStatus1 and txtStatus2 and
users may complete one or the other or both of these. If
they do they should also complete both the txtSubject1 and
txtSubject2 fields. If they forget, they must be stopped
from moving to the next record and a message must be
displayed reminding them to complete them.

What code could I use and where please?

I'd use the form's BeforeUpdate event for this. Code would look
something like this:

'----- start of example code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Me!txtStatus1 & vbNullString) > 0 _
Or Len(Me!txtStatus2 & vbNullString) > 0 _
Then

If Len(Me!txtSubject1 & vbNullString) = 0 _
Or Len(Me!txtSubject2 & vbNullString) = 0 _
Then
Cancel = True ' cancel the update
MsgBox "Please fill in Subject1 and Subject2."
End If

End If

End Sub
'----- end of example code -----

 

[JohnB]

Thanks for this Dirk.
I tried many times today to view the Forms section and
kept getting a Newsgroup Not Available Message. I tried it
before sending my repost and have just tried again now -
same message. Im in the UK. perhaps access to the site is
being restricted to certain users while they are doing the
upgrade. Thanks for the code - looks good but Ill have to
wait till tomorrow to try it. Will reply to you then.
Cheers, JohnB

 

[Marshall Barton]

Hi. I posted about this a few days ago in the Forms
section and received a reply that I queried. Since then
the Forms section has not been available. I thought Id
take another shot here, which is where I should have
posted in the first place. Sorry to be so impatient.

I want to be able to force the users to complete two
fields on a form if they complete one or two other fields.

Lets say the form contains txtStatus1 and txtStatus2 and
users may complete one or the other or both of these. If
they do they should also complete both the txtSubject1 and
txtSubject2 fields. If they forget, they must be stopped
from moving to the next record and a message must be
displayed reminding them to complete them.

What code could I use and where please?

Use the form's BeforeUpdate event. The code would look
something like this air code:

If Not IsNull(txtStatus1) Or Not IsNull(txtStatus2) Then
If IsNull(txtSubject1) Or IsNull(txtSubject2) Then
MsgBox "fill in the subject fields"
Cancel = True
End If
End If

 

[JohnB]

Thanks for this Marsh. I now have three different
suggestions as to how to do this. A good lesson for me, I
think. Cheers, JohnB

 

[JohnB]

Works perfectly, as does Dirks suggestion. Thanks again,
JohnB

 

[JohnB]

Thanks again Dirk - works fine, as does Marshes
suggestion. Incidentally, today I can see the first page
of the Forms section but none of the older pages, one of
which contains my earlier post and reply from Klatuu.
Cheers, JohnB