Autofill

[zatigh]

I am creating a form to enter patient information. Patient are identified by their medical record number. Patients can an will be return patients so there will be multiple records on patients. When I enter their medical record number, I would like other information such as name and address to autofill if they have been a patient before. How do I set this up?

[ADezii]

1. Let's assume you have a Table named tblPatients with the following Fields:
   1. [RecordNumber]
   2. [FirstName]
   3. [LastName]
   4. [City]
   5. [State]
   6. [Zip]
2. Let's also assume you have a Form with the following Fields (Text Boxes) :
   1. [txtRecordNumber]
   2. [txtFirstName]
   3. [txtLastName]
   4. [txtCity]
   5. [txtState]
   6. [txtZip]
3. Place the following, or similar, code in the AfterUpdate() Event of txtRecordNumber:
   

   Private Sub txtRecordNumber_AfterUpdate()
   Dim txtRecID As TextBox
    
   Set txtRecID = Me![txtRecordNumber]
    
   If Not IsNull(txtRecID) And IsNumeric(txtRecID) Then        'legitimate value for Record Number?
     'Is there a Patient with this Record Number?
     If DCount("*", "tblPatients", "[RecordNumber] = " & txtRecID) <> 0 Then
       Me![txtFirstName] = DLookup("[FirstName]", "tblPatients", "[RecordNumber] = " & txtRecID)
       Me![txtLastName] = DLookup("[LastName]", "tblPatients", "[RecordNumber] = " & txtRecID)
       Me![txtAddress] = DLookup("[Address]", "tblPatients", "[RecordNumber] = " & txtRecID)
       Me![txtCity] = DLookup("[City]", "tblPatients", "[RecordNumber] = " & txtRecID)
       Me![txtState] = DLookup("[State]", "tblPatients", "[RecordNumber] = " & txtRecID)
       Me![txtZip] = DLookup("[Zip]", "tblPatients", "[RecordNumber] = " & txtRecID)
     Else
       MsgBox "No Patient exists with a Record Number of [" & txtRecID & "]"
       'Should also clear Fields on any prior entries
     End If
   Else
     MsgBox "Invalid entry for Record Number"
   End If
   End Sub

4. Let me know if you have any questions.