Text Field Based on Query Problem

[popen]

I have a form, unbound, with a combo and a listbox. These are both bound to Select Queries.
When a user updates the combo with their choice, and a record in the listbox is highlighted, an additional query will pull back all records that fall into this criteria. (The query refers to both the combo and listbox for criteria).
Now, for the problem:
I also have an ADDITIONAL query, which gives a COUNT of the 1st query. Ie, total number of records.
I also have a command button that, when pressed, makes a field visible (Invisible by default) and I need it to have the value from the COUNT query. All this is for is to allow the user to view the total number of records quickly and easily, based on thier criteria of the Combo and Listbox. No matter how I try, I cannot get this to work. I have bound the textbox to the query, tried requery methods 'On Click' properties of the command button also. It just will not go where I need it to!

Can anyone help on this?

Thanks in Advance,

-Neil

 

[glynch]

I'm not sure I understand exactly what you are trying to do, but it sounds like you are trying to run a query from a form that gets the record count of another query. This won't work. Add a field to the query whose records want to count (I'm thinking that this is the query that feeds the list box), use one of the fields already in that query and do a count function on it. This will force you to have a group by clause including all of the other fields in the query, but that is ok. THen set the controlsource of the Count textbox like this:

=MyListbox.column(6)

The column number you designate in the recordsource property of the textbox must equal the number order of the Count field in the query that feeds the listbox, remember it is zero based, so column(6) actually refers to the 7th field in the query.

HTH

 

[popen]

Thanks for that Glynch.
Not quite what I am trying to do, but I will try to explain better:
Yes, I am trying to pull a count query from another query on a form.
Basically, I have 3 queries concerning this form:
qryCWDetails feeds the Listbox for Calender Week Details.
qryNPOID feeds the combobox with NPO Details.
qryDispatch is the final query which is activated from a separate command button and runs a report. qryDispatch takes CW Details and NPOID details from those selected on the form. This is all OK and runs with no problems.

What I am trying to achieve is to have the textbox 'txtTotal' to show a count of records in 'qryDispatch' without actually running the report. IE, when the user clicks on the 'Calculate' cmdbutton, this will hopefully show a figure depending on what they have chosen from the listbox and cmbobox. This will give them a figure they need without having to run the report.

I hope this is a better explanation!

Many thanks,

-NEIL

 

[glynch]

You can dump the results of the final query into a hidden listbox on that same form and do as I suggested in the first post.

 

[cogent1]

shouldn't be too difficult...

It sounds like you can just write some code on the Click event of your count button and use rs.Recordcount to return the number of records in a query based on the contents of your combos . Send me the relevant part of your database as a zip file if you like and I'll have a go...

 

[bradcccs]

I may be off track here but . . .

From the sound of it, you already have a query that is returning the correct count figure, and you just need it to display on the form.

AND, by your description, your query criterias are based on the form combo and list boxes. Thus (I think) you will only be receiving one entry in your Count Query.

IF this is correct, you could try:

=dlookup("countofrequiredfield","CountQuery")

You will have to view the query result to get the correct field name and query name of course.

When no criteria exists (ie the combo and listbox are null) the dlookup will return an error. You could avoid this by including an iif(isnull(combobox) or isnull(listbox),"",dlookup......)

However, given that you plan to hide the box until the user elects to display it, the iif may be unnecessary.

Just a thought.

Brad.

 

[popen]

Thanks for the replies everybody.
I'm working on some specifications at the moment, but I will give your suggestions a go tomorrow and post back then.

Many thanks until tomorrow (!)

-Neil