Using MS Access/Where Clause open event

Advertisement


Question
QUESTION: Scott
I have to apologise as your answer to my recent enquiry regarding the where clause in the open form event was in fact correct. Whilst I thought it was not working, it was filtering the Form records. What I did not realise was that the unbound combo box which selects records was still using the old record source which then listed the original names and this caused me to think that the filtering was not occurring. As soon as I select a members name in the combo box the filtering was then removed.
Could you please tell me therefore how to change the record source of the combo box to show the same filtering at the same time as the open form event.
My code is:DoCmd.OpenForm "Enquirer", acNormal, "", "([Enquirer].[Form received]) Is Not Null", , acNormal
Jim

ANSWER: OK, first, a combobox has a ROWsource and a form a RECORDsource. So I'm not clear what you need.

If you have an unbound combobox that is used to select a SINGLE record and you want that combo to reflect the currently displayed record, use the On Current event and add code like:

Me.searchcombo = Me.Pkcontrol

where searchcombo is the name of the unbound combo and PK control is the name of the control bound to your PK field. This is assuming that the unbound combo's bound column is returning the PK. If its not then use a control that holds the corresponding value.

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: Scott
Sorry to have confused you.
My Enquirer form has a Combobox which when a name of an individual is selected, opens his record.
When the Enquirer form opens with the applied filter activated from another form, I want the dropdown list of my Combobox to show only those filtered names of those filtered records so that I can then select any to view their record. I therefore need the RowSource to change. I have produced a query and have added that to the open form event on the activating form: [Forms]![Enquirer]![Combo66].RowSource = "FormsReceived". This works fine, however, I woul like to take the sql of that query and name it as a string variable rather than having a lot of queries for each open form event I make.
I have tried this in VBA code but it does not recognise the name of the string I have applied.
Dim strsql As String
strsql = "SELECT Enquirer.ID, Enquirer.Surname, Enquirer.[First Names], Enquirer.[Form received]" & _
         "FROM Enquirer " & _
         "WHERE (Enquirer.[Form received]) Is Not Null;"
DoCmd.OpenForm "Enquirer", acNormal, , "[Enquirer].[Form received] Is Not Null", , acNormal
[Forms]![Enquirer]![Combo66].RowSource = "strsql"
Could you advise me where the error is or should I content myself with building and using a query name.
I am quite new to VB so each day is a learning curve.
Jim

Answer
Ahh that's clearer.

Use this:

[Forms]![Enquirer]![Combo66].RowSource = strsql

By putting strsql in quotes you were changing the RowSource to just strsql as a string. You need to remove the quotes because strsql is a variable and you ant it filled with the contents of the variable.  

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.