"Delete" record from random access file

[girish_mu]

Hello! I have the following coding for the user defined type.

Option Explicit
Type Employee
    Emp_ID As Integer
    Name As String * 25
End Type

I use the following to show the records I have (10 in all) in the text boxes, txtID and txtName.

Dim Recrd As Employee
Dim RecNo As Long
Dim fnum As Integer
Dim CurrentRecord As Long

Private Sub Form_Load()

    'Get the length of a record
    RecNo = Len(Recrd)
    'Get a free file number
    fnum = FreeFile
    'Open the file storing the information, if it does not exist create it
    Open App.Path & "/Employee.dat" For Random As #fnum Len = RecNo
    'Update the current record
    CurrentRecord = 1
    'Get the last record
    RecNo = LOF(fnum) / Len(Recrd)
    'If the file has only 1 record
    If LastRecord = 0 Then
        LastRecord = 1
    End If
    'Fill the textboxes with record 1
    ShowCurrentRecord
    
End Sub

Private Sub ShowCurrentRecord()
    If CurrentRecord = 0 Then
        MsgBox "no records in file"
        Exit Sub
    End If
    'This procedure fills the textboxes with the data
    Get #fnum, CurrentRecord, Recrd
    'Display the information
    txtID.Text = Trim(Recrd.Emp_ID)
    txtName.Text = Trim(Recrd.Name)
End Sub

I have two buttons that allow to move through the records:

Private Sub Command2_Click()
    'Check if we have reached end of file
    If CurrentRecord = RecNo Then
        MsgBox "You have reached the End of the File.", vbExclamation, "Eagle Supermarket"
        Exit Sub
    End If
    'Go to next record
    CurrentRecord = CurrentRecord + 1
    ShowCurrentRecord
End Sub

Private Sub Command3_Click()
    'Check if we are at the beginning of the file
    If CurrentRecord = 1 Then
        MsgBox "You are at the Beginning of File.", vbExclamation, "Eagle Supermarket"
        Exit Sub
    End If
    'Go to previous record
    CurrentRecord = CurrentRecord - 1
    ShowCurrentRecord
End Sub

Well, I have two problems when I need to 'delete' a record...in fact, I need to copy all the records,except the one I have to 'delete',in a file and then rename this temporary file. But the problem is that the record to be deleted becomes blank and when I run the program form again, the record that should have been 'deleted' is filled with different characters (some weird ones too...)....
My second problem is that if ever the first record is deleted, how would I do to load the first record....the first record has Emp_ID 1 and it increments automatically. So, if the record with Emp_ID 1 is deleted, what should I do to correctly load the file into the textboxes and then be able to use the two buttons that allow to move backward and forward... The code for the Delete button is as follows:

Private Sub cmdDelete_Click()
    'Defines new record structure
    Dim Recrd1 As Employee

    Dim RecNo1 As Long 'Stores the number of records in file
    Dim fnum1 As Integer
    'Allocate a free file number
    fnum1 = FreeFile
    'Open the random access file
    Open App.Path & "/Employee_temp.dat" For Random As #fnum1 Len = Len(Recrd)
    'Find the number of records currently in the file
    RecNo1 = LOF(fnum1) / Len(Recrd1)
   
    
    Dim rec As Long
    Dim count As Integer
    Dim recrd_id As Integer
    recrd_id = Recrd.Emp_ID
    
    If Recrd.Emp_ID = 1 And RecNo = 1 Then
        CurrentRecord = 0
    End If
    
    count = 1
    rec = 0
    
    Do While count <= RecNo
        Get #fnum, count, Recrd
        If Recrd.Emp_ID = recrd_id Then
            count = count + 1
            Get #fnum, count, Recrd
        End If
        Recrd1.Emp_ID = Recrd.Emp_ID
        Recrd1.Name = Recrd.Name
        Put #fnum1, Recrd1.Emp_ID, Recrd1
        MsgBox Recrd1.Emp_ID
        MsgBox Recrd1.Name
        rec = rec + 1
        count = count + 1
    Loop
    Close #fnum1
    Close #fnum
    
    FileCopy App.Path & "\Employee_temp.dat", App.Path & "\Employee.dat"
    Kill App.Path & "\Employee_temp.dat"
    Open App.Path & "\Employee.dat" For Random As #fnum Len = RecNo
    ShowCurrentRecord
    
End Sub

[Andrea_Rossini]

When you

Put #fnum1, Recrd1.Emp_ID, Recrd1

you will move the file cursor to the location specified by Recrd1.Emp_ID. this will create a blank record... ie if you delete record 5 you will write record 4 correctly but you will write record 6 in position 6. It must be 5.

you should use a count1 variable

    count = 1
    count1 = 1
    rec = 0
    
    Do While count <= RecNo
        Get #fnum, count, Recrd
        If Recrd.Emp_ID = recrd_id Then
            count = count + 1
            Get #fnum, count, Recrd
        End If
        Recrd1.Emp_ID = Recrd.Emp_ID
        Recrd1.Name = Recrd.Name
        ' Put #fnum1, Recrd1.Emp_ID, Recrd1
        Put #fnum1, count1, Recrd1
        MsgBox Recrd1.Emp_ID
        MsgBox Recrd1.Name
        rec = rec + 1
        count = count + 1
        count1 = count1 + 1
    Loop
    Close #fnum1
    Close #fnum

[dglienna]

Why not just add another field to each record - RcdDeleted ?

That way, you could remove them all regularly, and rebuild the keys then.

[girish_mu]

Huh,I didn't quite understand u...any algorithm or coding concernin ur solution dglienna??

[dglienna]

Just insert another field, call it [IsDeleted]. Make it a True/False type.
Set it to FALSE for every record. In the delete routine, just set that field to True.

When you read records in, you just ignore the ones that are deleted. Don't put them into the grid! Not IsDeleted

This preserves the original autonumber field.

[aio]

Just insert another field, call it [IsDeleted]. Make it a True/False type.
Set it to FALSE for every record. In the delete routine, just set that field to True.

When you read records in, you just ignore the ones that are deleted. Don't put them into the grid! Not IsDeleted

This preserves the original autonumber field.

Agree - marking a record as deleted is the usual (or normal) operation in random access file because you cannot "physically" delete a specific record. It must only be "ignored"

[girish_mu]

Hmm,i see....but I really needed to show all the fields....n I have used z previous solution by Andrea_Rossini. Thank you!

[Rich2189]

You can loop through all the records from the record you want to delete so if you have 4 records and you delete number 2

for i = 2 to reccount -1

record(i) = record(i+1)

next i

Then you use the set end of file pointer api to remove the last empty record.

Public Sub removeLastRecord(fileName As String, recordLen As Integer)
If fileName = vbNullString Or recordLen <= 0 Then

	Catch 0
Else

	Dim numberOfRecords As Integer

	numberOfRecords = FileLen(fileName) \ recordLen

	If numberOfRecords < 2 Then

		DeleteFile fileName

	Else
	
		Dim hFile As Long
		Dim FileSizeLow As Long
		Dim FileSizeHigh As Long
	
		hFile = CreateFile(fileName, GENERIC_WRITE Or GENERIC_READ, 0&, ByVal 0&, OPEN_ALWAYS, FILE_ATTRIBUTE_NORMAL, 0&)
	
		FileSizeLow = GetFileSize(hFile, FileSizeHigh)
	
		If dwFileSizeHigh = 0 Then
			
		
			If SetFilePointer(hFile, FileSizeLow - recordLen, FileSizeHigh, FILE_BEGIN) > 0 Then
		 
				'move the file pointer
				SetEndOfFile hFile
			End If
		 
		Else
			Catch 1
	
		End If
		
		CloseHandle hFile
		
	End If
End If

End Sub