Use Form to Update Query

[ryguy7272]

I have a Form that is bound to a Query and the source of the Query is a
Table. This Table, named ‘Radon’ has patient health information and a unique
ID for each patient. This ‘Radon Table is linked to another Table, named
‘PatientTable’, via the unique ID. The PatientTable has all information
pertaining to each patient. So far so good. I read here how to update a
Query based on a Form:
http://allenbrowne.com/ser-61.html

At first the records were not updatable and I realized it was because my
Joins were not indexed correctly. I applied a PK, and everything seemed to
work. Emphasis on SEEMED… The query is not updatable, and the data in the
‘Radon’ Table is updated too, so this is great, but only the first record in
the Table is updated!! On my Form, I have the ID, the patient’s FIrstName
and LastName. I enter the ID, and the FirstName and LastName are updated via
a Dlookup. This seems to be the way to go. The only problem is that if I
enter Patient 2323, I actually end up undating the data for patient 2222,
because that is the first record in the ‘Radon’ Table.

I have a Command Button that users can click, and this code runs:
Private Sub UpdatePatient_Click()
MsgBox "Thanks!! " & Me!FirstName & " " & Me!LastName & " has been
successfully updated!!"
End Sub

I think the Query updates without the code running, but when the code fires
a MessageBox pops up notifying the user that the information is indeed
updated. Is this the right way to of this kind of thing? Also, how do I get
the correct patient records updated, according to the ID number? If I move
to the next record using the control at the bottom of the Form, the correct
records are updated, but this seems to run independent of my ID, and my
FirstName and LastName, which are linked to the ID.

Two separate questions. I’ve hit a wall and I can’t seem to figure this out…


Thanks,
Ryan---

 

[John W. Vinson]

I have a Form that is bound to a Query and the source of the Query is a
Table.

What's the SQL view of the query?

This Table, named ‘Radon’ has patient health information and a unique
ID for each patient. This ‘Radon Table is linked to another Table, named
‘PatientTable’, via the unique ID. The PatientTable has all information
pertaining to each patient.

Are you storing the "same kind" of information in the PatientTable as in the
RadonTable? Don't. Information should be stored only once.

So far so good. I read here how to update a
Query based on a Form:
http://allenbrowne.com/ser-61.html

Ummmm... that describes the various reasons why a Query might not be
updateable. It does NOT describe "how to update a query based on a form".

At first the records were not updatable and I realized it was because my
Joins were not indexed correctly. I applied a PK, and everything seemed to
work. Emphasis on SEEMED… The query is not updatable, and the data in the
‘Radon’ Table is updated too, so this is great, but only the first record in
the Table is updated!! On my Form, I have the ID, the patient’s FIrstName
and LastName. I enter the ID, and the FirstName and LastName are updated via
a Dlookup. This seems to be the way to go. The only problem is that if I
enter Patient 2323, I actually end up undating the data for patient 2222,
because that is the first record in the ‘Radon’ Table.

DLookUp will *DISPLAY* information from a table. It will not update it.

I have a Command Button that users can click, and this code runs:
Private Sub UpdatePatient_Click()
MsgBox "Thanks!! " & Me!FirstName & " " & Me!LastName & " has been
successfully updated!!"
End Sub

This code posts a *MESSAGE* saying that something's been updated but it
certainly doesn't update anything. What are the Control Sources of
Me!FirstName and Me!LastName? If they're dlookups, nothing is being updated.

I think the Query updates without the code running, but when the code fires
a MessageBox pops up notifying the user that the information is indeed
updated.

Yes. The button code does just that: pops up a message. That's ALL that it
does.

Is this the right way to of this kind of thing? Also, how do I get
the correct patient records updated, according to the ID number? If I move
to the next record using the control at the bottom of the Form, the correct
records are updated, but this seems to run independent of my ID, and my
FirstName and LastName, which are linked to the ID.

Unless you post your query, and any other code you have on this form, nobody
will be able to tell what you're actually doing.

Two separate questions. I’ve hit a wall and I can’t seem to figure this out…

We can't either because you haven't yet posted what you're doing.

 

[ryguy7272]

Thanks John! Below is my SQL:
SELECT PatientTable.MR, PatientTable.FirstName, PatientTable.LastName,
MontefioreRADON.PointDose2D, MontefioreRADON.ThreeD, MontefioreRADON.IMRT,
MontefioreRADON.Brachtherapy, MontefioreRADON.SRS, MontefioreRADON.IGRT,
MontefioreRADON.CALYPSO, MontefioreRADON.FuducialMarkers,
MontefioreRADON.SBRT, MontefioreRADON.TreatmentPlanning,
MontefioreRADON.MotionStudy, MontefioreRADON.MRI_Fusion,
MontefioreRADON.PET_CT_Fusion, MontefioreRADON.Without, MontefioreRADON.With,
MontefioreRADON.IV, MontefioreRADON.Oral, MontefioreRADON.Specify,
MontefioreRADON.Head_Brain, MontefioreRADON.Head_Neck, MontefioreRADON.Chest,
MontefioreRADON.Pelvis, MontefioreRADON.Breast_LTRT, MontefioreRADON.Abdomen,
MontefioreRADON.OtherAreaInterest, MontefioreRADON.Supine,
MontefioreRADON.Prone, MontefioreRADON.ArmsUp, MontefioreRADON.ArmsSides,
MontefioreRADON.ArmsAkimbo, MontefioreRADON.ReversedTable,
MontefioreRADON.OtherPosition, MontefioreRADON.Vac_Loc,
MontefioreRADON.AquaplastMask, MontefioreRADON.Wingboard,
MontefioreRADON.KneeWedge, MontefioreRADON.UniversalWedge,
MontefioreRADON.Head_Neck_Shoulder_Device, MontefioreRADON.Hearrest,
MontefioreRADON.PituitaryBoard, MontefioreRADON.BreastBoard,
MontefioreRADON.OtherImmobilization
FROM PatientTable INNER JOIN MontefioreRADON ON PatientTable.MR =
MontefioreRADON.MR;

This is essentially from a Table with numerous data points. All I'm trying
to do is update the yes/no fields for the appropriate ID (which is MR;
hospital uses MR instead of ID). When I make a change, the change is only
applied to the first record in the Table. I would like to enter an MR number
in my Form (named 'PatientForm') and have the change reflected in the
appropriate record, based on MR. It may be something simple; I have never
done this before so I don't know for sure. Maybe my design is flawed. Maybe
there is a better way to do this. I'd appreciate some guidance.

Regards,
Ryan---

 

[John W. Vinson]

Thanks John! Below is my SQL:
SELECT PatientTable.MR, PatientTable.FirstName, PatientTable.LastName,
MontefioreRADON.PointDose2D, MontefioreRADON.ThreeD, MontefioreRADON.IMRT,
MontefioreRADON.Brachtherapy, MontefioreRADON.SRS, MontefioreRADON.IGRT,
MontefioreRADON.CALYPSO, MontefioreRADON.FuducialMarkers,
MontefioreRADON.SBRT, MontefioreRADON.TreatmentPlanning,
MontefioreRADON.MotionStudy, MontefioreRADON.MRI_Fusion,
MontefioreRADON.PET_CT_Fusion, MontefioreRADON.Without, MontefioreRADON.With,
MontefioreRADON.IV, MontefioreRADON.Oral, MontefioreRADON.Specify,
MontefioreRADON.Head_Brain, MontefioreRADON.Head_Neck, MontefioreRADON.Chest,
MontefioreRADON.Pelvis, MontefioreRADON.Breast_LTRT, MontefioreRADON.Abdomen,
MontefioreRADON.OtherAreaInterest, MontefioreRADON.Supine,
MontefioreRADON.Prone, MontefioreRADON.ArmsUp, MontefioreRADON.ArmsSides,
MontefioreRADON.ArmsAkimbo, MontefioreRADON.ReversedTable,
MontefioreRADON.OtherPosition, MontefioreRADON.Vac_Loc,
MontefioreRADON.AquaplastMask, MontefioreRADON.Wingboard,
MontefioreRADON.KneeWedge, MontefioreRADON.UniversalWedge,
MontefioreRADON.Head_Neck_Shoulder_Device, MontefioreRADON.Hearrest,
MontefioreRADON.PituitaryBoard, MontefioreRADON.BreastBoard,
MontefioreRADON.OtherImmobilization
FROM PatientTable INNER JOIN MontefioreRADON ON PatientTable.MR =
MontefioreRADON.MR;

This is essentially from a Table with numerous data points. All I'm trying
to do is update the yes/no fields for the appropriate ID (which is MR;
hospital uses MR instead of ID). When I make a change, the change is only
applied to the first record in the Table. I would like to enter an MR number
in my Form (named 'PatientForm') and have the change reflected in the
appropriate record, based on MR. It may be something simple; I have never
done this before so I don't know for sure. Maybe my design is flawed. Maybe
there is a better way to do this. I'd appreciate some guidance.

Well, there's a short answer and a long answer.

The short answer is that you should use a Form based on PatientTable, with a
Subform based on MontefioreRADON, linked by MR. You can use the combo box
wizard to put a combo box on the main form - choose the option "Use this combo
to find a record". It should - MUST!! - be an *unbound* combo box with code in
its AfterUpdate event to jump to a record on the form (rather than overwriting
a record).

The long answer is that your RADON table is simply incorrect. You're storing
data in fieldnames. That's good spreadsheet logic but it's wrong for a
relational table. If you have a checklist, it should be a "tall thin" table
with one *RECORD* per checklist item, not one *FIELD*. It sounds like you
based your table design on an existing paper form - a very good way to get an
*incorrect* relational table design.