By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,147 Members | 744 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,147 IT Pros & Developers. It's quick & easy.

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

P: 10
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
Feb 16 '12 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDOB_AfterUpdate()
  2. Dim dteDate As Date
  3. Dim txt As TextBox
  4.  
  5. Set txt = Me![txtDOB]
  6. If IsNull(txt) Or Not IsDate(txt) Then Exit Sub
  7.  
  8. dteDate = txt
  9.  
  10. 'If we get here, we have a Valid Date, but does a Record exist with this Date
  11. If DCount("*", "tblEmployees", "[DOB] = #" & txt & "#") Then
  12.   MsgBox "A Record already exists with a Date of [" & txt & "]!", _
  13.           vbExclamation, "Date Duplication"
  14.     DoCmd.RunCommand acCmdUndo          'UNDO any changes made?
  15. Else       'Do nothing if a Record does not exist for this Date
  16.   Exit Sub
  17. End If
  18.  
  19. 'Search for the Date entered into txtDOB in ALL Fields
  20. DoCmd.FindRecord dteDate, acEntire, False, acSearchAll, False, acAll
  21. End Sub
P.S. - I realize that DOB (Date of Birth) is probably a bad example, but it is for Demo purposes only.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 16 '12 #2

ADezii
Expert 5K+
P: 8,638
This is a little trickier than you think, and would require some Logic in the AfterUpdate() Event of the DOB Text Box similar to:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDOB_AfterUpdate()
  2. Dim dteDate As Date
  3. Dim txt As TextBox
  4.  
  5. Set txt = Me![txtDOB]
  6. If IsNull(txt) Or Not IsDate(txt) Then Exit Sub
  7.  
  8. dteDate = txt
  9.  
  10. 'If we get here, we have a Valid Date, but does a Record exist with this Date
  11. If DCount("*", "tblEmployees", "[DOB] = #" & txt & "#") Then
  12.   MsgBox "A Record already exists with a Date of [" & txt & "]!", _
  13.           vbExclamation, "Date Duplication"
  14.     DoCmd.RunCommand acCmdUndo          'UNDO any changes made?
  15. Else       'Do nothing if a Record does not exist for this Date
  16.   Exit Sub
  17. End If
  18.  
  19. 'Search for the Date entered into txtDOB in ALL Fields
  20. DoCmd.FindRecord dteDate, acEntire, False, acSearchAll, False, acAll
  21. End Sub
P.S. - I realize that DOB (Date of Birth) is probably a bad example, but it is for Demo purposes only.
Feb 17 '12 #3

P: 10
Perfect! Thanks ADezii! After a few modifications, I got the code to work. :)
Feb 17 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.