Field Validation to only allow duplicates if a different field has been filled on the duplicate record (1 Viewer)

[zmizzi1]

I have a button that prints out names based on an employee ID field. but I need to be able to stop people from duplicating records if they haven't completely filled out the previous record with their employee ID

 

[The_Doc_Man]

A little clarification, please.

Let's say someone fills out a form intended to contain name "John S Smith" but only completes "John S" and forgets the "Smith." Are you talking about that kind of thing or something else? And it wouldn't hurt to know whether those parts are in three separate fields or one long field.

 

[zmizzi1]

A little clarification, please.

Let's say someone fills out a form intended to contain name "John S Smith" but only completes "John S" and forgets the "Smith." Are you talking about that kind of thing or something else? And it wouldn't hurt to know whether those parts are in three separate fields or one long field.

its intended for no names to be typed at all but rather a PIN. fields go as follows: ID, First Name, Last Name, Various other unrelated fields that also need to be filled before allowing duplication.

so you type in the PIN and your name appears following a button click verifying that you typed the correct pin, i need there to be a validation phase before this happens to ensure nobody is creating a new record before finishing the previous one they had created.

 

[jdraw]

In simple English, what do you foresee as the logic of such a verification?
Whatever code you use would likely be placed in the BeforeUpdate event of the control where the PIN is entered.

 

[zmizzi1]

In simple English, what do you foresee as the logic of such a verification?
Whatever code you use would likely be placed in the BeforeUpdate event of the control where the PIN is entered.

If the record with a duplicate ID is completely filled, then allow the button to work else go to the record that hasn't been filled and delete this one.

 

[Pat Hartman]

I would check the ID for duplicates in the ID control's AfterUpdate event. If the record already exists for that ID, ask the user if he wants to go to it or tell him he must change the ID to continue.

 

[The_Doc_Man]

Here is the issue: In order for Access to be able to know this, you will have two records, one of which exists already (in an incomplete state, but it has been stored.) The other hasn't been stored yet. The logic for detecting this has to have a "tell" somehow that lets it know that a pending record has not yet been completed but HAS been stored. It is not clear to me how you would do that unless someone tries to enter what WOULD be a duplicate ID if successfully entered. This would lead to a constrain violation that would trigger an error. If you don't handle the error correctly, you will crash the form, which is not too good a thing to have happen.

So my first question is, why don't you just prevent the first record (that is incomplete) from being stored in the first place. That way, the 2nd record NEVER becomes and issue. This is a case of (as the Old West cowboys would say) "heading it off at the pass." If you use the Form_BeforeUpdate to validate a record and it detects that the record is incomplete, you can cancel the update and thus never allow an incomplete record to be stored.

 

[zmizzi1]

Here is the issue: In order for Access to be able to know this, you will have two records, one of which exists already (in an incomplete state, but it has been stored.) The other hasn't been stored yet. The logic for detecting this has to have a "tell" somehow that lets it know that a pending record has not yet been completed but HAS been stored. It is not clear to me how you would do that unless someone tries to enter what WOULD be a duplicate ID if successfully entered. This would lead to a constrain violation that would trigger an error. If you don't handle the error correctly, you will crash the form, which is not too good a thing to have happen.

So my first question is, why don't you just prevent the first record (that is incomplete) from being stored in the first place. That way, the 2nd record NEVER becomes and issue. This is a case of (as the Old West cowboys would say) "heading it off at the pass." If you use the Form_BeforeUpdate to validate a record and it detects that the record is incomplete, you can cancel the update and thus never allow an incomplete record to be stored.

So the record has to be completed in stages which is why we can't just prevent incomplete records from being stored. could the problem be solved by making the validation occur before the temporary record is created through some kind of ID search form? the form generate a new record if the duplicate record is complete and redirects you to the incomplete record otherwise.

 

[jdraw]

As requested in #4, what exactly is the logic?
What are the steps involved?
What makes the record complete?

You can always use DCount("yourfield","yourTable","condition") >0 to check for duplicate.

To me, your requirement is not clear. Try again to describe what you want to accomplish. Use sample data if necessary.

 

[zmizzi1]

As requested in #4, what exactly is the logic?
What are the steps involved?
What makes the record complete?

You can always use DCount("yourfield","yourTable","condition") >0 to check for duplicate.

To me, your requirement is not clear. Try again to describe what you want to accomplish. Use sample data if necessary.

step 1 enter your employee ID

step 2 click the search button

If the record with a matching ID has no empty fields, then make a new record else go to the record that hasn't been filled.

DCount won't work because I'm not verifying that there are no duplicates, i'm verifying that the duplicates have been completed.

so that's really what i need help with. how can i look at a matching record and validate that it has no empty fields