Auto-fill

[Chi]

Hi,

In my Data Entry form has FULLNAM, FIRSTNAM and LASTNAM fields. Would you
please show me how I can make the FIRSTNAM and LASTNAM text boxed are filled
in by themselves after I entered people name in the FULLNAM textbox?

Ex: If I entered SUE TRAN in the FullName textbox, The firstName "Sue" will
appear in the FIRSTNAM text box and the TRAN will be seen on the LASTNAME
text box.

Thanks
Chi

Chi

 

[Jeff Boyce]

What do you want to have happen when someone enters "Cher"?

How about when someone enters "Billy Jean King"? ... or "John Jacob
Jingleheimer Schmidt"?

If you want FirstName and LastName, why not have THOSE fields for data
entry, and use a query to concatenate them together for FullName?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Hi,

In my Data Entry form has FULLNAM, FIRSTNAM and LASTNAM fields. Would you
please show me how I can make the FIRSTNAM and LASTNAM text boxed are filled
in by themselves after I entered people name in the FULLNAM textbox?

Ex: If I entered SUE TRAN in the FullName textbox, The firstName "Sue" will
appear in the FIRSTNAM text box and the TRAN will be seen on the LASTNAME
text box.

If you're storing FULLNAM in the table... DON'T.

Storing the same information redundantly is neither necessary nor good design.
You can derive the FULLNAM field from the contents of the other two fields; if
you store all three, you could very easily end up with erroneous data such as
FIRSTNAM = John, LASTNAM = Doe, and FULLNAM = "June Cleaver".

Simply store the first and last names, and when needed, use a Query with a
calculated field

FULLNAM: [FIRSTNAM] & " " & [LASTNAM]

 

[Chi]

Hi John and Jeff,

Thank you for your advices. I usually have PatientID which has autonumber is
a primary key. Howver, the numbers 1,2,3...... is a unique identifier . For
example,

Below are my records:

PatientID FirstName LastName Address Phone

1 Sue Tran 15250 568-8965
2. Ben Tran 25625 458-8956
3. Sue Tran 15250 568- 8965

The primary key (PatientID) isn't work very well because I am able to enter
" Sue Tran" many times in the table. That is why I made the FULLNAM is
primary key.

Please help

Thanks
Chi

Hi,

In my Data Entry form has FULLNAM, FIRSTNAM and LASTNAM fields. Would you
please show me how I can make the FIRSTNAM and LASTNAM text boxed are filled
in by themselves after I entered people name in the FULLNAM textbox?

Ex: If I entered SUE TRAN in the FullName textbox, The firstName "Sue" will
appear in the FIRSTNAM text box and the TRAN will be seen on the LASTNAME
text box.

If you're storing FULLNAM in the table... DON'T.

Storing the same information redundantly is neither necessary nor good design.
You can derive the FULLNAM field from the contents of the other two fields; if
you store all three, you could very easily end up with erroneous data such as
FIRSTNAM = John, LASTNAM = Doe, and FULLNAM = "June Cleaver".

Simply store the first and last names, and when needed, use a Query with a
calculated field

FULLNAM: [FIRSTNAM] & " " & [LASTNAM]

 

[Jeff Boyce]

People don't usually come with unique identifying numbers (e.g., barcodes),
and the supposedly unique identifying numbers assigned to folks (e.g., in
the USA, SSNs) are rife with problems! Uniquely identifying persons is
problematic, at best.

Using only their names is not a good solution -- different individuals share
the same name(s).

Using only their names and addresses is not a good solution -- father and
son may share the same name and address.

I don't understand why your application would allow the same person to be
entered as a Patient more than once?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi John and Jeff,

Thank you for your advices. I usually have PatientID which has autonumber
is
a primary key. Howver, the numbers 1,2,3...... is a unique identifier .
For
example,

Below are my records:

PatientID FirstName LastName Address Phone

1 Sue Tran 15250 568-8965
2. Ben Tran 25625 458-8956
3. Sue Tran 15250 568- 8965

The primary key (PatientID) isn't work very well because I am able to
enter
" Sue Tran" many times in the table. That is why I made the FULLNAM is
primary key.

Please help

Thanks
Chi

Hi,

In my Data Entry form has FULLNAM, FIRSTNAM and LASTNAM fields. Would
you
please show me how I can make the FIRSTNAM and LASTNAM text boxed are
filled
in by themselves after I entered people name in the FULLNAM textbox?

Ex: If I entered SUE TRAN in the FullName textbox, The firstName "Sue"
will
appear in the FIRSTNAM text box and the TRAN will be seen on the
LASTNAME
text box.

If you're storing FULLNAM in the table... DON'T.

Storing the same information redundantly is neither necessary nor good
design.
You can derive the FULLNAM field from the contents of the other two
fields; if
you store all three, you could very easily end up with erroneous data
such as
FIRSTNAM = John, LASTNAM = Doe, and FULLNAM = "June Cleaver".

Simply store the first and last names, and when needed, use a Query with
a
calculated field

FULLNAM: [FIRSTNAM] & " " & [LASTNAM]

 

[John W. Vinson]

Hi John and Jeff,

Thank you for your advices. I usually have PatientID which has autonumber is
a primary key. Howver, the numbers 1,2,3...... is a unique identifier . For
example,

Below are my records:

PatientID FirstName LastName Address Phone

1 Sue Tran 15250 568-8965
2. Ben Tran 25625 458-8956
3. Sue Tran 15250 568- 8965

The primary key (PatientID) isn't work very well because I am able to enter
" Sue Tran" many times in the table. That is why I made the FULLNAM is
primary key.

Names are not good primary keys, because they are not unique. I know three
people in the small town of Parma, all named Fred Brown. If Fred Brown and his
son Fred Brown both came to your clinic, would you turn one away!?

I would stick with the autonumber PatientID, and instead of uniquely indexing
the full name, put some VBA code in the Form's BeforeUpdate event to check for
duplicates. If you find a duplicate name, *warn* the user and give them the
option of opening the existing record - but also give them the option of
adding the record anyway. Here's some sample code. It assumes that you have a
Form based on the patient table with no filters or criteria.

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim strSQL As String
Dim rs As DAO.Recordset
Dim iAns As Integer
strSQL = "[FirstName] = """ & Me!FirstName & """ AND [LastName] = """ _
& Me!LastName & """"
' e.g [FirstName] = "Eileen" AND [LastName] = "O'Hara"
' The triplequotes evaluate to one doublequote character
Set rs = Me.RecordsetClone
rs.FindFirst strSQL
If Not rs.NoMatch Then ' a record was found for this name
iAns = MsgBox(Me.[FirstName] & " " & Me.[LastName] & " already exists!" _
& vbCrLf & "Go to the existing record? Click Yes to go, " _
& "No to add this as a new record, Cancel to quit:", vbYesNoCancel)
Select Case iAns
Case vbYes ' user clicked Yes
Cancel = True ' cancel what the user was about to add
Me.Bookmark = rs.Bookmark ' jump to the found record
Case vbNo ' user clicked No, do nothing - just let the update proceed
Case vbCancel ' user clicked Cancel, erase the form
Cancel = True
Me.Undo
End Select
End If
Set rs = Nothing ' clean up after yourself
End Sub