Populating a listbox from sequential data from a .dat file

[thomasfmendiola]

Hi All,

I have been trying to create a form that allows a user to view all records in a .dat file . I then want to populate the list box on the form with the phone number and names contained in the .dat file, I am also trying to incorporate error handling when opening the .dat file. I have been working on this for a few hours and keep getting an error. And at this point I am at a loss as to what I am doing wrong. Feel free to explain to me what I am doing wrong because I would rather know the code then just have a solution provided to me. Thanks in advance. Also I have attached a .jpg of the error box. Here is what I have:

Private Sub Form_Load()

On Error GoTo ErrorHandler

Dim lsContactPhoneNumber As String
Dim lsContactFirstName As String
Dim lsContactLastName As String

Open "C:\Users\Thomas F. Mendiola\Documents\friends.dat" For Input As #1

Do While EOF(1)
Input #1, lsContactPhoneNumber, lsContactFirstName, lsContactLastName
lstFriends.AddItem lsContactPhoneNumber, lsContactFirstName, lsContactLastName
Loop
Close
End Sub

 

[DCrake]

Can you provide a copy of the dat file?

 

[thomasfmendiola]

Attached is a copy of the .dat file converted to a .txt. Thanks again.

 

[DCrake]

Just a quicky Is it not Line Input #1, .....

 

[DJkarl]

Line Input wouldn't work with the method that's trying to be used, Input # works to populate the individual variables where Line Input you would have to split the line into the individual records yourself.

I think it might be blowing up on your file path, you have spaces in the folder path, you may need to surround the filepath with double quotes. To test this try moving the file to something like C:\Temp and see if it imports.

 

[ChrisO]

List box…

Row Source Type: Value List
Column Count: 3
Column Widths: 3cm;3cm;3cm

Private Sub Form_Load()
    Dim lsContactPhoneNumber As String
    Dim lsContactFirstName   As String
    Dim lsContactLastName    As String
    
    Open "C:\friends.txt" For Input As #1
    
    Do While Not EOF(1)
        Input #1, lsContactPhoneNumber, lsContactFirstName, lsContactLastName
        lstFriends.AddItem lsContactPhoneNumber & ";" & lsContactFirstName & ";" & lsContactLastName
    Loop
    
    Close #1

End Sub

Hope that helps.

Chris.

 

[DCrake]

Was only aware today that there was a difference between Line Input and Input. This just proves know one knows everything.

 

[thomasfmendiola]

Thanks all for your input. I used the code that ChrisO provided and copy & pasted and the same error came up. So I ended up just deleting the form I initially was using and when I pasted the code into a clean new form it worked flawlessly! Thanks again for all of your help!!!

Thomas

 

[ChrisO]

Your original code:

Do While EOF(1)
Not a good idea to loop while you are at end of file.
It did not cause an error because the file had some data in it.
If you open a file with data you will not be at end of file and therefore not enter the loop.


lstFriends.AddItem lsContactPhoneNumber, lsContactFirstName, lsContactLastName
This was the source of the error.
Note that AddItem is singular, you can only add one item (and an index if you like)
If you have more than one item then they need to be concatenated, with a list separator, to make one item.
If you compile your code it would have produced a compile error.
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Wrong number of arguments or invalid property assignment
---------------------------
OK Help
---------------------------


Close
This works but may work too well; it closes all Open file handles.
Best to specify which file handle you wish to close.
However, if you encounter an error and wish to cleanup then Close, without file handle, is probably the best thing to do.

Chris.

 

[thomasfmendiola]

Chris,

Thanks so much for your time and explanation of my error. I understand now why AddItem produced the error and this will aid me in avoiding that kind of mistake in the future. Again, your knowledge is greatly appreciated.

Thomas

 

[ChrisO]

No problem but all I did was fix the code.

You may wish to look at the situation in another way altogether.
This file is being loaded into a Value List of a list box and there may be limits as to the size of the list. Furthermore, you may want to sort what the list box displays and doing that is not easy with a Value List.

So in the long run you may want to load the file into a table, query the table and base the list box on the query.

Just a thought.

Chris.

 

[LPurvis]

Hi all.

A little late to this party I realize.
That's the exact format of the file from which you're pulling in values?
Can't you just query it?
Three column combo with the RowSourceType property back to "Table/Query".

SELECT * FROM [Text;FMT=Delimited;HDR=No;DATABASE=C:\Data\YourPath;].[Friends#txt]

If you're uncertain of the path, then assign that property in code after validating the file location.
(Or load a recordset with that same query and then - when it returns records - assign it to the combo control.
Set Me.cboName.Recordset = rst).

Cheers.

 

[ChrisO]

Well, yes you can Leigh (When you know how. )

The file is in post #3.

To overcome the sorting problem I changed the file to have a first row of “X”,”Y”,”Z”.

Then: -
lstFriends.RowSource = "SELECT * FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\;].[Friends.txt] Order By Z"

This is one of the reasons I tend not to play in the SQL forums.

Thanks Leigh,
Chris.

 

[LPurvis]

Cool.

Fortunately, we can sort by ordinal position too, so the column aliases can be optional too.

lstFriends.RowSource = "SELECT * FROM [Text;FMT=Delimited;HDR=No;DATABASE=C:\;].[Friends.txt] Order By 3"

Cheers.

(Edit: Sorry, hadn't realized there was a sorting requirement... Just dipping in really. Not really here. You ain't seen me - right? ;-)

 

[ChrisO]

Thanks Leigh.

No, there was no sorting requirement, just having a bit of fun with this.

So if we want Column Heads in the List Box as well we use: -

    lstFriends.RowSource = " SELECT" & _
                           "     F1 As [Phone]," & _
                           "     F2 As [First Name]," & _
                           "     F3 As [Last Name]" & _
                           " FROM [Text;FMT=Delimited;HDR=No;DATABASE=C:\;].[Friends.txt]" & _
                           " ORDER BY 3"

It’s 3:25 here (on the wrong side of the clock) so I’m off to bed.

Might try some speed tests a little later today.

Thanks again Leigh (If you were here ),
Chris.

 

[LPurvis]

3:25 AM. Yoinks, that sounds like me before I became rubbish (i.e. stopped a substantial intake of caffeine lol).

Cheers.