Form jumps to previous record when data is added to form

[Kurt]

Sometimes the underlying recordset jumps to the previous
record the moment data is added to the current record via
a form. Here're the details:

I'm developing a database to store medical intake
information for patients. Patients and their case
information are initially entered in a separate database
where tblPatients stores patient info and tblCases stores
case information. (I have these tables linked in my
medical database.)

Since a patient can have more than one case, there is a
one-to-many relationship between tblPatients and
tblCases. And since each case can have only one medical
intake record, there is a one-to-one relationship between
tblCases and tblMedicalIntake.

So my medical database has three main tables:

tblPatients (table linked from another database)
--------------
PatientID (PK)
PatientName

tblCases (table linked from another database)
------------
CaseNumber (PK)
PatientID
CaseDate
etc.

tblMedicalIntake
----------------------
CaseCode (PK)
IntakeDate
IntakeLocation
IntakeType
etc.

The main medical intake form is based on a query that
brings together these three tables:

SELECT tblPatients.PatientID, tblPatients.PatientName,
tblCases.CaseNumber, tblMedicalIntake.*
FROM tblPatients
INNER JOIN (tblCases LEFT JOIN tblMedicalIntake ON
tblCases.CaseNumber =
tblMedicalIntake.CaseCode) ON tblPatients.PatientID =
tblCases.PatientID;

Here's what happens:

I open the main form and select a patient from a combo
box. The combo box requeries a second combo box to show
only case numbers for that patient. I then select a case
number and the After Update event pulls up the medical
intake record for that patient's particular case. This
part works.

But sometimes (I can't always replicate this), the moment
I try to add data (e.g., checking a yes/no box will do
it) to the form, the recordset jumps to the previous
record. I can tell this happens because the name and case
number shown in unbound text control on the form changes
to show a different patient than the one I originally
selected. However, the originally selected patient's name
remains in the form's caption (Me.Caption code in the On
Current), and the original record number remains in the
navigation toolbar at the bottom. But clearly the
recordset seems to have been changed because any info. I
add to the form is not saved.

Any idea why this is happening?

The After Update event for the second combo box, which
calls and bookmarks the record, is:

Private Sub cboFindCase_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CaseNumber] = " & Str(Nz(Me!
[cboFindCase], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks. - Kurt

 

[Kurt]

I believe I've fixed the problem. I took a tip from
http://support.microsoft.com/kb/191883/EN-US/ and added
Me.Requery in the After Update event of the second combo
box (the one which lists cases for the patient and calls
the record). By requering the form before the record is
called, I haven't been able to replicate the problem
since.

-----Original Message-----
Sometimes the underlying recordset jumps to the previous
record the moment data is added to the current record via
a form. Here're the details:

I'm developing a database to store medical intake
information for patients. Patients and their case
information are initially entered in a separate database
where tblPatients stores patient info and tblCases stores
case information. (I have these tables linked in my
medical database.)

Since a patient can have more than one case, there is a
one-to-many relationship between tblPatients and
tblCases. And since each case can have only one medical
intake record, there is a one-to-one relationship between
tblCases and tblMedicalIntake.

So my medical database has three main tables:

tblPatients (table linked from another database)
--------------
PatientID (PK)
PatientName

tblCases (table linked from another database)
------------
CaseNumber (PK)
PatientID
CaseDate
etc.

tblMedicalIntake
----------------------
CaseCode (PK)
IntakeDate
IntakeLocation
IntakeType
etc.

The main medical intake form is based on a query that
brings together these three tables:

SELECT tblPatients.PatientID, tblPatients.PatientName,
tblCases.CaseNumber, tblMedicalIntake.*
FROM tblPatients
INNER JOIN (tblCases LEFT JOIN tblMedicalIntake ON
tblCases.CaseNumber =
tblMedicalIntake.CaseCode) ON tblPatients.PatientID =
tblCases.PatientID;

Here's what happens:

I open the main form and select a patient from a combo
box. The combo box requeries a second combo box to show
only case numbers for that patient. I then select a case
number and the After Update event pulls up the medical
intake record for that patient's particular case. This
part works.

But sometimes (I can't always replicate this), the moment
I try to add data (e.g., checking a yes/no box will do
it) to the form, the recordset jumps to the previous
record. I can tell this happens because the name and case
number shown in unbound text control on the form changes
to show a different patient than the one I originally
selected. However, the originally selected patient's name
remains in the form's caption (Me.Caption code in the On
Current), and the original record number remains in the
navigation toolbar at the bottom. But clearly the
recordset seems to have been changed because any info. I
add to the form is not saved.

Any idea why this is happening?

The After Update event for the second combo box, which
calls and bookmarks the record, is:

Private Sub cboFindCase_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CaseNumber] = " & Str(Nz(Me!
[cboFindCase], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks. - Kurt
.