CODEWITHSUNDEEP
ms-accessvba
lowak
lowak

Reputation: 1284

Access query based on query results

I am new to Access and queries, however trying to learn fast :)

The project has 2 tables: tblClientData contains only data of clients, tblClientComments contains all the commentaries made to all clients.

  1. Database

    • Table with Clients data called tblClientData
    • Table with comments to Clients called tblClientComments
    • Form showing all upper data with functionality to find, add or change existing data

I wanted to make an option button - if ticked it would only show certain Clients, when unticked it would show all again. Problem raises here.

Criteria to this query comes from another table (tblClientComments) within this database. For example: if Client was contacted by an employee - he is making commentary.

In order to finish this task I need to get all unique IDs from tblClientComments and then add each of it as criteria to query tblClientData. I tried to make an array with values from tblClientComments and then create a query but so far still fighting with syntax.

Or maybe there is another way?

Upvotes: 0

Views: 80

Answers (1)

ZX9
ZX9

Reputation: 977

I don't see any reason that you need VBA to build your query. A simple SQL IN() statement should certainly suffice:

SELECT Client.ID, Client.OtherInfo
FROM tblClientsData As Client WHERE Client.ID IN(SELECT tblClientComments.CustID From tblClientComments)

Now, you have a few options. (I'm assuming you're using a checkbox for simplicity.)

  1. Assign SQL to the query you're using for your form. Since these changes are dependent on changes to the checkbox, assign a VBA sub to the After Update event:
    • Go to Layout View
    • Click on your checkbox
    • Go to the Event tab on the Property Sheet
    • Hit the ... button beside the On Click
    • Hit Code Builder

Now put in some code:

If(Forms!yourForm!yourCheckbox = True) Then
    CurrentDB.QueryDefs("yourQuery").SQL = "SELECT Client.ID, Client.OtherInfo FROM tblClientsData As Client WHERE Client.ID IN(SELECT Comments.CustID From Comments)"
Else
    CurrentDB.QueryDefs("yourQuery").SQL = "SELECT Client.ID, Client.OtherInfo FROM tblClientsData As Client"
End If

You can then just use Forms!yourForm.Refresh to reflect your changes with the checkbox.

  1. An alternate, likely simpler solution might be to use a filter which you turn on/off. DoCmd.ApplyFilter should be a very straightforward way to change the form's contents. (Note that the example at the last hyperlink is very similar to your situation.)

Upvotes: 2

Related Questions