Help to fix command button code?

[Garindan]

I have code for a command button on a form. There's a field on the form which the user selects to choose which records to print. I am trying to have the command button first show a message box if no records are selected -

If Me!txtSelected = 0 Then

MsgBox "Please select an Order to print", vbOKOnly, "Error"

and cancel on 'Ok' if none selected. If there are records selected then skip this bit and go to message box 'Are you sure' ok/cancel. Cancel obviously quits, ok calls another sub then opens the report. I would also like to requery the subform at the end.

I've been trying to get my code right but am new to vba and keep getting errors about no if's or if's in the wrong places etc

Here's the code:-

Private Sub btnOpenDelivRpt_Click()

  If Me!txtSelected = 0 Then

    MsgBox "Please select an Order to print", vbOKOnly, "Error"
    
  Else
  
    MsgBox "Are you sure?", vbOKCancel
    
  End If

  If MsgBox("Are you sure?", vbOKCancel) = vbCancel Then Exit Sub
  Else
  If MsgBox("Are you sure?", vbOKCancel) = vbOK Then
  
  'Send selected records to delivery report.
  
    Call UpdateTable '/  <<<<<<<<   Call sub function
    DoCmd.OpenReport "rptOrderDeliveries", acViewPreview

  End If

  Exit Sub
  
End Sub

Also, I'm trying to get the UpdateTable function to update the field in the subform 'Processed' to yes for those selected records in the subform. The records are selected with a 'Delivered' yes/no field. I'm not sure I've got this bit quite right either?

Private Sub UpdateTable()
Dim rsTemp     As DAO.Recordset
Dim i          As Integer

'Create a copy of this forms Recordset

Set rsTemp = Me.fsubUnprocessedOrders.Form.RecordsetClone

rsTemp.MoveFirst

'Loop through all records and update to new value

For i = 1 To rsTemp.RecordCount

    If Me.CurrentRecord = True Then
        CurrentDb.Execute "UPDATE tblCustomerOrders SET tblCustomerOrders.Processed = Yes where OrderNumber =" & Me.OrderNumber
    End If

    rsTemp.MoveNext

Next i

'Release resources

rsTemp.Close

Set rsTemp = Nothing
End Sub

Many thanks for any help!!!

 

[DCrake]

Private Sub btnOpenDelivRpt_Click()

If Me!txtSelected = 0 Then

    MsgBox "Please select an Order to print", vbOKOnly, "Error"
    [COLOR="DarkRed"]Exit Sub[/COLOR]  
Else
  
    If MsgBox("Are you sure?", vbOKCancel) = vbCancel Then
        [COLOR="darkred"] Exit sub    [/COLOR]    
     Else
             'Send selected records to delivery report.
  
            Call UpdateTable '/  <<<<<<<<   Call sub function
            DoEvents

            DoCmd.OpenReport "rptOrderDeliveries", acViewPreview


    End If
End Sub

 

[vbaInet]

As an alternative:

Private Sub btnOpenDelivRpt_Click()
    If Nz(Me!txtSelected, 0) = 0 Then
        MsgBox "Please select an Order to print", vbOKOnly, "Error"
    Else
        If MsgBox("Are you sure?", vbOKCancel) = vbOK Then
        
            'Send selected records to delivery report.
            Call UpdateTable '/  <<<<<<<<   Call sub function
            DoEvents
            
            DoCmd.OpenReport "rptOrderDeliveries", acViewPreview
        End If
    End If
End Sub

 

[Garindan]

Excellent thankyou to you both!! Now I just have to fix the second part.

 

[Garindan]

Would someone be able to help with this second part? Just in case it's confusing I've tried to explain it here:-

A toggle button on frmOrderProcessing is linked to the 'Delivered' Yes/No field. A user selects these to decide which orders to deliver, and the report which opens is filtered to show these selected orders.

The table tblCustomerOrders, subform fsubUnprocessedOrders and its query qselUnprocessedOrders have a field called 'Processed' (Yes/No). When an order is entered into the database its Processed field is 'no', and qselUnprocessedOrders has 'No' criteria in this field so that it shows all the new orders which have yet to be processed.

So... the user selects which orders to send to the report, using the 'Delivered' Yes/No field. When the command button is pressed I would like to update the Processed field to 'Yes' for all the selected orders (where the 'Delivered' field is set to 'Yes'.

Then open the report, then requery the subform, which will remove those orders as their 'Processed' field is now set to yes and the query/subform only shows 'Processed' No orders.

Private Sub btnOpenDelivRpt_Click()
    If Nz(Me!txtSelected, 0) = 0 Then
        MsgBox "Please select an Order to print", vbOKOnly, "Error"
    Else
        If MsgBox("Are you sure? This will open delivery sheet and remove orders from form", vbOKCancel) = vbOK Then
        
            'Send selected records to delivery report.
            Call UpdateTable '/  <<<<<<<<   Call sub function
            DoEvents
            
            DoCmd.OpenReport "rptOrderDeliveries", acViewPreview
            
            ' Requery the subform
            Me.fsubUnprocessedOrders.Requery
    
        End If
    End If
End Sub

Private Sub UpdateTable()
Dim rsTemp     As DAO.Recordset
Dim i          As Integer

'Create a copy of this forms Recordset

Set rsTemp = Me.fsubUnprocessedOrders.Form.RecordsetClone

rsTemp.MoveFirst

'Loop through all records and update to new value

For i = 1 To rsTemp.RecordCount
        CurrentDb.Execute "UPDATE tblCustomerOrders SET tblCustomerOrders.Processed = Yes where OrderNumber =" & Me!fsubUnprocessedOrders.Form!OrderNumber
    rsTemp.MoveNext
Next i

'Release resources

rsTemp.Close

Set rsTemp = Nothing
End Sub

Does it need to be something like:-
CurrentDb.Execute "UPDATE tblCustomerOrders SET tblCustomerOrders.Processed = Yes where OrderNumber =" & Me!fsubUnprocessedOrders.Form!OrderNumber, Delivered = 'True'?

And I cant get past 'rsTemp As DAO.Recordset' at the mo, it says 'Compile Error: User-defined type not defined'

 

[vbaInet]

And I cant get past 'rsTemp As DAO.Recordset' at the mo, it says 'Compile Error: User-defined type not defined'

Updating using a recordset isn't needed.

The table tblCustomerOrders, subform fsubUnprocessedOrders and its query qselUnprocessedOrders have a field called 'Processed' (Yes/No). When an order is entered into the database its Processed field is 'no', and qselUnprocessedOrders has 'No' criteria in this field so that it shows all the new orders which have yet to be processed.

So... the user selects which orders to send to the report, using the 'Delivered' Yes/No field. When the command button is pressed I would like to update the Processed field to 'Yes' for all the selected orders (where the 'Delivered' field is set to 'Yes'.

One little tip, if you're not show how to write an Update statement, use the Query Builder, copy and amend the SQL accordingly.

If the query qselUnprocessedOrders contains all the NOs then create an UPDATE query (using the Query Builder), select the table and the query qselUnprocessedOrders. Set the Criteria to CustomerID = 1. Copy the SQL of this query and amend it as you see fit. Then run using CurrentDb.Execute().

Then open the report, then requery the subform, which will remove those orders as their 'Processed' field is now set to yes and the query/subform only shows 'Processed' No orders.

So the steps will be:

' Force any unsaved changes
If Me.Dirty Then
    Me.Dirty = False
End If

CurrentDb.Execute ...

Me.Requery

DoCmd.OpenReport "ReportName", acPreview

 

[boblarson]

And I cant get past 'rsTemp As DAO.Recordset' at the mo, it says 'Compile Error: User-defined type not defined'

I'll give you the answer for why this happened just for your future knowledge but you do need to use the update query method that vbaInet has shown.

The reason you got that error is you do not have a DAO reference set.

 

[Garindan]

Brilliant yes thankyou. It actually dawned on me last night that it didn't need to be complicated. I simply needed to update all records in qselUnprocessedOrders.Processed to true if Delivered was true.

I followed your advice and constructed it as an update query to do what I want and that was fine. However, when I copy the SQL to to button code it needs to be slightly different and I can't work out where my "" etc should be.

I have:-

Private Sub btnOpenDelivRpt_Click()

    ' Force any unsaved changes
    If Me.Dirty Then
    Me.Dirty = False
    End If
            
    If Nz(Me!txtSelected, 0) = 0 Then
        MsgBox "Please select an Order to print", vbOKOnly, "Error"
    Else
        If MsgBox("Are you sure? This will open delivery sheet and remove orders from form", vbOKCancel) = vbOK Then
            
            DoEvents
            
            DoCmd.OpenReport "rptOrderDeliveries", acViewPreview
            
            CurrentDb.Execute "UPDATE qselUnprocessedOrders SET qselUnprocessedOrders.Processed = Yes"
WHERE (((qselUnprocessedOrders.Delivered) = Yes))
            
            ' Requery the subform
            Me.fsubUnprocessedOrders.Requery
    
        End If
    End If
End Sub

How should this line be - 'CurrentDb.Execute "UPDATE qselUnprocessedOrders SET qselUnprocessedOrders.Processed = Yes"
WHERE (((qselUnprocessedOrders.Delivered) = Yes))'? Is my If Me.Dirty statement ok as it is?

Many many thanks! I'm very nearly there!!!!

 

[vbaInet]

You are missing -->

If the query qselUnprocessedOrders contains all the NOs then create an UPDATE query (using the Query Builder), select the table and the query qselUnprocessedOrders. Set the Criteria to CustomerID = 1. Copy the SQL of this query and amend it as you see fit.

That's what determines which Customer records to update.

 

[Garindan]

You are missing -->Set the Criteria to CustomerID = 1
That's what determines which Customer records to update.

Sorry I'm unsure what you mean? I'm happy for the update query to update ALL records where Delivery = Yes to Processed = Yes in that query because its the only time it is used in the database and it provides what i need.

Plus the subform uses continuous forms so there are many selected at once, and i want it to update all of them when the command button is clicked.

It's just that the syntax of the line 'CurrentDb.Execute "UPDATE qselUnprocessedOrders SET qselUnprocessedOrders.Processed = Yes"
WHERE (((qselUnprocessedOrders.Delivered) = Yes))' is not right in my code.

 

[vbaInet]

The subform is linked to the Customers table isn't it? Customers table being the record source of the parent form.

 

[Garindan]

No the subform source is qselUnprocessedOrders. The main form is unbound (just for searching).

 

[vbaInet]

If you open the query, can you edit a record or is it read-only?

What error do you get on that code line?

 

[Garindan]

Nevermind, I've got it working I simply didn't have it on one line in the code when I copied and pasted the sql from the query design.

So the exact sql from the query design was

UPDATE qselUnprocessedOrders SET qselUnprocessedOrders.Processed = Yes
WHERE (((qselUnprocessedOrders.Delivered)=Yes));

but when I pasted it into the code it was

UPDATE qselUnprocessedOrders SET qselUnprocessedOrders.Processed = Yes[COLOR="red"]"[/COLOR]
WHERE (((qselUnprocessedOrders.Delivered)=Yes));

I removed that and put it on one line and now it all works!!

Many thanks for all your help!!!!!!!!!!

 

[vbaInet]

You're welcome!

 

[boblarson]

By the way, the reason it was having problems is you were missing a space after the YES and before the ending quote (") on the end of the first line.

You had:

CurrentDb.Execute "UPDATE qselUnprocessedOrders SETqselUnprocessedOrders.Processed = Yes"
WHERE (((qselUnprocessedOrders.Delivered) = Yes));"

and you would need a space and line extension

CurrentDb.Execute "UPDATE qselUnprocessedOrders SETqselUnprocessedOrders.Processed = Ye[COLOR=red]s  _[/COLOR]
WHERE (((qselUnprocessedOrders.Delivered) = Yes));"

Or

CurrentDb.Execute "UPDATE qselUnprocessedOrders SETqselUnprocessedOrders.Processed = Ye[COLOR=red]s " & _[/COLOR]
[COLOR=red]"[/COLOR]WHERE (((qselUnprocessedOrders.Delivered) = Yes));"