Open a Form based on a query

[slobad23]

Sounds easy and I can create a form where the results are from a query, that is not a problem.

What I want to be able to do is have a form that contains filters for people to use. So they can add a date to a field then click the button to open a form containing only records that are above that date. to select an ID and have only records come back from that ID.

I tried creating a query based on SQl like this:

SELECT person.*
FROM person
WHERE Form!PersonForm.ControlName > person.date;

Am I even on the right track here?

 

[dhookom]

Do you have a form to open? If so, what's its name?

Do you have a form with controls for users to enter or select criteria values? If so, what are the control names, related field names, and data types?

I use code to build a WHERE CONDITION that can be used in the DoCmd.OpenForm method.

Duane
Hook'D on Access
MS Access MVP

 

[slobad23]

I have been trying to avoid VBA as much as I can as I don't really know a lot about it and would hope there were ways of doing it through wizards. I will use code if I have to.

I have a form called Reporting which has a control called DateSelect which allows the user to enter a date. I then have a button which will open a form called DateSelectFilter that is based on a query called ContactDateSelect

This query currently (after tinkering around) has criteria like this:

Table: ContactDate.DateField
Criteria: > [Forms]![Reporting]![DateSelect]

 

[dhookom]

What about your current setup doesn't work? It should properly filter out dates that are less than or equal to the date entered into the control on the Reporting form.

Duane
Hook'D on Access
MS Access MVP

 

[slobad23]

I have got this to work. I know there must be something I did differently this time round to get it to work but what it was I don't know.

There is a problem now with adding additonal criteria.

There is a field in the table called "AccountManager" and I need that to be filtered on too.

I have added
Criteria: =[Forms]![Reporting]![AccountManagerSelect]

to the AccountManager field in the query. It stops working when I add this though. It's fine without it. When I try doing this in a query all on its own it still doesn't work. The form control AccountManagerSelect is a combo box based on a table query to obtain its values and is unbound.

This page

http://www.databasedev.co.uk/query_filter.html

sayd I should be able to just add the same code as I have already to get it to filter on the name I select in the combo box but it doesn't work.

Can you help?

 

[dhookom]

I expect there may be an issue with a code value versus a description value. Again, I can't tell without seeing your combo box actual value as well as the SQL of the query etc.

Duane
Hook'D on Access
MS Access MVP