Using MS Access/Criteria

Advertisement


Question
QUESTION: Dear Scott
I have a datasheet subform on a Tracking form that populates with ID and name of certain individuals from the Main Enquirer table based on a Tracking query.
In the surname control of the subform the dblclick event opens an Enquirer Form the record source of which is a query based on the ID field of that subform.
This works when only one name is populated in the datasheet but not when several are present.
How do I make the query identify the ID field of the datasheet  record row I want to view from the subform?
I hope this makes sense!
Jim

ANSWER: What code are you using to open the form? What is the SQL for the Query that is the recordsource?

The way I would do this is to NOT use a query, but use the WHERE clause of the OpenForm method:

DoCmd.OpenForm "formname",,,"[ID] = " & Me.ID

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Dear Scott
I have learnt something new by this method and have achieved a lot but having done so has caused me a problem in one area.
I am attempting to use the same Where clause in the OpenForm method to filter a field where the field on the form Is Not Null. I am struggling to get it to work.
I have tried DoCmd.OpenForm "Enquirer", , , [Form received] = ([Form received] Is Not Null) and whilst the 'Is not Null' goes blue, it does not work in that it raises an error and does not recognise the field. I have tried numerous combinations to no avail. I am clearly missing something!
Can you help put on the right track please?
Jim

ANSWER: Try:

DoCmd.OpenForm "Enquirer", , , "[Form received] Is Not Null")

The WHERE clause has to be a string so it needs to be in quotes. The syntax is fieldname...condition.


Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Dear Scott

I cannot get this to work either. The form opens but does not filter out the null records.
I notice that you have a closing bracket which with or without or placing in an opening bracket, does not seem to work.
The 'Is Not Null' does not go blue to indicate that the syntax is correct when enclosed in quotes.
I went down the Is not Null route because I could not find Is Text.

Jim

Answer
First, sorry about the extra parentheses, that was a typo.

Second, is Form Received a field in the Recordsource of the form?

Third, are you sure there are null records instead of zero length strings? You might test this by doing a query That shows Null records.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.