Autofill A Form Window From A Table?

Hi all,

Sorry if this has been asked before, but I've used the Search function and can't find exactly what I'm trying to achieve.



The Current Setup

I have a database that I'm now trying to expand upon by adding extra features. One of these features I'd like to add is the ability to, firstly, know how many records in the entire datasheet have not been officially closed (i.e. have their 'rmaclosed' column empty), and, secondly, to have a list autogenerated that shows all of these records and allows the user to double click an item from the list and have that record open.

Now, to make life easier, I should point out that I already have a successfully working search function in my database. Clicking a button from the switchboard opens up a form that allows the user to enter full or partial text into a box, then, at the click of another button, the main window of the form will automatically fill out with a list of all those records which match the entered text. The user can then double click anything from this generated list and the appropriate record will be opened.
Working on the assumption that the code for what I want to achieve would be near-identical to this search function code, I have replicated both the form and code, and am now trying to adapt it; it is this adaption I'm having trouble with.


The Current Situation

Take a look at the attached image. That is how my form looks to the user when opened.
Now what I really, really want to achieve is for that central window to be automatically filled out with the listed column headings of any record stored in the table maindata that has a Null value in the column rmaclosed when the form is opened. However, I don't see any option for 'OnLoad', so I'm willing to compromise and just have the user click a button.

You'll see the button there for 'List RMAs'. When clicked, that should populate the main window with the records that have not had their 'rmaclosed' column filled out. The code behind the button - which is where I'm having the problem - is as follows:


Private Sub cmdSearch_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "SELECT maindata.ID, maindata.rmanumber, maindata.company, maindata.rmalogged, maindata.initials " & _
"FROM maindata"

strWhere = "WHERE"

strOrder = "ORDER BY maindata.ID;"

'THIS IS THE BIT I'M HAVING TROUBLE WITH
If IsNull(maindata.rmaclosed) Then
strWhere = strWhere & " (maindata.rmaclosed) Like '*" & maindata.rmaclosed & "*' AND"
End If


strWhere = Mid(strWhere, 1, Len(strWhere) - 5)


Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub


This code has been adapted from that which - successfully - runs my filtered search engine. The problem is that my search engine runs a query based on text entered onto the search form by the user, whilst the form I'm trying to write should simply list every record in the main table, filtered according to the state of the 'rmaclosed' column.

As I said in the opening paragraph, although it's not essential it would be really nice to have a counter somewhere on the form that would show the total numebr of records in the filtered list.