Form Link CheckBox to Record

[ssanfu]

PMFJI,

@Shank, there are 2 reasons for the error message. Looking at the following code (i've refactored the code)

sSQL = sSQL & " WHERE [NoSkus]=""" & Me.txtGroupNoSkus & """ & [MustShipFrom] =""" & Me.txtGroupWhsID & [Sku] = """ & Me.txtGroupSku & """""

1) The field "NoSkus" is a number (double???) field which does not need delimiters.
2) The fields "MustShipFrom" and "Sku" need/require delimiters because they are text fields.
3) The WHERE clause NEEDS/REQUIRES either an "OR" or an "AND" between the criteria.

This is what the WHERE clause look like:

sSQL = sSQL & " WHERE [NoSkus]= " & Me.txtGroupNoSkus  & " AND [MustShipFrom] = '" & Me.txtGroupWhsID & "' AND [Sku] = '" & Me.txtGroupSku & "'"

Note: I delimit Text with single quotes.

Here is the WHERE clause is again, maybe easier to see:

WHERE 
[NoSkus]= " & Me.txtGroupNoSkus  & " AND            '<<-- No delimiters needed because [NoSkus] is a Number field
[MustShipFrom] = '" & Me.txtGroupWhsID & "' AND     '<<-- Delimiters needed because [MustShipFrom] is a Text field 
[Sku] = '" & Me.txtGroupSku & "'"                   '<<-- Delimiters needed because [Sku] is a Text field

The same goes for the Sub cmdUnselectAll_Click(). In this case you can use the same WHERE clause

Private Sub cmdSelectAll_Click()
    Dim sSQL As String

    sSQL = "Update [PODetail Temp ShipThese] SET [ShipThese]=True "
    sSQL = sSQL & " WHERE [NoSkus]= " & Me.txtGroupNoSkus & " AND [MustShipFrom] ='" & Me.txtGroupWhsID & "' AND [Sku] = '" & Me.txtGroupSku & "'"
    Debug.Print sSQL
    
    CurrentDb.Execute sSQL, dbFailOnError
    Me.subShipThese.Requery
End Sub

Private Sub cmdUnselectAll_Click()
    Dim sSQL As String

    sSQL = "Update [PODetail Temp ShipThese] SET [ShipThese]=False "
    sSQL = sSQL & " WHERE [NoSkus]= " & Me.txtGroupNoSkus & " AND [MustShipFrom] ='" & Me.txtGroupWhsID & "' AND [Sku] = '" & Me.txtGroupSku & "'"
    Debug.Print sSQL
    
    CurrentDb.Execute sSQL, dbFailOnError
    Me.subShipThese.Requery
End Sub

I removed the Link Master Field and the Link Child Field because the data is from the same table. I added a criteria to the sub form record source.



Other issues:


1) You should not use spaces in object names. (I removed the spaces from the table name.)


2) EVERY module should have these two lines at the top:

Option Compare Database
Option Explicit

3) Every table should have a PK field (of type Autonumber - IMHO)

[shank]

Vlad - Excellent! I see where you changed to singe quotes around an integer. I thought I tried every combination, but obviously not.

Much appreciated!

[shank]

Steve - I downloaded and will compare to Vlad's. Vlad's Rev2 works as expected, but I need to learn more and appreciate your input!

Thank you!