Code to set multiple check boxes in a query

[tl mike]

I have a print button and if we print multiple items off a query (that only shows unprinted records) and select yes button that all items that were printed correctly from the query I want it to check yes in the combo box.

I already have it for when I print a single document I just dont know how to go about doing it for mulitple records in the query.

 

[pbaldy]

One way,

UPDATE TableName
SET FieldName = True
WHERE KeyField IN (SELECT KeyField FROM QueryName)

 

[tl mike]

I tired using the code but it does not like the IN it is thinking that it is an end of a statement.

 

[pbaldy]

Can you post your SQL? I just ran this on a test db:

UPDATE tblclocks SET test = True
WHERE id IN (SELECT id FROM query1);

 

[tl mike]

Here is my code for the command button

Also I am using access 2003 if that has anything to do with my problem with the code

Private Sub cmdPrintAll_Click()
On Error GoTo Err_cmdPrintAll_Click

    Dim stDocName As String

    stDocName = "PromoPackMemo"
    DoCmd.OpenReport stDocName, acPreview

    Dim Message, Buttons, Choice
      Message = "Are you sure you want to complete this order?"
      Buttons = vbYesNo
      Choice = MsgBox(Message, Buttons)
      
      If Choice = vbYes Then
    
    Update tblPromoPack
    Set MemoPrinted = True
    WHERE PromoPackPK IN (SELECT PromoPackPK FROM qryMemoPrinted)

      
      
      If Choice = vbNo Then
        Exit Sub

Exit_cmdPrintAll_Click:
    Exit Sub

Err_cmdPrintAll_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintAll_Click
    
        End If
    End If

End Sub

 

[pbaldy]

You can't simply put SQL in code like that. Try

If Choice = vbYes Then

CurrentDb.Execute "Update tblPromoPack Set MemoPrinted = True WHERE PromoPackPK IN (SELECT PromoPackPK FROM qryMemoPrinted)"

Once you have it working, you can break it onto 2 lines.

 

[tl mike]

It worked thank you

For anyone that needs this code I used

Private Sub cmdPrintAll_Click()
On Error GoTo Err_cmdPrintAll_Click

    Dim stDocName As String

    stDocName = "PromoPackMemo"
    DoCmd.OpenReport stDocName, acPreview

    Dim Message, Buttons, Choice
      Message = "Are you sure you want to complete this order?"
      Buttons = vbYesNo
      Choice = MsgBox(Message, Buttons)
      
If Choice = vbYes Then
  Dim db As Database
  Set db = CurrentDb()
CurrentDb.Execute "Update tblPromoPack Set MemoPrinted = True WHERE PromoPackPK IN (SELECT PromoPackPK FROM qryMemoPrinted)"

    Set db = Nothing
     
      
      If Choice = vbNo Then
        Exit Sub

Exit_cmdPrintAll_Click:
    Exit Sub

Err_cmdPrintAll_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintAll_Click
    
        End If
    End If

End Sub

 

[pbaldy]

No problem. If you're going to go through the work of creating and setting a variable though, you might as well use it.

db.Execute ...