Bound to query problem (1 Viewer)

[John Sh]

I have a continuous form with a select query as the record source.
The first three controls on the form are bound to the query.
If I bind the last two controls I can't write to them. If I leave them unbound then a change in any of the unbound controls affects the entire column.
Is there a simple way around this or do I resort to a single form and bind that to the table rather than a query?

Option Compare Database
Option Explicit

Private Sub btnClose_Click()
    DoCmd.Close acForm, Me.Name
'    DoCmd.OpenForm "Choices"
End Sub

Private Sub Form_Current()
    Dim bCount As Integer
    bCount = DCount("*", "Main", "isnull([BayNo])")
    If bCount = 0 Then
        Call btnClose_Click
    End If
    Me.txtBay.SetFocus
End Sub


Private Sub txtShelf_AfterUpdate()
'    DoCmd.OpenQuery shelfQ
'    DoCmd.openqueryNewShelfQ
    Me.txtBay = ""
    Me.txtShelf = ""
End Sub

Private Sub txtShelf_GotFocus()
    Me.txtShelf.BackColor = RGB(218, 255, 94)
End Sub

Private Sub txtbay_GotFocus()
    Me.txtBay.BackColor = RGB(218, 255, 94)
End Sub

Private Sub txtBay_LostFocus()
    Me.txtBay.BackColor = RGB(240, 240, 240)
End Sub

Private Sub txtShelf_LostFocus()
    Me.txtShelf.BackColor = RGB(240, 240, 240)
End Sub

The SQL for the query follows

SELECT Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo
FROM Main
GROUP BY Main.Family, Main.Infrafamily, Main.BoxNo, Main.BayNo, Main.ShelfNo
HAVING (((Main.BoxNo) Is Not Null))
ORDER BY Main.Family, Main.Infrafamily, Main.BoxNo;

 

[GPGeorge]

Consult Allen Browne's list of reasons queries become read only. One of them applies to your SQL here. I'll leave it to you to figure out which one.

 

[John Sh]

Consult Allen Browne's list of reasons queries become read only. One of them applies to your SQL here. I'll leave it to you to figure out which one.

A link?

 

[GPGeorge]

Yes. It's not easy to see because the text is only slightly different from normal text, unfortunately. Modern web design seems to favor subtlety over clarity. The first sentence in post #2 is a hyperlink.

 

[John Sh]

Yes. It's not easy to see because the text is only slightly different from normal text, unfortunately. Modern web design seems to favor subtlety over clarity. The first sentence in post #2 is a hyperlink.

Thanks.
I should have tweaked to the blue text.