Autofill a form window from a table?

[Tezcatlipoca]

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.

 

[macca the hacke]

The list counter is quite easy. Put a list box in, and set the row source to SELECT Count(maindata.id) AS CountOfid
FROM maindata
WHERE (((maindata.rmaclosed) Is Null));

 

[Tezcatlipoca]

Sorry, macca, but I don't quite follow you. I have created a list box, the properties of which can be seen in the attached image.

When run as a proper form, said box is just blank, despite the RowSource containing the code you mention. What am I doing wrong?

 

[macca the hacke]

have i got the names correct for the fields? Is it the ID you want a count of?

 

[Tezcatlipoca]

Um...I think so. My table, 'maindata' is sorted by the primary key, ID. The column in question is called 'rmaclosed', and contains a date if the case has actually been dealt with.

What I need, is for this counter to look at that table and simply show the total number of records that do not have their 'rmaclosed' column completed.

 

[macca the hacke]

Hi
As long as I have got my tabel / field names correct, then there is no reason for it not to work - please see attached screen shots. However, I cannot see anything wrong with your screen shot either (apart from maybe the fact you have 4 columns when you only need 1?). All I did was insert a new list box, then when comes up with wizard I pressed cancel, and inserted row source of : SELECT Count(maindata.id) AS CountOfid
FROM maindata
WHERE (((maindata.rmaclosed) Is Null Or (maindata.rmaclosed)=""));

 

[macca the hacke]

Sorry, I didn't initially realise that rmaclosed was a date field. You will need to amend row source slightly to
SELECT Count(maindata.id) AS CountOfid
FROM maindata
WHERE (((maindata.rmaclosed) Is Null));
This is because being a date field can't filter for records with ""

 

[Tezcatlipoca]

Ah-ha....works perfectly now. Thanks a lot, macca, sincerely appreciated!

Any idea on the answer to my main query, out of interest?

 

[macca the hacke]

To show all records that have have not been closed, you could just vary the above theme. Have a list box which has the following as the row source:
SELECT DISTINCTROW maindata.id, maindata.rmanumber, maindata.company, maindata.rmalogged, maindata.initials, maindata.rmaclosed FROM maindata WHERE (((maindata.rmaclosed) Is Null));

On double click of the record you could get it to open up a form that shows the individual record by putting code something like:

DoCmd.OpenForm "frmFormName", , , "[Txtid] = " & Me!lstName

where frmFormName is the name of the new form that will open showing individual record; TxtId is the text box on that form showing ID of that particular record, and lstName is the name of this new list box on your form

 

[Tezcatlipoca]

Ah, good idea, macca; will try coding that now. Don't worry about the double-clicking, I already have that fully functional in my search engine, so will just port the code across.

If you're interested, I use the following as the 'OnDblClick' event:

Private Sub NAME OF LISTBOX_DblClick(Cancel As Integer)
DoCmd.OpenForm "NAME OF MAIN VIEWING FORM", , , "[ID] = " & Me.NAME OF LISTBOX, , acNone
End Sub

 

[Tezcatlipoca]

Just tried your code and ported my search engine double click into the new form, everything works perfectly now! Thank you, very very much macca!