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.

__________________________________________________ ________________

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 
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.

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
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

Auto populate form

Post your reply

Similar topics