Process order lines selected in a subform

PS.

Whoops!
Just realised I can't use the check box as it selectes all the lines to be invoiced. Not just the one its on - but anywaty I will have some sort of field that can be used to diffentiate which lines to invoice. So the above still applies.

Lou

Your 'do...while' loop never advances through the collection.

>I can't use the check box as it selectes all the lines to be invoiced.
That's characteristic of unbound controls, and is most apparent in continuous forms since the records are all together.

Try adding a boolean field to the underlying table for this.  The form can then be filtered according to the yes/no field...

Badotz: Yes but why? Is it becuase what I am trying to do is impossible in the way I'm trying  OR is it the "Do while" check that is wrong?

mbizup: Yes I'm about to ttry this. But Will it work with the do while loop?

Thanks
Lou

The loop never advances, and consequently evaluates the same item, and consequently evaluates the same item, and consequently evaluates the same item, kind of thing.

You shouldnt need a loop.

Just do this:
Me.Filter = "MyYesNoField = TRUE"
Me.FilterOn = True

To Clear it:
Me.Filter = ""
Me.FilterOn = False

Alternatively,  If invoice number is in the underlying recordsource you can get by without adding any fields.  Just filter for fields where invoiceNumber is null or blank:

Me.Filter = "NZ(MyInvoiceNumber,'')= ''"
Me.FilterOn = True

Dear All

So far so good -  using a linked check box seem to work with the loop. Do while must be OK???

Thanks - I was very pessimistic that you could loop through records like this without using a recordset (new to VBA) but it looks like you can??? If I am doing this in a totally wrong way please let me know!!!

Lou

Dear All

SORRY! What worked was the following - not the loop.

  'Check if any lines were selected for invoicing
   
     Dim linesq As Boolean
    linesq = False
     
        If Item_Order_Line.Form!InvMe = True Then
        linesq = True
        End If
 
      If linesq = False Then
     MsgBox "Please select item(s) to invoice.", 0 + 64
      End If

Did you try my filtering suggestion?  It will display all lines that are checked, for the user to enter invoice numbers.  You can also add code to handle "no records checked".   Let me know if you are interested in this approach.

mbizup:

This is a good method to know. But in this situation it will be better for the users to see any uncheckd lines - also the invoice number willl be added automatically (I hope!) - the plan is to transfer the current next invoice number (hidden on the order screen) to an invoice screen where the lines will also be displayed.

Thanks
 Lou

mbizup:

Thanks for th esuggestion I dodn't know how to do that and it will be handy for the future, but , the code I wrote works OK so I'll use that for now becasue I'm short of time.
Shouldn't it be an ADODB recordset?

Cheers
Lou

>the code I wrote works OK ...
Sorry about that.  I thought you were still trying to work out the loop.  If you have this resolved, you should post a request in community support to close the question and refund your points.

mbizup
You are indeed a genius.

I'm fairly new to all this VBA and I thought it WAS working (although I admit I couldn't see how - with no loop). However it wasn't  - only for the first record (what an idiot I am).

So I reluctantly turned to your solution  - reluctant because I'm not very good at the syntax for findfirst and people keep saying I should be using a ADODB connection.

I eventualy got it workign with this
    Dim linesq As Boolean
    Dim rst As DAO.Recordset
   Set rst = CurrentDb.OpenRecordset("order_line", dbOpenDynaset)
   
    'Check if any lines were selected for invoicing
    rst.FindFirst "Selected = True And [Order_ID] = " & Me!Order_ID
    linesq = Not rst.NoMatch         '* linesq = false if nomatch, true if some selected = true
    If linesq = False Then
        MsgBox "Please select item to invoice.", 0 + 64
        GoTo Exit_My_Sub
    End If

'Check if the selected lines are already invoiced
 
    rst.FindFirst "Selected = True And Inv_No < 1 And [Order_ID] = " & Me!Order_ID
    linesq = Not rst.NoMatch         '* linesq = false if nomatch, true if some InvMe = true
    If linesq = False Then
        MsgBox "One or more items selected have already been invoiced, please deselect.", 0 + 64
        GoTo Exit_My_Sub
    End If
rst.Close
Set rst = Nothing

Thanks very very much!

Lou

I'm glad I could help out.  :)  

ADO and DAO can be used pretty much interchangeably within Access.  In this case, I'd posted DAO code to handle the form's recordset, assuming a bound form.  As you worked out in what you just posted, you can also use a DAO recordset to handle any dataset that is linked to your database.  This code can also be converted to ADO easily.  Access 2003 VBA Help has a very good side-by-side comparison of the two methods (search for it as 'DAO vs ADO').  It's good information to know, but as they say "if it aint broke, don't fix it'.  Converting the code would be a very good academic exercise, but I don't think you would gain any other benefits.

>> but I don't think you would gain any other benefits.

Except for breaking free of the shackles of MS Access...

I wish ADO had a findfirst! I couldn't find an equivalent???

Lou

Perhaps the 'WHERE' clause of your SQL command is the appropriate place for filtering?

Yes you are right of course but I was being lazy - I aren't experienced enough not to struggle over syntax and I'd already figured out where you put findfirst even if the search critera syntax took me about an hour to work out!!!
Thanks for you comments!

Lou

No worries - all we can hope for is self-sufficiency on your part. Remember: Google is your friend.