Solved

Deleteing records from a dBase table

Posted on 2000-03-31
8
212 Views
Last Modified: 2013-11-24
I am a complete beginner to VB6 and attempting to write a database program for a Pal.

My problem is deleting the selected data from the dBase table called FLEET after I have added it to the dBase table called CUSTOMER

I would like to delete the records that were displayed in the listbox lstShowChoice



Private Sub FindCar()
    Dim Name As String
    Dim Address As String
    Dim Registration As String
    Dim DaysOnHire As Integer
    Dim DateOfHire As Date
    Dim TodayDate As Date
   
'Hide and clear the listbox first
lstShowChoice.Visible = False
lstShowChoice.Clear


Set Database = OpenDatabase(PathToDB)
Set Recordset = Database.OpenRecordset("SELECT * FROM Fleet")
   
    'error trap here
    If Recordset.EOF Then MsgBox "This Database is empty ? ", vbOKOnly
    If Recordset.EOF Then Exit Sub
   
    With Recordset
   .MoveLast
   MyTotalCount = .RecordCount
   .MoveFirst

   ' LOOP THROUGH ALL THE RECORDS RETURNED
   For iVal = 0 To MyTotalCount - 1
   
      If !RegNo = txtChoice.Text Then _
      lstShowChoice.AddItem "Registration " + !RegNo + "   Vehicle Type " + _
      Format$(!Type) + "   Mileage " + Format$(!mileage) + "   Daily Rate £" + _
      Format$(!cost) + "   Date of Last Service " + Format$(!LastService)
     
      'Display the choice
      lstShowChoice.Visible = True
   
      .MoveNext
     
   Next iVal
   
   
 
‘The selection is entered into the dBase table  “Hire”

EnterIntoDbase:

    Dim db As Database
    Dim rs As Recordset
    Set Database = OpenDatabase(App.Path & "\Customer.mdb")
    Set rs = Database.OpenRecordset("Hire")
   
    Name = txtName.Text
    Address = txtAddress.Text
    Registration = txtChoice.Text
    DaysOnHire = txtNoOfDays.Text
    DateOfHire = txtDate.Text
   
    rs.AddNew
 
    rs("Name") = Name
    rs("Address") = Address
    rs("RegNo") = Registration
    rs("DaysHire") = DaysOnHire
    rs("Date") = DateOfHire
   
    rs.Update
   
    ' Recordset.Close
     Database.Close

'***************************************
    'now remove the vehicle from        the "Fleet"  table                  
'***************************************
 Data1.DatabaseName = PathToDB
     
    Set Database = OpenDatabase(PathToDB)
    Set rs = Database.OpenRecordset("Select * FROM Fleet")


'***************************************
    ‘I am totally lost here !!!!!!!!!!!!!
'***************************************  

End Sub




While experimenting, I find that by using F8 to step
through this sub, it removes the test record ABC 333.
But when I run the program, it does not remove the record.
Any help gratefully recieved !




Private Sub RemoveRecord()

    '************************************************
    'now remove the vehicle from the "Fleet"  table *
    '************************************************
    'Delete the Record
    PathToDB = App.Path & "/proper.mdb"

    Data1.DatabaseName = PathToDB
   
    Set Database = OpenDatabase(PathToDB)
    Set Recordset = Database.OpenRecordset("SELECT * FROM Fleet")

   
    'if no records remain, exit sub
    If Data1.Recordset.EOF Then Exit Sub
   
   
    'allow records to be deleted
    With Recordset
   .MoveLast
   MyTotalCount = .RecordCount
   .MoveFirst
   ' LOOP THROUGH ALL THE RECORDS RETURNED
   For iVal = 0 To MyTotalCount - 1
   
    If Recordset("RegNo") = "ABC 333" Then Recordset.Delete
   
   Next iVal
   End With



End Sub


0
Comment
Question by:kennys
  • 4
  • 4
8 Comments
 
LVL 14

Accepted Solution

by:
mgrattan earned 100 total points
ID: 2675286
To remove a specific item from a recordset you need to first locate it and then delete it.

Dim db as database, rs as recordset
Dim sCrit as criteria

set db = OpenDatabase("DatabaseName")
set rs = db.openrecordset("TableName")

sCrit = "[FieldName] = " & SomeVariable

rs.FindFirst sCrit

If not rs.eof then
    rs.delete
    rs.update
End If

rs.close
set rs = Nothing
set db = Nothing
 

0
 

Author Comment

by:kennys
ID: 2675801
dim sCrit as Criteria gives an error

"user defined type not defined"
0
 

Author Comment

by:kennys
ID: 2676291
Edited text of question.
0
 

Author Comment

by:kennys
ID: 2676292
Edited text of question.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 14

Expert Comment

by:mgrattan
ID: 2676628
dim sCrit as criteria should be
dim sCrit as String

Sorry for the error.
0
 

Author Comment

by:kennys
ID: 2676734
While experimenting, I find that by using F8 to step
through this sub, it removes the test record ABC 333.
But when I run the program, it does not remove the record.
Any help gratefully recieved !




Private Sub RemoveRecord()

    '************************************************
    'now remove the vehicle from the "Fleet"  table *
    '************************************************
    'Delete the Record
    PathToDB = App.Path & "/proper.mdb"

    Data1.DatabaseName = PathToDB
     
    Set Database = OpenDatabase(PathToDB)
    Set Recordset = Database.OpenRecordset("SELECT * FROM Fleet")

     
    'if no records remain, exit sub
    If Data1.Recordset.EOF Then Exit Sub
   
     
    'allow records to be deleted
    With Recordset
   .MoveLast
   MyTotalCount = .RecordCount
   .MoveFirst
   ' LOOP THROUGH ALL THE RECORDS RETURNED
   For iVal = 0 To MyTotalCount - 1
     
    If Recordset("RegNo") = "ABC 333" Then Recordset.Delete
     
   Next iVal
   End With



End Sub


0
 
LVL 14

Expert Comment

by:mgrattan
ID: 2676766
Hi again kennys!

Instead of looking at each record one at a time you might want to try this:

Dim db as Database,rs as Recordset
Dim sCrit as String

Set db = OpenDatabase(PathToDB)
Set rs = db.OpenRecordset("SELECT * FROM Fleet", dbOpenDynaset)

sCrit = "[RegNo] Like 'ABC 333'"

rs.FindFirst sCrit
If Not rs.NoMatch Then
    rs.delete
    rs.update
End if


I believe the reason your code isn't working is becuase the recordset is not being Updated.  The FindFirst method I outlined will be much quicker than looking at each record one at a time.  Also, for your code to work correctly, there needs to be a Recordset.MoveNext command and it would be better executed as follows:

Do Until Recordset.EOF
    With Recordset
        .MoveFirst
        If Recordset!RegNo = "ABC 333" Then
            .Delete
            .Update
        End If
    End With
    Recordset.MoveNext
Loop

Mike.




0
 
LVL 14

Expert Comment

by:mgrattan
ID: 2676768
Correction:

Recordset.MoveFirst

Do Until Recordset.EOF
    If Recordset!RegNo = "ABC 333" Then
        Recordset.Delete
        Recordset.Update
    End If
    Recordset.MoveNext
Loop

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now