mohamedmatter
Registered User.
- Local time
- Today, 14:23
- Joined
- Oct 25, 2015
- Messages
- 112
yes i beginner i try learn
I can not do what you ask me. So I attached the file to an example of me. Thank you for following up on my problem. So I did not understand your previous comment"So help me to help you.
What do you not understand from what I have suggested as a way to get what you are asking.?
? DCount("[Lab]","DevicesTbl", "Lab = 'Lab-A' AND device = 'LCD'")
3
=DCount("Lab","DevicesTbl", "Lab = 'Lab-A' AND device = 'LCD'")
=DCount("*,"DevicesTbl", "Lab = 'Lab-A' AND device = 'LCD'")
=DCount("*","basequery",strWhere)
=DCount("*","basequery",strWhere & " AND Lab='Lab-A'")
Private Sub Text0_Change()
Dim SQL As String, strSelect As String, strWhere As String, strOrder As String
strSelect = "SELECT SchoolNameTbl.ID, SchoolNameTbl.SchoolName, DevicesTbl.Lab, DevicesTbl.device, DevicesTbl.Kind " _
& "FROM SchoolNameTbl INNER JOIN DevicesTbl ON SchoolNameTbl.ID = DevicesTbl.ID "
strWhere = "[SchoolName] like '*" & Me.Text0.Text & "*' "
strOrder = " ORDER BY SchoolNameTbl.SchoolName"
SQL = strSelect & "WHERE " & strWhere & strOrder
Me.FrmHlp.Form.RecordSource = SQL
Me.FrmHlp.Form.Text13 = DCount("*", "basequery", strWhere & " AND Lab='Lab-A'")
Me.FrmHlp.Form.Text11 = DCount("*", "basequery", strWhere)
End Sub
I can not do what you ask me. So I attached the file to an example of me. Thank you for following up on my problem. So I did not understand your previous comment"
think you will need to create a base query for your SQL join as you have for the search control.
Then you can use DCount for your each of your criteria but append the current search criteria as well.?"
From my immediate window, from your db:
Enter the above code in the control source of the relevant text box, image below:Code:? DCount("[Lab]","DevicesTbl", "Lab = 'Lab-A' AND device = 'LCD'") 3
This should give you an idea how to configure your other counts.
Also check out this link for further.
I read the request for the counts as for the records selected in the form?, not the whole table. That is why I thought making a base query was required.?
Admittedly, I didn't check the actual recordsource of the form, but there appeared to be as many records in the table as there were in the form listing.
=DCount("*","basequery","[SchoolName] like '*" & [Text0].[Text] & "*' " & " AND [Lab]='Lab-A'")
Thank you very much for helping you and cutting off part of your time to help membersFrom my immediate window, from your db:
Enter the above code in the control source of the relevant text box, image below:Code:? DCount("[Lab]","DevicesTbl", "Lab = 'Lab-A' AND device = 'LCD'") 3
This should give you an idea how to configure your other counts.
Also check out this link for further.
JHB has taken a different approach.
For completeness and to try and get you to understand what my approach on it was...
A DCount cannot specify joins, only the WHERE criteria without the WHERE keyword.?
So create a base query that has the join the same as the form recordsource join.
Break your recordsource sql down to three parts.
strSelect. strWhere and strOrder so you can concatenate them to give you the same sql string as you have now. This then gives you the ability to use the strWhere string/clause elsewhere.
Now use strWhere string/clause and concatenate the other specific criteria for each of the controls.
eg:
StrWhere would currently be "[SchoolName]like '*" & Me.Text0.Text & "*' "
**Count all record in school search
This would result in a where criteria of
no additional criteria requiredCode:=DCount("*","basequery",strWhere)
**For All device in Lab A
Code:=DCount("*","basequery",strWhere & " AND Lab='Lab-A'")
and so on.
So my database would look like
Code:Private Sub Text0_Change() Dim SQL As String, strSelect As String, strWhere As String, strOrder As String strSelect = "SELECT SchoolNameTbl.ID, SchoolNameTbl.SchoolName, DevicesTbl.Lab, DevicesTbl.device, DevicesTbl.Kind " _ & "FROM SchoolNameTbl INNER JOIN DevicesTbl ON SchoolNameTbl.ID = DevicesTbl.ID " strWhere = "[SchoolName] like '*" & Me.Text0.Text & "*' " strOrder = " ORDER BY SchoolNameTbl.SchoolName" SQL = strSelect & "WHERE " & strWhere & strOrder Me.FrmHlp.Form.RecordSource = SQL Me.FrmHlp.Form.Text13 = DCount("*", "basequery", strWhere & " AND Lab='Lab-A'") Me.FrmHlp.Form.Text11 = DCount("*", "basequery", strWhere) End Sub
Thank you very much for helping you and cutting off part of your time to help members
after test your modification i found error in dcount
for example when i search to school-d the counter to "Count Lcd Device in Lab A"= 3 but no lcd in lab a in school-D
I can't understand what it means"Set each variable to 0 before the recordset is opened"Set each variable to 0 before the recordset is opened.