Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Deleteing records from a dBase table

Posted on 2000-03-31
8
215 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:kennys
ID: 2676292
Edited text of question.
0
 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SCCM2012 sp2 CU3 cant see boot images missing tabs 3 93
Excel - Save a copy of work book 13 88
How to make an ADE file by code? 11 94
IT Desktop Support 11 90
This collection of functions covers all the normal rounding methods of just about any numeric value.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

808 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