Stop code after "True" condition found.

I have a continuous form that lists parts to order. I run some code to check the value of some checkboxes on the form (part selected or not). I then verify that at least one part is selected by doing a RecordsetClone loop. It works fine. If all the parts are selected it runs the queries to add the selected part to the 'orders' tablea and closes the form.  If none are selected it throws up a MsgBox that says "None Selected".  The problem is, if one is selected and one isn't, for instance, it will place the order, close the form, but then throw up the MsgBox 'None Selected' anyway. How can I prevent that from happening?

Here is my code:
 
Dim someSelected As Boolean
someSelected = False


With Me.RecordsetClone
    .MoveFirst
    Do Until .EOF

'!order is the checkbox pertaining to a part number
'If this part is ordered set someSelected = true
'the problem is, it keeps going and if it finds a false
'after a true it does the MsgBox

    If !order = True Then
    someSelected = True
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    strSQL = "Select * from tblLastOrders where press = '" & Me.txtMoldNumber & "'"
    
    

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)

    With rst
        .Edit
        
        ![tool] = Me.toolNumber1
        ![ControlNumber] = Me.ControlNumber
        ![TimeStamp] = Now()
        ![orderedBy] = Me.txtEmpNumber
        .Update
        .Close
    End With

    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryThesePartsNeeded"
    DoCmd.OpenQuery "qryAppendCribOrders"
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmHelloName", acSaveNo
    DoCmd.Close acForm, "frmScheduleList", acSaveNo
    DoCmd.OpenForm "Form1"
    DoCmd.Close acForm, "frmPartsListSelection", acSaveNo
    Else
    someSelected = False
    MsgBox "No Items Selected. Please select an item before placing order."
    
    End If

    

    .MoveNext
    
    Loop
End With

Open in new window


Thanks for any help you can give me.
LVL 1
G ScottAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
loop thru all the records first to find if a check box was selected before the processing


Dim someSelected As Boolean
someSelected = False


With Me.RecordsetClone
    .MoveFirst
    Do Until .EOF
       If !order = True Then
          someSelected = True
          exit do
       end if
       .movenext
    Loop

'continue with the processing

   .MoveFirst
    Do Until .EOF
'!order is the checkbox pertaining to a part number
'If this part is ordered set someSelected = true
'the problem is, it keeps going and if it finds a false
'after a true it does the MsgBox

    If !order = True Then
 '    someSelected = True
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    strSQL = "Select * from tblLastOrders where press = '" & Me.txtMoldNumber & "'"
    
    

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)

    With rst
        .Edit
        
        ![tool] = Me.toolNumber1
        ![ControlNumber] = Me.ControlNumber
        ![TimeStamp] = Now()
        ![orderedBy] = Me.txtEmpNumber
        .Update
        .Close
    End With

    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryThesePartsNeeded"
    DoCmd.OpenQuery "qryAppendCribOrders"
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmHelloName", acSaveNo
    DoCmd.Close acForm, "frmScheduleList", acSaveNo
    DoCmd.OpenForm "Form1"
    DoCmd.Close acForm, "frmPartsListSelection", acSaveNo
    Else
    someSelected = False
    MsgBox "No Items Selected. Please select an item before placing order."
    
    End If

    

    .MoveNext
    
    Loop
End With

Open in new window

0
Rey Obrero (Capricorn1)Commented:
use this one


Dim someSelected As Boolean
someSelected = False


With Me.RecordsetClone
    .MoveFirst
    Do Until .EOF
       If !order = True Then
          someSelected = True
          exit do
       end if
       .movenext
    Loop

'continue with the processing
if someSelected = True then
   .MoveFirst
    Do Until .EOF
'!order is the checkbox pertaining to a part number
'If this part is ordered set someSelected = true
'the problem is, it keeps going and if it finds a false
'after a true it does the MsgBox

 '   If !order = True Then
 '    someSelected = True
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    strSQL = "Select * from tblLastOrders where press = '" & Me.txtMoldNumber & "'"
    
    

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)

    With rst
        .Edit
        
        ![tool] = Me.toolNumber1
        ![ControlNumber] = Me.ControlNumber
        ![TimeStamp] = Now()
        ![orderedBy] = Me.txtEmpNumber
        .Update
        .Close
    End With

    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryThesePartsNeeded"
    DoCmd.OpenQuery "qryAppendCribOrders"
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmHelloName", acSaveNo
    DoCmd.Close acForm, "frmScheduleList", acSaveNo
    DoCmd.OpenForm "Form1"
    DoCmd.Close acForm, "frmPartsListSelection", acSaveNo


    

    .MoveNext
    
    Loop

    Else
    someSelected = False
    MsgBox "No Items Selected. Please select an item before placing order."
    
    End If
End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
G ScottAuthor Commented:
Hmmm..giving me a 2467 Error "..refers to an object that is closed or doesn't exist" on line 30:

strSQL = "Select * from tblLastOrders where press = '" & Me.txtMoldNumber & "'"

Which is weird because the form is still open. Any ideas? Thanks capricorn1.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rey Obrero (Capricorn1)Commented:
are you using the last posted code?
0
G ScottAuthor Commented:
I sure am.

Attached is the DB with just the forms/queries/tables in question.
Sample.accdb
0
Rey Obrero (Capricorn1)Commented:
what version of access are you using?

can you make a blank .mdb, import the relevant objects to the .mdb and upload.
0
Jeffrey CoachmanMIS LiasonCommented:
ExpertOnNothing,

FWIW, you *really* need to clean up your code:
Variable naming
Declarations
Indenting
Logic flow
...etc
...It is hard to follow the logic and syntax with the code the way you have it written now.

In any event, your issue is that you are not saving the record before you run the code, thus no records are selected at that time.

Please insert this to the top of your code, (...after the lines where you declare and set the SomeSelected variable)
    DoCmd.RunCommand acCmdSaveRecord

Alternatively you could use this:
If me.Dirty=True then
    Me.Dirty=False
end if


JeffCoachman
0
G ScottAuthor Commented:
capricorn1 - it worked. I moved the DoCmd.OpenQuery commands outside of the second loop and it's working great. Thanks!

Jeff - No, I know. See, my problem is most the time I am not exactly sure what I am doing so I try a thing or two, get it working, either on my own or with EE's help, and then I never go back and 'fix' it. I need to get better at commenting too.

I am really just learning this stuff. Are there any tutorials that would tell me about standards or best practices for programming.
0
G ScottAuthor Commented:
Here is how I fixed it:

 
Private Sub Command27_Click()
Dim someSelected As Boolean
DoCmd.RunCommand acCmdSaveRecord
someSelected = False


With Me.RecordsetClone
    .MoveFirst
    Do Until .EOF
       If !order = True Then
          someSelected = True
          Exit Do
       End If
       .MoveNext
    Loop

'continue with the processing
If someSelected = True Then
   .MoveFirst
    Do Until .EOF
'!order is the checkbox pertaining to a part number
'If this part is ordered set someSelected = true
'the problem is, it keeps going and if it finds a false
'after a true it does the MsgBox

 '   If !order = True Then
 '    someSelected = True
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    strSQL = "Select * from tblLASTORDERS where press = '" & Me.txtMoldNumber & "'"
    
    

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)

    With rst
        .Edit
        
        ![tool] = Me.toolNumber1
        ![ControlNumber] = Me.ControlNumber
        ![TimeStamp] = Now()
        ![orderedBy] = Me.txtEmpNumber
        .Update
        .Close
    End With

    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    

   
   


    

    .MoveNext
    
    Loop
     DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryThesePartsNeeded"
    DoCmd.OpenQuery "qryAppendCribOrders"
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmHelloName", acSaveNo
    DoCmd.Close acForm, "frmScheduleList", acSaveNo
    DoCmd.OpenForm "Form1"
     DoCmd.Close acForm, "frmPartsListSelection", acSaveNo
    Else
    someSelected = False
    MsgBox "No Items Selected. Please select an item before placing order."
    
    End If
End With


End Sub

Open in new window

0
G ScottAuthor Commented:
* I say 'I fixed it', but really it was all capricorn1's code. : )
0
Jeffrey CoachmanMIS LiasonCommented:
Not wanting to be picky about this, ...but it was my one line of code that fixed the underlying issue here.

In other words, it would have been fairer if you had spit the points to acknowledge this...

JeffCoachman
0
G ScottAuthor Commented:
Hey Jeff, to be honest, before I moved that code I tested your idea and it still bombed out. I dug in a bit deeper, made the move that I talked about, and it worked. I just forgot to remove your line of code. And, I already do the accmdsaverecord command on the actual buttons for the part numbers.

If you look at the original code that capricorn posted, it was closing the form in the middle of the loop, that's why I was getting that error about the form not being found. When it was looping back the form was already closed. Right?
0
Jeffrey CoachmanMIS LiasonCommented:
Like I said, I'm not crying over this...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.