Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
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
 
Old October 9th, 2003, 12:44 PM
Authorized User
 
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default [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
 
Old October 9th, 2003, 02:53 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How many records do you have in the database? Is it deleting all of your records?

Sal
 
Old October 9th, 2003, 03:06 PM
Authorized User
 
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 9th, 2003, 03:56 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 9th, 2003, 08:15 PM
Authorized User
 
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 10th, 2003, 08:19 AM
Authorized User
 
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm still working around it and do not understand is the code incorrect?

slypunk
 
Old October 10th, 2003, 08:52 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 10th, 2003, 11:18 AM
Authorized User
 
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 10th, 2003, 12:40 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What do you mean by another code running?
Could you please show that code?



Sal
 
Old October 10th, 2003, 01:42 PM
Authorized User
 
Join Date: Jun 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to delete a piece of code Charlie05 C# 2005 2 August 3rd, 2008 11:32 AM
Delete MS Access Foreign Key from Code pkipe Access 3 February 21st, 2006 06:45 PM
pg190 edit/delete code for 'people' table ?? jeronimo BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 0 June 28th, 2004 01:29 AM
Urgent: Implement Cascade Delete through Code rcmetta BOOK: Professional Crystal Reports for VS.NET 0 June 7th, 2004 12:49 AM
delete problem hosefo81 PHP Databases 1 March 7th, 2004 07:40 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.