Fill out name fields if record exists

[Guest]

I have a form for entering patients referral details for patients at my
hospital. A patient can have many referrals and I have a number of subforms
on the main form to capture various clinical details of the patients
referrals to hospital. I have the patient's unique ID as my primary key
(PatID). When I enter an exisiting PatID and tab out of this field the cursor
moves to FName ready to enter Forname but also populates the subforms with
the existing data from previous hospital visits. What I'd like to happen is
for the FName, SName, DOB and Gender fields to also populate with that
patients details if the PatID is recognised as already existing. Is this
possible and how?

Thanks in advance,
Ian.

 

[tina]

if you have a Patients table that stores the first and last names, DOB,
gender, etc, then you should NOT be storing that data in any other table.
doing so breaks normalization rules. recommend you read up on relational
design principles, to ensure that your tables/relationships structure is set
up correctly, before moving forward with the rest of your design. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Guest]

Tina,

Thanks for your concerns, my fault for not explaining properly but I am
using the primary key of the Patients table as the foreign key in the other
tables and not breaking normalisation rules.

The form I use to enter the patient is based on the Patients table with
subforms on that form using the PatID as the foreign key as the link.

Thanks,
Ian.

 

[Pete D.]

Below is a sample, in the afterupdate of your paticent id you can feed other
fields from that table. Below should give you a jump off point.

Private Sub DropReturn_AfterUpdate()
On Error Resume Next
If DropReturn = True Then
Me!RemainWith = False
Me!DropOff = False
Me!U_Drive_It = False
Me!DropReturnTime = Time()
Me!DropReturnTime.SetFocus
End If
End Sub

 

[tina]

hmm, okay. if the main form is bound to the Patients table, then the names,
dob, gender etc, are already visible in controls bound to those fields,
correct? i'm not sure why you want to display them again in a subform...but
you can refer to the controls on the parent form in expressions used in
unbound textbox controls in a subform, using the full syntax, as

=[Forms]![MainFormName]![ControlName]

in order to *display* the values. if that doesn't quite do it for you, post
back with more details of the mainform/subform setup, and we'll work it out.

hth

 

[jrmask via AccessMonster.com]

Here is code that takes me to a form with existing records. It looks as
though you want to return to the existing form for a Patient and then add
more referrals in the subforms? This is code I got from srfreeman and have
modified for use in several db's. Put it in the Before_Update event in the
PatID control. When you type in a new PatID it will let you enter a new
patient, if there is an existing PatID then it should take you to that form.

Private Sub PatID_BeforeUpdate(Cancel As Integer)
'*********************************
'Original Code sample courtesy of srfreeman - modified to fit current need
jrm
'*********************************

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.PatID.Value
stLinkCriteria = "[PatID]=" & "'" & SID & "'"

'Check Patient Table for duplicate PatID
If DCount("PatID", "YourTable", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "This PatID " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
'Go to record of original PatID
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

 

[jrmask via AccessMonster.com]

Be careful with this line
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
It should be all on one line
& vbCr & vbCr & "You - - - - - - record.", vbInformation _

Here is code that takes me to a form with existing records. It looks as
though you want to return to the existing form for a Patient and then add
more referrals in the subforms? This is code I got from srfreeman and have
modified for use in several db's. Put it in the Before_Update event in the
PatID control. When you type in a new PatID it will let you enter a new
patient, if there is an existing PatID then it should take you to that form.

Private Sub PatID_BeforeUpdate(Cancel As Integer)
'*********************************
'Original Code sample courtesy of srfreeman - modified to fit current need
jrm
'*********************************

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.PatID.Value
stLinkCriteria = "[PatID]=" & "'" & SID & "'"

'Check Patient Table for duplicate PatID
If DCount("PatID", "YourTable", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "This PatID " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
'Go to record of original PatID
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

I have a form for entering patients referral details for patients at my
hospital. A patient can have many referrals and I have a number of subforms

[quoted text clipped - 9 lines]

Thanks in advance,
Ian.

 

[Guest]

Hi jrmask,

Thanks for your reply and many apologies for my delay in getting back but I
had to go out of teon for a few days. I tried your code but am getting a
frustrating error message: "Run-time error 3464. Data type mismatch in
criteria expression", from the line:

If DCount("PatID", "YourTable", stLinkCriteria) > 0 Then

I can get Dcount to work if I exclude the criteria but it obviously returns
a count of all the records. I tried "Googling" the error but with no real
joy. From a thread on this forum I also tried replacing the single quotes
around SID with double quotes but that made no difference either. I'd
welcome any thoughts you may have.

Thanks,
Ian

Here is code that takes me to a form with existing records. It looks as
though you want to return to the existing form for a Patient and then add
more referrals in the subforms? This is code I got from srfreeman and have
modified for use in several db's. Put it in the Before_Update event in the
PatID control. When you type in a new PatID it will let you enter a new
patient, if there is an existing PatID then it should take you to that form.

Private Sub PatID_BeforeUpdate(Cancel As Integer)
'*********************************
'Original Code sample courtesy of srfreeman - modified to fit current need
jrm
'*********************************

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.PatID.Value
stLinkCriteria = "[PatID]=" & "'" & SID & "'"

'Check Patient Table for duplicate PatID
If DCount("PatID", "YourTable", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "This PatID " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
'Go to record of original PatID
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

I have a form for entering patients referral details for patients at my
hospital. A patient can have many referrals and I have a number of subforms
on the main form to capture various clinical details of the patients
referrals to hospital. I have the patient's unique ID as my primary key
(PatID). When I enter an exisiting PatID and tab out of this field the cursor
moves to FName ready to enter Forname but also populates the subforms with
the existing data from previous hospital visits. What I'd like to happen is
for the FName, SName, DOB and Gender fields to also populate with that
patients details if the PatID is recognised as already existing. Is this
possible and how?

Thanks in advance,
Ian.

 

[John W. Vinson]

I can get Dcount to work if I exclude the criteria but it obviously returns
a count of all the records. I tried "Googling" the error but with no real
joy. From a thread on this forum I also tried replacing the single quotes
around SID with double quotes but that made no difference either. I'd
welcome any thoughts you may have.

Text fields require your choice of either singlequote or doublequote
delimiters; they're interchangable. If PatID is a Number datatype (or an
autonumber), you should have no delimiters at all:

stLinkCriteria = "[PatID]=" & SID

John W. Vinson [MVP]

 

[jrmask via AccessMonster.com]

Did John's suggestion help you get your problem solved?

Hi jrmask,

Thanks for your reply and many apologies for my delay in getting back but I
had to go out of teon for a few days. I tried your code but am getting a
frustrating error message: "Run-time error 3464. Data type mismatch in
criteria expression", from the line:

If DCount("PatID", "YourTable", stLinkCriteria) > 0 Then

I can get Dcount to work if I exclude the criteria but it obviously returns
a count of all the records. I tried "Googling" the error but with no real
joy. From a thread on this forum I also tried replacing the single quotes
around SID with double quotes but that made no difference either. I'd
welcome any thoughts you may have.

Thanks,
Ian

Here is code that takes me to a form with existing records. It looks as
though you want to return to the existing form for a Patient and then add

[quoted text clipped - 50 lines]

 

[Guest]

Hi Guys,

John's solutions sorted the problem, thanks very much to both of you for
your help with my issue.

Ian.

Did John's suggestion help you get your problem solved?

Hi jrmask,

Thanks for your reply and many apologies for my delay in getting back but I
had to go out of teon for a few days. I tried your code but am getting a
frustrating error message: "Run-time error 3464. Data type mismatch in
criteria expression", from the line:

If DCount("PatID", "YourTable", stLinkCriteria) > 0 Then

I can get Dcount to work if I exclude the criteria but it obviously returns
a count of all the records. I tried "Googling" the error but with no real
joy. From a thread on this forum I also tried replacing the single quotes
around SID with double quotes but that made no difference either. I'd
welcome any thoughts you may have.

Thanks,
Ian

Here is code that takes me to a form with existing records. It looks as
though you want to return to the existing form for a Patient and then add

[quoted text clipped - 50 lines]

Thanks in advance,
Ian.