SELECT Query using VBA to filter

[Hesco]

Question: I am trying to write a piece of VBA Code that will actuate when I open my report titled ‘Workers’. The Code is supposed to select three employees randomly and then filter (exclude) one of the employees based on their ID number.

I was able to create a query, switch to SQL view and use that statement as the model for the code below. I understand that I can make a query in Access but I am trying to reduce the query clutter in my database while increasing my skills and knowledge of VBA/Code. I had success making a DELETE query and think that it must be possible to make something like this but I have a limited understanding.

I have read in the forum that you cannot use a SELECT query in VBA but there has to be some work around for the desired results.

Private Sub Report_Open(Cancel As Integer)
Dim strQuery As String
strQuery = "SELECT TOP 3 Workers.ID, Workers.FullName, Rnd([ID]) AS Expr1 FROM Workers WHERE (((Workers.ID)<>9)) ORDER BY Rnd([ID]);"
DoCmd.RunSQL strQuery


End Sub

Any help with this would be great. Please see attached file.

[June7]

Can't 'run' a SELECT query, only action queries like DELETE, UPDATE, INSERT can be run.

Open a SELECT query in VBA as a recordset.

Or use the constructed SELECT statement to set the RecordSource property of form or report.

[Hesco]

How do I use the constructed SELECT statement to set the RecordSource property? I like doing research, if you can point me in a direction I will be happy to follow. :-)

[Hesco]

DUDE!!!! June7!!! I get it now!!! I just did a keyword search on your reply!!!!! I found this link and it filled in the blanks!!! Its not VBA but it works for me!!

[June7]

Yes, using a query as the RecordSource of form or report is basic Access functionality, no need for VBA although VBA can be used to change the RecordSource property. However, I find that there is seldom a reason to do that.

[Hesco]

Thanks! This forum is GREAT!