Solved

Deleteing records from a dBase table

Posted on 2000-03-31
8
213 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

911 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

21 Experts available now in Live!

Get 1:1 Help Now