CHANGE IN MY CODE FOR FORM 1

[netrusher]

Below is the code that I currently have in the BeforeUpdate Event of Soe_Code. This works well. I have added a new wrinkle for this code as follows. If my FutureEffective field is true I do not want to find previous forms that are still open. That works!

Now my Dilemma: If the FutureEffective is false I want to find previous forms that are still open (that works too) but I do not want to include previous forms where FutureEffective is true. Right now if FutureEffective is false the code finds the latest Form that is open and takes me to that form. That form could have the FutureEffective box checked as true and I want to avoid that. If FutureEffective is false I just want to find the lastest fom that is still open but only where FutureEffective is false. I hope someone can assist me with this.

Private Sub ctrSoeCode_BeforeUpdate(Cancel As Integer)

Dim Rs As DAO.Recordset
   Dim count As Integer, Criteria As String
   Dim chgDate, Worker As String, Form As String
   Dim SL As String, DL As String
   Dim msg As String, Style As Integer, Title As String
   
   Me.Process = Me.ctrSoeCode.Column(2)
   Me.SOE_Description = Me.ctrSoeCode.Column(1)
   
   SL = vbNewLine
   DL = SL & SL
      
   Criteria = "([Soe_Code] =  '" & Me.ctrSoeCode.Column(0) & "') AND " & _
              "([OMS Completed] =  False)"
   count = DCount("[Soe_Code]", "all_trucks_table", Criteria)
   
   chgDate = DMax("[date_of_Change]", "all_trucks_table", Criteria)
   If IsNull(chgDate) Then
      'Msg = "Cannot aquire date!" & DL & _
       '     "No Records Returned matching Criteria!"
      'Style = vbCritical + vbokobnly
      'Title = "No Date Error! . . ."
      'MsgBox Msg, Style, Title
   Else
         
      Criteria = "([Soe_Code] =  '" & Me.ctrSoeCode.Column(0) & "') AND " & _
                 "([OMS Completed] =  False) AND " & _
                 "[Date_of_Change] = #" & chgDate & "#"
      Worker = DLookup("[Soe_Worker]", "all_trucks_table", Criteria)
      Form = DLookup("[Form #]", "all_trucks_table", Criteria)
      
   If Me.FutureEffective = False And count > 0 Then
         msg = "SOECode: " & Me.ctrSoeCode.Column(0) & " is still open " & count & " Time(s)." & DL & _
               "Latest Time: " & Format(chgDate, "General Date") & SL & _
               "Latest User: " & Worker & DL & _
               "Latest Form: " & Form & DL & _
               "The OMS for this SOE is currently being worked on. " & DL & _
               "1. Click OK (you will automatically go to the Form shown above) " & SL & _
               "2. Add new changes (in the SOE Change Details Box)" & SL & _
               "3. Make sure you put today's date with your new changes" & SL & _
               "4. Un-check and Re-check the SOE Ready for OMS Check Box"
        Style = vbInformation + vbOKOnly
        Title = "Consolidation Notice!"
        MsgBox msg, Style, Title
        'then after the MsgBox line...
                        Set Rs = Me.RecordsetClone
                        Rs.FindFirst "[Form #] = " & Form
                        If Not Rs.EOF Then
                        Me.Undo
                        Cancel = True
                        Me.Bookmark = Rs.Bookmark
                        End If
        Else
        If Me.FutureEffective = True Then
           Me.Process = Me.ctrSoeCode.Column(2)
           Me.SOE_Description = Me.ctrSoeCode.Column(1)
        
        End If
      End If
   End If

End Sub

 

[Opieo]

I am not sure what the names of your other forms would be, and I am pretty new to visual basic coding of any sort, but I do know the general ideas of how it might be possible.
After you know that this form's future effective is false, add a check for the previous form's future effective to check what it is before you do anything.

To check another form other than the selected one:
If Forms!YourFormNameHere.FutureEffective = True
' Do not do anything with this other form
Else
' Do what you want here since this is the form you want

If there are multiple forms, you could use a Do Until loop with a check variable set to false. Once you find the form with the Else statement (so future effective is false on other form), set your check variable to true.

I hope this helps.
The main line here that I am hoping will help is the how to check another forms property or object with:
Forms!YourFormNameHere.FutureEffective

Good luck!

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[netrusher]

As you can see in the code I listed above it searches for form numbers. Each form is assigned an autonumber and that is what the code is looking for. I would just like to be able to modify my existing code but am unsure how to do that. If you or anyone else has any more suggestions please let me know.

Allen

 

[Opieo]

Again, I am sorry for my overall lack of VBA knowledge. I have been coding in VBA for less than 2 months.

I better understand now though, how your code works and what you are requesting.

My new idea is (if this makes sense) can't checking for Future Effective on the other forms just be added to the criteria of the DLookup?

Criteria = "([Soe_Code] = '" & Me.ctrSoeCode.Column(0) & "') AND " & _
"([OMS Completed] = False) AND " & _
"[Date_of_Change] = #" & chgDate & "#"

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[Opieo]

Ok, didn't mean to click submit there, wasn't finished, was going to click preview, and I don't see where to edit the post.

Criteria = "([Soe_Code] = '" & Me.ctrSoeCode.Column(0) & "') AND " & _
"([OMS Completed] = False) AND " & _
"([Date_of_Change] = #" & chgDate & "#) AND " & _
"([FutureEffective] = False)"



I hope I am understanding DLookup right and that this helps.

Again, good luck!

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[netrusher]

Opieo,

I was trying that yesterday and could not get it to work. I just now changed the criteria in two places and it worked. Thanks for your help!

Allen

 

[Opieo]

No problem, glad I could help =)

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.