Auto populate form

[kickergirl]

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.

__________________________________________________ ________________

Private Sub SSN_BeforeUpdate(Cancel As Integer)
 
 
  Dim db As DAO.database
  Set db = CurrentDb()
  Dim rst As DAO.Recordset
 
 
  Set rst = db.openrecordset("Select * from [Ind Part] where SSN = '" & CStr(Me!SSN) & "'", dbopensnapshot)
 
  If rst.RecordCount = 0 Then
  Me!SSN = rst!SSN
  Me![First_Name] = rst!FName
  Me![Last_Name] = rst!LName
  Me![Gender_OptGrp] = rst!Sex
  Me![Race_Combo] = rst!Race
  Me![Ethnicity_OptGrp] = rst!Ethnicity
 
  If (Not IsNull(dlookup("SSN", "Ind Part", SSN = Me!SSN))) Then
  MsgBox "SSN has already been entered."
  End If
 
  End If
 
End Sub
 

__________________________________________________ ________________
Thanks!

P.S. I am using Access 2000 on Windows 2000.

[JKing]

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!

[Scott Price]

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

[Scott Price]

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

[JKing]

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.

[kickergirl]

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.

[kickergirl]

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?

[JKing]

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

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[kickergirl]

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

Private Sub SSN_AfterUpdate()
 
  Dim strCriteria As String
  Dim rst As DAO.Recordset
 
 
  Set rst = Me.RecordsetClone
 
  strCriteria = "[SSN] = '" & Me![SSN] & "'"
 
 
  rst.FindFirst strCriteria
 
  Me!SSN = rst!SSN
  Me![F_Name] = rst!FName
  Me![L_Name] = rst!LName
  Me![Gender_OptGrp] = rst!Sex
  Me![Race_Combo] = rst!Race
  Me![Ethnicity_OptGrp] = rst!Ethnicity
 
  DoCmd.OpenForm "DupEntry"
 
 
End Sub

[JKing]

Give this a try and let me know if it's what you're looking for.

Private Sub SSN_AfterUpdate()
   Dim rst As DAO.Recordset
 
    Set rst = Me.RecordsetClone
 
    rst.FindFirst "[SSN] = '" & Me![SSN] & "'"
 
    If rst.NoMatch = False Then
        Me.Undo
        Me.Bookmark = rst.Bookmark
        MsgBox "SSN already exists!"
    End If
End Sub
 

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.

[kickergirl]

Thanks!!!! It works!

[JKing]

You're welcome, glad to help.

Jared

[lindabaldwin]

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.

Private Sub Medical_Record_Number_AfterUpdate()
   Dim rst As DAO.Recordset
 
    Set rst = Me.RecordsetClone
 
    rst.FindFirst "[Medical Record Number] = '" & [Medical Record Number] & "'"
 
    If rst.NoMatch = False Then
        Me.Undo
        Me.Bookmark = rst.Bookmark
        MsgBox "Medical Record Number already exists!"
    End If
End Sub
 

[lindabaldwin]

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:

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

Here's the adapted version of the code you suggested to kickergirl:

Private Sub MRN_AfterUpdate()    
    Dim rst As DAO.Recordset
        Set rst = Me.RecordsetClone
 
        rst.FindFirst "[MRN] = '" & Me![MRN] & "'"
 
        If rst.NoMatch = False Then
            Me.Undo
            Me.Bookmark = rst.Bookmark
            MsgBox "Medical Record Number already exists!"
       End If
End Sub
 

Thanks for any help you can offer.

Linda

[JKing]

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

[lindabaldwin]

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

[JKing]

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.

[lindabaldwin]

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

[lindabaldwin]

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