By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,254 Members | 2,418 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

Can you put a filter expression on a lookup?

P: 39
One to Many relationship: Manufactuerer to Model



Manufactuerer table has [ManufacturerID], [ManufacturerName]


Products Table has [ProductID], [ProductName], [ManufacturerID]


Order table has [OrderID], [ManufacturerID], [ProductID]


In an order form, I'm trying to filter the [ModelID] lookup based on the already selected [ManufacturerID] lookup. I'm looking for some help on what to use as a filter expression, and where to add it for the ModelID lookup. All guidance appreciated guys thanks.
Feb 19 '07 #1
Share this Question
Share on Google+
38 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
In the After Update event of the ManufacturerID combobox (cboManufacturer) you can change the row source of the ModelID combobox (cboModel). This assumes that ManufacturerID is a foreign key in the Model table. It also assumes ManufacurerID is a number type.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboManufacturer_AfterUpdate()
  2.  
  3.   Me.cboModel.RowSource = "SELECT ModelID FROM Model " & _
  4.                   "WHERE ManufacturerID=" & Me.cboManufacturer
  5.   Me.cboModel.Requery
  6.  
  7. End Sub
  8.  
Mary
Feb 20 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
This Tutorial (Example Filtering on a Form.) will give a full explanation of the subject.
Feb 20 '07 #3

P: 39
First off, thanks a ton for the help.

There's one weird thing I can't clear up, but I think I'm almost there. When I make a selection in the cboManufacturer lookup it does filter the ModelID lookup correctly. The only thing is, all the selections are INVISIBLE.

The reason I can tell this, is that if I select a manufacturer that has very few models, the ModelID lookup has very few, invisible, selections. Conversly if I select a manufacturer that has a ton of models, the ModelID lookup has a huge, invisible, list.

If I check the table that the controls are bound to, the form IS updating the records correctly, there I CAN see the values and they're NOT invisible.

So weird!

Here's my code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ManufacturerID_AfterUpdate()
  2.     Me.ModelID.RowSource = "SELECT ID FROM" & _
  3.     " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
  4.     " ORDER BY ModelName"
  5.  
  6.     Me.ModelID = Me.ModelID.ItemData(0)
  7. End Sub
Feb 20 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
What values do you have in your ComboBox for the following properties :
  1. Column Count?
  2. Column Widths?
  3. List Rows?
  4. List Width?
Feb 20 '07 #5

P: 39
Here's the data - hope it makes sense!

Column Count
2

Column Widths?
0";1"

List Rows?
16

List Width?
1"
Feb 20 '07 #6

Rabbit
Expert Mod 10K+
P: 12,366
Here's the data - hope it makes sense!

Column Count
2

Column Widths?
0";1"

List Rows?
16

List Width?
1"
Column Count = 1, you only have one column in your row source.
Column Width = 1", it's invisible because you set your first column to 0", which is the only column you have in the first place.
Feb 20 '07 #7

P: 39
Ohh man. Makes perfect sense. Those settings were left over because the combobox had an ID column that was hidden.

That fixed the problem - I can see it now - only I see the ID numbers instead of the name.

Is there any way to make it work like a normal combo box where it shows the ProductName text column and still actually inputs the ID number?

You rock. Thanks
Feb 20 '07 #8

Rabbit
Expert Mod 10K+
P: 12,366
Expand|Select|Wrap|Line Numbers
  1. Private Sub ManufacturerID_AfterUpdate()
  2.     Me.ModelID.RowSource = "SELECT ID, ProductName  FROM" & _
  3.     " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
  4.     " ORDER BY ModelName"
  5.  
  6.     Me.ModelID = Me.ModelID.ItemData(0)
  7. End Sub
Column Count = 2
Bound Column = 1, this is the column that will be stored in your table. So first column which will be ID.
Column Widths = 0"; 1"
Feb 20 '07 #9

NeoPa
Expert Mod 15k+
P: 31,494
That's what I woulda said :)
Nice one Rabbit.
Feb 20 '07 #10

P: 39
Yeah that makes perfect sense also. Somehow column 2 isn't being populated with the txt name though, It's just blank. With both clumns set to 1", column 1 has the ID's, but column 2 is empty. Is there something in the code that isn't populating it?

It should be coming from the ProductName column in the ProductDetails table.

The ProductDetails table has the following columns:

ID (populated into column 1)
ProductName
ManufacturerID (foreign key to manufacturer Details table)
ProductCost

We're almost there! It's killing me!
Feb 20 '07 #11

Rabbit
Expert Mod 10K+
P: 12,366
You set both to 1"? Did you try 0";1" first? The reason I ask is because your list width is set to 1" which means anything longer than that won't be shown. And if you set both to 1", then only the ID field will show.
Feb 20 '07 #12

P: 39
Yeah I tried that first, then did 1,1 to troubleshoot.

Just now I did 0,2 but that didn't work either, It's just like column 2" is always blank.
Feb 20 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Yeah that makes perfect sense also. Somehow column 2 isn't being populated with the txt name though, It's just blank. With both clumns set to 1", column 1 has the ID's, but column 2 is empty. Is there something in the code that isn't populating it?

It should be coming from the ProductName column in the ProductDetails table.

The ProductDetails table has the following columns:

ID (populated into column 1)
ProductName
ManufacturerID (foreign key to manufacturer Details table)
ProductCost

We're almost there! It's killing me!
Did you change the query to include ProductName as per Rabbit's query in post #9.

Mary
Feb 20 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Just posting a pointer to previous post. As we posted together you may have missed it.

Mary
Feb 20 '07 #15

P: 39
Oh man I did miss it!! I saw the post, but missed the code. I didn't think and just assumed it was a quote of my previous post.

Anyway now it works. Man you guys rock and this site rocks. Thanks so much!
Feb 20 '07 #16

Rabbit
Expert Mod 10K+
P: 12,366
Not a problem, glad to be of assistance.
Feb 20 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
Oh man I did miss it!! I saw the post, but missed the code. I didn't think and just assumed it was a quote of my previous post.

Anyway now it works. Man you guys rock and this site rocks. Thanks so much!
You're welcome. We are always glad to help.
Feb 20 '07 #18

P: 39
Alright here's one more :)

I added some code on the "onChange" event for the modelID that does a DLookup for the corresponding price, and enteres quantity 1. This works great when the modelID is clicked and updated with input. (Hooray I did this all by myself!)

When ModelID is auto-populated from the code, though, it doesn't trigger the "onChange" event. This is only triggered if I manually click the (already populated) selection.

I tried putting the dlookup on the "afterUpdate" and "onDirty" events, but none of them are triggered. I guess it's probably some kind of requiry that needs to happen, but I tried a requery on the modelID at the end of the code that updates it with no luck. Must be something I'm missing. Here's the code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ManufacturerID_Change()
  2.     Me.ModelID.RowSource = "SELECT ID, ModelName  FROM" & _
  3.     " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
  4.     " ORDER BY ModelName"
  5.  
  6.     Me.ModelID = Me.ModelID.ItemData(0)
  7.     ModelID.Requiry
  8. End Sub

Expand|Select|Wrap|Line Numbers
  1. Private Sub ModelID_Change()
  2.     OrdDetCost = DLookup("Cost", "ProductDetails", "ID=" & ModelID)
  3.     Quantity = 1
  4. End Sub
Feb 21 '07 #19

Rabbit
Expert Mod 10K+
P: 12,366
I don't know if this will solve your problem but it's .Requery not .Requiry.
Feb 21 '07 #20

P: 39
You're right I had a typo there, it was just from the pasting into the site though. It's all kosher in the code, but still doesn't work.

Stupid problem eh?
Feb 21 '07 #21

Rabbit
Expert Mod 10K+
P: 12,366
Copy and paste the ModelID_Change code into the ManufacturerID_Change code at the end.
Feb 21 '07 #22

P: 39
Works! Why didn't I think of that? 0.0
Feb 21 '07 #23

Rabbit
Expert Mod 10K+
P: 12,366
Glad to be of help, good luck.
Feb 21 '07 #24

P: 39
Alright guys the database is looking good, just a few issues lurking around though.

Since the form is in datasheet view, when one record is completed another line is automatically created below it where I can enter another order line-item.

The problem is, if I select the manufacturer and model on one line, then go back up to another line to edit the model after making a mistake, then that line's rowsource is set to the wrong manufacturerID. It's filtering the cboxModel based on the cboxManufacturer of the last line entered. Am I making sense?

It filter's based on the last manufacturer selected, not on that specific record's manufacturer.

Anyway, I tried some code to correct this, which sorta works.

Expand|Select|Wrap|Line Numbers
  1. Private Sub ModelID_GotFocus()
  2.     ManufacturerID.Requery
  3.     Me.ModelID.RowSource = "SELECT ID, ModelName  FROM" & _
  4.     " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
  5.     " ORDER BY ModelName"
  6. End Sub
What I'm doing here is resetting the cboxModel's rowsource everytime it gains focus. This way if I go back up a line to edit the model then it shows the correct rowsources. This actually works. The problem is though that when this happens it makes all the other rows cboxModel's disappear. The entry's are still there and will be recorded, but they stop showing since apparently the filter is applying to all rows and making the selections disappear. Hopefully I'm making sense.
Feb 24 '07 #25

Rabbit
Expert Mod 10K+
P: 12,366
I don't know why it's doing that but try putting it in the on current event of the form.
Feb 25 '07 #26

P: 39
I don't know either. It's confusing.

Would you mind taking a look? It's really hard to explain what's going on, but you'll see immediately if you open the database.


I posted the database:

http://home.comcast.net/~bdockery81/PPC.zip

It's in Access 2007, though. Let me know if you need me to save it to 2003.
Feb 27 '07 #27

Rabbit
Expert Mod 10K+
P: 12,366
Sorry, I'm wary of downloading anyone's database. Maybe someone else will take a look.

Have you tried the On Current event? Did that work?
Feb 27 '07 #28

P: 39
Ok I understand. You have nothing to worry about, though.

The on current event made it worse. Every time the event hit it would clear all the modelID's, even if nothing was edited.

Without the onCurrent the ModelID values stay the same as long as nothing is edited.


What happens is that the filter is applied to every row, not just the current one. So the rowsource filter hits and all the sudden the value that was there is filtered and becomes invisible.
Feb 27 '07 #29

Rabbit
Expert Mod 10K+
P: 12,366
Well, I'm stumped. But that's good for you since I'm not the most advanced one here. I'm sure someone else will be able to help you.
Feb 28 '07 #30

MMcCarthy
Expert Mod 10K+
P: 14,534
This sounds like bad structural design. If you are filtering a lookup field in the subform based on a selection in the main form then you shouldn't have the subform in continuous view. There is no way to solve this problem as it is caused by the filter you have created.

Mary
Feb 28 '07 #31

P: 39
It's actually not based on the main form, though. The only thing in the main form is the OrderID and UserID.


The subform has both ManufacturerID and ModelID. ModelID is filtered off of ManufacturerID.
Feb 28 '07 #32

MMcCarthy
Expert Mod 10K+
P: 14,534
It's actually not based on the main form, though. The only thing in the main form is the OrderID and UserID.


The subform has both ManufacturerID and ModelID. ModelID is filtered off of ManufacturerID.
Then in the On current event of the subform put
Expand|Select|Wrap|Line Numbers
  1. Me.ModelID.Requery
Mary
Feb 28 '07 #33

P: 39
Thanks for the help Mary.


I just tried what you suggested above and here's what happens with the modified code:


1. Select manufacturerID, and everything works fine, modelID is filtered and auto-assigned the first selection in the lookup.

Next Row

2. Select manufacturerID and modelID is autofiltered as it should, but now the first row's modelID lookup changes to be blank. Also, now the first row's modelID lookup is filtered based off of the 2nd rows ManufacturerID, instead of the 1st row's as it should.

Thanks a lot for the help. It's really appreciated
Feb 28 '07 #34

MMcCarthy
Expert Mod 10K+
P: 14,534
You can't have them filtered separately. This is why a continuous view isn't desireable. Once you change the filter for one record it will change for all records in the view. There is no way around that. You either suffer the current status or change the view to single form rather than continuous.

Sorry but there is no other solution.

Mary
Feb 28 '07 #35

P: 39
Man it seems like what I'm doing should be the most common thing in the world. It's so hard to believe it's like this.


What's weird is that if I'm down to row 4 or 5, and go up and change the manufacturerID on row 1, it doesn't cause any problems. This is changing the rowsource filter, as shown in this code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ManufacturerID_AfterUpdate()
  2.  ' Filter the model cboBox based on the selection in the Manufacturer cboBox
  3.     Me.ModelID.RowSource = "SELECT ID, ModelName  FROM" & _
  4.     " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
  5.     " ORDER BY ModelName"
  6.     Me.ModelID = Me.ModelID.ItemData(0)
  7. end sub
but it doesn't make the other modelID's from all the other rows disappear.



If the SAME code is triggered by the ModelID, a gotFocus for example, it does cause the modelID lookup from the other rows to disappear.

Expand|Select|Wrap|Line Numbers
  1. Me.ModelID.RowSource = "SELECT ID, ModelName  FROM" & _
  2.     " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
  3.     " ORDER BY ModelName"
  4. end sub
Feb 28 '07 #36

NeoPa
Expert Mod 15k+
P: 31,494
As Mary says, cascading ComboBoxes cannot work in a sensible way on a datasheet form. This is because there is only one definition for the ComboBox control. When you change the Row Source then there is no question of any of the ComboBox instances keeping their old Row Source.
Your only option, as far as I can see, is to convert your form to single-form view.
To lose the functionality of your cascading ComboBox would be a shame.
Mar 2 '07 #37

P: 39
I've almost got it "tricked" by re-applying the row-source code every time a model field gains focus, but it's never going to be quite right, I guess.

It seems like I'm just doing a very standard order processing setup that a million other people have probably done. Of course you're going to need multiple line-items on each order. So crazy.

Can anyone think of any other way to have multiple items associated to a single order that might work?
Mar 2 '07 #38

NeoPa
Expert Mod 15k+
P: 31,494
When entering or changing the data, go to a separate form (or maybe sub-form). When displaying the order as a whole, you can show the order detail lines in a list.
That's how the orders are displayed on our order-entry system (Not MS Access I may add).
Mar 2 '07 #39

Post your reply

Sign in to post your reply or Sign up for a free account.