Losing subform current record after delete

[shauntwo]

I've got a tab control that drives several things, and I'm running into a problem.

The user searches for a particular patient using unbound controls, and a patient subform displays the associated results. When the user selects the patient they want in the sub form datasheet, this selection drives the results shown in the other two main tabs of the form, like patient details and transactions. The problem is, whenever the user deletes a particular transaction in another tab, I lose the primary key of the patient they previously selected at startup. Instead, the user sees the transactions of the first patient in the underlying table.

For my transactions sub form, for example, I make this association as such:
Me.subfrm_transaction_transactions.Form.Filter = "PATIENT_ID = " & Me.FRM_SUB_PATIENT.Form!PATIENT_ID

I've created a variable to capture this patient ID, and this remedies part of the problem, but I'm really hoping there's a more elegant way to preserve the selected patient in the subform, regardless of any actions the user makes elsewhere in the main tabs.

Thanks!

 

[Shirley]

Usually on my search tab I use a grid with 2 buttons. One button to find and th next button to select. The following code is what I use on the select button. I hope it helps.

Private Sub cmdSelect_Click() '
If Not IsNull([SU20_subform].Form.TripID) Then
TripID.SetFocus
DoCmd.FindRecord [SU20_subform].Form.TripID
End If

End Sub

 

[shauntwo]

Thanks for the post, Shirley. I'm not sure my previous post was clear enough, so I'll try this:

I've got Patient Search, Patient Edit, and Trasaction Edit tabs. In the Search tab, when a user literally clicks on the row in the Patient subform corresponding to the patient they "want", I want to use this PATIENT_ID throughout the rest of the form. I've been able to everywhere except for when the user deletes a transaction in the Transaction Edit tab. In that case, it's like the Patient Search subform's underlying data "resets," because the PATIENT_ID is changed to the first person listed in the underlying table.

In essence, I want to retain the PATIENT_ID from the selected row.

 

[ItIsHardToProgram]

If I follow what you are saying, you have a subform, patient search, related to the patient ID of your mainform (transaction edit) and you want to be able to delete a transaction without reseting the patient search????

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[shauntwo]

It's not as crazy as you think.

If I select patient "John Doe" in my Patient Search subform, I can click on the Transactions tab and see all transactions for John Doe in a different subform. If, for whatever reason, I want to delete one of Doe's transactions, after the acCmdDeleteRecord command, I want the transactions subform to still show any remaining transactions for John Doe. In other words, I definitely DON'T want to reset my Patient Search results.

Thanks for your help!

 

[ItIsHardToProgram]

Have you tried deleting transactions through a query?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Shirley]

Are all the fields on one table or query?

 

[shauntwo]

No, I haven't. I suppose I can always turn to that, but I've got a sneaking suspicion that there's some method or property I just haven't considered yet. I'm stumped why a complete unrelated subform's current record would be affected by deleting a transaction in a different subform.

I haven't done much SQL deletes in Jet. Is there a recommended syntax you can suggest?

 

[shauntwo]

Shirley, the answer to your question is yes.

 

[ItIsHardToProgram]

I believe from experience and from what I have seen around that most people go through recordset and SQL to play with records, I believe it is the best way and in your case the best solution, running with SQL would allow you to delete more then one transaction at a time, making the rendering alot better?

Your thoughts?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Shirley]

My suggestion is the create two tables or queries. Because when you delete it is deleting whole record with the patient ID to. Then you could created subform for each tab. Also create a delete query that will only delete certain thing from the table not the patient ID.

 

[ItIsHardToProgram]

Sorry I awnsered too fast...

I believe you will need to locate the entry you selected using a control from your subform.

once you have located it through your recordset, you can run a simple delete query...

Then again maybe you could just take that value from your subform and run it in a simple Docmd.runsql command putting "WHERE transNumber = " & yourSubformValueSelected

like I mentioned, the advantage you have of locating your entry in a recordset is that you can play around it after...

your thoughts?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Wait... you have one table containing all information located in each subform that each run a query????

If so you need to change that arrangement doing relationships between tables so that you can work around your problem... what Shirley said basically...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Remou]

I think the best bet is to create a text field on the main form that is filled in with the click event of the subform:

[tt]Private Sub ID_Click()
'Search form code
Forms!MainForm.txtID = Me.ID
End Sub[/tt]

Each of the related subform controls can then use this code as the link master field:

Link Child Field : ID
Link Master Field : txtID

 

[ItIsHardToProgram]

I believe it is already done?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[shauntwo]

Remou,

I *guess* I could create an invisible text box that simply stores the PATIENT_ID that a user implicitly picks when they highlight a patient record in the subform. However, I'd prefer to understand WHY the reference to the subform's PATIENT_ID gets reset upon a (seemingly) unrelated record delete. I can add new transactions or edit them and this problem doesn't occur - only when I delete them.