Copy Items from Listbox into Text Fields

[lucour]

Hi,

I am using some VBA code to extract values from a text file, and store them in a listbox in my Access form. These are unbound listboxes. Is there a way (preferably using VBA) that I can copy each item out of the listbox into corresponding text box fields ?

Thanks !

 

[WayneRyan]

lucour,

I don't know if you have a multi-select listbox, or if you wanted to copy
only the select item(s) to textboxes.

How would you do multi-select?

Need more info.

It probably looks something like the following, but which row (I used i)
and which column?

Me.txtSomeTextBox = Me.lstSomeListBox.ItemData(i).Column(2)

Wayne

 

[lucour]

Not sure if I was clear enough in my original post. My VBA code imports tape numbers from a text file. This listbox looks like this:

LANBK1111
LANBK2222
LANBK3333
LANBK4444

This is an unbound listbox. I want some code to copy each tape number in the listbox to a corresponding text field. These text fields will be bound fields from a table (so I can store, and create reports).

LANBK1111 would be copied into txtField1
LANBK2222 would be copied into txtField2
LANBK3333 would be copied into txtField3

I would like this all to happen automatically via code.

Thanks !

 

[WayneRyan]

lucour,

OK, but why use the listbox as an intermediate point.

Why not just import the names into a table directly. Then you can
deal with them with queries (or whatever).

Basically, you put them in a listbox, you get them out!

Honestly, I need more info here. What are you trying to do? I don't think
that it was that easy to get them into the listbox.

?
Wayne

 

[lucour]

Hi WayneRyan,

If I can get by without using the listbox as the intermediate point, all the better. It is just that the original VBA code that I found was using a listbox.

What am I trying to do ? I need to search through a text file for every occurence of the string 'LANBK'. To the right of this, there is a 4 digit numerical tape number (ie: LANBK1111 or LANBK2222). When the first occurence of LANBKnnnn is found, I want to copy this into txtField1 of my record ........ when the second occurence of LANBKnnnn is found, I want to copy into txtField2 of my record...... when the third occurence of LANBKnnnn is found, I want to copy into txtField3 of my record........ and so on..... and so on....... PLEASE NOTE THAT I NEED ALL OF THESE TO STAY WITHIN THE SAME RECORD !!!

Thanks !

 

[WayneRyan]

Lucour,

That sounds easy enough, but all in one record!!??!!

What if there are hundreds of them? You actually couldn't put that
many fields in a record. Even if you could, you'd have great trouble
working with them.

Something like this should work for extracting them into a table.

Dim dbs As Database
Dim rst As Recordset
Dim buf As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("YourTable")
Open "C:\SomeFile.txt" For Input As #1

Line Input #1, buf
While Not EOF(1)
   If Instr(1, buf, "LANBK") > 1 then
      rst.AddNew
      rst!TapeNumber = Mid(Buf, Instr(1, buf, "LANBK") + 5, 4)
      rst.Update
   End If
   Line INput #1, buf
   Wend
Close #1

Wayne

 

[lucour]

Hi WayneRyan,

Why do I have to have them all in the same record ? Let me explain: The LANBKnnnn are tape numbers that are used in our Production Intel Server backups on a nightly basis. We identify a backup set by date, and all of the tapes used on a particular date would be the set. For example, a backup kicks off on July 12th, 2004, and tapes LANBK1111, LANBK2222, LANBK3333, and LANBK4444 are used. Each of these tapes comprise the set of backup tapes that were used on July 12th. The date is the unique identifier for each backup. So, as you can see, I cannot have tapes spread over separate records. The date and the tapes have to stay together as a group.

BTW,

Your code worked fine to get the tapes into a table, however.

Thanks !