macro with applyfilter problem

[landmeter]

GENERAL
i use four comboboxes (keuzelijst met invoervak) to filter the query for a
form;
i choose a value from the first combobox ;
then the form should be udated and the 3 other comboboxes values are
updated to;
PROBLEM
the form is not updated? the comboxes are!
selecting a value from the second combobox - nothing happens
selecting again in the first combobox, but a different value from the first
time - the form updates but uses the first value?
i show all records
use the first combobox again with yet another value
the form updates with the first value?

what is wrong?

this is in a 2007 converted working fine access 2003 project

 

[Steve Schapel]

Landmeter,

You forget to mention which actions are in the macros, and when the
macros run!

 

[landmeter]

Steve

the macro runs at afterupdate
the macro requeries the input for the other comboboxes and applies a filter
to the underlaying query
(there is no need to choose from the other comboboxes
the first combobox already filters the query behind the form on one field
choosing a second comboxitem queries on two fields)

i have been testing
it seems access keeps in mind the first selected item and is unable to
change it
using code has the same effect

 

[Steve Schapel]

Landmeter,

the macro runs at afterupdate

After Update event of what?

the macro requeries

.... using what syntax, exactly?

the input for the other comboboxes

.... Do you mean the Row Source of the other comboboxes? What exactly is
the SQL view of these queries?

and applies a filter
to the underlaying query

.... Do you mean the query that is the Record Source for the form? What
exactly is the SQL view of this query?

(there is no need to choose from the other comboboxes
the first combobox already filters the query behind the form on one field
choosing a second comboxitem queries on two fields)

.... Can you please give details and examples to illustrate this process?

i have been testing
it seems access keeps in mind the first selected item

.... for example?

and is unable to
change it
using code has the same effect

What code?

 

[landmeter]

ok, sorry

GENERAL INFO
my database has 16 related tables
i 've a form based on a query
the query contains 5 related tables, all fields are used (using the *), one
field of one table is used tot sort the query
the form has a header with some navigation buttons
and 4 comboboxes
the form has a tabthing (?) with 9 tabs
the tabs show several fields of the query

at openening all records are shown
i choose an item from the first combobox (klgemlijst)
this requeries the other comboxes
and applies a filter to the form
i use a macro for this
the macro has five items
the first item applies a filter
the filter aguments are Qdos;
[dossiers].[deelgemeente]=[Forms]![Fdos]![klgemlijst];
the next 3 items requeries 3 comboboxes
the last macroitem navigates to the last record

ANSWERS TO YOUR QUESTIONS
1 afterupdate of the combobox (klgemlijst)

2 the syntax : Qdos; [dossiers].[deelgemeente]=[Forms]![Fdos]![klgemlijst];

3 the rowsource of one of the the comboxes : SELECT DISTINCT
Qdos.klanten.anaam, Qdos.deelgemeente FROM Qdos WHERE
((Qdos.deelgemeente=[forms]![Fdos]![klgemlijst])) ORDER BY Qdos.klanten.anaam;

4 the recordsource of the query the form is based on :
SELECT DISTINCTROW dossiers.*, Ttf.*, klanten.*, Twat.*, *
FROM Twat RIGHT JOIN (klanten RIGHT JOIN ((dossiers LEFT JOIN klanten AS
klanten_1 ON dossiers.via = klanten_1.klantID) LEFT JOIN Ttf ON
dossiers.dossierID = Ttf.dossierID) ON klanten.klantID =
dossiers.opdrachtgever) ON Twat.wat = dossiers.wat
ORDER BY dossiers.dossierID;

5
the form shows all records
i choose an item from the first combobox
the form requeries and shows all records that contain the chosen item in the
field [deelgemeente], lets say "Baasrode"
i make a new choise from the same combobox [deelgemeente] = "Lebbeke"
the form requeries but shows the results from the first time [deelgemeente]
="Baasrode"

6
when i do this in the query it works
i put [Forms]![Fdos]![klgemlijst] in the criteria for the field [deelgemeente]
while the form is open and the combobox is on "Lebbeke" and the results in
the form on "Baasrode"
the querie results [deelgemeente]="Lebbeke" as it should be

7
i have replaced the macro with simple code for the afterudate of the first
combobox
code:
DoCmd.ApplyFilter "Qdos", "[dossiers].[deelgemeente] =
[Forms]![Fdos]![klgemlijst]"
Me![klgemlijst].Requery
Me![klnaamlijst].Requery
Me![Veld79].Requery
this as the same result

i am not used to talk access
hope it is clear
it must be something in the form
but as i said it works fine in access 2003

 

[landmeter]

i have made a reply but i dont see it !?

 

[landmeter]

--
de aarde is rond maar de wereld is plat

i have made a reply but i dont see it !?
--
de aarde is rond maar de wereld is plat

Landmeter,

the macro runs at afterupdate

After Update event of what? - after update of the combobox

the macro requeries

.... using what syntax, exactly? - in the arguments of the applyfilter : Qdos; [dossiers].[deelgemeente]=[Forms]![Fdos]![klgemlijst];

the input for the other comboboxes

.... Do you mean the Row Source of the other comboboxes? What exactly is
the SQL view of these queries? - the rowsource! the SQL : SELECT DISTINCT Qdos.deelgemeente FROM Qdos ORDER BY Qdos.deelgemeente;

and applies a filter
to the underlaying query

.... Do you mean the query that is the Record Source for the form? What
exactly is the SQL view of this query? YES! the SQL : SELECT DISTINCTROW dossiers.*, Ttf.*, klanten.*, Twat.*, *

FROM Twat RIGHT JOIN (klanten RIGHT JOIN ((dossiers LEFT JOIN klanten AS
klanten_1 ON dossiers.via = klanten_1.klantID) LEFT JOIN Ttf ON
dossiers.dossierID = Ttf.dossierID) ON klanten.klantID =
dossiers.opdrachtgever) ON Twat.wat = dossiers.wat
ORDER BY dossiers.dossierID;

(there is no need to choose from the other comboboxes
the first combobox already filters the query behind the form on one field
choosing a second comboxitem queries on two fields)

.... Can you please give details and examples to illustrate this process? the header of the form as 4 comboboxes. 1 gets all the towns in the table and afterudpate shows all the records for that town in the query. This first combobox also requeries the other three comboboxes (streets in the town - customurs in the town and cadastral sections in the town). Chosing a second comboxes requeries on town and one of the other 3 comboboxes. SQL : Qdos; [dossiers].[deelgemeente]=[Forms]![Fdos]![klgemlijst] And [dossiers].[straat]=[Forms]![Fdos]![Veld79];

i have been testing
it seems access keeps in mind the first selected item

.... for example? i choose from the first combox town (deelgemeente in dutch) = "Lebbeke" : the form shows all records for "Lebbeke". I make a new choise : town = "Baasrode". The form requeries (you can see it calculating) and shows the results for the first town = "Lebbeke". This is the same for the other comboboxes. Access uses the first pick.

and is unable to
change it
using code has the same effect

What code? : DoCmd.ApplyFilter "Qdos", "[dossiers].[deelgemeente] = [Forms]![Fdos]![klgemlijst]"

Me![klgemlijst].Requery
Me![klnaamlijst].Requery
Me![Veld79].Requery

this worked in access 2003 (mdb).
i saved in 2007 (accdb)

am i using the correct english access word "combobox" for a dropdown list?

thanks for reading all this

 

[landmeter]

i made another reply i dont see here