One liner to find the position/index of a value in list box??

[JB510]

I have a list of 400 or more serial numbers in a list box called Listbox1.


I have a source SerialNumberToFind with a value in it.

I want a command like :
UnitNumber = Me.ComboSNs.ListIndex(SerialNumberToFind)
and it return the position it is in the Listbox.

Ex. SerialNumberToFind = "B5SN123456"
That is in the list box at location 345.
UnitNumber would be 345.

The issue is it don't like the one liner above and I don't want to loop through with a compare each time because I have many units to look up and a loop takes lots of cycles.

Is there a one liner?

[Micron]

A sql statement or query that returns a record with the SN value? Of course, the record would also need a field that returns the unique line number in that field, and that field probably should not be an autonumber field.

[Bulzie]

Is there a reason you need it to work like this instead of maybe a combo box where they type in that SerialNumbertoFind value and just have it select that record? Are you wanting the logic to have it automatically go to that SN in the list?

[orange]

JB510,

What exactly is the business process that is involved? There may be options f readers understood the requirement in context.
Micron and Bulzie have responded and are seeking a little more direction also.

[JB510]

I have a database that stores serial numbers for a company. It tracks individual PCBAs and when they are received, tested, and grouped together into a final part serial number.
Ex. PCBA1 = SN1, PCBA2=SN2 etc.. The three PCBAs get stacked together in a product and they are referenced under a single new MasterSN1.
My database has a SerialNumber table.
In it, an individual SN field and a MasterSN field.
When individual cards come in they are recorded with just the SN equal to the Serial number of the PCBA (MasterSN blank) and an Active field as True.
When the PCBAs get put together, the original records are checked off Active (=False) and copied to thee new similar records where Active = True and the MasterSN is now in active in all three records with unique numbers.
A history of all product in, out, sold, stacked etc. can be recorded.

My Query:
The stacked PCBAs are tested on a testbed under a build number which stores them in a txt file. (The txt file contains all stacked SNs, starting with a unique masterSN for each unit.)
When you want to close a build, you type in the build number and the database pulls all the MasterSNs from the txt file into a list box to show the user how many were recorded tested and passed.
The user verifies this quantity is correct and hits a macro button that has to go get all the individual SNs including the masterSN and puts it into an array. That button creates an array that will store SerialNumber1(x), SerialNumber2(x), etc, MasterSN(x) where x it the location in the list box shown on the screen listbox. I can then move the file records as mentioned above to record the history.

For now I am using a loop to search for each unit to get the index (x) but I would like to just ask a one liner to look up the MasterSN in the list box to get the index value.

[orange]

For clarity-- can you confirm or revise the following?

are PCBAs /cards the same thing? 
 Your company receives, tests, and assembles PCBs into a final part/product/unit
 Each PCBA has a unique serial number. Ex. PCBA1 = SN1, PCBA2=SN2 etc.. 
 Each PCBA is marked Active =False by default 
 Three PCBAs get stacked together in a product and  are referenced under a single new MasterSN1.
 Somewhere the MasterSerial Number is recorded with the individual PCBA serial numbers associated with this product
 MasterSerial number is assigned when the 3 PCBAs are combined/stacked in a Product/Unit ??Is this a build??
 At same time those 3 PCBAs are changed  to Active = True
 
You can have PCBAs that are not part of a Product/Unit
Each  such PCBA will have a unique SerialNumber and be marked Active = False

======
Can you post a graphic of your database tables and relationships?

[June7]

Just how are you interacting with this text file? Have you set a table link to it?

A graphic of schema might be helpful. Or could provide files for analysis.