Duplicate prevention at point of data entry

[Guest]

I have a database with a field named "Job #." Job # must be unique, so I
have the index set as Yes (no duplicates).

The problem I am running into is that data is entered through a form, and if
someone completes data entry and there is already a job with the Job #, they
receive an error message about a duplicate value. If they go back and change
the Job # to something we know does not exist, they still get the error.

I understand that we can do a Before Update on the field and provide them
with a user-friendly message.

My question is if there is an easy way to know what the next available job
number is, without forcing the user to open the table every time.

Another concern is that this database is used by multiple users throughout
the day and as one user commits data, it is not appearing in the open table
on another users computer. The users have expressed a desire to not have to
close the table all the time. They want to minimize it during the day, and
restore as needed. Is there an easy way to refresh the table view, or query
view without forcing the end-user to re-run the query?

Thanks in advance.

 

[David F Cox]

Perhaps the wrong perspective?

It might be better if there was a "New job" button that allocated the next
job number in sequence based on something like (SELECT MAX(Jobnum) FROM
Jobs ) +1

 

[Guest]

Hi Ken

Check out this site

http://www.databasedev.co.uk/duplicates.html

 

[Guest]

Wayne,

Thanks for that link.

Not being a programmer, I was able to get most of the functionality;
however, when it gets to the step of taking you to the existing record, I was
getting a type mismatch error. When I debug, I look at the rsc.FindFirst
stLinkCriteria line and when I hover over the stLinkCriteria it displays
"[JobNumber]='60460'"

For now I commented out that step and deleted some of the vb stuff in the
MsgBox section.

 

[Guest]

David,

Thank you for your reply. The only problem with this solution is that the
Job Number is actualy a text field because their jobs are in the format
12345-67, where 12345 are the job number and 67 are modifications.

Perhaps I could redesign it so that 12345 are numeric and use your
incrementing idea; and then 67 would be in a separate field. I could then
use multi-field indexing to prevent duplicates.

 

[John Vinson]

Not being a programmer, I was able to get most of the functionality;
however, when it gets to the step of taking you to the existing record, I was
getting a type mismatch error. When I debug, I look at the rsc.FindFirst
stLinkCriteria line and when I hover over the stLinkCriteria it displays
"[JobNumber]='60460'"

That code assumes that JobNumber is a Text type field. If it's a
Number datatype field, just remove the two ' marks. Text fields
require either " or ' as delimiters; Number fields should not have any
delimiter; date fields use # as a delimiter.

John W. Vinson[MVP]