kennys
asked on
Deleteing records from a dBase table
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("SE
'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("Hi
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("Se
'*************************
‘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("SE
'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
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("SE
'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("Hi
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("Se
'*************************
‘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("SE
'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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Edited text of question.
ASKER
Edited text of question.
dim sCrit as criteria should be
dim sCrit as String
Sorry for the error.
dim sCrit as String
Sorry for the error.
ASKER
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("SE
'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
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("SE
'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
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.
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.
Correction:
Recordset.MoveFirst
Do Until Recordset.EOF
If Recordset!RegNo = "ABC 333" Then
Recordset.Delete
Recordset.Update
End If
Recordset.MoveNext
Loop
Recordset.MoveFirst
Do Until Recordset.EOF
If Recordset!RegNo = "ABC 333" Then
Recordset.Delete
Recordset.Update
End If
Recordset.MoveNext
Loop
ASKER
"user defined type not defined"