Link to home
Start Free TrialLog in
Avatar of Louverril
Louverril

asked on

Process order lines selected in a subform

I have an "order "form with "Item_Order_Line" as a subform.

I have an invoice button on the order form to start a create invoice process. On the Item_Order_Line subform I have tick boxes against each line.

I want the invoice button to select the lines  which have been ticked (unbound tickbox) - so I can fill them with an invoice number. I have written the following code to first check if any have been ticked, but it just gets stuck in a loop. Is this because I cannot loop through screen rows or is the "do while " wrong?

If I should be using a recordset how do I recognise the selected lines - they have just been ticked (and the tick not saved anywhere).

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

Please help - ASAP please!
Thanks
Lou

Avatar of Louverril
Louverril

ASKER

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.
Avatar of mbizup
>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
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.