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

Auto populate form

P: 22
I have created a form that requests basic demographic information on a training participant as well as information on all the training the participant has taken part in. The demographic information is collected in one table and the training information is written to a separate table using a subform. The two tables have a one to many relationship. The SSN is the common identifier between the two.

I have already incorporated a search box on the form so the user may search to see if the SSN they are trying to enter already exists in the database. But in case they do not do that and just begin to enter the SSN, I would like the user to receive notification that the SSN they are trying to enter already exists and then the information for that SSN be automatically populated on the form.

Currently, the code I am using notifies the user the SSN they have entered already exists and it populates the SSN and the training information. However, it does not fill in the participants name and other demographic information. In fact, a duplicate record is created in the demographic table.

Here is the code I am using, which is an amalgamation of various suggestions I have found on the web. Any hints/guidance on what I am doing wrong would be greatly appreciated.

__________________________________________________ ________________
Expand|Select|Wrap|Line Numbers
  1. Private Sub SSN_BeforeUpdate(Cancel As Integer)
  2.  
  3.  
  4.   Dim db As DAO.database
  5.   Set db = CurrentDb()
  6.   Dim rst As DAO.Recordset
  7.  
  8.  
  9.   Set rst = db.openrecordset("Select * from [Ind Part] where SSN = '" & CStr(Me!SSN) & "'", dbopensnapshot)
  10.  
  11.   If rst.RecordCount = 0 Then
  12.   Me!SSN = rst!SSN
  13.   Me![First_Name] = rst!FName
  14.   Me![Last_Name] = rst!LName
  15.   Me![Gender_OptGrp] = rst!Sex
  16.   Me![Race_Combo] = rst!Race
  17.   Me![Ethnicity_OptGrp] = rst!Ethnicity
  18.  
  19.   If (Not IsNull(dlookup("SSN", "Ind Part", SSN = Me!SSN))) Then
  20.   MsgBox "SSN has already been entered."
  21.   End If
  22.  
  23.   End If
  24.  
  25. End Sub
  26.  
__________________________________________________ ________________
Thanks!

P.S. I am using Access 2000 on Windows 2000.
Aug 14 '07 #1
Share this Question
Share on Google+
18 Replies


JKing
Expert 100+
P: 1,206
Hi there, I believe the problem is that you're filling the bound controls with information which is creating a duplicate record rather than moving to the existing record.

Have a look at this tutorial that explains searching on a form.
Programmatically Searching for a Record

Let me know if this is what you're looking for!
Aug 14 '07 #2

Scott Price
Expert 100+
P: 1,384
Is the SSN your primary key? At the table level, are you setting it to make sure there are no duplicates entered?

Normally speaking you would use code to go to a specific record (or filter your form to a specific record) rather than populate the controls with specific data.

The database is doing exactly what you are telling it to do with your code: i.e. it's creating another record because you are telling it to do so! However, that's not quite what you want.

Have a look at this tutorial, I think it's closer to what you want to do: Example Filtering on a Form

If you continue having problems, post back here and we'll do our best to help you out! (in the future please wrap your code in the code tags... Select/highlight the code, go to the top of your message window and click the # button. Edit the first [code] tag to this for vba code: [code=vb] this just makes it easier to read :-)

Regards,
Scott
Aug 14 '07 #3

Scott Price
Expert 100+
P: 1,384
Go with Jared's solution first! He's smarter than I am :-) (and faster too, as he beat me to the reply... must have been posting at the same time again :-)

Regards,
Scott
Aug 14 '07 #4

JKing
Expert 100+
P: 1,206
Go with Jared's solution first! He's smarter than I am :-) (and faster too, as he beat me to the reply... must have been posting at the same time again :-)

Regards,
Scott
Thank you! Happens all the time around here though.

The article posted by Scott is also a good one and you should have a look there as well.
Aug 14 '07 #5

P: 22
Thanks to both of you! For a newbie like me, the article referred to by Scott was more helpful. However, I have printed the other one as well since I am sure I will be able to use it later.
Aug 14 '07 #6

P: 22
Sorry guys, I spoke to soon. I just checked my tables and the table containing the demographic information is now showing duplicates. How do I prevent this?
Aug 14 '07 #7

JKing
Expert 100+
P: 1,206
I'm assuming you've changed your code around. Could you post the code that you are now using.

Also could you post the metadata for the tables you are using in this format:
[code]
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Aug 14 '07 #8

P: 22
Table Name=tblParticipant

SSN; String
FName; String,
LName; String
Race; String
Sex; String
Ethnicity; String


Table Name=tblTrain

SSN; String
Start; Date/Time
End; Date/Time
Cluster; String
FundYr; String

Expand|Select|Wrap|Line Numbers
  1. Private Sub SSN_AfterUpdate()
  2.  
  3.   Dim strCriteria As String
  4.   Dim rst As DAO.Recordset
  5.  
  6.  
  7.   Set rst = Me.RecordsetClone
  8.  
  9.   strCriteria = "[SSN] = '" & Me![SSN] & "'"
  10.  
  11.  
  12.   rst.FindFirst strCriteria
  13.  
  14.   Me!SSN = rst!SSN
  15.   Me![F_Name] = rst!FName
  16.   Me![L_Name] = rst!LName
  17.   Me![Gender_OptGrp] = rst!Sex
  18.   Me![Race_Combo] = rst!Race
  19.   Me![Ethnicity_OptGrp] = rst!Ethnicity
  20.  
  21.   DoCmd.OpenForm "DupEntry"
  22.  
  23.  
  24. End Sub
Aug 14 '07 #9

JKing
Expert 100+
P: 1,206
Give this a try and let me know if it's what you're looking for.

Expand|Select|Wrap|Line Numbers
  1. Private Sub SSN_AfterUpdate()
  2.    Dim rst As DAO.Recordset
  3.  
  4.     Set rst = Me.RecordsetClone
  5.  
  6.     rst.FindFirst "[SSN] = '" & Me![SSN] & "'"
  7.  
  8.     If rst.NoMatch = False Then
  9.         Me.Undo
  10.         Me.Bookmark = rst.Bookmark
  11.         MsgBox "SSN already exists!"
  12.     End If
  13. End Sub
  14.  
It's fairly simple. Checks the recordset to see if the SSN exists. If it does undo any changes to the current record and move to the existing SSN.
Aug 14 '07 #10

P: 22
Thanks!!!! It works!
Aug 14 '07 #11

JKing
Expert 100+
P: 1,206
You're welcome, glad to help.

Jared
Aug 14 '07 #12

P: 21
Hi there,

I have a similar problem and I tried using the code you recommended, but it didn't work. I have two tables that are related by member record number. One contains visit information and the other contains patient information (name, doctor, etc). I want Access to auto populate the patient data upon entering the member record number. What did I do wrong?

Here's the code.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Medical_Record_Number_AfterUpdate()
  2.    Dim rst As DAO.Recordset
  3.  
  4.     Set rst = Me.RecordsetClone
  5.  
  6.     rst.FindFirst "[Medical Record Number] = '" & [Medical Record Number] & "'"
  7.  
  8.     If rst.NoMatch = False Then
  9.         Me.Undo
  10.         Me.Bookmark = rst.Bookmark
  11.         MsgBox "Medical Record Number already exists!"
  12.     End If
  13. End Sub
  14.  
Oct 29 '07 #13

P: 21
You're welcome, glad to help.

Jared
Hi Jared,

I have been looking for a solution to a very similar problem that Kickergirl had. I have three tables linked by medical record number. The first is used on the parent form. It contains the medical record number, first name, middle initial, last name, physician name, and a couple of other fields. The second table contains appoint information. The third table contains delivery information. The two subforms populate when I enter the medical record number on the parent form, but the parent form does not populate with the patient information. I tried using DLookup, but when I do that, I run into problems. The form only populates once I click on the subform, but then it tells me that the record cannot be saved because it has a duplicate primary key. Your advice to kickergirl made a lot of sense. I tried the code you suggested to her, but it just won't work. Any suggestions?

Here's the code I used:
Expand|Select|Wrap|Line Numbers
  1. Private Sub MRN_AfterUpdate()
  2.     FirstName = DLookup("FirstName", "SweetSuccessData", "MRN = '" & Me.MRN & "'")
  3.     MI = DLookup("MI", "SweetSuccessData", "MRN = '" & Me.MRN & "'")
  4.     LastName = DLookup("LastName", "SweetSuccessData", "MRN = '" & Me.MRN & "'")
  5.     mdfname = DLookup("mdfname", "SweetSuccessData", "MRN = '" & Me.MRN & "'")
  6.     mdlname = DLookup("mdlname", "SweetSuccessData", "MRN = '" & Me.MRN & "'")
  7.     MediCal = DLookup("MediCal", "SweetSuccessData", "MRN = '" & Me.MRN & "'")
  8.     EDCdate = DLookup("EDCdate", "SweetSuccessData", "MRN = '" & Me.MRN & "'")
  9.     DeliverAtVMC = DLookup("DeliverAtVMC", "SweetSuccessData", "MRN = '" & Me.MRN & "'")
  10.     AdmitDate = DLookup("AdmitDate", "SweetSuccessData", "MRN = '" & Me.MRN & "'")
  11. End Sub
  12.  
Here's the adapted version of the code you suggested to kickergirl:
Expand|Select|Wrap|Line Numbers
  1. Private Sub MRN_AfterUpdate()    
  2.     Dim rst As DAO.Recordset
  3.         Set rst = Me.RecordsetClone
  4.  
  5.         rst.FindFirst "[MRN] = '" & Me![MRN] & "'"
  6.  
  7.         If rst.NoMatch = False Then
  8.             Me.Undo
  9.             Me.Bookmark = rst.Bookmark
  10.             MsgBox "Medical Record Number already exists!"
  11.        End If
  12. End Sub
  13.  
Thanks for any help you can offer.

Linda
Nov 1 '07 #14

JKing
Expert 100+
P: 1,206
Hi there,

Could you explain what isn't working? What is the code currently doing for you after you type an MRN in? Are there any errors?

Just for clarification, is the MRN field bound or unbound?

If you're getting the duplicate key error its because access is trying to save your current record with the newly entered MRN you are trying to search by which already exists.

The logic behind the code I wrote was as follows:
a) check to see if the value exists
b) if it exists undo changes to the current form
c) move to the form with the entered value
Nov 2 '07 #15

P: 21
Hi there,

Could you explain what isn't working? What is the code currently doing for you after you type an MRN in? Are there any errors?

Just for clarification, is the MRN field bound or unbound?

If you're getting the duplicate key error its because access is trying to save your current record with the newly entered MRN you are trying to search by which already exists.

The logic behind the code I wrote was as follows:
a) check to see if the value exists
b) if it exists undo changes to the current form
c) move to the form with the entered value
Hi Jared,

When I type in an MRN and hit enter, the focus changes to the next control, but nothing happens. I receive no error messages. The MRN is bound to a table where it is the primary key and no duplicate entries are allowed.

I tried using DLookup, with an unbound textbox with a query as its control source. That worked to auto populate the controls in the parent form, but then I couldn't add data to or even move to any of the subforms (which also auto populated). When I did so, I received the "The changes you requested to the table were not successful because they would creat duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." This is my code:

Private Sub txtfindMRN_AfterUpdate()
MRN = DLookup("MRN", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
FirstName = DLookup("FirstName", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
MI = DLookup("MI", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
LastName = DLookup("LastName", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
mdfname = DLookup("mdfname", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
mdlname = DLookup("mdlname", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
MediCal = DLookup("MediCal", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
EDCdate = DLookup("EDCdate", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
DeliverAtVMC = DLookup("DeliverAtVMC", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
AdmitDate = DLookup("AdmitDate", "SweetSuccessData", "MRN = '" & Me.txtfindMRN & "'")
End Sub

I'm sure it's archaic and inefficient, but unfortunately, it was the only way I could get the form to autopopulate at all. Any advice on this would help greatly!

Your logic makes sense to me, but how do I get the computer to allow me to enter in new records and edit existing ones?

Thanks.

Linda
Nov 2 '07 #16

JKing
Expert 100+
P: 1,206
I think you might want to try a different route.
A possibility is to add an unbound control to your form. You could use a textbox or a combo box etc. Then use this control to allow the user to type in or select the record they want to find or "search" for.

Take a look at this article on searching programmatically: Searching

Adding and editing forms should be default functionality of a bound form.
Nov 3 '07 #17

P: 21
I think you might want to try a different route.
A possibility is to add an unbound control to your form. You could use a textbox or a combo box etc. Then use this control to allow the user to type in or select the record they want to find or "search" for.

Take a look at this article on searching programmatically: Searching

Adding and editing forms should be default functionality of a bound form.
Hi Jared,

Thank you for your advice. I have tried using an unbound text box and it does work, but I still cannot edit. I can add, but cannot edit. Even if I am able to retrieve a record and I do nothing else to it, I get the duplicate record error message. The table properties are set to yes for data entry and allowing edits, but I still get the error message that it cannot save the record because it is a duplicate primary key. Is there a properties setting that I am missing?

Thanks.

Linda
Nov 5 '07 #18

P: 21
Hi Jared,

I scrapped the whole thing and started over. It works fine now. I am not sure what exactly was wrong, but working with what I had was just fruitless. Thanks for your advice and assistance.

Linda
Nov 6 '07 #19

Post your reply

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