form from query, how to throw an error?

[helraizer1]

Hi folks,

I am making a solution to a problem using access, I have a query that is

SELECT *
FROM tblMembers
WHERE (((tblMembers.CustomerID)=Forms!frmLogin![CID]));

I have then got a form (frmMembers) that loads the data from it.

frmLogin is just a text box (named CID) with a button that takes the user to the frmMembers with that. The data that is loaded is based on the input from frmLogin, if the user ID is either blank or does not exist it loads blank.

How would I do it that if the ID is either NULL or doesn't match any in tblMembers it returns an error message?

Hope that helps.

Sam

[Jim Doherty]

Hi folks,

I am making a solution to a problem using access, I have a query that is

SELECT *
FROM tblMembers
WHERE (((tblMembers.CustomerID)=Forms!frmLogin![CID]));

I have then got a form (frmMembers) that loads the data from it.

frmLogin is just a text box (named CID) with a button that takes the user to the frmMembers with that. The data that is loaded is based on the input from frmLogin, if the user ID is either blank or does not exist it loads blank.

How would I do it that if the ID is either NULL or doesn't match any in tblMembers it returns an error message?

Hope that helps.

Sam

 Dim Myval 
 
'test for a null and convert to zero if encountered and if zero throw a message 
IF NZ(Me!CID,0)=0 THEN
DoCmd.Beep
Msgbox "You must supply a value to login!",vbexclamation,"System Message"
Exit sub
'otherwise we have a value entered so test it by counting on members for the number entered
Else
MyVal=DCount("[CustomerID]","tblMembers",[CustomerID="& Me!CID)
End If
 

'you got here so we have a number value we can test on. If its zero then no member exists

 IF MyVal =0 then 
DoCmd.beep
Msgbox "No Member exists having the number you supplied!",vbexclamation,"System Message"
Exit Sub
Else
'we have a number greater than zero so therefore a member (assuming no duplicates of course)
Msgbox "Success a member does exist with that number so I am going to let you in! Have a nice day!
....your code then here to open the form
End if
 

I'm sure you can see the general principle of this if assigned to your command button.

Regards

Jim :)