Form/Subform - No Advance if no subform entry 1

[BobJacksonNCI]

Hello!

I previously posted - thread958-733421 - but still have the problem that Users can use navigation buttons (standard) to advance to a new record repeatedly with no subform records created. This leaves orphans in the "one" table.

Environment = Access 2000 Project with SQL 7 engine.

Forms are used to enter new records and view/edit existing records.

Using a Form populated based on Select statement with parameter from another form (employee selection). Subform uses a stored procedure with parameter to maintain synchronization with the main form.

Example: User wants to enter a new Time Off request.
Selects employee in selection form.
Selects button that brings up Time Off form/subform.
(Each form can have up to eight subform records.)
Logic finds existing Time Off records and presents the most recent one.
User uses > button, or >* button until form with blank subform appears.
User inadvertently selects > again, resulting in a new form record with blank subform (ready for data entry).
BUT, there has already been stored a form record with no subform record - which is what I want to avoid.

I started experimenting with creating my own navigation buttons. These are on the form, so this approach will likely require working with a recordset clone - which I use in the subform for validation.

But I'm wondering if I can't manipulate the Allow Additions property to stop these unwanted orphans. Keep in mind that I would prefer having navigation buttons at the form level only...

Sorry for the long read - trying to provide all information that is needed.

Thanks in Advance!

 

[BobJacksonNCI]

Roy,

I comprehend: count versus empty recordset.

And I replaced the logic that checks for above when the "close" button is selected as you recommended.

This may be a resolved thread.
Right now I'm tinkering with the code to enable/disable the custom navigation buttons depending on the situation. This is expected to reside entirely in the On Current event code.

Instead of adding Undo to the mix, I chose to include a check for me.newrecord - if true, me.refresh. So the dynamic stored procedure finds the rascal. I discovered this necessity this morning during testing.

I'm posting to provide a status update for you and also in case others searching for answers might benefit. Since these modifications won't be "live" until next week, I can't guarantee the process, yet.

Will post again when "live" and functioning correctly.
Hopefully, that post will just be a comment and not a code correction!
Bob

Following are portions of code related to the On Current event and the Close form button. Note that the subform module contains only code used to validate data entered.

Private Sub Form_Current()

'CUSTOM NAVIGATION BUTTONS
Dim rc As ADODB.Recordset

'FOLLOWING TO ALLOW USER TO DELETE FORMS THAT HAVE NOT REACHED PAYROLL
If Me.txtStatus = 1 Then
Me.cmdDeleteForm.Visible = True
Else
Me.cmdDeleteForm.Visible = False
End If

'IF FORM STATUS = NEW OR USER = PAYROLL, CAN EDIT.
If txtStatus = 1 Or Me.cmdDeleteForm.Visible = True Then
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If

'FOLLOWING PART OF LOGIC TO REQUIRE USERS TO BUTTON OUT, NOT "X"
booClose = False

If Me.NewRecord Then 'IF NEW RECORD, PREFILL DATA FROM CALLING FORM
Me.lblEdit.Visible = False
Me.lblEnter.Visible = True 'INDICATE DATE ENTRY MODE
Me![txtSSN] = Forms!frm_MAIN!SSN
Me![txtEmpLastName] = Forms!frm_MAIN!EmpLastName
Me![txtEmpLglFirstName] = Forms!frm_MAIN!EmpLglFirstName
Me![txtEmpMidName] = Forms!frm_MAIN!EmpMidName
Me![txtDept] = Forms!frm_MAIN!Dept
Me![txtSupervisor] = Forms!frm_MAIN!txtMGRName

Else 'IF EXISTING RECORD, NO PREFILL AS USER MAY EDIT
Me.lblEdit.Visible = True 'INDICATE EDIT MODE
Me.lblEnter.Visible = False

End If

'ENABLE/DISABLE CUSTOM NAVIGATION BUTTONS
'Make a clone of the recordset underlying the form so we can move around that without affecting
'the form's recordset
Set rc = Me.RecordsetClone

'If this is a new record then disable the <Prev>, <Next> and <New> buttons and enable the others.
'Then exit the procedure. WORK IN PROGRESS
If Me.NewRecord Then
cmdNBfirst.Enabled = True
cmdNBprev.Enabled = False
cmdNBnext.Enabled = False
cmdNBlast.Enabled = True
cmdNBnew.Enabled = False
Exit Sub
End If

'And finally close the cloned recordset
Set rc = Nothing

'LOAD CUSTOM NAVIGATION CONTROL
Me!txtRcdCount.Value = Me.Recordset.RecordCount

End Sub

Private Sub cmdCLOSE_Click()
On Error GoTo Err_cmdCLOSE_Click

'IF THE HELP WINDOW IS OPEN, CLOSE IT
stDocName = "~fhlp_TIME_OFF"
If SysCmd(acSysCmdGetObjectState, acForm, stDocName) = acObjStateOpen Then
DoCmd.Close acForm, stDocName
End If

'IF NEW RECORD ON MAIN FORM AND NO RECORDS ON SUBFORM
'DON'T SAVE PRELOADED RECORD ON MAIN FORM
Dim rs As ADODB.Recordset
Dim stSQLCommand As String

'IN CASE RECORD IN BUFFER NOT YET WRITTEN
If Me.NewRecord Then
Me.Refresh
End If

Set rs = Forms!frm_TIME_OFF!frm_TIME_OFF_SUBFORM.Form.RecordsetClone

If rs.BOF And rs.EOF Then 'BOTH BOF AND EOF = NO RECORDS

stSQLCommand = "ALTER PROCEDURE rjackson.MgrTB_spr_Time_Off_Delete AS "
stSQLCommand = stSQLCommand & "DELETE FROM dbo.MgrTB_tbl_Time_Off WHERE dbo.MgrTB_tbl_Time_Off.FormID_One = '" & [Forms]![frm_TIME_OFF]![txtFormID_One] & "' "
ADOSQLPassThrough stSQLCommand

stSQLCommand = "Execute rjackson.MgrTB_spr_Time_Off_Delete"
ADOSQLPassThrough stSQLCommand

MsgBox "Deleted form because there was no Date Requested entry", vbOKOnly, _
"CLOSE WITH NO DATE REQUESTED"

End If
'FOLLOWING PART OF LOGIC TO REQUIRE USERS TO BUTTON OUT, NOT "X"
booClose = True

DoCmd.Close acForm, "frm_TIME_OFF", acSaveNo

Exit_cmdCLOSE_Click:
Set rs = Nothing
Exit Sub

Err_cmdCLOSE_Click:
MsgBox Err.Description
Resume Exit_cmdCLOSE_Click

End Sub

 

[BobJacksonNCI]

Roy, AceMan1, et al,

The following modifications have been live for two days and seem to be doing exactly what I want. I am reposting the "On Current" code because it's easier than trying to explain what changed and what didn't. NOTE: The Close routine works as posted above. Code not related to the issue was deleted this time. The custom navigation buttons have no code added beyond what is created by the Wizard.

Not much code, but part of the resolution was dealing with the Close form button code.

Thanks Again!
Bob

Private Sub Form_Current()

'CUSTOM NAVIGATION BUTTONS
Dim rst As ADODB.Recordset

'ENABLE/DISABLE CUSTOM NAVIGATION BUTTONS
Set rst = Me.Recordset.Clone(adLockReadOnly)

'TURN ALL BUTTONS ON, LEFT TO RIGHT
cmdNBfirst.Enabled = True
cmdNBprev.Enabled = True
cmdNBnext.Enabled = True
cmdNBlast.Enabled = True
cmdNBnew.Enabled = True

'IF NEW FORM, DISABLE NEXT & NEW BUTTONS
If Me.NewRecord Then
cmdNBnext.Enabled = False
cmdNBnew.Enabled = False
End If

'CLONE INITIATES WITH CURRENT RECORD IDENTIFIED AS FIRST RECORD
'MUST SYNCHRONIZE WITH ACTUAL
rst.Bookmark = Me.Bookmark

'IF FIRST RECORD, CANNOT GO TO PREVIOUS
If rst.AbsolutePosition = 1 Then
cmdNBprev.Enabled = False
End If

'IF LAST RECORD, CANNOT GO TO NEXT
If rst.AbsolutePosition = rst.RecordCount Then
cmdNBnext.Enabled = False
End If

Set rst = Nothing

'LOAD CUSTOM NAVIGATION CONTROL
Me!txtRcdCount.Value = Me.Recordset.RecordCount

End Sub