moving between forms to related records

[Anthony]

Hi there

I have a form "Operation", that is related to three other forms by a field
called "PatientID". These forms are set up so that when I am in the
"Operation" form, I select a value in a combo box called "OperationType1"
(fill out the other fields) then click on a command button, which takes me
to the related record in one of the three linked forms, depending on the
value selected from the combo box, "OperationType1". Only three values in
this combo box are linked to tables; the other values are not.

This set up works as it should and takes me to the correct record in the
correct form when adding new patients and when viewing existing patient's
records. It also works as it should when changing values in the combo box
from those linked to other forms, to values that are not linked to any other
form.

The problem occurs when i want to change the value in the "OperationType1"
combo box from a value that is not linked to another form, to one that is. I
change this value, then click on the command button, but the max record
displayed instead of the related record. The code I am using in the command
button is:

****Start Code****
Private Sub Report_Click()
On Error GoTo Err_Report_Click

Dim StDocName As String
Dim StDocName1 As String
Dim StDocName2 As String
Dim StDocName3 As String

Dim strWhere As String
Dim stQuery As String
DoCmd.Requery stQuery

Dim frmF As Form

Set frmF = Form_Operation
frmF.Refresh


Dim StDocNameA As String
Dim StDocNameB As String
Dim StDocNameC As String

Dim stLinkCriteria As String
StDocNameA = "DHS Form"
StDocNameB = "Carpal Form"
StDocNameC = "AntStab Form"

stLinkCriteria = "[PatientID]=" & Me!PatientID


strWhere = "[PatientID]=" & Me!PatientID
StDocName = "Operation Report"
StDocName1 = "Operation Report 1"
StDocName2 = "Operation Report 2"
StDocName3 = "Operation Report 3"

If frmF.OperationType1 = "dynamic hipscrew and plate" Then

DoCmd.Close

DoCmd.OpenForm StDocNameA, , , stLinkCriteria


' DoCmd.OpenReport StDocName1, acViewPreview, , strWhere
' DoCmd.Maximize

Else

If frmF.OperationType1 = "carpal tunnel release" Then

DoCmd.Close

DoCmd.OpenForm StDocNameB, , , stLinkCriteria


' DoCmd.OpenReport StDocName2, acViewPreview, , strWhere
' DoCmd.Maximize

Else

If frmF.OperationType1 = "anterior stabilisation" Then

DoCmd.Close
DoCmd.OpenForm StDocNameC, , , stLinkCriteria
DoCmd.Maximize

Else

DoCmd.Close
DoCmd.OpenReport StDocName, acViewPreview, , strWhere
DoCmd.Maximize

End If
End If
End If

Exit_Report_Click:
Exit Sub

Err_Report_Click:
MsgBox Err.Description
Resume Exit_Report_Click

End Sub
***End Code***

I can't see why this would not take me to the correct record in the related
form. The Default value for "PatientID" in all of the related forms is set
to
"=DMax("[PatientID]","Operation")". If I remove this, the default value
becomes zero and this is the value that appears in the "PatientID" combo box
on the related form when it is opened from the "Operation" form.

Any ideas??
Thanks in advance
Anthony

 

[Jeff Boyce]

Anthony

I'm having a bit of trouble visualizing what your underlying data structure
looks like. Forms can be built on anything (or nothing), so info about the
forms and their relationships doesn't provide quite enough.

I would point out that it is possible (and given an appropriately-relational
db design, even easy) to use a mainform/subform design that saves having to
select by making ALL related info available. Coupled with a tab control,
you can have a main form for selecting a Patient, and each subform could be
placed on a different tab.

Again, this is with an admittedly limited understanding of your data.

 

[Anthony]

Hi Jeff. Thanks for your response.

All forms mentioned in my original posting have underlying tables that are
related by a PatientID field. The first form/table is called "Patient
Details" and, as its name suggests, contains data on each of the patient. A
command button is pressed to open the "Operation" form, which contains data
on operations performed on each patient and also has its own underlying
table. Within this form, the "OperationType" field is a combo box containing
some values that, when selected, result in one of three forms being opened
after a command button is pressed. The fields in these three forms contain
additional data relating to each operation that is specific to these
operations, therefore, not appropriate to include in the 'general' operation
form/table.

I hope that helps clarify the data structure a bit.

I may need to explore subforms if I can't get the three Operation
'sub-forms' to open at the correct PatientID, but I would prefer to keep
them separate if possible.

Anthony