how i make a query on combobox selction

[eldarbs]

I have a continuous form with the fields - Category ID Product ID supplier ID. like in the image i attached.
I want to create a query condition that make filtering - once I choosed one of the option in the
categories id combobox then in the produacts ID selection box will show selection that only
products belonging to the same category that was coosed befor and also for supplier ID
combobox will selection of suppliers who provide this product.


I want to know the syntax of the query.
thanx

[June7]

Here is one way: http://datapigtechnologies.com/flash...combobox2.html

However, use of dependent comboboxes that have RowSource with lookup does not work well on continuous forms.

[eldarbs]

That's right, in the example you gave me it works fine, but when I set the condition in the subfrom it ask me each time by a massagebox to enter
the parameter - Manually.
it's look like it does not recognize the parameter that selected in the previous combobox selection, which by the way it's happens rather than in continuous form or any type of a way ofsubform
I set the query conditions actually second combobox as in the picture i attached ..
what i need to change there so that it will run the query true subform ??

[June7]

Did you also build the VBA event procedures to requery the comboboxes?

Want to provide your db for analysis? Follow instructions at bottom of my post.

[eldarbs]

yes i did. i put this :

Private Sub .Product Category cmb fterUpdate()
Me.Product cmb.Requery
End Sub

and the thing is that the system msgbox popup everytime i update the Value in the Product Category CMB, and the message is show that the query in the Product CMB thas'nt get the value from the Product Category CMB after it updated' so it ask me it self...
and also if i try the same in the subfrom it self - when i open it not from the main from but directly, it's work well..
so what is the different that's make it not working if i operate it from the mainform ??

[June7]

Flying blind, really need to see db.

[eldarbs]

thanks for the effort in this thread
i attached a data base that i build for this exampel.
my real data base is based on the nNorthwind database that i modified, just that a lot of it is writting in Hebrew language and And for now i start to translete it to english. but for now the problem itself exists in this database 14 i have attached in the same way so if we will found a solution for it, is likely that I will copy it to my Northwind modified database and if there will be still a problem until then I will translate it completely for chacking it itself.
i ziped the database because of the Weight Limit.
the form that need to be open is "Customer Details"
and in the "Singel form" it work well.
thanks again.

[June7]

Suggest you review http://access.mvps.org/access/lookupfields.htm

I recommend you don't use a lookup for the Region values. I avoid lookups whenever possible. I would just save the region names instead of numeric key in City table.

Also, there is no need to save the region into Details table. The region can always be retrieved by joining to City table in a query.
This sql as RowSource for the city combobox should work, it is like sql I have in my project:
SELECT city.c_id, city.city FROM city WHERE city.r_id=r_cmb ORDER BY city;
However, doesn't work in your form. Made me suspect form corruption. I rebuilt both forms and then the sql worked.

[eldarbs]

hey,
i read the article about the lookup fields, if i understand it right - you mean that if i'm using a combobox, so if the RowSource for it will be query like in the db i send you that successful show in the form the right value - is not the best way ? because in other queries it wont work well - like what ?? ..
and if i understand it right it supouse to be that instad of nomeric values in the datasheet,it will be a text value directly from the fields in the source table - how ??..
about the no need to saving the region in details tabe you meen that - the region combobox that in the from will not bond to any field, just the user make the selection hr needs from it in the form, and the value that will get in the city combobox will fill the field in the details table and then what ? - query that retern the region value that belong to the city that chosed like : SELECT city.r_id, city.city FROM city , written in the region Rowsource in the order table ?? or somathing else.. because it dosn't returen nothing ??

[June7]

Just noticed that the primary key in each table is number field, but saving as foreign key in related tables in a text field. This is a mismatch of data types and won't be able to join these fields in query. Related primary and foreign keys must be the same data type - in this case, number as an autonumber fields is a number type.

I am just saying might want to reconsider use of lookup regarding the region value. Save the region name in city table but no region value in details then anytime you need the associated region, include the city table in query, like:
SELECT details.city, region FROM details LEFT JOIN city ON details.city = city.c_id;

Could even consider the same idea with regard to the city. Make city name the primary key and save the name to details.

Also, you have the primary keys in city and coustomer tables with the same name. Advise different names like city_id and cust_ID.

However, if you assign the city and region name fields instead of the autonumber fields as primary key and save the names as foreign key, there will be no issue of datatype mismatch.

You have detail records with no value in the o_id field.

This is what a query with your current structure (after the fk fields are changed to number type) would look like:
SELECT [Customer Details].o_id, details.o_d_id, coustomer.last_name, coustomer.f_name, city.city, region.region
FROM region RIGHT JOIN (city RIGHT JOIN (coustomer RIGHT JOIN ([Customer Details] INNER JOIN details ON [Customer Details].o_id = details.o_id) ON coustomer.c_id = [Customer Details].c_id) ON city.c_id = details.city) ON region.r_id = city.r_id;

This is a query after changes on region field.
SELECT [Customer Details].o_id, details.o_d_id, coustomer.last_name, coustomer.f_name, details.city, city.region
FROM city RIGHT JOIN (coustomer RIGHT JOIN ([Customer Details] INNER JOIN details ON [Customer Details].o_id = details.o_id) ON coustomer.c_id = [Customer Details].c_id) ON city.c_id = details.city;

[eldarbs]

Hey, sorry for the delay in these it's holidays here.
I went here a little bit lost, so i want to atart over....
1. I asked the in the first for a solution for a query the will make right a filtering for a combobox in continuous sub forms, and you sent me a link to a great example that I copied it to my databases
2. I discovered that there is a problem and it does not work well as I wanted and in that point you said send me the database.
3. I sent you another database with a structure more or less the same - which turn out to be ineffective, especially because there is a option we solve one and not the other one..
4. You gave me some Comments about the basic level of the work with that database and i'm not sure i gut you Completely.. like :
you wrote "Just noticed that the primary key in each table is number field, but saving as foreign key in related tables in a text field..." - I changed all those fields to numbers..
--- "SELECT details.city, region FROM details LEFT JOIN city ON details.city = city.c_id..." - Access gives an error on this query ? ..
and how you can select details.region from details.. when you told mo not to put region field in the details table - "Save the region name in city table but no region value in details.." ??
--- "SELECT [Customer Details].o_id, details.o_d_id, coustomer.last_name, coustomer.f_name, city.city, region.region
FROM region RIGHT JOIN (city RIGHT JOIN (coustomer RIGHT JOIN ([Customer Details] INNER JOIN details ON [Customer Details].o_id = details.o_id)
ON coustomer.c_id = [Customer Details].c_id) ON city.c_id = details.city) ON region.r_id = city.r_id;..." - Also Access gives an error.. and here i lost you also - what ia the purpose of this query ?? - is that the query to solve the main Question of filtering the combobox values ??
5. becouse of all that mabey i should upload the original database and then you can check it on ? and if yes it is a one that based on the nNorthwind database that i modified i translete it back to english,
I upload it here,on the main form you need to click on the "New Customer Orde" button' and then on the open form - "orders form" in the orders details subfrom there is the three combobox with the problem that i mean first.
I will thank you for your's attention and reply..

[June7]

This query pulls region from City table, not Details, because of the LEFT JOIN:
SELECT details.city, region FROM details LEFT JOIN city ON details.city = city.c_id;

[eldarbs]

O.K. i understand it now and it's works (i didn't change the city field in the order table to number type..)
but how i find this help me with the filtering combobox query ?

[June7]

Now that you have the pk/fk fields set as correct datatype, review the tutorial on dependent comboboxes I referenced in post 2.

Can't review your db until later tonight.

[June7]

Suggest you change the query for OrderDetails RecordSource to:
SELECT Orders.*, [Orders Status].[Status Name], [Customers Extended].[Contact Name], [Customers Extended].[Customers Type], [Customers Extended].Company, [Customers Extended].[Mobile Phone], [Customers Extended].[Home Phone], [Customers Extended].City, [Customers Extended].Street, [Customers Extended].[E-mail Address]
FROM [Customers Extended] RIGHT JOIN ([Orders Status] RIGHT JOIN Orders ON [Orders Status].[Status ID] = Orders.[Status ID]) ON [Customers Extended].ID = Orders.[Customer ID];

Aside from that, I can't offer any advice. I don't understand why the form is not allowing edits in some controls, including the subform. When it opens from button New Customer Order, I get nasty error message "A problem occurred while Microsoft Access was communicating with the OLE server". These issues are too complex for me to deal with.