 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / March 2005Filtering a form by a different table
G'mornin' all! I've been trying to do this for a couple of days and I've had enough of struggling so I'm hoping one of you has tried this before! I've got a form with contact details of companies on it. It's based on a table called 'MainData'. I've also got a table with a list of product types, it's called 'ProductDimension'. I want people to be able to filter the companies by the product type. I've made a form that just has a drop down with product types, press a button and the contact details form shoud be opened with a filter applied. It never works! I always get an access prompt asking me for the product type, but even if I enter it there it doesn't work. Can anyone please help me! Thanks very much in advance.. Pete  SignatureCheers, Foss Foss Any chance your underlying data/tables use the "lookup" data type field? This causes considerable confusion, as it stores something different than it displays... You told us about two tables, but didn't "connect the dots". How does Access know which Companies are connected to which Products? SignatureMore info, please ... Jeff Boyce <Access MVP> > G'mornin' all! > [quoted text clipped - 16 lines] > Thanks very much in advance.. > Pete Jeff, Thanks for the speedy response! Sorry about the lack of info, I'm never sure what people need to know! :-S There's another table that I didn't mention, it's called ProductList and this contains the information connecting the other two tables. Here's how: - MainData connects to ProductList using an autonumber field. - ProductList connects to ProductDimension using the product code which is an integer. I believe that some fields in both MainData and ProductDimension have lookups associated with them. By that I mean that to enter data into the tables you can just use a drop down or you can type the value in. Does that explain it any better? Cheers, Pete > Foss > [quoted text clipped - 26 lines] > > Thanks very much in advance.. > > Pete Pete Thanks for greater detail -- it's tough offering specific advise on general questions! First thing to consider is to work in forms, not directly in the tables (tables store data, forms display it). Take a quick scan through the tablesdbdesign newsgroup on the topic of lookup fields. You'll find a STRONG consensus against using them in table definitions, for the reason in my previous post. I assume your Main and ProductList tables connect on a field that is a LongInt. In Main, it is an Autonumber, right? But in ProductList, it is a LongInt, right?? To do the filtering your original post suggesting, use a form. Put a combo box in the form that lists Product types. In the AfterUpdate event of that combo box, requery a listbox. The listbox uses a query that lists Companies, but uses, as a criterion, a "product" ... which you get from referring to the form! Use something like (your syntax may vary): Forms!frmYourForm!cboYourComboBox as the criterion for the query that underlies a listbox on frmYourForm. SignatureGood luck Jeff Boyce <Access MVP> > Jeff, > [quoted text clipped - 15 lines] > Cheers, > Pete Jeff, Too true! Thanks for the assistance, it's much appreciated! Right, I've taken off all the lookup things from any tables. They only happen in forms now. All the fields that are used in joins are longints, except the one autonumber of course! I'm not sure if your suggestion will fit what I'm trying to do. Although to be honest, that may have a lot to do with the fact that I'm getting a little over my head!! I've got a form called ChooseType. All it's got on it is a drop down with all the products, and a button. What I'm trying to work towards, is that when that button is pressed, the existing contact details form will open. The only difference should be that only the companies dealing with the product are displayed. So I suppose, come to think of it, I need a WHERE statement, that I can put on the form. I've just given that a try but I can't get it right at all. This is what I used as the form filter: KompassDimension.[ProductType]="Fruit" Any idea why this doesn't work? Cheers, Pete > Pete > [quoted text clipped - 42 lines] > > Cheers, > > Pete Pete Not sure I quite understand yet -- let me try this... If a user selects a "Produce", you want them to see all Companies offering that product. If so, one way to do that is to put an unbound combo box in the header of the form. This combo box only shows products. The form itself shows Companies, but only shows Companies for the selected product. You do this by building a query that uses a parameter for the Product, and in the criterion, you "point" to the form's combo box to get the Product. The query criterion looks something like: Forms!frmYourForm!cboYourComboBox Hope that helps! Jeff Boyce <Access MVP> > Jeff, > [quoted text clipped - 72 lines] > > > Cheers, > > > Pete |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.