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

Need to filter combo box but only new records

P: 24
I have a form with a subform in my database that I use to manage deliveries. A combo box on the subform displays items of inventory and thier current status. The code behind the combo box is...
Expand|Select|Wrap|Line Numbers
  1. SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, [Inventory Status].InventoryStatus
  2. FROM [Inventory Status] INNER JOIN Inventory ON [Inventory Status].InventoryStatusID = Inventory.InventoryStatusID;
A second combo box on the same subform allows me to change the inventory status; eg: from 'Available' (1) to 'Sold' (5).

The subform allows me to display multiple items for delivery. When I select a new item for delivery I do not want to see any of the items that have a status of Sold. I have tried setting the criteria in the combo box query to InventoryStatusID < 5 but this clears the previous entries where I set the status to Sold.
I think I need to put an 'IF New Record' statement into the combo box query. Something like...
'If new record select from Inventory where Inventory status less than 5, Else select unfiltered'.

Can someone help please
Jun 23 '09 #1
Share this Question
Share on Google+
18 Replies


ADezii
Expert 5K+
P: 8,623
@Richard Penfold
I am a little hazy on your request, but to me it appears that you wish to dynamically change the RowSource of the 1st Combo Box based on whether or not it is a New Record. The general idea would be something similar to:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. If Me.NewRecord Then
  4.   strSQL = "SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, " & _
  5.            "[Inventory Status].InventoryStatus FROM [Inventory Status] INNER JOIN " & _
  6.            "Inventory ON [Inventory Status].InventoryStatusID = Inventory.InventoryStatusID;"
  7. Else
  8.   strSQL = "SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, " & _
  9.            "[Inventory Status].InventoryStatus FROM [Inventory Status] INNER JOIN " & _
  10.            "Inventory ON [Inventory Status].InventoryStatusID = Inventory.InventoryStatusID " & _
  11.            "WHERE [Inventory Status].InventoryStatusID < 5;"
  12. End If
  13.  
  14. Me![cboFirstCombo].RowSource = strSQL
Jun 23 '09 #2

P: 24
Hi ADezii,
You have the right idea but I don't think I gave you enough information in my first post.
Deliveries_Inventory is a bound control combo box
Expand|Select|Wrap|Line Numbers
  1. Record Source = Deliveries_Inventory
  2. Row Source = SELECT Inventory.InventoryID,
  3.                     Inventory.ProductCode,
  4.                     Inventory.SerialNumber,
  5.                     [Inventory Status].InventoryStatus
  6.              FROM [Inventory Status] INNER JOIN Inventory
  7.                ON [Inventory Status].InventoryStatusID=Inventory.InventoryStatusID;
When I select an item from inventory to deliver I need to filter out inventory that already has a status of 'Sold' , but still be able to see records on the same subform that were processed previously that now have a status of 'Sold'

I imagine I can do this if I can include If or Case statements as part of the row Source, similar to that shown below

If
New Record
Expand|Select|Wrap|Line Numbers
  1. SELECT Inventory.InventoryID,
  2.        Inventory.ProductCode,
  3.        Inventory.SerialNumber,
  4.        [Inventory Status].InventoryStatus
  5. FROM [Inventory Status] INNER JOIN Inventory
  6.   ON [Inventory Status].InventoryStatusID = Inventory.InventoryStatusID
  7. WHERE ((([Inventory Status].InventoryStatus)<>"Sold"));
Else
Expand|Select|Wrap|Line Numbers
  1. SELECT Inventory.InventoryID,
  2.        Inventory.ProductCode,
  3.        Inventory.SerialNumber,
  4.        [Inventory Status].InventoryStatus
  5. FROM [Inventory Status] INNER JOIN Inventory
  6.   ON [Inventory Status].InventoryStatusID = Inventory.InventoryStatusID;
End If

But I could be wrong...
Jun 24 '09 #3

ADezii
Expert 5K+
P: 8,623
@Richard Penfold
So, if I understand you correctly, after you select an Item from the Deliveries_Inventory Combo Box, and if this is a New Record, you wish to display all entries in Deliveries_Inventory that are not Sold. Again, if it is a New Record, you also wish to display Records in the Sub-Form that are Sold?
Jun 24 '09 #4

P: 24
Hi ADezii,
That is correct mate. I think I have the query right but I do not know the syntax to turn the queries into an If-then-else statement that I can put into query builder.
Jun 24 '09 #5

ADezii
Expert 5K+
P: 8,623
@Richard Penfold
I do not know the syntax to turn the queries into an If-then-else statement that I can put into query builder.
Kindly explain what you mean.
Jun 24 '09 #6

P: 24
Hi ADezii,
Lets start at the beginning...
I have a Transactions table that has a 1-to-many relationship with Transaction Details table. Transaction Details has a 1-to-many relationship with Deliveries table. I also have an Inventory Status table that has a 1-to-many relationship with an Inventory table. The Inventory table itself has a 1-to-many relationship with the Deliveries table. Referential integrity is enforced on all joins.

My form is a Main form/Subform. Main form bound to Transactions Table, Subform bound to Deliveries Table. On the subform I have a bound control Combo box The control source is bound to the Deliveries table. The Row source selects from Inventory and Inventory Status tables. I have formatted the combo box to display product code, serial number and status in the drop down list but only the product code after selecting from the list. Other fields on my form are auto completed resulting from my selection. For any one transaction there can be many inventory items delivered. Simplisticly the form looks like this:
___________
Main
Customer Order Number Other details
___________
SubForm
Inventory Item Serial Number Status Delivery Date
Inventory Item Serial Number Status Delivery Date
Inventory Item Serial Number Status Delivery Date
etc...

Now to the problem...
In Design Mode...When I open the properties of Deliveries_Inventory combo box and click the Row Source 'Build' button, it launches Query Builder. In Query Builder I have a query to select the fields I want from the Inventory and Inventory Status tables. When I return to Forms Mode the combo box lists all inventory items by product code, serial number and status. Using this form I select from inventory, items that my customer has requested. Using another control on the form I set the status of the item selected to Sold so that I do not try to sell the same item twice. Over time, many inventory items will be sold and I do not want to trawl through an endless list to find items that are 'Available'.

I can set a criteria on the combo box in Query Builder to filter out inventory items that have a status of sold but when I move from one transaction to another, items selected previously are now blank. I need items already entered not to be filtered out by the query but new entries filtered so that I do not see previously sold items listed.

I think I should be able to do this in Query Builder by editing the query in SQL View but I do not know how to code the 'If New - Then - Else' statements I need around the queries.
Jun 25 '09 #7

ADezii
Expert 5K+
P: 8,623
Hello Richard Penfold. At this point I am hopelessly lost and cannot comprehend exactly what the problem is without some visual cue. Is there any possibility of sending me the DB to my private E-Mail Address?
Jun 25 '09 #8

P: 24
Hi ADezii,
It will take some time to get a copy of the database to you but I guess I overcomplicated the problem with my explanation. If we focus on the combo box alone I think you can help.

The dropdown list as viewed in the combo box design grid is attached to this post.

Column 1 is hidden so the combo box drop down list displays: Product, Serial Number and Status

After making a selection from the drop down list, the combo box is populated with that selection. Each time I make a selection, a new row is generated on my form.
The combo box in the new row is blank (a new record) until I make a selection from the drop down list.
An image of the form is also attached to this post.

Initially, the Inventory Status is set to "Available". After making a selection I change this status using another control on my form, to "Sold"

If I set a criteria for the combo box: 'Inventory Status <> "Sold"', on reopening the form, the drop down list does not include items with the status "Sold" but the combo box is also blank in all previous rows.

How do I make the combo box not list items where the status is "Sold" but still populate previous rows with my selection, now having a status of "Sold"?
Attached Images
File Type: jpg Design Grid.jpg (18.3 KB, 469 views)
File Type: jpg Form.jpg (12.8 KB, 290 views)
Jun 27 '09 #9

ADezii
Expert 5K+
P: 8,623
Hello Richard. For some reason or another I am really stuck on this Thread, and I do apologize for my incompetence in this matter. The good news is that I requested a few of my comrades to have a look at it for you. If you still have the capability to E-Mail the DB to me in the meantime, I would be happy to have a look at it. Just let me know, and I'll send you my E-Mail Address in a Private Message.
Jun 27 '09 #10

NeoPa
Expert Mod 15k+
P: 31,419
I will certainly have a look at this when I get some time.

A bit tied up travelling & transporting just now - getting son home from unversity etc.

PS. I have some experience in these matters and I very much doubt the problem will be ADezii's incompetence. For your part Richard (other than struggling to remember to use the code tags), my impression, without a good look yet, is that you take some pains to express your questions as clearly as you can.

If you do manage to send a copy of the database to ADezii, can I ask for your permission for him to send a copy on to me to review too? He already has my private details, and I prefer not to share them generally.
Jun 27 '09 #11

puppydogbuddy
Expert 100+
P: 1,923
Hi Dezii and Richard,
In response to your question<<<How do I make the combo box not list items where the status is "Sold" but still populate previous rows with my selection, now having a status of "Sold"? >>>

try changing your criteria to this:
Inventory Status Like 'Available'
Jun 27 '09 #12

puppydogbuddy
Expert 100+
P: 1,923
Richard,
By the way the syntax of the statement I gave you above is how it should look in the criteria row of the query grid that you attached. I believe the syntax you used >>> 'Inventory Status <> "Sold"' is not correct based on the way the quotation marks were used.
Jun 27 '09 #13

P: 24
Hi All,
Thank you for responding.

puppydogbuddy,
Thanks for your input. I tried your suggestion but the end result is the same as mine. It does however give me the opportunity to demonstrate the problem more clearly.
If I create a criteria to filter out items with a status of sold, it works fine for new records but also blanks all previous records. Please see images attached.

ADezii,
Thanks for escalating my issue - I had not realised that this community could react in this way.
Please advise the email address you want me to send a zipped copy of my database to - On receipt please copy the database to NeoPa

NeoPa,
Thanks for getting involved again - I thought I had consumed enough of your time previously.
I am going to have to re-read the instruction book again as I do not know how to create tags for the code I put into a post. I apologise for my error.
puppydogbuddy's suggestion demonstrates the issue. Compare the images attached below with those attached previously. I get all or nothing - Perhaps I am being greedy - I want both.
This is why I am looking for some way to allow previous entries (now 'Sold' status) to be displayed along with new entries where the drop down list does not include 'Sold'.
Attached Images
File Type: jpg Criteria.jpg (16.0 KB, 368 views)
File Type: jpg Filtered.jpg (19.7 KB, 398 views)
Jun 28 '09 #14

FishVal
Expert 2.5K+
P: 2,653
Hello, Richard.

Let me add my 5c to the discussion.
  • First and foremost you need to realize that in continuous or datasheet form controls set in one row is the same controls set that in another. Changing properties of one control will be applied to all the set of rows.
  • Combobox control though capable to display data which is not in the list of combobox's rows will do it only in a certain conditions - relevant thread, however I guess it is not applicable in your case.
  • Making combobox behave as you like in datasheet or continuous form is almost a hopeless try. You'd better think about another visual interface like Listbox-to-Listbox or Listbox-to-Subform.
  • Determining item to be available or not in the way you do it, seems to me not suitable since it looses history context - item once defined as unavailable becomes unavailable forward and backward. That means you couldn't determine available items in context of any previous order - if you ever want to go back to previous one (e.g. to make corrections) you will be unable to retrieve relevant items available in context of this order.

Regards,
Fish.
Jun 28 '09 #15

ADezii
Expert 5K+
P: 8,623
@Richard Penfold
Hello Richard. I downloaded your Database, and I do believe that I have arrived at a solution. In the Current() Event of the Deliveries Subform, copy and Paste the following Code (not the 1st and last lines which are for display only):
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim strSQL As String
  3.  
  4. If Me.NewRecord Then
  5.   strSQL = "SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, " & _
  6.            "[Inventory Status].InventoryStatus FROM [Inventory Status] INNER JOIN Inventory " & _
  7.            "ON [Inventory Status].InventoryStatusID=Inventory.InventoryStatusID WHERE " & _
  8.            "[Inventory Status].InventoryStatus <> 'Sold';"
  9. Else
  10.   strSQL = "SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, " & _
  11.            "[Inventory Status].InventoryStatus FROM [Inventory Status] INNER JOIN Inventory " & _
  12.            "ON [Inventory Status].InventoryStatusID=Inventory.InventoryStatusID;"
  13. End If
  14.  
  15. Forms!Deliveries![Deliveries SubForm].Form![Deliveries_InventoryID].RowSource = strSQL
  16. End Sub
P.S. - The Database is too large to display here, but once you verify that this is, in fact, the solution, I'll send it to you privately. Please tell me that this is what you are looking for (LOL)!
Jun 30 '09 #16

P: 24
Hi Adezii,
Your code works beautifully!

There is a minor bug but I can live with it. If you move to a transaction with no deliveries, then to a transaction where a deliveries record is populated, the first populated record is blank until you click in the box.

Again many, many thanks
Jul 2 '09 #17

ADezii
Expert 5K+
P: 8,623
@Richard Penfold
You are quite welcome, I still have the DB and when I get a chance, I'll have a look at the 'BUG'. BTW, you are going to have to be a little more specific. What Main Form/Sub-Form/Control combination are we talking about?

@NeoPa, puppydogbuddy, FishVal, OldBirdman, and missinglinq. Thanks for responding to this Thread - I know you guys are always there when the 'Call' comes in!
Jul 2 '09 #18

NeoPa
Expert Mod 15k+
P: 31,419
I like to when I can ADezii.

Unfortunately, as you know, I've been off with health problems so haven't responded to much recently.

I'm glad this was resolved anyway, and I'm hoping to get back on more often soon.
Jul 23 '09 #19

Post your reply

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