Another query question - Display record count as label 2

[jamez05]

Hi,

I know how to do a dcount on a table to get a record count and display that. However, I want to get a record count from a query and display that instead. Not sure what I'm doing wrong (always comes up blank):

         strSQL = "SELECT "
         strSQL = strSQL & "COUNT(tmp.Rec_ID) AS Records"
         strSQL = strSQL & " FROM tmpExcelUpload tmp INNER JOIN Data D ON tmp.[Site ID] = D.[Site ID]"
        With Forms![TabMenu]![Maintenance].Form![ExcelUploadResults].Form
        ![LabelDupSiteIDs].Caption = Records & " Record(s)that have the same Site ID already exist "
        End With

 

[Remou]

You do not appear to have a DAO recordset.

[tt]Set rs=Currentdb.OpenRecordset(strSQL)[/tt]

You may need to .MoveLast, as well.

 

[lameid]

Additionally, you are not referencing the field Records in a recordset.

It is worth mentioning that dcount can take a query as the domain just as easily as a table. You will of course have to use a texbox instead of a label if you go this route.

 

[TheAceMan1]

How are ya jamez05 . . .

A starting point:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim SQL As String, prp As Property

   Set db = CurrentDb
   Set prp = Forms![TabMenu] _
                  ![Maintenance].Form _
                  ![ExcelUploadResults].Form _
                  ![LabelDupSiteIDs].Properties("Caption")
   SQL = "SELECT COUNT(tmp.Rec_ID) AS Records " & _
         "FROM tmpExcelUpload [purple][b]As[/b][/purple] tmp " & _
         "INNER JOIN Data [purple][b]As[/b][/purple] D ON tmp.[Site ID] = D.[Site ID]"
   Set rst = db.OpenRecordset(SQL, dbReadOnly)
   
   If rst.BOF Then
      prp = "Site ID Not Found!"
   Else
      prp = rst!Records & " Record(s)that have the same " & _
            Site ID already exist!"
   End If
   
   Set prp = Nothing
   Set rst = Nothing
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[jamez05]

Thanks for your input. I've changed it around a little. The query now lists the individual records in a list box. I have this working. However, I would still like to display the total number of records in the query. Not sure how to do this, thinking its dcount. I would also like to get the distinct project ID codes into a list that I can query against later. Here's what I have so far. Any advice is greatly appreciated:

        'Showed the records from the temp table.  Now show data in temp table that conflicts with that from the data table
        'the first check is to look for data in the temp that already has a site id in the data table
        'Create a list of Site IDs that already exist - not sure how to do this
        Dim ExistingSiteIDs As String
        Dim DupIDs As Property ' does this have to be set as a property?
         strSQL = "SELECT "
         strSQL = strSQL & "DISTINCT tmp.Rec_ID, tmp.[Site ID]"
         strSQL = strSQL & " FROM tmpExcelUpload tmp INNER JOIN Data D ON tmp.[Site ID] = D.[Site ID]"
        Set rs = CurrentDb.OpenRecordset(strSQL)
  
        With Forms![TabMenu]![Maintenance].Form![ExcelUploadResults].Form
        ![LabelDupSiteIDs].Caption = " The following " & DupIDRecordCount - not sure how to do & " Record(s) will not be processed because their Site ID already exists in the data table: "
        ![listDupSite].ColumnCount = 2
        ![listDupSite].ColumnHeads = True
        ![listDupSite].RowSourceType = "table/query"
        ![listDupSite].RowSource = strSQL
        End With
        
        db.Close
         
         Next
        'Finished - start the cleanup
        'Delete temp table
        ' DoCmd.RunSQL "DELETE FROM tmpExcelUpload"
         'Refresh the site listing
         Me.Refresh
         MsgBox "Import Processing Completed"
      Else
        MsgBox "Import Processing Cancelled"
      End If
    End With
    
Exit_ExcelUpload:
    'Finish cleaning up
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Set fDialog = Nothing
    Set DupIDs = Nothing
    Set rs = Nothing
    Set db = Nothing

 

[PHV]

Perhaps something like this ?
...
DupIDRecordCount = 0
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not (rs.BOF Or rs.EOF)
rs.MoveLast
DupIDRecordCount = rs.RecordCount
End If
rs.Close
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886