Unique values across 2 fields from different tables

[Micron]

So are you saying the query isn't working on the overflow field because of the empty values?

Can't say. I didn't download your attachments since 9 times out of 10 I can't open or work with 2016 db's thus I'm not sure what stage you're at (trying to fix cascading combos or a final query based on their values). I don't think <> or Not In works with Null as Null basically means "value unknown". Therefore you cannot compare it to anything else and I suspect you can't 'not' select it since it really doesn't exist. I take it from your post 13 that "doesn't work" is a result of this, although as my signature states, "doesn't work" doesn't usually help us. Is Not Null (queries) or Not IsNull() (vb) is how you deal with Null.

[June7]

See if this is workable.

Create and save a UNION query:
SELECT allocated_bin_1 AS AllocatedBin FROM tblProduct
UNION SELECT allocated_bin_2 FROM tblProduct;

Code behind frmProduct:

Option Compare Database
Option Explicit

Private Sub Form_Current()
Me.cbxBin2.Visible = Not IsNull(Me.cbxBin1)
Me.cbxBin1.Requery
Me.cbxBin2.Requery
End Sub

Private Sub cbxBin1_GotFocus()If Me.NewRecord Then
    Me.cbxBin1.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
    "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
    "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
    "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
    "WHERE AllocatedUNION.AllocatedBin Is Null " & _
    "ORDER BY tblBinType.bin_type, tblBinType.priority;"
    Me.cbxBin2.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
    "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
    "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
    "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
    "WHERE AllocatedUNION.AllocatedBin Is Null " & _
    "ORDER BY tblBinType.bin_type, tblBinType.priority;"
Else
    Me.cbxBin1.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
    "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
    "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
    "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
    "WHERE (((AllocatedUNION.AllocatedBin)=[Forms]![frmProduct]![allocated_bin_1] Or (AllocatedUNION.AllocatedBin) Is Null)) " & _
    "ORDER BY tblBinType.bin_type, tblBinType.priority;"
    
    Me.cbxBin2.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
    "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
    "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
    "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
    "WHERE (((AllocatedUNION.AllocatedBin)=[Forms]![frmProduct]![allocated_bin_2] Or (AllocatedUNION.AllocatedBin) Is Null)) " & _
    "ORDER BY tblBinType.bin_type, tblBinType.priority;"
End If
Me.cbxBin1.Requery
Me.cbxBin2.Requery
End Sub

Private Sub cbxBin1_GotFocus()
Me.cbxBin1.Dropdown
End Sub

Private Sub cbxBin1_AfterUpdate()
Me.cbxBin2.Visible = Not IsNull(Me.cbxBin1)
End Sub

Private Sub cbxBin2_GotFocus()
Me.cbxBin2.Dropdown 'this one isn't working and the dropdown click is acting odd and I don't know why
End Sub

[anthgav]

I renamed my combo boxes and union query to match your code and now I'm getting a syntax error...




--------------------------------------------------------------------------------------------------------------------------

On another note I tried filtering out the allocated bins from 'allocated_bin_1' and 'allocated_bin_2' in a saved query.
I created this sql first to show which bins are allocated...

SELECT tblProduct.allocated_bin_1 AS [allocated_bin_to_product]
FROM (tblBin RIGHT JOIN (tblBinType RIGHT JOIN tblAllocatedBin ON tblBinType.[bin_type_id] = tblAllocatedBin.[allocated_bin_type]) ON tblBin.[bin_id] = tblAllocatedBin.[allocated_bin]) RIGHT JOIN tblProduct ON tblAllocatedBin.[allocated_bin_id] = tblProduct.[allocated_bin_1]
UNION ALL
SELECT tblProduct.allocated_bin_2  AS [allocated_bin_to_product]
FROM tblProduct LEFT JOIN (tblBin RIGHT JOIN (tblBinType RIGHT JOIN tblAllocatedBin ON tblBinType.[bin_type_id] = tblAllocatedBin.[allocated_bin_type]) ON tblBin.[bin_id] = tblAllocatedBin.[allocated_bin]) ON tblProduct.allocated_bin_2 = tblAllocatedBin.allocated_bin_id
WHERE (((tblProduct.allocated_bin_2)<>0));

Then I merged the previous sql into this query to show what bins are available...

SELECT tblProduct.allocated_bin_1 AS [allocated_bin_to_product]
FROM (tblBin RIGHT JOIN (tblBinType RIGHT JOIN tblAllocatedBin ON tblBinType.[bin_type_id] = tblAllocatedBin.[allocated_bin_type]) ON tblBin.[bin_id] = tblAllocatedBin.[allocated_bin]) RIGHT JOIN tblProduct ON tblAllocatedBin.[allocated_bin_id] = tblProduct.[allocated_bin_1]
UNION ALL
SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type, tblBinType.priority
FROM tblBinType RIGHT JOIN (tblBin RIGHT JOIN (tblAllocatedBin LEFT JOIN qryAllocatedBinsToProduct ON tblAllocatedBin.allocated_bin_id = qryAllocatedBinsToProduct.allocated_bin_to_product) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type
WHERE (((tblAllocatedBin.allocated_bin_id) Not In ([qryAllocatedBinsToProduct].[allocated_bin_to_product])));

When I use the final query in my combo box I can finally produce the desired result I've been looking for to only show the bins available, but now I've hit another snag. I can only get the allocated_bin_id to show up in the combobox selected text field and not the bin name.
The combobox drop down list show 'ID, Bin, Bin Type, Priority' all in that order. My column count is 4 and the widths are '0cm;3cm;3cm;3cm' yet the collapsed box is blank even though a bin is allocated to that product. When the widths are set to '3cm;3cm;3cm;3cm' the ID shows in the box ok. The allocated_bin_id is the value stored in the control source of the combo box and the bin name is stored in another table. This issue has arose since I've used a pre-built query and not attaching the combobox directly to the tables. Any idea why this is?

[Micron]

Re: the syntax error: This Private Sub cbxBin1_GotFocus()If Me.NewRecord This
Private Sub cbxBin1_GotFocus()If Me.NewRecord Then
is really 2 lines; probably was a cut & paste error -
Private Sub cbxBin1_GotFocus()
If Me.NewRecord Then

[anthgav]

I'm now getting this error with the code...

[anthgav]

I've decided I want to try and solve this without using VB code and I'm close. As I've mentioned in my earlier post I have created queries that filter the bins to only show what doesn't have products in. I have called the query from my combobox with this code...

SELECT qryAvailableBinsForProduct.allocated_bin_id, qryAvailableBinsForProduct.bin, qryAvailableBinsForProduct.bin_type
FROM qryAvailableBinsForProduct
ORDER BY qryAvailableBinsForProduct.bin;

As you can see from the images my column count and width are set and the combobox shows the expected result.



As the combobox shows the ID, I change the width of the 1st column to 0 so it displays the BIN number but I get a blank value instead, this is the bit that has me stumped, why isn't it behaving as I expect? Does it have anything to do with using a pre-built query on the combobox?

[June7]

Yes, it does. Combobox RowSource is a lookup with alias. The combobox list excludes items already used. This means the alias value is not available for display. Hence the VBA code to conditionally set the RowSource.

And that was a cut/paste error as Micron explained. Second one this week. I really need to double check. Line that errors needs to be two lines.

Private Sub cbxBin1_GotFocus()
  If Me.NewRecord Then

[anthgav]

I corrected that line but then I got this error.

I'm now getting this error with the code...

[June7]

Ambiguous name means there are two procedures with the same name of cbxBin1_GotFocus. If you did copy/paste of my code, you probably already had this event and my code also has the event. Delete one of them.

When you do VBA edits, need to run Debug>Compile. Also, periodically Compact & Repair.

[anthgav]

Your code is now up and running and everything works as expected. I've now hit a snag.
In the queries there are some ORDER BY statements...

"ORDER BY tblBinType.bin_type, tblBinType.priority;"

I need the fields to be ordered like this instead...

"ORDER BY tblBin.bin, tblBinType.priority;"

tblBin.bin is the field that shows as the bound value to the combobox so when I change the ORDER BY to tblBin.bin the combobox field is now blank, so I'm back to square one again, how can I get around this?

[June7]

I am confused. The comboboxes BoundColumn is 1 and the field is tblAllocatedBin.allocated_bin_id. ORDER BY any field in the query. I adjusted to order by tblBin.bin and works just fine.

[anthgav]

In your original code you repeated 'Private Sub cbxBin1_GotFocus()' twice causing an error. In the 2nd one you had...

Private Sub cbxBin1_GotFocus()
Me.cbxBin1.Dropdown
End Sub

I assume I need to move...

Me.cbxBin1.Dropdown

into the first GotFocus, where abouts should I put it in the code below?

Private Sub cbxBin1_GotFocus()If Me.NewRecord Then    Me.cbxBin1.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
    "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
    "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
    "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
    "WHERE AllocatedUNION.AllocatedBin Is Null " & _
    "ORDER BY tblBinType.bin_type, tblBinType.priority;"
    Me.cbxBin2.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
    "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
    "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
    "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
    "WHERE AllocatedUNION.AllocatedBin Is Null " & _
    "ORDER BY tblBinType.bin_type, tblBinType.priority;"
Else
    Me.cbxBin1.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
    "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
    "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
    "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
    "WHERE (((AllocatedUNION.AllocatedBin)=[Forms]![frmProduct]![allocated_bin_1] Or (AllocatedUNION.AllocatedBin) Is Null)) " & _
    "ORDER BY tblBinType.bin_type, tblBinType.priority;"
    
    Me.cbxBin2.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
    "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
    "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
    "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
    "WHERE (((AllocatedUNION.AllocatedBin)=[Forms]![frmProduct]![allocated_bin_2] Or (AllocatedUNION.AllocatedBin) Is Null)) " & _
    "ORDER BY tblBinType.bin_type, tblBinType.priority;"
End If
Me.cbxBin1.Requery
Me.cbxBin2.Requery
End Sub

[June7]

Sorry for that copy/paste error. I should have taken another look at my code after Micron's post 19. Unfortunately can no longer edit the earlier post. My code in the Current event is actually:

Private Sub Form_Current()
Me.cbxBin2.Visible = Not IsNull(Me.cbxBin1)

If Me.NewRecord Then
    Me.cbxBin1.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
...

[anthgav]

Ah, that makes sense now. Here is my final code, I've tested and everything is functioning as expected.
Can you just confirm it looks correct, I've renamed my combos to my own name and changed the visibility to enabled instead.

Option Compare Database
Option Explicit


Private Sub Form_Current()
    Me.secondaryBinSelectBox.Enabled = Not IsNull(Me.masterBinSelectBox)
    If Me.NewRecord Then
        Me.masterBinSelectBox.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
        "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
        "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
        "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
        "WHERE AllocatedUNION.AllocatedBin Is Null " & _
        "ORDER BY tblBin.bin, tblBinType.priority;"
        
        Me.secondaryBinSelectBox.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
        "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
        "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
        "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
        "WHERE AllocatedUNION.AllocatedBin Is Null " & _
        "ORDER BY tblBin.bin, tblBinType.priority;"
    Else
        Me.masterBinSelectBox.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
        "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
        "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
        "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
        "WHERE (((AllocatedUNION.AllocatedBin)=[Forms]![frmProduct]![allocated_bin_1] Or (AllocatedUNION.AllocatedBin) Is Null)) " & _
        "ORDER BY tblBin.bin, tblBinType.priority;"
        
        Me.secondaryBinSelectBox.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type " & _
        "FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (AllocatedUNION RIGHT JOIN tblAllocatedBin ON " & _
        "AllocatedUNION.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
        "tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
        "WHERE (((AllocatedUNION.AllocatedBin)=[Forms]![frmProduct]![allocated_bin_2] Or (AllocatedUNION.AllocatedBin) Is Null)) " & _
        "ORDER BY tblBin.bin, tblBinType.priority;"
    End If
    Me.masterBinSelectBox.Requery
    Me.secondaryBinSelectBox.Requery
End Sub


Private Sub masterBinSelectBox_GotFocus()
    Me.masterBinSelectBox.Dropdown
End Sub


Private Sub masterBinSelectBox_AfterUpdate()
    Me.secondaryBinSelectBox.Enabled = Not IsNull(Me.masterBinSelectBox)
End Sub


Private Sub secondaryBinSelectBox_GotFocus()
    Me.secondaryBinSelectBox.Dropdown
End Sub

[June7]

Enabled property of textbox and combobox can be controlled with Conditional Formatting. That would be necessary if form is Continuous or Datasheet view. But VBA will work for Single view.