IF Statement

[DS]

I have this If statement that works great. Except for the third option.
On the third option I need to delete all records that have the same
LineID as the one I am deleting. Thanks for any suggestions.

Private Sub Command74_Click()
If [ItemType] = "Mod" And [Text70] > 1 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Refresh
ElseIf [ItemType] = "Main" And [Text70] = 1 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Refresh
ElseIf [ItemType] = "Main" And [Text70] > 1 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
' Where [LineID] = [Text72]
Me.Refresh
End If

End Sub

DS

 

[Reg]

Hi DS,

The best way to delete all records with a specified LineID is to issue a SQL
statement. In your case, the statement you need is:

CurrentDb.Execute "DELETE FROM TableName WHERE [LineID]=" & [Text72],
dbFailOnError

You will need to replace TableName with the name of whatever table you are
deleting from.

Reg

 

[DS]

Hi DS,

The best way to delete all records with a specified LineID is to issue a SQL
statement. In your case, the statement you need is:

CurrentDb.Execute "DELETE FROM TableName WHERE [LineID]=" & [Text72],
dbFailOnError

You will need to replace TableName with the name of whatever table you are
deleting from.

Reg

I have this If statement that works great. Except for the third option.
On the third option I need to delete all records that have the same LineID
as the one I am deleting. Thanks for any suggestions.

Private Sub Command74_Click()
If [ItemType] = "Mod" And [Text70] > 1 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Refresh
ElseIf [ItemType] = "Main" And [Text70] = 1 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Refresh
ElseIf [ItemType] = "Main" And [Text70] > 1 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
' Where [LineID] = [Text72]
Me.Refresh
End If

End Sub

DS

Reg
Heres what I did, but it deleted the fields in the records but not the
records thenselves!!!!! Thanks
DS


Private Sub Command74_Click()
If [ItemType] = "Mod" And [Text70] > 1 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Refresh
ElseIf [ItemType] = "Main" And [Text70] = 1 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Refresh
ElseIf [ItemType] = "Main" And [Text70] > 1 Then
CurrentDb.Execute "DELETE FROM SalesDetails WHERE [LineID]=" & [Text72]

Me.Refresh
End If

End Sub

 

[Douglas J. Steele]

What do you mean? Are you saying that there are blank rows in the table now?
Or are you saying that the display didn't reduce in size? I'm assuming the
latter. You might try Me.Requery instead of (or in addition to) Me.Refresh.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Reg
Heres what I did, but it deleted the fields in the records but not the
records thenselves!!!!! Thanks
DS


Private Sub Command74_Click()
If [ItemType] = "Mod" And [Text70] > 1 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Refresh
ElseIf [ItemType] = "Main" And [Text70] = 1 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Refresh
ElseIf [ItemType] = "Main" And [Text70] > 1 Then
CurrentDb.Execute "DELETE FROM SalesDetails WHERE [LineID]=" &
[Text72]

Me.Refresh
End If

End Sub

 

[DS]

What do you mean? Are you saying that there are blank rows in the table now?
Or are you saying that the display didn't reduce in size? I'm assuming the
latter. You might try Me.Requery instead of (or in addition to) Me.Refresh.

No. Not blank rows but blank fields that say "deleted". I changer
Refresh to Requery and that did the trick!
Thank You for your help.
DS

 

[Van T. Dinh]

Change your Refresh at the end to Requery as per Douglas' advice.