VB/Access Form question

[spiceAlex]

I'm using the where clause in the openForm method to find the phone number associated with my form. It all works fine. If the phone number is not found, a blank form comes up which is fine, but I'd like to also to have a msg box appear saying, "No contact found." I don't know how to search the fields for a null value. Can I use FindRecord in some sort of condiditonal statement? If so, how?

Here's what I've got. 'phone' is the phone number of the caller that is coming into our phone system so will never be null. Thanks in advance.

phone = WVrCall.RemoteNumber
caller = "[Contact Main Phone] = '" & phone & "'"

oAccess.DoCmd.OpenForm MM_DB_TBLE,acNormal, , caller

 

[dinger2121]

you can use -
MsgBox "your message text

 

[ProgramError]

I don't know how to search the fields for a null value

use Is Null in the criteria of a query. Queries can also find things too.

Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[YoungManRiver]

spiceAlex,

I think what you need is:

if WVrCall.RemoteNumber > 0 then
   phone = WVrCall.RemoteNumber
   caller = "[Contact Main Phone] = '" & phone & "'"
   oAccess.DoCmd.OpenForm MM_DB_TBLE,acNormal, , caller
else
   MsgBox "Caller not found!"
   Exit Sub   ' (or Function if processing follows)
end if

See if that works!

YMR

 

[spiceAlex]

Thanks, youngManRiver - just back from lunch and it looks like it will work. I'm trying now.

Alexandra

 

[spiceAlex]

Hi ProgramError - the field will never be null because WVrCall.RemoteNumber is the var that holds the phone number coming in. This starts the whole process.
Thanks, though.

 

[YoungManRiver]

S,

Then you need to build a test against the phone number to insure it is a valid number ie template right NNN-NNN-NNNN and so-on.

Are you capturing this from CID or is test against [Contact Main Phone]? If test against contact lookup then code would be:

if [Contact Main Phone] > 0 then
   phone = WVrCall.RemoteNumber
   caller = "[Contact Main Phone] = '" & phone & "'"
   oAccess.DoCmd.OpenForm MM_DB_TBLE,acNormal, , caller
else
   MsgBox "Caller not found!"
   Exit Sub   ' (or Function if processing follows)
end if

Notice I always test even text against "> 0" as this is a better test than {Not Null} or {<> ""} in VBA coding.

YMR

 

[spiceAlex]

Let me back up.
We have new phone software. Call comes in, searches the Access Forms for a match of that number, then pops up that form. The call taht comes in is WVrCall.RemoteNumber.

Right now if it finds the match it pops up the right form. If it doesn't it pops up a blank form. That's fine, I just want the msg box if there's no match.

It'd be dandy if the openForm method had and 'else' clause. I just don't know VB well enough.