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.NewRe cord = 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
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.NewRe
If Item_Order_Line.Form!InvMe
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
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...
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...
ASKER
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
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
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
Me.Filter = "NZ(MyInvoiceNumber,'')= ''"
Me.FilterOn = True
ASKER
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
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
ASKER
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
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
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.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
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.
ASKER
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("o rder_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
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("o
'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.
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...
Except for breaking free of the shackles of MS Access...
ASKER
I wish ADO had a findfirst! I couldn't find an equivalent???
Lou
Lou
Perhaps the 'WHERE' clause of your SQL command is the appropriate place for filtering?
ASKER
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
Thanks for you comments!
Lou
No worries - all we can hope for is self-sufficiency on your part. Remember: Google is your friend.
ASKER
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