[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Deleteing records from a dBase table

Posted on 2000-03-31
8
Medium Priority
?
225 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

649 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