asked on

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.

Rey Obrero (Capricorn1)

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

G Scott

ASKER

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.

Rey Obrero (Capricorn1)

are you using the last posted code?

G Scott

ASKER

I sure am.

Attached is the DB with just the forms/queries/tables in question.
Sample.accdb

Rey Obrero (Capricorn1)

what version of access are you using?

can you make a blank .mdb, import the relevant objects to the .mdb and upload.

Jeffrey Coachman

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

G Scott

ASKER

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.

G Scott

ASKER

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

G Scott

ASKER

* I say 'I fixed it', but really it was all capricorn1's code. : )

Jeffrey Coachman

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

G Scott

ASKER

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?

Jeffrey Coachman

Like I said, I'm not crying over this...

Stop code after &quot;True&quot; condition found.

Stop code after "True" condition found.