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 InventoryStatus ID < 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_Inve ntory 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_Inve ntory Combo Box, and if this is a New Record, you wish to display all entries in Deliveries_Inve ntory 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_Inve ntory 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.