count records in search from (1 Viewer)

[Gasman]

JHB provided you with a solution.

The control for Lab A LCD is populated by this code

  Set rst = dbs.OpenRecordset("SELECT SchoolName, Lab, device, Kind " _
  & "FROM SchoolNameTbl INNER JOIN DevicesTbl ON SchoolNameTbl.ID = DevicesTbl.ID " _
  & "WHERE [SchoolName]like '*" & Me.Text0.Text & "*' " _
  & "AND Lab='Lab-A' AND Device='LCD'")
  If Not rst.EOF Then
    rst.MoveLast
    Me.FrmHlp.Form!CountLCDLabA = rst.RecordCount
  End If

however once set it is not cleared, that is why you see 3, that is left over from a previous search.
So set each control to 0 before issuing the sql for the recordset.

  [B]Me.FrmHlp.Form!CountLCDLabA = 0[/B]
  Set rst = dbs.OpenRecordset("SELECT SchoolName, Lab, device, Kind " _
  & "FROM SchoolNameTbl INNER JOIN DevicesTbl ON SchoolNameTbl.ID = DevicesTbl.ID " _
  & "WHERE [SchoolName]like '*" & Me.Text0.Text & "*' " _
  & "AND Lab='Lab-A' AND Device='LCD'")
  If Not rst.EOF Then
    rst.MoveLast
    Me.FrmHlp.Form!CountLCDLabA = rst.RecordCount
  End If

Use the same logic for the other controls.

 

[mohamedmatter]

JHB provided you with a solution.

The control for Lab A LCD is populated by this code

  Set rst = dbs.OpenRecordset("SELECT SchoolName, Lab, device, Kind " _
  & "FROM SchoolNameTbl INNER JOIN DevicesTbl ON SchoolNameTbl.ID = DevicesTbl.ID " _
  & "WHERE [SchoolName]like '*" & Me.Text0.Text & "*' " _
  & "AND Lab='Lab-A' AND Device='LCD'")
  If Not rst.EOF Then
    rst.MoveLast
    Me.FrmHlp.Form!CountLCDLabA = rst.RecordCount
  End If

however once set it is not cleared, that is why you see 3, that is left over from a previous search.
So set each control to 0 before issuing the sql for the recordset.

  [B]Me.FrmHlp.Form!CountLCDLabA = 0[/B]
  Set rst = dbs.OpenRecordset("SELECT SchoolName, Lab, device, Kind " _
  & "FROM SchoolNameTbl INNER JOIN DevicesTbl ON SchoolNameTbl.ID = DevicesTbl.ID " _
  & "WHERE [SchoolName]like '*" & Me.Text0.Text & "*' " _
  & "AND Lab='Lab-A' AND Device='LCD'")
  If Not rst.EOF Then
    rst.MoveLast
    Me.FrmHlp.Form!CountLCDLabA = rst.RecordCount
  End If

Use the same logic for the other controls.

Thank you very much. Is it advice on the name of a book to learn how to write vba codes. as I did

 

[Gasman]

You could start with this?
https://ixz.es/VBA%20For%20Dummies%205th%20Ed.pdf

Ignore the title, it should give you the basics.