Dlookup + Dcount Template

[Ollie_3670]

Searched the internet, searched the forum. One thing I just don't understand is how you actually use these. I must be insanely stupid.

I've made all the needed queries, I've made a combo box which uses a query to display all courses (2 columns, course_code and Course_Name) and all the queries ready to be read by the textbox

Here is a sql example of a query, that i've added line spaces in for legibility:

SELECT TblCourse.Course_Code, Count(TblStudent.EU_Status) AS CountOfEU_Status

FROM TblCourse INNER JOIN (TblApplicationHeader INNER JOIN (TblStudent INNER JOIN TblApplicationLine ON TblStudent.Student_ID = TblApplicationLine.Student_ID) ON (TblStudent.Student_ID = TblApplicationHeader.Student_ID) AND (TblApplicationHeader.Application_ID = TblApplicationLine.Application_ID)) ON TblCourse.Course_Code = TblApplicationLine.Course_Code fyi, I know this inner join is monstrous, but it's access generated...obviously!

WHERE (((TblStudent.EU_Status)="EU National") AND ((TblApplicationLine.Offer_Type)="CF"))
GROUP BY TblCourse.Course_Code;


Now I do not know how to basically select a course from the combo box, and then have an automatically textbox tell me the "Count(TblStudent.EU_Status) AS CountOfEU_Status" part.

I know I need to set the control source of the textbox to something fancy, I know that with my query I need to tell it where to find the course_code rather than displaying all of them that contain 1 or more results. But the question to all is how. I have about 12 textboxes all needing this done, but once I've done one I'll be sorted!

all I need is a template I can understand of the information I need to put into both of the above rather than the exact answer.

teach a man how to fish and he'll eat for life and all that...

 

[ajetrumpet]

look at this:

http://www.access-programmers.co.uk/forums/showthread.php?t=155420&highlight=autopopulate+textbox

also, you don't need a query. use the statement as the rowsource for the combo. no query necessary, but you can do it if you want to.

you might also be able to get this done with a simple DCOUNT() on the AFTERUPDATE of the combo event.


there ya go...I gave you the equipment to fish, now you can eat!

 

[Ollie_3670]

This was a real big help with several forms!

However, I have a busier form which this method is inadequate for

I have to display 25 figures on one screen. All of these figures are count totals of applications again. So no text values, just numeric

25 count values mean 25 text boxes to display each figure and I can't display these from a cbo with hidden columns. too many different layers of grouping etc in the query

Pretty sure now, I need to know how to tell my text box to display a field from a query, the queries only show one field anyway mind, the rest are hidden.

Thanks for everything though so far!

 

[vbaInet]

So you want to get the count of each combo box's records?

 

[Ollie_3670]

I was originally planning to do the count at the query level and use Dlookup, rather than use Dcount to count all of the records a query outputs

However, I suppose I could do it either way! If telling a textbox to count the amount of records a query outputs is easier, or you know how to do it, I'm all ears!

 

[vbaInet]

Still not entirely show what you want to count?

 

[Ollie_3670]

OK, fair one, let me try again, but this time with a scaled down scenario you can relate to more!

Let's say I work in a small European sales department for Microsoft. My boss would like to be able to see, the number of EU-nationals, and Non-EU customers who buy each product (ie, MSAccess, MSWord, MSPowerPoint)

The design preference is to select a Micrsosoft product from a combobox, and then the two textboxes to automatically count the number of EU sales, and the number of Non-EU sales.

How would I display these in textboxes?

 

[vbaInet]

You mentioned the DCount function. What happened to that?

 

[Ollie_3670]

From what I have read, it seemed that using DCount or Dlookup was the way to make a textbox display the numbers from a query.

 

[vbaInet]

There are other ways. For example you could set a combobox's or listbox's rowsource to the returned sql you would like to count and call the ListCount method of the list/combo box.

You could count the recordset too.

 

[Ollie_3670]

If I can get the "Count(fieldname)" field's value from a query to appear in a textbox, I'm a happy chappy!

Whatever way you find easiest explaining!

 

[vbaInet]

I gave an explanation of one of the methods in my last post re combo box/list box

Can you show a screenshot of your form and what you're counting.

Also, are you counting values on a subform?

 

[Ollie_3670]

Alrighty, I got a little confused initially! So, in layman's terms what you suggest is:

• Make a Combo box instead of a textbox
  
• Tell it to get it's information from one of the queries.
  
• Then use that "Listcount" function to count how many records the combo box holds?

Ill get that screen shot for you as soon as!

 

[Ollie_3670]

Here's the PrntScreen!

 

[vbaInet]

1. Make a hidden listbox
2. Set it's rowsource to one of the queries using code (or in layman's terms, tell it to get it's information from one of the queries.)
3. Use the ListCount method to count how many records are in the listbox.
4. Return the count value to a text box.

This was pretty much what ajetrumpet hinted too.

You're form doesn't look busy at all unless you just showed a sample.

I still think the DCount function is the way to go with this. You set the control source of the text box to the dcount. To get this working, create a query which would show course_code, Eu_national, Non_EU fields only. I'm guessing the EU_National and Non_EU fields are Yes/No fields. Here's the syntax:

=DCount("*", "Name_Of_Query", "[Course_Code] = [COLOR=Red][B]'[/B][/COLOR][Combobox1][COLOR=Red][B]'[/B][/COLOR] AND [EU_National] = Yes")

If, however, the national fields are combined in one and your query is only showing Course_Code and National_Status then your DCount would look like this:

=DCount("*", "Name_Of_Query", "[Course_Code] = [COLOR=Red][B]'[/B][/COLOR][Combobox1][COLOR=Red][B]'[/B][/COLOR] AND [National_Status] = [COLOR=Red][B]'[/B][/COLOR]EU  National[COLOR=Red][B]'[/B][/COLOR]")

The quotes in red indicate how you handle strings. Just as you would in a query criteria.

 

[Ollie_3670]

I'll try all this in the morning! ..Is it sad that this excites me? ha!

and yes I wish the form was that simple! but I'll have maybe 20 or so textboxes instead of 2.

Thanks again!

Edit: Right It bugged me too much, so I gave it a go!

I plugged it in and there were no error messages! (yey) But, the textboxes also registered ("0") when they should be registering a larger number.

=DCount("*","QryUberTest","[Course_Code] = '[courseSearch]' AND [EU_Status] = 'EU  National'")

is my Dcount code in the controlsource for the textbox

SELECT TblCourse.Course_Code, TblStudent.EU_Status
FROM (TblStudent INNER JOIN TblApplicationHeader ON TblStudent.[Student_ID] = TblApplicationHeader.[Student_ID]) 
INNER JOIN (TblCourse INNER JOIN TblApplicationLine ON TblCourse.[Course_Code] = TblApplicationLine.[Course_Code]) 
ON TblApplicationHeader.[Application_ID] = TblApplicationLine.[Application_ID];

is my query that I experimented it on. I have now removed the extra column in the CourseSearch combobox, so now it only shows one, but that didn't fix it either

 

[vbaInet]

Forgot to mention concatenation in the string handling section. Try this:

=DCount("*","QryUberTest","[Course_Code] = '" & [courseSearch] & "' AND [EU_Status] = 'EU National'")

You must ensure that the string 'EU National' is exactly as it appears. I noticed an extra space between EU and National. Finally, the bound column of the combo box must contain the course code.

 

[Ollie_3670]

YES! Amazing, now to expand it all myself!

On the bright side the queries only get as difficult as one more "where clause" field:

=DCount("*","QryUF_NonEU","[Course_Code] = '" & [courseSearch] & "' AND [EU_Status] = 'EU National'  AND [Offer_Type]  = 'UF'")

Which works as well!

 

[vbaInet]

YES! Amazing, now to expand it all myself!

On the bright side the queries only get as difficult as one more "where clause" field:

=DCount("*","QryUberTest","[Course_Code] = '" & [courseSearch] & "' AND [EU_Status] = 'EU National'  AND [Offer_Type]  = 'UF'")

Which works as well!

A great way to spend your Sunday evening Ollie lol.

Glad you got it working.

 

[Ollie_3670]

YES! Amazing, now to expand it all myself!

On the bright side the queries only get as difficult as one more "where clause" field:

=DCount("*","QryUberTest","[Course_Code] = '" & [courseSearch] & "' AND [EU_Status] = 'EU National'  AND [Offer_Type]  = 'UF'")

Which works as well!