Unique values across 2 fields from different tables

[anthgav]

Brilliant. This post has made me bang my head against the wall a few times but we got there in the end. I've learned a lot from your help and have a clearer understanding of using the back end of Access with events. This is my first Access application and am learning everyday and am enjoying it. Just want to say I appreciate you support so thanks. I'll mark the post as read once I've tested the form thoroughly.

[anthgav]

I have another issue. My frmProduct is linked to a main navigation form. When I open the Navigation Form the frmProduct which is linked to it produces parameter errors...



After reading up I believe I need to alter these lines in the code...

[Forms]![frmProduct]![allocated_bin_1]
[Forms]![frmProduct]![allocated_bin_2]

I've tried changing them to the following but it still does not work...

[Forms]![frmNavigation]![frmProduct].[form]![allocated_bin_1]
[Forms]![frmNavigation]![frmProduct].[form]![allocated_bin_2]

and

[Forms]![frmNavigation]![frmProduct]![allocated_bin_1]
[Forms]![frmNavigation]![frmProduct]![allocated_bin_2]

How do I make this work so that frmProduct works when opened by itself and when in frmNavigation?

[June7]

Because the comboboxes are on the same form, try not including the form prefix.

"WHERE (((AllocatedUNION.AllocatedBin)=[allocated_bin_1] Or (AllocatedUNION.AllocatedBin) Is Null)) " & _

"WHERE (((AllocatedUNION.AllocatedBin)=[allocated_bin_2] Or (AllocatedUNION.AllocatedBin) Is Null)) " & _

[anthgav]

Hi

Sorry for bringing an old post up but I've found a bug on this form.

When I open the form up and click the drop down "Search Product" I select the top product in the list which then populates my form, for some reason the "Master Bin" and "Secondary Bin" don't populate. This only happens with the top product in the list for some reason. If I start click around the form and go back to it then eventually it shows up, any idea what is causing this?




This is the current code on the form.

Option Compare Database
Option Explicit

Private Sub Form_Current()
'Script for the product page which removes used bins from the bin combo box list so it only shows available bins.
'Also displays the selected bin in the combo box text field
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 (qryAllocatedBinsToProduct RIGHT JOIN tblAllocatedBin ON " & _
"qryAllocatedBinsToProduct.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
"tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
"WHERE qryAllocatedBinsToProduct.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 (qryAllocatedBinsToProduct RIGHT JOIN tblAllocatedBin ON " & _
"qryAllocatedBinsToProduct.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
"tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
"WHERE qryAllocatedBinsToProduct.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 (qryAllocatedBinsToProduct RIGHT JOIN tblAllocatedBin ON " & _
"qryAllocatedBinsToProduct.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
"tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
"WHERE (((qryAllocatedBinsToProduct.AllocatedBin)=[allocated_bin_1] Or (qryAllocatedBinsToProduct.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 (qryAllocatedBinsToProduct RIGHT JOIN tblAllocatedBin ON " & _
"qryAllocatedBinsToProduct.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
"tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type " & _
 "WHERE (((qryAllocatedBinsToProduct.AllocatedBin)=[allocated_bin_2] Or (qryAllocatedBinsToProduct.AllocatedBin) Is Null)) " & _
"ORDER BY tblBin.bin, tblBinType.priority;"
End If
Me.masterBinSelectBox.Requery
Me.secondaryBinSelectBox.Requery
End Sub

Private Sub Form_Load()    
'Makes the form default to add new entry    
DoCmd.GoToRecord , , acNewRec
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

Private Sub productSearchBox_GotFocus()    
Me.productSearchBox.Dropdown    
Me.productSearchBox.Requery
End Sub

[June7]

Posted code is not readable. Please edit.

[anthgav]

Sorry, I've edited it, see above.

[June7]

Don't see any issue with code. Expect I would have to review db to analyze.

[anthgav]

I've uploaded the file, see attached.

Planner.zip

[Micron]

Only took a brief look I admit (running out of time here) but isn't this due to improper referencing of form controls when the form is on a navigation form? It can be compounded by changing the default name for the nav form and forgetting that as well. I think the syntax is

Forms!NavFormName.NavigationSubform.Form.ControlName

not

[Forms]![frmNavigation]![frmProduct].[form]![allocated_bin_2] or
[Forms]![frmNavigation]![frmProduct]![allocated_bin_2]

The default name for the navigation subform control is bolded red above. Unless its name was changed, the object appears to be missing from the reference.

[June7]

Form prefix not needed.

References to allocated_bin_1 and allocated_bin_2 are field names, not controls.

Concatenate the field names to pull value from current record.

"WHERE (((qryAllocatedBinsToProduct.AllocatedBin)=" & [allocated_bin_1] & " Or (qryAllocatedBinsToProduct.AllocatedBin) Is Null)) " & _

"WHERE (((qryAllocatedBinsToProduct.AllocatedBin)=" & [allocated_bin_2] & " Or (qryAllocatedBinsToProduct.AllocatedBin) Is Null)) " & _

[anthgav]

Form prefix not needed.

References to allocated_bin_1 and allocated_bin_2 are field names, not controls.

Concatenate the field names to pull value from current record.

"WHERE (((qryAllocatedBinsToProduct.AllocatedBin)=" & [allocated_bin_1] & " Or (qryAllocatedBinsToProduct.AllocatedBin) Is Null)) " & _

"WHERE (((qryAllocatedBinsToProduct.AllocatedBin)=" & [allocated_bin_2] & " Or (qryAllocatedBinsToProduct.AllocatedBin) Is Null)) " & _

I've changed the code as suggested but the first entry in my search box drop down doesn't populate the Master and Secondary Bin values. Below is my code.

Option Compare DatabaseOption Explicit

Private Sub Form_Current()
'Script for the product page which removes used bins from the bin combo box list so it only shows available bins.
'Also displays the selected bin in the combo box text fieldMe.secondaryBinSelectBox.Enabled = Not IsNull(Me.masterBinSelectBox)
If Me.NewRecord Then
Me.masterBinSelectBox.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type, tblBin.bin_width_mm " & _
"FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (qryAllocatedBinsToProduct RIGHT JOIN tblAllocatedBin ON " & _
"qryAllocatedBinsToProduct.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
"tblBinType.bin_type = tblAllocatedBin.allocated_bin_type " & _
"WHERE (((qryAllocatedBinsToProduct.AllocatedBin) Is Null)) " & _
"ORDER BY tblBin.bin, tblBinType.priority;"

Me.secondaryBinSelectBox.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type, tblBin.bin_width_mm " & _
"FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (qryAllocatedBinsToProduct RIGHT JOIN tblAllocatedBin ON " & _
"qryAllocatedBinsToProduct.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
"tblBinType.bin_type = tblAllocatedBin.allocated_bin_type " & _"WHERE (((qryAllocatedBinsToProduct.AllocatedBin) Is Null)) " & _
"ORDER BY tblBin.bin, tblBinType.priority;"ElseMe.masterBinSelectBox.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type, tblBin.bin_width_mm " & _
"FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (qryAllocatedBinsToProduct RIGHT JOIN tblAllocatedBin ON " & _
"qryAllocatedBinsToProduct.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
"tblBinType.bin_type = tblAllocatedBin.allocated_bin_type " & _"WHERE (((qryAllocatedBinsToProduct.AllocatedBin)=" & [allocated_bin_1] & " Or (qryAllocatedBinsToProduct.AllocatedBin) Is Null)) " & _
"ORDER BY tblBin.bin, tblBinType.priority;"
Me.secondaryBinSelectBox.RowSource = "SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type, tblBin.bin_width_mm " & _
"FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (qryAllocatedBinsToProduct RIGHT JOIN tblAllocatedBin ON " & _
"qryAllocatedBinsToProduct.AllocatedBin = tblAllocatedBin.allocated_bin_id) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON " & _
"tblBinType.bin_type = tblAllocatedBin.allocated_bin_type " & _"WHERE (((qryAllocatedBinsToProduct.AllocatedBin)=" & [allocated_bin_2] & " Or (qryAllocatedBinsToProduct.AllocatedBin) Is Null)) " & _
"ORDER BY tblBin.bin, tblBinType.priority;"
End If
Me.masterBinSelectBox.RequeryMe.secondaryBinSelectBox.Requery
End Sub

Private Sub Form_Load()
'Makes the form default to add new entry
DoCmd.GoToRecord , , acNewRec
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

Private Sub productSearchBox_GotFocus()
Me.productSearchBox.Dropdown
Me.productSearchBox.Requery
End Sub

[June7]

Recommend convert embedded macros to VBA

Recommend not setting lookups in tables

Did a bunch of trial and error and observed a lot of bizarre behavior.

1. selecting first item is not triggering OnCurrent event

2. changed combobox to sort on allocated_bin_1 and now the first item triggers OnCurrent but value 1W05ACBA04AM does not and it is in row 20

3. removed sorting from combobox RowSource and no value in first row triggers OnCurrent

4. again set sorting on allocated_bin_1 and added an 'x' in front of 1W05ACBA04AM and now 2400-0200-123123123 will not trigger event

5. put in an 'x' in front of 2400-0200-123123123 and now 2500-1100 will not trigger event

6. put in an 'x' in front of 2500-1100 and all values trigger event

7. set sorting back to product field and again first row will not trigger event

I am at a total loss as to why this is the case.

[anthgav]

Recommend convert embedded macros to VBA

Recommend not setting lookups in tables

Did a bunch of trial and error and observed a lot of bizarre behavior.

1. selecting first item is not triggering OnCurrent event

2. changed combobox to sort on allocated_bin_1 and now the first item triggers OnCurrent but value 1W05ACBA04AM does not and it is in row 20

3. removed sorting from combobox RowSource and no value in first row triggers OnCurrent

4. again set sorting on allocated_bin_1 and added an 'x' in front of 1W05ACBA04AM and now 2400-0200-123123123 will not trigger event

5. put in an 'x' in front of 2400-0200-123123123 and now 2500-1100 will not trigger event

6. put in an 'x' in front of 2500-1100 and all values trigger event

7. set sorting back to product field and again first row will not trigger event

I am at a total loss as to why this is the case.

This bug is driving me nuts, I've spent all day trying to figure out a work around. If I navigate to the first entry using the navigation buttons then the bin shows up but as soon as I use the search box for the first entry it doesn't show again. As it works with the nav buttons is there some sort event I can trigger in the script after selecting the item in the search box to refresh it?

Also if I change the Bound Column from 1 to 2 on the Master and Secondary Bins to show an ID instead of a Bin number then it works as expected.

[anthgav]

I managed to work out a quirky solution to the first entry not populating the bin numbers. I added this to my code...

Private Sub productSearchBox_Click()
If Me.Recordset.AbsolutePosition = 0 Then
DoCmd.GoToRecord Record:=acNext
DoCmd.GoToRecord Record:=acPrevious
End If
Me.masterBinSelectBox.Requery
Me.secondaryBinSelectBox.Requery
End Sub