A
Anthony
Hi
My database contains five forms, which each have underlying tables linked by
a field called "PatientID". The user enters data into the first form, then
hits a command button to go to the second form, then (most of the time) hits
another command button to preview a report containing all the information
entered in the forms.
However, the second form, "Operation", contains a combo box called
"OperationType1". There are seven items on the drop-down list of this combo
box, but three of these values, when selected, result in the user being
taken to another form once they hit the command button to open the report,
so that they may enter additional operation specific to the type of
operation selected. This set up works fine for adding new patients or
viewing data for existing patients.
To allow the user to change the type of operation in the "OperationType1"
field, I added some code (see below) in the before update section of this
control's property sheet.
Private Sub OperationType_BeforeUpdate(Cancel As Integer)
Dim stDocNameX As String
stDocNameX = "Macro1"
Dim stDocName As String
Dim StDocNameA As String
Dim StDocNameB As String
Dim stLinkCriteria As String
stDocName = "DHS Form"
StDocNameA = "Carpal Form"
StDocNameB = "AntStab Form"
stLinkCriteria = "[PatientID]=" & Me!PatientID
Dim var1 As Variant
Dim var2 As Variant
Dim var3 As Variant
var1 = DLookup("[PatientID]", "DHSOp", "[PatientID]=" & Me!PatientID)
var2 = DLookup("[PatientID]", "CarpalOp", "[PatientID]=" & Me!PatientID)
var3 = DLookup("[PatientID]", "AntStabOp", "[PatientID]=" &
Me!PatientID)
If (Not IsNull(var1)) Then
MsgBox "To change this operation, you must first delete its
associated record in the DHSOp table!!"
Cancel = True
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close
Else
If ([OperationType1] = "dynamic hipscrew and plate") Then
'this next line is here just so that I know that I've reached
this section of code
MsgBox "You have reached the macro subroutine"
Cancel = True
DoCmd.RunMacro stDocNameX
' DoCmd.Close
***snip***
This code allows me to change (for an existing patient) from a 'linked'
value on the drop-down list (i.e., a value which triggers the opening of the
"DHS Form", "Carpal Form" or "AntStab Form") to an 'unlinked' value, but for
some reason it will not allow me to change the value in the drop-down list
in "OperationType1" from an 'unlinked' value to a 'linked' value. When
changing from an unlinked value to a linked value, a macro is activated
which uses the SetValue command to take me to the correct value in the
'linked' form.
The problem is that the value in "OperationType1" does not update to the new
value after it has been changed. I change the value of "OperationType", the
macro runs and takes me to the correct record on the appropriate 'linked'
form. I then press a command button on this form to preview the report and I
find that the [OperationType1] has not updated, nor has it been updated in
the underlying table.
Any ideas why this would occur???
Thanks in advance
Anthony
My database contains five forms, which each have underlying tables linked by
a field called "PatientID". The user enters data into the first form, then
hits a command button to go to the second form, then (most of the time) hits
another command button to preview a report containing all the information
entered in the forms.
However, the second form, "Operation", contains a combo box called
"OperationType1". There are seven items on the drop-down list of this combo
box, but three of these values, when selected, result in the user being
taken to another form once they hit the command button to open the report,
so that they may enter additional operation specific to the type of
operation selected. This set up works fine for adding new patients or
viewing data for existing patients.
To allow the user to change the type of operation in the "OperationType1"
field, I added some code (see below) in the before update section of this
control's property sheet.
Private Sub OperationType_BeforeUpdate(Cancel As Integer)
Dim stDocNameX As String
stDocNameX = "Macro1"
Dim stDocName As String
Dim StDocNameA As String
Dim StDocNameB As String
Dim stLinkCriteria As String
stDocName = "DHS Form"
StDocNameA = "Carpal Form"
StDocNameB = "AntStab Form"
stLinkCriteria = "[PatientID]=" & Me!PatientID
Dim var1 As Variant
Dim var2 As Variant
Dim var3 As Variant
var1 = DLookup("[PatientID]", "DHSOp", "[PatientID]=" & Me!PatientID)
var2 = DLookup("[PatientID]", "CarpalOp", "[PatientID]=" & Me!PatientID)
var3 = DLookup("[PatientID]", "AntStabOp", "[PatientID]=" &
Me!PatientID)
If (Not IsNull(var1)) Then
MsgBox "To change this operation, you must first delete its
associated record in the DHSOp table!!"
Cancel = True
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close
Else
If ([OperationType1] = "dynamic hipscrew and plate") Then
'this next line is here just so that I know that I've reached
this section of code
MsgBox "You have reached the macro subroutine"
Cancel = True
DoCmd.RunMacro stDocNameX
' DoCmd.Close
***snip***
This code allows me to change (for an existing patient) from a 'linked'
value on the drop-down list (i.e., a value which triggers the opening of the
"DHS Form", "Carpal Form" or "AntStab Form") to an 'unlinked' value, but for
some reason it will not allow me to change the value in the drop-down list
in "OperationType1" from an 'unlinked' value to a 'linked' value. When
changing from an unlinked value to a linked value, a macro is activated
which uses the SetValue command to take me to the correct value in the
'linked' form.
The problem is that the value in "OperationType1" does not update to the new
value after it has been changed. I change the value of "OperationType", the
macro runs and takes me to the correct record on the appropriate 'linked'
form. I then press a command button on this form to preview the report and I
find that the [OperationType1] has not updated, nor has it been updated in
the underlying table.
Any ideas why this would occur???
Thanks in advance
Anthony