Counting number of selected records in Acces database from VB

[MarcMellor]

I am a very amateur programmer trying to develop an educational assessment program. I need to count the number of questions (selected according to certain criteria)that are deposited in an Access database and display this number in a text box. So far I have this:
Dim QuestionCount As Integer
Data1.Recordsource = "SELECT COUNT(*) AS QuestionCount FROM_
QuestionsDatabase WHERE Subject = 'Biology'_
AND WHERE Module = '2' AND WHERE Level = '1'"
Text1.Text = QuestionCount
Subject, Module and Level are all fields.
Unfortunately this just generates a zero, even if I cut out all the criteria.
Where am I going wrong?

 

[WilMead]

For starters, the quantity you want to look at is, Data1.Recordsource.!QuestionCount.
An option explicit at the very top of your module helps with this kind of thing. Instead of getting a zero you would have seen the compile error, "Variable not defined." It may have pointed you in a better search direction.






Wil Mead
wmead@optonline.net

 

[MarcMellor]

Thanks Wil Mead but I am still having trouble with this. I have tried the above and it doesn't seem to work (do you really mean "RecordSource.!" ??). I have been toying with:

Data1.Recordset.RecordCount = "SELECT COUNT(*) AS_
QuestionCount FROM QuestionDatabase WHERE Subject_
= 'Biology AND Module = '2' AND Level = '1'
TextBox1.Text = QuestionCount

But an error message comes up that says it can't assign a read only property.
There's gotta be a way.......

 

[sachiel]

I assume that you are using an ADODC, right?
Right up to a point:

TextBox1.Text = QuestionCount
needs to be
TextBox1.Text = Data1.Recordset("QuestionCount&quot

Also, make sure that your ActiveConnection is connected to the database Those are the two real keys to ADO connections

This won't work by the way:
Data1.Recordset.RecordCount = "SELECT COUNT(*) AS_
QuestionCount FROM QuestionDatabase WHERE Subject_
= 'Biology AND Module = '2' AND Level = '1'
TextBox1.Text = QuestionCount


You could also
Data1.Recordsource = "SELECT * FROM _
QuestionsDatabase WHERE Subject = 'Biology'_
AND WHERE Module = '2' AND WHERE Level = '1'"
TextBox1.Text = Data1.RecordSet.RecordCount

--Sachiel

 

[MarcMellor]

Almost there!!!
Thanks Sachiel, your second suggestion seems to work .... well kind of. I'm now getting numbers up in the text box. Unfortunately not the right numbers! My overall code is obviously much more complex than the above as I am setting up the selection parameters from list boxes (Subject, Module, Level, etc). I am not using ADODC but the simpler Data tool from the standard toobox. I could change to an ADODC as I know how to use them but my selection criteria seems to work fine selecting the questions. I have a second Data tool to do the count (as I want the questions and the number available showing at the same time) and I have copied the exact same bit of code for both Data Tools. The only difference is that one is connected to a FlexGrid and the other to my text box with your code above. Both finish with a refresh statement. An example of what is wrong is that with a particular set of selections there are 23 questions in my grid but the number 33 in the text box, or if I select the whole database of 3460 questions the textbox comes up with the number 1! Any further thoughts?

 

[sachiel]

The ADODC has a nifty little property called the Filter.

I am assuming that the 3 selection criteria are static, right? You don't have to probe the db for the list of Subjects, right? So all of the list/combo boxes could be set up ahead of time. The ADODC.Filter is like a micro query. You specify the original recordsource (SELECT * FROM....) and lets say nothing is picked in any of the boxes. I might have:

Private Sub cboSubject_Click() {
MainADODC.Filter = "Subject = '" & cboSubject.text & "'"
txtCount.text = MainADODC.RecordSet.RecordCount
}

When the cbo was changed, the ADODC would be "thinned out." I believe that this would work, but you might have to experiment. You could also narrow down the available choices in the next 2 lists based on the first list's choice. I do this with Manufacturer/Model/Serial Number in a program that I am working on now. I don't worry about the count, but if .recordset.recordcount doesn't accurately reflect the count, do an ADODC movefirst, and loop through til adodc.EOF. Count them manually.

Good luck

PS. I am no stickler for uber efficient code, but 2 Datasources is a waste. You should be able to get the information out of what you have in the first

 

[Imhoteb]

Hi,

Try this sql statement.
If Module and Level are numeric fields then leave the singel quotes out of the sql statement.

Data1.Recordsource = "SELECT * FROM _
QuestionsDatabase WHERE Subject = 'Biology'_
AND WHERE Module = 2 AND WHERE Level = 1"
TextBox1.Text = Data1.RecordSet.RecordCount

 

[MarcMellor]

Thanks to you all.
It's now sorted. Quite why I was geting off the wall numbers with 2 Datasources I don't know, but I suddenly realised that, as the code was exactly the same, the second was redundant. When I dumped it, the numbers were much better (ie in the right ball park) but still wrong. Then I noticed that that if I clicked the button that entered the selection a second time the number changed to the correct count. All it needed was a second refresh, one for selecting the question and one for the count. Now its perfect!! I've been struggling over this for a couple of months now, so I can't tell you how pleased I am.
Well done and thanks again. On to the next problem .....
Marc