P
Pat Dools
Hello, I have a clinical research trials database which sometimes requires
that the user navigates from one form to another, instead of returning back
to a Main Switchboard, from which all forms are accessible. I use the
following code to navigate to the next form (‘FCycDoseVitalAmpFU’), after
validating that certain required fields are filled in. This code is behind a
Command Button in the footer of the form (fCycDoseVitalAmp’):
Private Sub CommandGoToAmpFU_Click()
If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.cyclenum.Value & "") = 0 Then
MsgBox "You must enter a value into Cycle."
Me.cyclenum.SetFocus
ElseIf Len(Me.vs_Adoseday.Value & "") = 0 Then
MsgBox "You must enter a value into Cycle."
Me.vs_Adoseday.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord acDataForm, "FCycDoseVitalAmpFU", acNewRec
Call ClearControls(Me)
DoCmd.GoToControl "nofollowup"
DoCmd.Close acForm, "fCycDoseVitalAmp"
End If
End Sub
Then, every form looks up to a form (‘fEnterPatientInfo’) and fills in key
Header fields, and this code is in the ‘On Current’ Event of every form. It
first checks if it is a New Record, and if so, looks up the values and does
not lock the controls on that form, otherwise it does lock all controls.
Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
Call LockControls(Me, False)
Else
Call LockControls(Me, True)
End If
End Sub
Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err
' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!id = Forms!fEnterPatientInfo!id
!ptin = Forms!fEnterPatientInfo!ptin
!site = Forms!fEnterPatientInfo!site
End With
SetAutoValues_err:
'MsgBox Err.Description
Resume Next
End Sub
Public Sub LockControls(frm As Form, LockValue As Boolean)
On Error Resume Next
Dim ctl As Control
For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox
ctl.Locked = LockValue
Case acComboBox
ctl.Locked = LockValue
Case acListBox
ctl.Locked = LockValue
Case acCheckBox
ctl.Locked = LockValue
Case acToggleButton
ctl.Locked = LockValue
Case acCommandButton
ctl.Locked = LockValue
Case acSubform
ctl.Locked = LockValue
Case acOptionGroup
ctl.Locked = LockValue
Case acOptionButton
ctl.Locked = LockValue
End Select
End With
Next ctl
Set ctl = Nothing
Set frm = Nothing
End Sub
There are a couple of things that aren’t working correctly. First, with the
debugger turned on, when this line of code is hit in the ‘Private Sub
CommandGoToAmpFU_Click()’ sub, I get the message ‘The Command or action
SaveRecord isn’t available now’. Second, when the next form is opened, we
get ALL the records that have been entered using that form
(‘FCycDoseVitalAmpFU’), instead of just the records pertaining to the patient
currently active in the lookup form (‘fEnterPatientInfo’). That is to say
you can navigate through ALL records using the Record Navigator buttons,
instead of just the ones that share the same patient ID info. If you enter
the form ‘FCycDoseVitalAmpFU’ from the Main Switchboard, then you see only
the records that match the patient in the lookup form using the standard
MSAccess Record Navigation buttons. In debugging this, it looks like when
‘FCycDoseVitalAmpFU’ is opened, it is not seen as a new record at first, goes
thru and locks all controls, THEN goes thru the ‘SetAutoValues’ code for new
records and makes sure the controls on the form are not locked, if that is
even possible. In any case, I’m wondering what is incorrect about this code
stream that is preventing me from navigating from one form to the next and
still only have the correct patient records show (those matching the patient
currently active in the lookup form (‘fEnterPatientInfo’), instead of having
ALL records entered using that form visible when using the Record Navigation
buttons? Thx, Patrick
that the user navigates from one form to another, instead of returning back
to a Main Switchboard, from which all forms are accessible. I use the
following code to navigate to the next form (‘FCycDoseVitalAmpFU’), after
validating that certain required fields are filled in. This code is behind a
Command Button in the footer of the form (fCycDoseVitalAmp’):
Private Sub CommandGoToAmpFU_Click()
If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.cyclenum.Value & "") = 0 Then
MsgBox "You must enter a value into Cycle."
Me.cyclenum.SetFocus
ElseIf Len(Me.vs_Adoseday.Value & "") = 0 Then
MsgBox "You must enter a value into Cycle."
Me.vs_Adoseday.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord acDataForm, "FCycDoseVitalAmpFU", acNewRec
Call ClearControls(Me)
DoCmd.GoToControl "nofollowup"
DoCmd.Close acForm, "fCycDoseVitalAmp"
End If
End Sub
Then, every form looks up to a form (‘fEnterPatientInfo’) and fills in key
Header fields, and this code is in the ‘On Current’ Event of every form. It
first checks if it is a New Record, and if so, looks up the values and does
not lock the controls on that form, otherwise it does lock all controls.
Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
Call LockControls(Me, False)
Else
Call LockControls(Me, True)
End If
End Sub
Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err
' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!id = Forms!fEnterPatientInfo!id
!ptin = Forms!fEnterPatientInfo!ptin
!site = Forms!fEnterPatientInfo!site
End With
SetAutoValues_err:
'MsgBox Err.Description
Resume Next
End Sub
Public Sub LockControls(frm As Form, LockValue As Boolean)
On Error Resume Next
Dim ctl As Control
For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox
ctl.Locked = LockValue
Case acComboBox
ctl.Locked = LockValue
Case acListBox
ctl.Locked = LockValue
Case acCheckBox
ctl.Locked = LockValue
Case acToggleButton
ctl.Locked = LockValue
Case acCommandButton
ctl.Locked = LockValue
Case acSubform
ctl.Locked = LockValue
Case acOptionGroup
ctl.Locked = LockValue
Case acOptionButton
ctl.Locked = LockValue
End Select
End With
Next ctl
Set ctl = Nothing
Set frm = Nothing
End Sub
There are a couple of things that aren’t working correctly. First, with the
debugger turned on, when this line of code is hit in the ‘Private Sub
CommandGoToAmpFU_Click()’ sub, I get the message ‘The Command or action
SaveRecord isn’t available now’. Second, when the next form is opened, we
get ALL the records that have been entered using that form
(‘FCycDoseVitalAmpFU’), instead of just the records pertaining to the patient
currently active in the lookup form (‘fEnterPatientInfo’). That is to say
you can navigate through ALL records using the Record Navigator buttons,
instead of just the ones that share the same patient ID info. If you enter
the form ‘FCycDoseVitalAmpFU’ from the Main Switchboard, then you see only
the records that match the patient in the lookup form using the standard
MSAccess Record Navigation buttons. In debugging this, it looks like when
‘FCycDoseVitalAmpFU’ is opened, it is not seen as a new record at first, goes
thru and locks all controls, THEN goes thru the ‘SetAutoValues’ code for new
records and makes sure the controls on the form are not locked, if that is
even possible. In any case, I’m wondering what is incorrect about this code
stream that is preventing me from navigating from one form to the next and
still only have the correct patient records show (those matching the patient
currently active in the lookup form (‘fEnterPatientInfo’), instead of having
ALL records entered using that form visible when using the Record Navigation
buttons? Thx, Patrick