?
Solved

Deleteing records from a dBase table

Posted on 2000-03-31
8
Medium Priority
?
223 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
[X]
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
  • 4
  • 4
8 Comments
 
LVL 14

Accepted Solution

by:
mgrattan earned 400 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 the different options available in the Backstage view in Excel 2013.

762 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