SQL SELECT trying to use textbox as search criteria and show results in listbox

Hi: Using SQL statements only to open a access database I have been able to load all fields in the recordset into a list box but I only included one field in the select statement. When you click on the cmdLookUp button it loads all 3 of the CompanyName field values in a list box this works:
Private Sub cmdLookUp_Click()
Dim pstrSQL As String
Dim prstCurrent As Recordset
Dim pfldCurrent As Field
Dim pstrLine As String
pstrSQL = "SELECT fldCompanyName FROM tblCustomers"
Set prstCurrent = MR_OS.gdbCurrent.OpenRecordset(pstrSQL)
Do Until prstCurrent.EOF
For Each pfldCurrent In prstCurrent.Fields
pstrLine = pfldCurrent
Next
lstCompanies.AddItem pstrLine
pstrLine = vbNullString
prstCurrent.MoveNext
Loop
End Sub

Now I am trying use a text box to enter search critera into ex: "*" and click the cmdLookUp button and it will show all of the fields in a specific field specified with a WHERE and
a LIKE ... It dont work here it is:

Private Sub cmdLookUp_Click()
Dim pstrSQL As String
Dim prstCurrent As Recordset
Dim pfldCurrent As Field
Dim pstrLine As String
'pstrSQL = "SELECT * FROM tblCustomers WHERE fldCompanyName LIKE & 'txtSelect'"
'pstrSQL = "SELECT * FROM tblCustomers WHERE fldCompanyName LIKE txtSelect"
'pstrSQL = "SELECT * FROM tblCustomers WHERE fldCompanyName LIKE 'txtSelect.Text'"
'pstrSQL = "SELECT * FROM tblCustomers WHERE fldCompanyName LIKE 'txtSelect'"
pstrSQL = "SELECT * FROM tblCustomers WHERE fldCompanyName LIKE '" & txtSelect.Text & "'"
Set prstCurrent = MR_OS.gdbCurrent.OpenRecordset(pstrSQL)
Do Until prstCurrent.EOF
For Each pfldCurrent In prstCurrent.Fields
pstrLine = pfldCurrent
Next
lstCompanies.AddItem pstrLine
pstrLine = vbNullString
prstCurrent.MoveNext
Loop
End Sub

The commented out pstrSQL lines (the entire SQL line should be commented out hope the format dosent mess it up) were
some of the ones Ive tried and none of them worked I tried alot of others also .

I want to be able to enter something into a text box ex: B*
click the look up button find all matching ones from the fldCompanyName and display the results in a listbox

plz help

[Maksim]

Your SQL statement seems correct. If you are sure that records that satisfy the criteria exist then try this:

A Recordset object contains a record pointer, called AbsolutePosition. Try setting it to 1 (the first record in a Recordset) and then incrementing it until it equals to RecordCount, so you loop through each record in your Recordset object. Something like this:

MyRecordset.AbsolutePosition = 1 'Initialize to 1

Do While MyRecordset.AbsolutePosition <= MyRecordset.RecordCount

'Do you stuff here.
'Use MyRecordset.Fields(<whatever field you need>)

Loop

Maybe this will work

Looping thru the recordset is not the problem I dont think. I am able to loop thru it in the first section of code above with
Do Until prstCurrent.EOF

and it lists each all the Company Names stored in the fldCompanyName feilds stored in the recordset into the list box with:

For Each pfldCurrent In prstCurrent.Fields
pstrLine = pfldCurrent
Next
pstrLine = vbNullString
lstCompanies.AddItem pstrLine
prstCurrent.MoveNext
Loop 'end of Do loop

That For Each loops through every feild stored in the recordset but since I only add one Feild fldCompanyName, which there are three records with that field it succsefully looped thru them and listed them into the list box lstCompanies

So I know it is looping thru the record set. I only included SELECT fldCompanyName FROM tblCustomers
so only that one field was added to the recordset from that table (there are only 3 records that have the fldCompanyName field) and all three show in the list box. But the problem is I need to select all fields into the recordset (for furture use) and only add to the list box the fields that meat info input into a text box as search criteria so if asterisk "*"
was typed into the text box(without the quotes of course), then click the look up button it should display all fields from the fldCompanyName as specified in:

pstrSQL = "SELECT * FROM tblCustomers WHERE fldCompanyName LIKE '" & txtSelect.Text & "'"
Set prstCurrent = MR_OS.gdbCurrent.OpenRecordset(pstrSQL)

basically I need to enter info into a text box example B* or whatever and click the look up button and all Company Names in the fldCompanyName matching B* (B all) are then added to a list box. So one of those company names matching that criteria can be selected and information such as Customer ID from that field in that same record can be used in other parts of the program.

The first section of code works perfectly but the second section where I tried to add the ability to use a text box as search criteria input ... I get an error that says
Invalid use of Null
and highlights this line
pstrLine = pfldCurrent

That would seem as if theres no value in the first field thru the loop. but I know there is. or its not adding any fields into the recordset.

That line works fine in the first section I changed nothing inside the Do loop at all.

[Edited by VB_Sponge on 04-17-2000 at 05:24 AM]