replace a line in the array and overwrite the file

[prgm]

Hello all,

I am trying to create and appln. where the data/records of individuals are listed inthe list box from a tex file.When the user clicks the particular record..it takes to another form and displays the name and id of the person selected and there is also an textbox where the user just enters some comment..i want those comments to be appended back to the same persons record and also saved to the file back(by over-writing the existing one)..i have tried to code it..but i have a problem here..when i try to append it back...it..replaces the first record of the file and not exactly to the selected line..Please help me..its really urgent...

Hope so i am clear to u.. am attaching my code..please lemme know soon..

mY TEXT FILE FORMAT IS :

JIM 4564656 CALIFORNIA|TOM 2234253 WISCONSIN|CAROL 889832 WASHINGTON

...AND SO ON..

FORM 1:
-------

' Defining the public member to be used in Form2

Public selIndex As Integer


Private Sub Command1_Click()

Dim i As Integer
selIndex = List1.Selected(i)


If List1.ListIndex = -1 Then Exit Sub

For i = List1.ListCount - 1 To 0 Step -1

If List1.Selected(i) = True Then

Form2.Show
End If

Next i

End Sub

Private Sub Form_load()

Dim selIndex As Integer
Dim nFileNum As Integer
Dim rep As Integer
Dim sNextLine As String
Dim lLineCount As Integer

' Get a free file number
nFileNum = FreeFile()

' Open Test.txt for input. App.Path returns the path your app is saved in
Open App.Path & "\Test.txt" For Input As FreeFile

sNextLine = Input(LOF(nFileNum), #nFileNum)
Close #(nFileNum)

sText = Split(sNextLine, "|")

List1.Clear
For rep = 0 To UBound(sText)
List1.AddItem sText(rep)
Next
End Sub




FORM2:
--------

Private Sub Command1_Click()

Dim userEnteredVal As String
nameStr = txtName.Text
MsgBox ("name" + nameStr)
childID = txtChildId.Text
MsgBox ("id" + childID)
locStr = txtLocn.Text
MsgBox ("locaiton" + locStr)
addStr = txtAddComment.Text
MsgBox ("add" + addStr)

' Creating the string based on user input
userEnteredVal = nameStr + " " + childID + " " + locStr + " " + addStr
MsgBox ("user" + userEnteredVal)
Dim rep As Integer
Dim str As String
str = ""

' Hiding the form
Form1.Hide


' Setting the new string into the array
'sText(mySelIndex) = userEnteredVal

' Creating the final string to be written to the file
For rep = 0 To UBound(sText)
If (rep = UBound(sText)) Then
MsgBox ("before" + str)
str = str + sText(rep)
MsgBox (str)
Else
str = str + sText(rep) + "|"
MsgBox (str)
End If
Next

MsgBox (" the str is " + str)

' Overwritting the file text

Open App.Path & "\test.txt" For Output As #1
Print #1, str

Close #1


' Clearing the contents of the list
Form1.List1.Clear

' Re-creating the list
For rep = 0 To UBound(sText)
Form1.List1.AddItem sText(rep)
Next


End Sub




Private Sub Form_load()

Dim mySplit() As String

' Getting values from Form1
mySelArr = sText


mySelIndex = Form1.selIndex

' Getting the selected String
mySelStr = Form1.List1.Text
MsgBox ("selected text" + mySelStr)


' Spliting the value based on space
mySplit = Split(mySelStr, " ")
' Getting the name, childid and location string
nameStr = mySplit(0)
MsgBox (nameStr)
childID = mySplit(1)
locStr = mySplit(2)

' Getting the additional data also

If UBound(mySplit) > 3 Then
MsgBox ("I am inside the 4th Element")
addStr = mySplit(3)
End If




' Printing the name
txtName.Text = nameStr

' Printing the Child ID
txtChildId.Text = childID

txtLocn.Text = locStr

txtAddComment.Text = addStr

End Sub





thanks a ton....
Pooja

 

[rmikesmith]

Pooja,

I hate to rain on your parade, but attempting to use a text file as a database file will result in a lot of extra work, at the very least! Inefficient, as well. You've already recognized one drawback: every time the user changes a record, the entire data set must be written back to the file. I am going to suggest the use of a random access file setup (since this will be somewhat similar to what you are already doing; still not the best way to go, but...).

For the following example code, I am using two Userforms, Form1 & Form2. Form1 contains a single ListBox (lstRecords) that displays the Name, ID & Location fields of each record. It is set up for 4 columns with the first having its width set to 0 to make it invisible. This column is used to hold the record number. Form2 contains: a TextBox (txtRecNum) to display the record number (with Locked=true to make it read-only), and four additional TextBoxes to display the Name, ID, Location and Comment fields; a CommandButton to save changed information. Form2 is launched & populated with data whenever the user clicks a record in lstRecords of Form1.

Form1's code module:

Dim Records() As RecType



Private Sub cmdClose_Click()
   Unload Me
End Sub


Private Sub lstRecords_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' Using this event because neither the Change nor Click events
' worked consistently.  Not sure why.
   RecNum = lstRecords.Value
   OneRec = Records(RecNum)
   Me.Hide
   Form2.Show
End Sub

Private Sub UserForm_Activate()
' Propagate changed data back to array & ListBox
' but don't execute on initial loading of form
   If IgnoreEvent Then Exit Sub
   Records(RecNum) = OneRec
   PopulateListbox
   
End Sub

Private Sub UserForm_Initialize()
Dim TotalRecs As Integer

   GetAllRecords Records, TotalRecs
   
   If TotalRecs = 0 Then
     MsgBox "No Records in database."
     Unload Me
   Else
     PopulateListbox
     IgnoreEvent = True
   End If
   
End Sub


Private Sub PopulateListbox()
Dim i As Integer

   On Error Resume Next
   With Me.lstRecords
     .Clear
     For i = LBound(Records) To UBound(Records)
       .AddItem i
       .List(.ListCount - 1, 1) = Records(i).Name
       .List(.ListCount - 1, 2) = Records(i).ID
       .List(.ListCount - 1, 3) = Records(i).Location
     Next i
   End With
End Sub

Form2 code module:

Private Sub cmdCancel_Click()
   Unload Me
   Form1.Show
End Sub


Private Sub cmdSave_Click()

   With OneRec
     .Name = txtName.Text
     .ID = txtID.Text
     .Location = txtLocation.Text
     .Comments = txtComments.Text
   End With
   WriteRecordToFile RecNum, OneRec
   Unload Me
   IgnoreEvent = False
   Form1.Show
   
End Sub


Private Sub UserForm_Initialize()
' Populate Textboxes with data.
' Trim function necessary because of fixed-length
' strings in RecType user-definded type
   txtRecNum.Text = RecNum
   With OneRec
     txtName.Text = Trim(.Name)
     txtID.Text = Trim(.ID)
     txtLocation.Text = Trim(.Location)
     txtComments.Text = Trim(.Comments)
   End With
   
End Sub

Standard code module:

' Make the data filename & extension whatever
Const DBFILE As String = "MyData.dbf"


Public Type RecType
' Change the fixed lengths to whatever is appropriate
  Name As String * 30
  ID As String * 7
  Location As String * 30
  Comments As String * 500 
End Type


Public OneRec As RecType
Public RecNum As Integer
Public IgnoreEvent As Boolean




Sub OpenForm1()
   Form1.Show
End Sub


Sub CreateFile()
' Included to demonstrate how the structured file
' was created, with sample data.
Dim FNum As Long
Dim OneRec As RecType
Dim TotalRecs As Long

   FNum = FreeFile
   Open ThisWorkbook.Path & Application.PathSeparator & DBFILE For Random Access Read Write As #FNum Len = Len(OneRec)
   With OneRec
     .Name = "JIM"
     .ID = "4564656"
     .Location = "CALIFORNIA"
     .Comments = "User comment goes here."
     Put FNum, , OneRec
     
     .Name = "TOM"
     .ID = "2234253"
     .Location = "WISCONSIN"
     .Comments = ""
     Put FNum, , OneRec
     
     .Name = "CAROL"
     .ID = "889832"
     .Location = "WASHINGTON"
     .Comments = "I'm record number 3!"
     Put FNum, , OneRec
   End With
   Close FNum

End Sub


Sub GetAllRecords(ByRef arrRecs() As RecType, ByRef TotalRecs As Integer)
Dim FNum As Long
Dim i As Integer
   
   FNum = FreeFile
   Open ThisWorkbook.Path & Application.PathSeparator & DBFILE For Random Access Read Write As FNum Len = Len(OneRec)
   TotalRecs = LOF(FNum) / Len(OneRec)
   ReDim arrRecs(1 To TotalRecs)
   For i = 1 To TotalRecs
     RecNum = RecNum + 1
     Get FNum, RecNum, arrRecs(i)
   Next i
   Close FNum
   
End Sub


Sub WriteRecordToFile(ByVal RecordNumber As Integer, ByRef RecordToWrite As RecType)
Dim FNum As Long

   FNum = FreeFile
   Open ThisWorkbook.Path & Application.PathSeparator & DBFILE For Random Access Read Write As FNum Len = Len(RecordToWrite)
   Put FNum, RecordNumber, RecordToWrite
   Close FNum
   
End Sub

No error checking is included. Also, there is no code to add and delete records but this should give you a place to start.


Regards,
Mike