Help with a subform filter

[The-Plethora]

I am relitively new to access but I am learning

I am creating a database that will hold details of hand held scanners that are sent out to various customers. This records the product and serial number etc. I have got the products and supplier tabs that I have specified on the 1st form "end_user" working fine and put in some basic error checking such as locking fields.
The orders form is to show all the orders that were placed by a particular customer, refered to by "end_user_number (autonumber) primary key" and "end_user_description (Text)"
I can now add orders to the database fine and have managed to get the subform to refresh and show details of the newly added order by tagging the requery command onto the end of the save button, so the record gets saved and then the subform refreshed.
The problem: I just cannot figure what I am doing wrong! The subform in order history displays all the orders from all the users where I want to filter the orders that the current end_user_number is related to. I have tried using the child filter and master filter with no luck and am trying to code a VB line to filter instead (currently a temporary command button on the ordershistory subform) but I am not sure of the syntax. Any ideas greatfully recieved!



Code Attached

 

[ScottGem]

I think the problem here is that you are making things too complex. By basing your forms on joined queries rather than using main/sub forms you are giving yourself headaches.

If you want to show all orders for an end user, bind your main form on the End user table and the subform on the Order History and then link them on End user number. The Subform wizard will walk you through the process. Also you should not have Enuser name in the Orders History table that's redundant.

Take a look at the Northwinds sample database. It does what you are trying to do.

 

[Pat Hartman]

Sounds like you don't have the master/child links set properly on the subform. When they are set, Access will automatically filter the subform so it shows ONLY records related to the current record of the main form.

 

[ScottGem]

Sounds like you don't have the master/child links set properly on the subform. When they are set, Access will automatically filter the subform so it shows ONLY records related to the current record of the main form.

Pat,

Just so you know, that is correct. The links are blank. That was the first thing I looked out. But because the forms are based on multi-table queries, trying to create the links wasn't working.

 

[Pat Hartman]

I took a look at the database. There is too much rework involved for me to fix it plus it is way past my bed time. The main problem is that the relationship between orderHistory and ProductDetails is backwards. The order number needs to go in the product details table and the product information must all be removed from the order history table.

You will need to remove the relationship between the tables while you are fixing the tables. When the columns are moved to their proper tables, you can reestablish the relationship. This time, check the enforce RI box for all relationships. You may have to fix some data to get Access to accept this. Once the relationship is fixed, RI is enforced, and the data is in the appropriate tables, you'll need to fix the queries in both form's recordSources. The query for the main form should NOT contain joins to the tables that the subform will display. The query for the subform should NOT contain joins to the tables that the mainform will display. Don't forget to add the order number to the query for the subform. Once all that's fixed, you can set the master/child links and the form will work properly.

 

[The-Plethora]

Also you should not have Enuser name in the Orders History table that's redundant.

Yeah this is another "fun" thing I realise this is a duplicated field as the tables are related by end_user_number and so I have previously removed it as it doesnt do anything, but when I do and go to the orders form I get "parameter required orderhistory.end_user (or something similar). So that leads me to believe that "end_user" was referenced somewhere in the supporting visual basic code but I can not find it using the find tool in VB on the order form or any other form after searching, and I cant find an object with that same name anywhere and I am thinking I am going to have to do a lot of digging, prehaps it is reffered to somewhere as a default value or something in a properties box?

I will give what pat suggested a try and let you know how I get on. Many thanks for all your help and patience!

 

[The-Plethora]

Forgot to mention. The queries that I have created are just testing that all the fields relate correctly. Using this and running it through the query wizard and adding all the tables apart from product supplier I can see that all details are being updated correctly when I add a record and unique things like serial numbers are being recorded with each record.

Edit: I have attached a version of something I have attempted. I have added a test query that you can run that returns all the details for a user.
Also I have removed the subform on the orders form and now have the subject of this query in place instead

The only problem I have now...

1. The subform seems to be listing the query by product type, I can see all the products are there but I need to use the form < and > record arrows to see the other suppliers but if I select my end user from the 1st form and then seperately run the query alone it lists everything I need to see. So any idea why this subform that I have made the subject of that query is grouping things into suppliers?


I am sure my idea will hit a stone wall and I will have to do things the long way

I have got my query to display the results of the individual end_user_number by using this is the following criteria in the end_user_number in the query design view [Forms]![EndUser]![End_User_Number]

Uploaded changes

 

[The-Plethora]

Quick, Dirty, but it works!

See attached:

On checking the subform it had parent and child fields set up from when I ran the wizard the first time and it was using those to filter the subform by product. Now all the query does is filter the query by end_user_number from the entry in my query end_user_number criteria box.
I can see how easy it is to go wrong though and I am sure I will be better prepared for next time.
Thanks to everyone who offered help in this thread, rare to find so many people willing to help someone not so clued up!

 

[ScottGem]

Quick, Dirty, but it works!

I don't know what worked for you but the forms didn't work for me. Quick and Dirty is NOT the way to design a database. That just leads to spending more time trying to fix or get around the design errors.

Your relations look OK, but you still need to pull the End User field from the Order Details.

But you are still using mutli-table queries as the Recordsource for your forms and I don't see any links between main and subforms on the Order History form.

 

[Pat Hartman]

Apparently, you didn't think my comments had any validity.