Flagging Form Based on Prior Records

[Sonny Jim]

I want to flag a form IF a name field AND an address field (from two separate tables) both contain the same values that I currently am inputting on a previous record.

Specifically, if a customer "X" files another claim for the same loss location, then I want to flag the form.

What is the best way to get this kind of feedback on a form with the least amount of drag imposed on the inputting process?

 

[pbaldy]

What do you want to happen, and when? You can use a DCount() to check the values input against the table.

http://www.mvps.org/access/general/gen0018.htm

 

[spikepl]

Not clear to me what you want. If a prior record (name + address) exists, then you can wail, or the machine can make a sound, or the new rcord can get a tag saying "another record exists" or the thing can reject the input or it can perform a song and a dance.

SO which is it?

 

[Sonny Jim]

I would like to have a pop-up warn that, "Insured has made prior claims at this Loss Location" when a user types in an Insured's name that has had a prior claim made to the Loss Location being entered under this Insured's name.

 

[pbaldy]

Again, you can use DCount() to test, and pop up your message box if the count is greater than 0. You can use the before update event of the control or form.

 

[Sonny Jim]

Thanks, pbaldy, I will give DCount() a try, that sounds like it should do the trick!

 

[pbaldy]

No problem. The link should help with syntax if you need it. Post back if you get stuck.

 

[Sonny Jim]

I expect the most rudimentary thing I could do using the DCount function would be to use a text box to count of the amount of times that the last name & loss address on the current record have shown up on previous claim records.
*One thing to note is that, unfortunately, users have been able to type in usernames more than once without a unique identifier. For example, John Doe (primary key=1), John Doe (Primary key=2)
Here is the syntax that I have tried, in vain, to accomplish this:
Text box on my form with Control Source =DCount("strInsLastName","QueryforClaimInputForm"," strLossAddr =" & [strLossAddr])

• Why doesn’t this work?
• What should the correct syntax be?

 

[pbaldy]

If the field is text try:

=DCount("strInsLastName","QueryforClaimInputForm", " strLossAddr ='" & [strLossAddr] & "'")

 

[Sonny Jim]

This only counts up the number of records containing the current record's loss address.

 

[Sonny Jim]

I have gotten the following to work in my text box but I get an occasional "#Error" when an apostrophe is used in either field. Here is my code:

[FONT=&quot]=DCount("strInsLastName","QueryforClaimInputForm", " strLossAddr & strInsLastName ='" & [strLossAddr] & [strInsLastName] & "'")

[/FONT][FONT=&quot] [/FONT]What is the best way to handle this?[FONT=&quot]
[/FONT]

 

[Sonny Jim]

I found a solution by substituting the apostrophe with double quotes, so the code that works looks like this:

=DCount("strInsLastName","QueryforClaimInputForm", " strLossAddr & strInsLastName =""" & [strLossAddr] & [strInsLastName] & """")

Thanks again, pbaldy, for your generous and untiring help and guidance!!!

 

[pbaldy]

Happy to help!