Originally Posted by
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)) " & _
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.
HTML 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