QueryDef, sql String Issues (1 Viewer)

[Dreamweaver]

Been trying to get a Recordcount From A SQL String which works when Assigned as a RecordSource

The Query is made up of the following items
StrWhere = "[ArtistID]=1123" Just example
Qry = "QryMediaPlayer" This is the query assigned to the forms recordsource
I have tried running a db.openrecordset("SELECT * FROM " & Qry & " WHERE " & StrWhere, dbopensnapshot)
Which gives be an error expected 1

So Started looking at QueryDefs Which I havent used much at all

QueryDef object (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

QueryDef.ReturnsRecords property (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

Tried below which didnt work in that is always said there were no records

    'Test For Records
    'Set R = CurrentDb.CreateQueryDef("")
    'R.SQL = "SELECT * FROM " & Qry & " WHERE " & StrWhere
        'Set Rt = R.OpenRecordset()
   ' If Rt.RecordCount < 1 Then
      '  MsgBox "No Records Found For Your Selection", vbInformation + vbOKOnly, "No Records"
      '  R.Close
      '  Set R = Nothing
        
     '   Exit Sub
    'End If

I have tried if different ways but still get the expected 1 error except for above code

This has to be one of them silly mistakes but I can't see it.

many thanks mick

 

[Dreamweaver]

Think I just figured it out the QryMediaPlayer has A Lot of criteria I'll try a something

 

[Dreamweaver]

Nope didn't work

 

[Minty]

Why not just use

DCount("*","QryMediaPlayer","[ArtistID] = 1123)

If your criteria or the query were more dynamic, then a recordset might be the way to go but this seems too simple for that complication.

 

[Dreamweaver]

Solved it I used the listbox.listcount as the same sql was being used to update that

 

[Dreamweaver]

It is part of a compex system but will try that simple always works lol Thanks @Minty

 

[Dreamweaver]

Thanks @Minty Worked a treat Think It's time for a break lol

This is what I did in the end

C = DCount("*", Qry, StrWhere)