Hi Tim,

I don't want to use a combo box because there could be thousands to choose from. I could, and probably will, give them a way to look up patients by name. The thinking here is that they receive forms with the foreign key listed on the form. It will generally be faster and more convenient for a data entry person to type in a 5-digit id number than to look someone up by name. I certainly don't want a combo box with that many entries. I am looking for someplace to put that code like the LostFocus() in FoxPro and I haven't found it yet. Do you happen to know what that would be? I don't want to wait until they save because I want to give them immediate feedback if the number is not correct. I might even pop them into a screen or tab that would let them look up by name if the number is not correct.

The other thing I was wondering about was whether or not the validation should call a class in the business object for the patient rather than passing the SQL command itself. I noticed that the Patient object is not visible from there. I didn't know if it would be better to create a reference to the Patient (foreign table) business object and call one of it's methods from this rule.

Thanks for all your help.

>Hi Linda,
>
>>I am validating a foreign key entered in a text box. I already have a rule created by the business layer generator for a required entry. Now I want to make sure that the number matches a row in the foreign table. I would like some advice on best practice here. Currently, I have modified that validation rule to add code to check for a valid entry.
>>
>>

>>                int PatientCount = ExecuteScalar("SELECT COUNT(*) FROM Patient WHERE ClientPatientId = '" + ClientPatientId + "'");
>>                if (PatientCount = 0)
>>                {
>>                    Msg = "Unable to locate this patient.";
>>                    AddErrorProviderBrokenRule("ClientPatientId", Msg);
>>                }
>>

>>
>>If it is valid, I want to look up and display some related fields. I was thinking that I could use the event,
>>txtClientPatientId_TextChanged(object sender, EventArgs e), and have it call the foreign business object. The only problem is that it fires after each letter entered, doesn't it? Is this a reasonable approach to doing this? Is there a better solution?
>>
>>Thanks.
>
>Best practice I suspect would not be letting a user enter a foreign key directly like that. I don't know your exact situration so not to judge this, but one way would be to give them a combo box and fill it with a meaningful column from the foreign table but when they pick it, the combo will actually put the foreign key into the column for you. Check out the jump start in the deve guide where Shippers and Employee combo boxes are placed on the form. They display the shipper name or employee name, but the shipper_ID and Employee_ID are placed in the table column.
>
>If you want to allow the user to actually enter a foreign key like you suggest, then you would have to validate it. TextChanged may not be the best spot to put that for the reason you stated. You could put a button or you could check it after they leave the text box or tab.
>
>I hope that helps
>Tim