Preventing duplicate dates for an ID

[SlinkRN]

Hi!
I have a form to enter certification dates for each employee. Each employeeID can have only one set of dates (CPRDate, NRPDate, ACLSDate). I set the EmpID field for "do not allow duplicates". That works fine, but the error message that comes up will probably frustrate my users because they won't understand what the problem is. I would like the form to automatically go to the record that has the orignal dates for that employee, so that the user can just change those dates rather than enter duplicate dates. In other words, if they choose an employee from the combo box that already has dates entered - then I'd like the form to go to that entry. Is that possible? Thanks! Slink

 

[Swamp Thing]

This calls for a "find" macro or Subroutine, to be run when the user selects the combobox value. You can let the combobox wizard set that up and then reverse engineer the resulting code.

Before drawing the combobox, enable the wizard (depress the Magic Wand button in the toolbox). The wizard will offer you the option of configuring the combo for finding a record that matches the selection.

Also use the button wizard to create a "New ID" button for when the user wants to add a new ID.

Edit:

In other words, if they choose an employee from the combo box that already has dates entered - then I'd like the form to go to that entry.

Since you have specified "no dups" for the ID field, it doesn't matter whether there are dates entered or not -- there is only one record that you can go to for a given ID. So IMHO, the search is not affected by whether any dates have been entered or not. The point is that if the ID exists, we want to go to it. If it doesn't, we want to add it as a new row.

If the ID doesn't already exist, the combobox won't include it. That will tell the user that he needs to click the "New ID" button. If he tries to type in the non-existent ID into the combo box, you can detect this in the macro and give the user a message telling him he needs to create a new ID.

 

[SlinkRN]

Thanks so much for your reply. I had tried that option of the wizard lookup, but I need the combobox to show names that go with the ID. The names are found in a query since only the ID is stored in the Certifications Table. I tried using the wizard to find the ID, but then the names don't show up and I don't want the user to have to look up the name manually.
Is there a way to use the combobox to retrieve the names from a query AND find the record that matches that name/ID?
PS: I don't want the user to add another ID in this form because any new employees should be entered in another form (frmFBCStaff) because more information would be necessary for a new employee and once entered there, they would show up in the combobox.

 

[Swamp Thing]

The wizard should have let you define a lookup for your combobox while setting up the "find-on-select" part. For this, you need to have based the form on a query that returns the ID and Name fields from the Names table, and also the date fields from the Dates table. The query should join these two tables on the ID field, but only display the ID field from the name table. The join should be like "show all records from the names table, and only those records from the dates table that match records from the names table".

If the above query has been set up correctly, then the combobox wizard will let you pick up names and display them instead of the IDs, but still do the search based on the corresponding IDs.

It sounds complicated here, but the wizard makes each step clear. Basically the combo will have two columns: names in the left column and IDs in the right. You specify that the second column will provide the actual data, and the first will be only for the user's benefit.

If you are going to use Access a lot, you may prefer to break out of the wizard framework. As an Excel user new to Access myself, I find the wizards are analogous to the Macro Recorder: you usually find yourself customizing whatever the wizard serves up.

Cheers
-S.T.

 

[SlinkRN]

Thank you so much Swamp Thing. After much manipulation, I finally got it to work the way I wanted it to. I never would have figured it out without your suggestions Thanks again! Slink