Need to filter combo box but only new records

[Richard Penfold]

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...

SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, [Inventory Status].InventoryStatus
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

[ADezii]

@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:

Dim strSQL As String
 
If Me.NewRecord Then
  strSQL = "SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, " & _
           "[Inventory Status].InventoryStatus FROM [Inventory Status] INNER JOIN " & _
           "Inventory ON [Inventory Status].InventoryStatusID = Inventory.InventoryStatusID;"
Else
  strSQL = "SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, " & _
           "[Inventory Status].InventoryStatus FROM [Inventory Status] INNER JOIN " & _
           "Inventory ON [Inventory Status].InventoryStatusID = Inventory.InventoryStatusID " & _
           "WHERE [Inventory Status].InventoryStatusID < 5;"
End If
 
Me![cboFirstCombo].RowSource = strSQL

[Richard Penfold]

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

Record Source = Deliveries_Inventory
Row Source = SELECT Inventory.InventoryID,
                    Inventory.ProductCode,
                    Inventory.SerialNumber,
                    [Inventory Status].InventoryStatus
             FROM [Inventory Status] INNER JOIN Inventory
               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

SELECT Inventory.InventoryID,
       Inventory.ProductCode,
       Inventory.SerialNumber,
       [Inventory Status].InventoryStatus
FROM [Inventory Status] INNER JOIN Inventory
  ON [Inventory Status].InventoryStatusID = Inventory.InventoryStatusID
WHERE ((([Inventory Status].InventoryStatus)<>"Sold"));

Else

SELECT Inventory.InventoryID,
       Inventory.ProductCode,
       Inventory.SerialNumber,
       [Inventory Status].InventoryStatus
FROM [Inventory Status] INNER JOIN Inventory
  ON [Inventory Status].InventoryStatusID = Inventory.InventoryStatusID;

End If

But I could be wrong...

[ADezii]

@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?

[Richard Penfold]

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.

[ADezii]

@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.

[Richard Penfold]

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.

[ADezii]

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?

[Richard Penfold]

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"?

[ADezii]

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.

[NeoPa]

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.

[puppydogbuddy]

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'

[puppydogbuddy]

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.

[Richard Penfold]

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'.

[FishVal]

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.

[ADezii]

@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):

Private Sub Form_Current()
Dim strSQL As String
 
If Me.NewRecord Then
  strSQL = "SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, " & _
           "[Inventory Status].InventoryStatus FROM [Inventory Status] INNER JOIN Inventory " & _
           "ON [Inventory Status].InventoryStatusID=Inventory.InventoryStatusID WHERE " & _
           "[Inventory Status].InventoryStatus <> 'Sold';"
Else
  strSQL = "SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, " & _
           "[Inventory Status].InventoryStatus FROM [Inventory Status] INNER JOIN Inventory " & _
           "ON [Inventory Status].InventoryStatusID=Inventory.InventoryStatusID;"
End If
 
Forms!Deliveries![Deliveries SubForm].Form![Deliveries_InventoryID].RowSource = strSQL
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)!

[Richard Penfold]

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

[ADezii]

@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!

[NeoPa]

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.