cannot get form to load appropriate data -- unless it is in design view!

  • Thread starter Lee Taylor-Vaughan
  • Start date
L

Lee Taylor-Vaughan

Hi group.

I am trying to double-click an entry on a form and open another form based
on that entry.
However, it only works when the form that is wanted to be open is in design
view. the code i am using is as follows.

why would this code only work when "frmActivity" is in open in design view?
the rest of the code for the frmActivity is included at the end of this
post.

Private Sub OpenMission()
On Error GoTo Err_cmdOpenMission_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stControlNumber As String

stControlNumber = Me.lstDispMain

stDocName = "frmActivity"

stLinkCriteria = "[ControlNumberID]=" & "'" & stControlNumber & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenMission_Click:
Exit Sub

Err_cmdOpenMission_Click:
MsgBox Err.Description
Resume Exit_cmdOpenMission_Click

End Sub

Thanks
Lee




other code for rest of form: (its long!)

Option Compare Database
Option Explicit

Private Sub ShowHideFields()
'used to show appropriate fields on activity form.
'set to DispatchType combo box - after update_event and form current.
'd/c'd per d.smith - only pt transports are to be entered in to this db
'other data is obtained from REMS dispatch. (per d.smith) (1.18.04)

Me.RefFacility.Enabled = False

Select Case Me.DispatchType

Case "Cancel: Enroute to Scene"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "Cancel: On Scene"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = True
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "Cancel: Prior to Lift Off"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "Denial: Mechanical"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "Denial: Weather"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "Denial: Sick Crew Member"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "Denial: On Mission"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "Denial: Not Medically Appropriate"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False
Case "Fuel"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "Pt Transported: From Scene"
Me.chkExtrication.Enabled = True
Me.chkIndustrial.Enabled = True
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = True
Me.TimePtContact.Enabled = True
Me.TimeDispatchScene.Enabled = True
Me.TimeDispatchArrivalHosp.Enabled = True
Me.TimeDispatchLeaveHosp.Enabled = True
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = True
Me.lstActivityPatient.Visible = True
Me.lblPatientNote.Visible = True
Me.lblPatientNoteAdd.Visible = True

Case "Pt Transported: Interfacility"
Me.chkExtrication.Enabled = True
Me.chkIndustrial.Enabled = True
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = True
Me.TimePtContact.Enabled = True
Me.TimeDispatchScene.Enabled = True
Me.TimeDispatchArrivalHosp.Enabled = True
Me.TimeDispatchLeaveHosp.Enabled = True
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = True
Me.lstActivityPatient.Visible = True
Me.RefFacility.Enabled = True
Me.lblPatientNote.Visible = True
Me.lblPatientNoteAdd.Visible = True

Case "Reposition"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "Training"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = True
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "General Police"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "Maintainence"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = False
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case "Photo Shoot"
Me.chkExtrication.Enabled = False
Me.chkIndustrial.Enabled = False
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = False
Me.TimePtContact.Enabled = False
Me.TimeDispatchScene.Enabled = True
Me.TimeDispatchArrivalHosp.Enabled = False
Me.TimeDispatchLeaveHosp.Enabled = False
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = False
Me.lstActivityPatient.Visible = False
Me.lblPatientNote.Visible = False
Me.lblPatientNoteAdd.Visible = False

Case Else
Me.chkExtrication.Enabled = True
Me.chkIndustrial.Enabled = True
Me.TimeDispatchLiftOff.Enabled = True
Me.TimeDispatchOnScene.Enabled = True
Me.TimePtContact.Enabled = True
Me.TimeDispatchScene.Enabled = True
Me.TimeDispatchArrivalHosp.Enabled = True
Me.TimeDispatchLeaveHosp.Enabled = True
Me.TimeDispatchAvailable.Enabled = True
Me.cmdAddPatient.Visible = True
Me.lstActivityPatient.Visible = True

End Select

End Sub

Private Sub cmdAddPatient_Click()

OpenPatientNew

End Sub

Private Sub ControlNumberID_AfterUpdate() ''this does not work either.....
If Not IsNull(Me.ControlNumberID) Then Me.cmdAddPatient.Visible = True
If IsNull(Me.ControlNumberID) Then Me.cmdAddPatient.Visible = False

End Sub

Private Sub ControlNumberID_BeforeUpdate(Cancel As Integer)
On Error GoTo CNError

If Not IsNull(Me.ControlNumberID) Then
With Me.RecordsetClone
.FindFirst "[ControlNumberID] = '" & Me![ControlNumberID] & "'"

If Not .NoMatch Then
If MsgBox("There is already a record entered in the database
with this Control Number." _
& Chr(13) & "Click the 'OK' button to go to this record
now;" _
& "Click cancel to clear your entry and to enter a
different Control Number.") = vbOK _
Then
Cancel = True
Me.Undo
Me.Bookmark = .Bookmark
Else
Cancel = True
Me.ControlNumberID.Undo
End If
End If
End With
End If

ExitSub:
Exit Sub

CNError:
MsgBox Err.Number & " " & Err.Description
Resume ExitSub

End Sub

Private Sub datedispatch_DblClick(Cancel As Integer)
Me.DateDispatch = Date
End Sub

Private Sub DispatchType_AfterUpdate()
ShowHideFields
End Sub

Private Sub DispatchType_DblClick(Cancel As Integer)
Me.DispatchType = "Pt Transported: From Scene"
End Sub

Private Sub Form_Activate()
Me.Refresh
Me.lstActivityPatient.Requery
End Sub

Sub Form_Current()
On Error GoTo Form_Current_Err

'''If IsNull(Me.ControlNumberID) Or Not Me.ControlNumberID = "" Then
Me.cmdDeleteMission.Visible = False
'''If IsNull(Me.ControlNumberID) Or Me.ControlNumberID = "" Then
Me.cmdAddPatient.Visible = False
'''If Not IsNull(Me.ControlNumberID) Or Not Me.ControlNumberID = "" Then
Me.lstActivityPatient.Requery

ShowHideFields

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub


Private Sub OpenPatient()
On Error GoTo Err_OpenPatient_Click

If Not IsNull(Me.ControlNumberID) Then

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPatients"

stLinkCriteria = "[ControlNumberID]=" & "'" & Me![ControlNumberID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!frmpatients.ControlNumberID.Enabled = False
Forms!frmpatients.PatientRecID.Enabled = False
Else
MsgBox "You must enter the Control Number for this mission", vbCritical,
"Missing Data"
Me.ControlNumberID.SetFocus
End If


Exit_OpenPatient_Click:
Exit Sub

Err_OpenPatient_Click:
MsgBox Err.Description
Resume Exit_OpenPatient_Click

End Sub

Private Sub OpenPatientNew()
On Error GoTo Err_OpenPatient_Click

RunCommand acCmdSaveRecord

If Not IsNull(Me.ControlNumberID) Then

Dim stDocName As String
stDocName = "frmPatients"
DoCmd.OpenForm stDocName, , , , acFormAdd
Forms!frmpatients.ControlNumberID.Enabled = False
Forms!frmpatients.PatientRecID.Enabled = False
Else
MsgBox "You must enter the Control Number for this mission", vbCritical,
"Missing Data"
Me.ControlNumberID.SetFocus
End If

Exit_OpenPatient_Click:
Exit Sub

Err_OpenPatient_Click:
MsgBox Err.Description
Resume Exit_OpenPatient_Click

End Sub

Private Sub Form_GotFocus()
Me.lstActivityPatient.Requery
End Sub

Private Sub IncidentLocationCity_NotInList(strNewData As String, iresponse
As Integer)

Dim strMsgResp As String
Dim strInputState As String
Dim strInputCounty As String
Dim strMsgConf As String
Dim dbCurrent As DAO.Database
Dim rsData As DAO.Recordset
DoCmd.DoMenuItem A_FORMBAR, A_EDIT, A_UNDOFIELD, , A_MENU_VER20

strMsgResp = MsgBox("The City you have entered is not in the list;" _
& "do you wish to add it?", vbQuestion + vbYesNo, _
"City Not Found")


If strMsgResp = vbYes Then

strInputState = InputBox("Enter the State where this City (" _
& strNewData & ") is located" & Chr(13) & Chr(13) _
& "Enter Only the State Initials. I.e. NJ, PA, NY, CT" _
, "Adding New City", "NJ")

strInputCounty = InputBox("Enter the County where this City (" _
& strNewData & ") is located", "Adding New City")


strMsgConf = MsgBox("Is this correct?" _
& Chr(13) & Chr(13) & "Adding City: " & strNewData & Chr(13) _
& "State: " & strInputState & Chr(13) & "County: " _
& strInputCounty, vbInformation + vbYesNo, "Confirmation")

If strMsgConf = vbYes Then


Set dbCurrent = CurrentDB
Set rsData = dbCurrent.OpenRecordset("DispatchCity")

With rsData

.AddNew
!CityName = UCase(strNewData)

!State = UCase(strInputState)

!County = UCase(strInputCounty)
.Update
.Close
End With

Set dbCurrent = Nothing
Set rsData = Nothing
iresponse = DATA_ERRADDED

Exit Sub
Else
MsgBox "City not added to datafile", vbInformation, "Aborted"
Me.IncidentLocationCity = ""
iresponse = acDataErrContinue
Exit Sub
End If

iresponse = acDataErrContinue

Else
MsgBox "You must select a value from the list", vbInformation +
vbOKOnly, "Not Valid"
Me.IncidentLocationCity = Null
iresponse = acDataErrContinue
End If

End Sub

Private Sub IncidentLocationState_AfterUpdate()
Me.IncidentLocationCity.Requery
End Sub

Private Sub lstActivityPatient_DblClick(Cancel As Integer)
If Not IsNull(Me.lstActivityPatient) Then
OpenPatient
Else
OpenPatientNew
End If
End Sub
Private Sub cmdSaveClose_Click()
On Error GoTo Err_cmdSaveClose_Click

DoCmd.Close

Exit_cmdSaveClose_Click:
Exit Sub

Err_cmdSaveClose_Click:
MsgBox Err.Description
Resume Exit_cmdSaveClose_Click

End Sub
Private Sub cmdDeleteMission_Click()
On Error GoTo Err_cmdDeleteMission_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close
Exit_cmdDeleteMission_Click:
Exit Sub

Err_cmdDeleteMission_Click:
Select Case Err.Number
Case 3200
MsgBox "You cannot delete this record as there is/are associated
patient/s." & _
Chr(13) & "Delete patient/s first, and then retry deletion of this
mission.", vbInformation, "Cannot Erase, yet!"
Case Else
MsgBox Err.Number & Err.Description
End Select

Resume Exit_cmdDeleteMission_Click


End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
VBA - exporting serial letters to single documents 0
Update a date field in sub-form from dates fields on main form 1
Worked in Windows 2000 but not XP 0
Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
excel importing data 16
still having trouble 0
VBA works in orignal but fails in copy of database 1

Top