How to code an "And....If..." expression?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CW - 10 Jul 2007 18:44 GMT

I need a message box to appear under certain conditions when a record is
opened on a form.
I can get it to work with just one condition, as follows - if the "Paid"
control is empty, then the message pops up nicely, using this:

Private Sub Form_Current()
If Nz(Me.Paid, "") = "" Then
MsgBox "Not paid yet!"
End If

End Sub

However I need to put another filter in there, as this message is only
required to pop up when the value in the ClientType control is Private, and
not if it is Trade or Commercial.

I tried putting in another line similar to the above and ending it with And,
but VB didn't like that.

Can somebody tell me exactly what is needed, please!

Many thanks
CW

Reply to this Message

Klatuu - 10 Jul 2007 18:50 GMT

Private Sub Form_Current()
If Me.ClientType = "Private" And Len(Me.Paid & vbNullString) = 0 Then
MsgBox "Not paid yet!"
End If

Notice the change in the test on Me.Paid. This is more efficient.

Signature

Dave Hargis, Microsoft Access MVP

> I need a message box to appear under certain conditions when a record is
> opened on a form.
[quoted text clipped - 19 lines]
> Many thanks
> CW

Reply to this Message

CW - 10 Jul 2007 23:28 GMT

Dave -
Many thanks, but God this is so irritating - why do things like this in
Access have to be so picky!! It doesn't work!
The code is accepted in VB without any error, and when I open a record with
those attributes there is no error, which is good. Not so good, however, is
the fact that nothing at all happens. Grrrrrrrrrr!
I was away from my system when I posted earlier and my example data wasn't
quite accurate - the actual controls and values are as follows:
(i) if the Status control contains Booked
(ii) if the InsuranceValue control control contains £0.00 (that's the
default value of 0.00 with the poiunds sterling symbol in front of it).
I really appreciate your help as this is quite an important prompt that we
need to have in place, as our users frequently overlook arranging insurance.
Thanks a lot!
CW

> Private Sub Form_Current()
> If Me.ClientType = "Private" And Len(Me.Paid & vbNullString) = 0 Then
[quoted text clipped - 26 lines]
> > Many thanks
> > CW

Reply to this Message

Klatuu - 11 Jul 2007 14:48 GMT

I understand your frustration, but not your response.
What is not working?

Signature

Dave Hargis, Microsoft Access MVP

> Dave -
> Many thanks, but God this is so irritating - why do things like this in
[quoted text clipped - 42 lines]
> > > Many thanks
> > > CW

Reply to this Message

CW - 12 Jul 2007 23:16 GMT

Dave -
Sorry I didn't make it clear...
If I open one of my job forms for a record where the two criteria are met,
i.e.
(a) the Status control shows Booked
(b) the Insurance Value control shows £0.00
the Message Box should appear.
It doesn't. Nothing at all happens. No error, nada.
These two controls are bound to the underlying table and I have checked
(many times!) that those values are indeed held in the table.
I'll be most grateful if you can figure this out!!
Thanks again
CW

> I understand your frustration, but not your response.
> What is not working?
[quoted text clipped - 45 lines]
> > > > Many thanks
> > > > CW

Reply to this Message

Klatuu - 13 Jul 2007 14:38 GMT

Do you know how to set a breakpoint and step through the code?
I would put a breakpoint on the first line of code in the Current event and
step through it, checking the value of the controls as I go.
Let me know what you see.

Signature

Dave Hargis, Microsoft Access MVP

> Dave -
> Sorry I didn't make it clear...
[quoted text clipped - 59 lines]
> > > > > Many thanks
> > > > > CW

Reply to this Message

CW - 13 Jul 2007 19:50 GMT

Dave -
Got it!
I am really most grateful to you for sticking with my query.
What I found (eventually!) was a problem with the "Booked" value. This is
populated on the form via a combo which looks up the four or five status
levels in the Status table. This has only two columns: ID, and Status. The
control was showing Booked correctly when it was selected by a user. The
value Booked was storing in the Inquiries table, fine. However - the strange
thing is that the On Current code would only work when I used the ID for
Booked (which is 2) rather than the status description itself, Booked.
I guess I have got something crossed over somewhere in setting up that combo
and relating it to the lookup table and the table to store in, and the icing
on the cake would be if you can suggest where this has gone wrong - but don't
worry too much as at least I have got it working now, by using 2 instead of
Booked. It's a workaround, but hey, nobody else knows and I'm just so happy
that it works at last!!
Again, very many thanks
CW

> Do you know how to set a breakpoint and step through the code?
> I would put a breakpoint on the first line of code in the Current event and
[quoted text clipped - 64 lines]
> > > > > > Many thanks
> > > > > > CW

Reply to this Message

Klatuu - 13 Jul 2007 21:00 GMT

Great, so far
Now, the cross over thing. Based on your description, my first guess is
that you are using a combo box with at least two columns. Some place in your
code, you are assigning the value from the wrong column.
Let me know how that turns out

Signature

Dave Hargis, Microsoft Access MVP

> Dave -
> Got it!
[quoted text clipped - 83 lines]
> > > > > > > Many thanks
> > > > > > > CW

Reply to this Message