 |
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

October 9th, 2003, 12:44 PM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
[b][/b]Delete code problem! PLEEEEEEEASE HELP!
Hello,
I am using ADO to create my delete code in an Access Project. The form I am using is a list box with contract numbers. If I wish to delete one I simply select it a message box pops up asking me whether I would like to delete the number I say yes. Now it does the delete the selected but I have not been able to figure out why it also delete the first record. Any ideas I would greatly appreciate.
Thank you:D
Here is my code:
Private Sub cmdDelete_Click()
Dim rs As New ADODB.Recordset
Dim strContNo As String
'Get contract Number
Form_frmEnterContNo.lstContNo.SetFocus
strContNo = Form_frmEnterContNo.lstContNo.ItemData(Form_frmEnt erContNo.lstContNo.ListIndex) & "'"
' Check to make sure contract was selected
If strContNo = "" Then
MsgBox "You must select a Contract Number to delete!", vbInformation
Exit Sub
End If
rs.Open "Select * from tblContractNo where ContractNo='" & strContNo, CurrentProject.Connection, _
adOpenStatic, adLockOptimistic
With rs
' Check record status
If .RecordCount > 1 Then
MsgBox "You are trying to delete more than record", vbCritical
ElseIf .RecordCount = 0 Then
.MoveFirst
MsgBox "Contract Number " & strContNo & " not found in database!", vbInformation
ElseIf vbYes = MsgBox("Are you sure you want to delete " & strContNo & "?", vbYesNo) Then
' Delete record
Do
.Delete
.MoveNext
'--Will use if needed--
'If .EOF Then
'.MovePrevious
'If .BOF Then
'MsgBox "The recordset is empty"
'End If
'End If
'End If--
Loop Until .EOF
End If
'Close record set
.Close
End With
' cleanup:
Set rs = Nothing
End Sub
slypunk
__________________
slypunk
|

October 9th, 2003, 02:53 PM
|
Friend of Wrox
|
|
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
How many records do you have in the database? Is it deleting all of your records?
Sal
|

October 9th, 2003, 03:06 PM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:Originally posted by sal
How many records do you have in the database? Is it deleting all of your records?
Thanks:)
Sal
|
There is about 400 records; it is deleting the record I select(which is only what I want) and it's also deleting the first record (which is what I do not want).
slypunk
|

October 9th, 2003, 03:56 PM
|
Friend of Wrox
|
|
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Is it possible that you are attempting to delete the firs entry on the combo box?
strContNo = Form_frmEnterContNo.lstContNo.ItemData(Form_frmEnt erContNo.lstContNo.ListIndex) & ""
But instead are selecting the Index of the first item, that would be 1 in Access.
Sal
|

October 9th, 2003, 08:15 PM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:Originally posted by sal
Is it possible that you are attempting to delete the firs entry on the combo box?
strContNo = Form_frmEnterContNo.lstContNo.ItemData(Form_frmEnt erContNo.lstContNo.ListIndex) & ""
But instead are selecting the Index of the first item, that would be 1 in Access.
Sal
|
Should I use only the itemdata and not the listindex?
slypunk
|

October 10th, 2003, 08:19 AM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I'm still working around it and do not understand is the code incorrect?
slypunk
|

October 10th, 2003, 08:52 AM
|
Friend of Wrox
|
|
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
In your code
ElseIf .RecordCount = 0 Then
.MoveFirst
I do not thing that you can actually do a move first whrn a recordset.recordcount = 0. There are no records when it is = 0.
Your problem may come from your loop. Try to use:
select case rs.recordcount
' Check record status
case > 1
MsgBox "You are trying to delete more than record", vbCritical
case 0
.MoveFirst
MsgBox "Contract Number " & strContNo & " not found in database!", vbInformation
case else
if vbYes = MsgBox("Are you sure you want to delete " & strContNo & "?", vbYesNo) Then
' Delete record
Do
.Delete
.MoveNext
'--Will use if needed--
'If .EOF Then
'.MovePrevious
'If .BOF Then
'MsgBox "The recordset is empty"
'End If
'End If
'End If--
Loop Until .EOF
End If
end select
also try to uncomment your code for the .EOF and .BOF.
The do loop may be the one giving you the trouble. A do loop does whatever yo tell it and then it checks to see if a condition is met. you need a for loop.
Sal
|

October 10th, 2003, 11:18 AM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I tried it again and it's still deleting the first record. Do you think maybe it's because I have another code that is running and it's causing for the first record to also be deleted? Thank you for your patience I'm still kind of a rookie on this. :(
slypunk
|

October 10th, 2003, 12:40 PM
|
Friend of Wrox
|
|
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
What do you mean by another code running?
Could you please show that code?
Sal
|

October 10th, 2003, 01:42 PM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I thought it was another code but it is not I put breakpoints on all delete commands but my code is fine. Hmmmm....could it be the form's properties?
slypunk
|
|
 |