Link to home
Start Free TrialLog in
Avatar of Eric_Trogdon
Eric_Trogdon

asked on

Problem linking cancel button with nav bar

Apparently I'm still missing something with respect to this bound vs unbound stuff.  Now that I've got my list boxes synchronized both ways with the nav bar I have a new problem with my cancel button.

The idea of the cancel button is to handle the situation where a user starts to add a new record and then decides to bail out.  The code in the onClick for the cancel button resets the form back to the record it was on before the user hit the "add" button (triangle asterisk).

The record set for the form is keyed on tsk_id in descending order with the default being the first record (i.e. greatest value of tsk_id since it's in descending order).

When you start out the default (top) record is highlighted in the list83 list box and the nav bar correctly indicates record 1.  However if you then hit "add" followed by "cancel" you will end up back with the top record highlighted in list83 (correct) but the navbar will indicate the last record (e.g. 309) instead of the first (1).  I have not been able to figure out how to correct this, even after referring to your demo that helped me solve my earlier problem.

The attached code is the onclick for the cancel button.  Again please disregard the commented out code.

Thanks again for your help.
Private Sub Cancel_Add_Click()
  
Dim rs As Object, query As String
 
' Enable Status combo box, Contract, and SD_Number.
' These were disabled in Form_Current for the add new record case.
Me!STATUS.Enabled = True
Me!Contract.Enabled = True
Me!SD_NUMBER.Enabled = True
 
'Re-enable navigation buttons and list boxes
'Me.Recordset.Sort = "tsk_id desc"
'Me.NavigationButtons = True
Me!List83.Enabled = True
Me!TaskNoSearch.Enabled = True
   
''
'Roll back the new record.
'Add is only enabled when the focus is on a Pending/In Work PEST so we need to set the focus back to the right list box (List83).
      
'Me![List83] = SAVE_TSK_ID
'Me![List83].FindFirst "tsk_id = " & SAVE_TSK_ID
    ' Find the record that matches the control.
'     Me.Recordset.FindFirst "tsk_id = " & List83
'Me![TaskNoSearch] = Null
TSK_ID = SAVE_TSK_ID
SAVE_TSK_ID = Null
Me!List83 = TSK_ID
'''
'query = "select * from esis_tsk where tsk_id = " & TSK_ID & ";"
'Set rs = CurrentDb().OpenRecordset(query, dbOpenDynaset, dbSeeChanges)
'Me.TaskType = rs.TaskType
'rs.Close
'Set rs = Nothing
'Me.Recalc
'''
'Me.NavigationButtons = True
'Me!List83.Requery
Me![TaskNoSearch] = Null
'Update List83 to refresh nav bar
 
'CancelInProgress = True
'Me.List83_AfterUpdate
 
'Repopulate the form.
 
'query = "select * from esis_tsk where tsk_id = " & TSK_ID & " order by tsk_id desc;"
query = "select * from esis_tsk where tsk_id = " & TSK_ID & ";"
Set rs = CurrentDb().OpenRecordset(query, dbOpenDynaset, dbSeeChanges)
TASK_PREFIX = rs.PREF
TASK_SUFFIX = rs.SFX
Contract = rs.CONTR_ID
SD_NUMBER = rs.SD_NO
TASK_TITLE = rs.TITLE
TASK_RECEIVED_DATE = rs.RCVD_DT
ACTIVITYID = rs.ACTIVITYID
[Draft TD In Review_Date] = rs.DraftTDInReview_DT
Priority = rs.PRIORITY_ID
[Draft TD Approved_Date] = rs.DraftTDApproved_DT
BasicPESTCost = rs.BasicPESTCost
[CE To Price Estimating Date] = rs.CEToPriceEstimating_DT
Delta1 = rs.Delta1
[Mini Council Date] = rs.MiniCouncil_DT
Delta2 = rs.Delta2
[CE and TD to Customer Date] = rs.CETDToCustomer_DT
Delta3 = rs.Delta3
DUE_DATE = rs.DUE_DT
Delta4 = rs.Delta4
Delta5 = rs.Delta5
STATUS = rs.STAT_ID
TASK_NOTES_1 = rs.NOTE_1
TASK_NOTES_2 = rs.NOTE_2
TaskType = rs.TaskType
ACTIVE_IND = rs.ACTIVE_IND
rs.Close
Set rs = Nothing
 
'Requery the hidden fields
 
Me!TASK_ID.Requery
Me!ACTIVE_IND.Requery
Me!TaskType.Requery
 
'CancelInProgress = False
 
mfSkipCurrent = True
Me.Requery
mfSkipCurrent = False
Me.NavigationButtons = True
'     Me.Recordset.FindFirst "tsk_id = " & List83
 
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Si Ball
Si Ball
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Eric_Trogdon
Eric_Trogdon

ASKER

I'm not sure I understand how the undo is supposed to help.  Once I issue the undo, followed by EndSub, the focus is returned to the form, and the nav bar is still pointing to the wrong record.  Now what?

I also tried the DoCmd.GoToRecord to no effect.  I tried acLast and it had no effect.  When I tried acFirst I got a run-time error 2105 "You can't go to the specified record." ???

I don't think setting everything in the table to required will help me because what I actually have is a linked table that points to a SQL Server database, and I don't want everything to be required.

If you have any furhter suggestions it would be greatly appreciated.

Thanks for your help.
I figured it out.  The key in this case was to issue a Me.undo command to undo the add record before issuing the requery.  It turns out that requery actually does a close followed by an open and the close will commit any pending database transactions (such as an add record).  The undo command undoes the add so you can then do the requery.
nice one.