|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 20th, 2010, 10:38 PM
posted to microsoft.public.access
|
|||
|
|||
delete sub and stay on same parent record
access 2003
Main form has a option group Subform is a continuous form if user changes the option group the subform data is deleted. see code... Private Sub optCTorSTorGEN_AfterUpdate() If Me.NewRecord = False Then strMsg = "Are you sure you want to change?" _ & vbCr & vbCr & "This will erase the corresponding data to allow for new input." ans = MsgBox(strMsg, vbYesNo, "Confirm Changes") If ans = vbNo Then Me.Undo Me.optCTorSTorGEN.Requery MsgBox "The data has NOT been changed.", vbExclamation, "Important" Me.UnitNo.Requery Exit Sub Else 'Prepare for deleting record Me.f019WarrantyDetails.Form.AllowDeletions = True Dim strPONumber As String Dim strSQL As String strWarrantyUnitID = WarrantyUnitID.Value strSQL = "DELETE * FROM [t72WarrantyDetails] " _ & "WHERE [t72WarrantyDetails].WarrantyUnitID = " _ & strWarrantyUnitID CurrentDb.Execute strSQL, dbFailOnError ' Me.Requery ' Me.UnitNo.Requery End If End If If I take out the me.requery then user gets #Deleted in all fields. pretty ugly If I leave the me.requery it sets the record back to the first record in the parent form. User will get confused How can I delete the subform data and have it remain on the current parent record with cleared fields? Please help this is beyond my capabilities. -- deb 
|
|
#2
April 20th, 2010, 10:47 PM
posted to microsoft.public.access
|
|||
|
|||
|
delete sub and stay on same parent record
"deb" wrote in message
... access 2003 Main form has a option group Subform is a continuous form if user changes the option group the subform data is deleted. see code... Private Sub optCTorSTorGEN_AfterUpdate() If Me.NewRecord = False Then strMsg = "Are you sure you want to change?" _ & vbCr & vbCr & "This will erase the corresponding data to allow for new input." ans = MsgBox(strMsg, vbYesNo, "Confirm Changes") If ans = vbNo Then Me.Undo Me.optCTorSTorGEN.Requery MsgBox "The data has NOT been changed.", vbExclamation, "Important" Me.UnitNo.Requery Exit Sub Else 'Prepare for deleting record Me.f019WarrantyDetails.Form.AllowDeletions = True Dim strPONumber As String Dim strSQL As String strWarrantyUnitID = WarrantyUnitID.Value strSQL = "DELETE * FROM [t72WarrantyDetails] " _ & "WHERE [t72WarrantyDetails].WarrantyUnitID = " _ & strWarrantyUnitID CurrentDb.Execute strSQL, dbFailOnError ' Me.Requery ' Me.UnitNo.Requery End If End If If I take out the me.requery then user gets #Deleted in all fields. pretty ugly If I leave the me.requery it sets the record back to the first record in the parent form. User will get confused How can I delete the subform data and have it remain on the current parent record with cleared fields? Please help this is beyond my capabilities. Don't requery the main form, requery the subform. Instead of: ' Me.Requery .... say: Me.f019WarrantyDetails.Requery As a side note, I don't think this line: Me.f019WarrantyDetails.Form.AllowDeletions = True .... is necessary or desirable. You aren't deleting records through the subform, but rather via the delete query you are executing. The subform's AllowDeletions property is irrelevant to that. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
|
|
#3
April 20th, 2010, 10:58 PM
posted to microsoft.public.access
|
|||
|
|||
|
delete sub and stay on same parent record
Like this;
Private Sub optCTorSTorGEN_AfterUpdate() If Me.NewRecord = False Then strMsg = "Are you sure you want to change?" _ & vbCr & vbCr & "This will erase the corresponding data to allow for new input." ans = MsgBox(strMsg, vbYesNo, "Confirm Changes") If ans = vbNo Then Me.Undo Me.optCTorSTorGEN.Requery MsgBox "The data has NOT been changed.", vbExclamation, "Important" Me.UnitNo.Requery Exit Sub Else 'Grab the pk of the current record on the parent form Dim intPK As Integer intPK = Me!PKField 'Prepare for deleting record Me.f019WarrantyDetails.Form.AllowDeletions = True Dim strPONumber As String Dim strSQL As String strWarrantyUnitID = WarrantyUnitID.Value strSQL = "DELETE * FROM [t72WarrantyDetails] " _ & "WHERE [t72WarrantyDetails].WarrantyUnitID = " _ & strWarrantyUnitID CurrentDb.Execute strSQL, dbFailOnError Me.Requery 'Move back to the record we were on before With Me.RecordsetClone .FindFirst "PKField = " & intPK If Not .NoMatch Then Me.Bookmark = .Bookmark End With End If End If I have assumed that your pk filed is an integer data type. If not you'll need to modify the variable declaration and the .FindFirst line in the With statement. -- _________ Sean Bailey "deb" wrote: access 2003 Main form has a option group Subform is a continuous form if user changes the option group the subform data is deleted. see code... Private Sub optCTorSTorGEN_AfterUpdate() If Me.NewRecord = False Then strMsg = "Are you sure you want to change?" _ & vbCr & vbCr & "This will erase the corresponding data to allow for new input." ans = MsgBox(strMsg, vbYesNo, "Confirm Changes") If ans = vbNo Then Me.Undo Me.optCTorSTorGEN.Requery MsgBox "The data has NOT been changed.", vbExclamation, "Important" Me.UnitNo.Requery Exit Sub Else 'Prepare for deleting record Me.f019WarrantyDetails.Form.AllowDeletions = True Dim strPONumber As String Dim strSQL As String strWarrantyUnitID = WarrantyUnitID.Value strSQL = "DELETE * FROM [t72WarrantyDetails] " _ & "WHERE [t72WarrantyDetails].WarrantyUnitID = " _ & strWarrantyUnitID CurrentDb.Execute strSQL, dbFailOnError ' Me.Requery ' Me.UnitNo.Requery End If End If If I take out the me.requery then user gets #Deleted in all fields. pretty ugly If I leave the me.requery it sets the record back to the first record in the parent form. User will get confused How can I delete the subform data and have it remain on the current parent record with cleared fields? Please help this is beyond my capabilities. -- deb
|
|
#4
April 20th, 2010, 11:01 PM
posted to microsoft.public.access
|
|||
|
|||
|
delete sub and stay on same parent record
Actually, Dirk has the better answer (not surprising).
-- _________ Sean Bailey "deb" wrote: access 2003 Main form has a option group Subform is a continuous form if user changes the option group the subform data is deleted. see code... Private Sub optCTorSTorGEN_AfterUpdate() If Me.NewRecord = False Then strMsg = "Are you sure you want to change?" _ & vbCr & vbCr & "This will erase the corresponding data to allow for new input." ans = MsgBox(strMsg, vbYesNo, "Confirm Changes") If ans = vbNo Then Me.Undo Me.optCTorSTorGEN.Requery MsgBox "The data has NOT been changed.", vbExclamation, "Important" Me.UnitNo.Requery Exit Sub Else 'Prepare for deleting record Me.f019WarrantyDetails.Form.AllowDeletions = True Dim strPONumber As String Dim strSQL As String strWarrantyUnitID = WarrantyUnitID.Value strSQL = "DELETE * FROM [t72WarrantyDetails] " _ & "WHERE [t72WarrantyDetails].WarrantyUnitID = " _ & strWarrantyUnitID CurrentDb.Execute strSQL, dbFailOnError ' Me.Requery ' Me.UnitNo.Requery End If End If If I take out the me.requery then user gets #Deleted in all fields. pretty ugly If I leave the me.requery it sets the record back to the first record in the parent form. User will get confused How can I delete the subform data and have it remain on the current parent record with cleared fields? Please help this is beyond my capabilities. -- deb
|
|
#5
April 20th, 2010, 11:39 PM
posted to microsoft.public.access
|
|||
|
|||
|
delete sub and stay on same parent record
thank you, for knowing where to hit the machine -- deb "Dirk Goldgar" wrote: "deb" wrote in message ... access 2003 Main form has a option group Subform is a continuous form if user changes the option group the subform data is deleted. see code... Private Sub optCTorSTorGEN_AfterUpdate() If Me.NewRecord = False Then strMsg = "Are you sure you want to change?" _ & vbCr & vbCr & "This will erase the corresponding data to allow for new input." ans = MsgBox(strMsg, vbYesNo, "Confirm Changes") If ans = vbNo Then Me.Undo Me.optCTorSTorGEN.Requery MsgBox "The data has NOT been changed.", vbExclamation, "Important" Me.UnitNo.Requery Exit Sub Else 'Prepare for deleting record Me.f019WarrantyDetails.Form.AllowDeletions = True Dim strPONumber As String Dim strSQL As String strWarrantyUnitID = WarrantyUnitID.Value strSQL = "DELETE * FROM [t72WarrantyDetails] " _ & "WHERE [t72WarrantyDetails].WarrantyUnitID = " _ & strWarrantyUnitID CurrentDb.Execute strSQL, dbFailOnError ' Me.Requery ' Me.UnitNo.Requery End If End If If I take out the me.requery then user gets #Deleted in all fields. pretty ugly If I leave the me.requery it sets the record back to the first record in the parent form. User will get confused How can I delete the subform data and have it remain on the current parent record with cleared fields? Please help this is beyond my capabilities. Don't requery the main form, requery the subform. Instead of: ' Me.Requery ... say: Me.f019WarrantyDetails.Requery As a side note, I don't think this line: Me.f019WarrantyDetails.Form.AllowDeletions = True ... is necessary or desirable. You aren't deleting records through the subform, but rather via the delete query you are executing. The subform's AllowDeletions property is irrelevant to that. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
