How do I AutoFill fields if a Primary Key value has already been taken?

[jcrist30]

For the database I'm building, users will have to fill data into a form that uses a date as the Primary Key for the related table. The scenario for which I need the form to auto-fill is as follows:

If the user wants to enter data for 1/31/2012, but he or she forgot that data for that day had already been entered, is there a way that once they begin filling out the form and type "1/31/2012" in the Date (i.e. Primary Key) field, the rest of the fields will auto-populate with the previously entered data or the form will actually go to that exact record? Either would work, but I suppose it would need to actually go to the record as a duplicate Primary Key would be created?

Thanks!

JC

[NeoPa]

I think you'd need to code that yourself carefully, as there are a few gotchas that make it somewhat less straightforward than your brief explanation allows for. You'd need the code in the AfterUpdate event procedure of the PK control, but you'd need to handle (at least) saving any other data already entered into the form prior to the PK date being entered.

[ADezii]

This is a little trickier than you think, and would require some Logic in the AfterUpdate() Event of the DOB Text Box similar to:

Private Sub txtDOB_AfterUpdate()
Dim dteDate As Date
Dim txt As TextBox
 
Set txt = Me![txtDOB]
If IsNull(txt) Or Not IsDate(txt) Then Exit Sub
 
dteDate = txt
 
'If we get here, we have a Valid Date, but does a Record exist with this Date
If DCount("*", "tblEmployees", "[DOB] = #" & txt & "#") Then
  MsgBox "A Record already exists with a Date of [" & txt & "]!", _
          vbExclamation, "Date Duplication"
    DoCmd.RunCommand acCmdUndo          'UNDO any changes made?
Else       'Do nothing if a Record does not exist for this Date
  Exit Sub
End If
 
'Search for the Date entered into txtDOB in ALL Fields
DoCmd.FindRecord dteDate, acEntire, False, acSearchAll, False, acAll
End Sub

P.S. - I realize that DOB (Date of Birth) is probably a bad example, but it is for Demo purposes only.

[jcrist30]

Perfect! Thanks ADezii! After a few modifications, I got the code to work. :)