Results 1 to 9 of 9
  1. #1
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228

    Question Code skips over certain steps

    Hi all,



    I have written a bit of straightforward code to make sure none of my fields are empty. if they are empty, a msg box comes up and alerts the user to enter information or else it wont save the record.

    i have attached my code. for some reason, it does not check two of the fields: evaporation lot field, and diffusion lot field. everything else works fine.

    it is worth noting that, the evaporation and diffusion fields are auto populated upon selecting an entry from the Work Order combo box.

    any ideas of what is going on?
    Code:
    Private Function CheckBeforeSaving()'checks for data integrity
    
    
    Dim strNumStacks As String
    Dim intStackResponse As Integer
    strNumStacks = "Is this the correct number of stacks?"
    
    
    If IsNull(Me.CboWorkOrder) Then
    MsgBox "Please Select a Work Order"
    Me.CboWorkOrder.SetFocus
    CheckBeforeSaving = False
    ElseIf IsNull(Me.TxtPartNumber) Then
    MsgBox "No Part Number? Notify Engineering"
    Me.TxtPartNumber.SetFocus
    CheckBeforeSaving = False
    ElseIf IsNull(Me.txtEvaporationLot) Then
    MsgBox "No Evaporation Lot? Notify Engineering"
    Me.txtEvaporationLot.SetFocus
    CheckBeforeSaving = False
    ElseIf IsNull(Me.TxtDiffusionLot) Then
    MsgBox "No Diffusion Lot? Notify Engineering"
    Me.TxtDiffusionLot.SetFocus
    CheckBeforeSaving = False
    ElseIf IsNull(Me.TxtStacksBuilt) Then
    MsgBox "Please enter quantity of stacks being built"
    Me.TxtStacksBuilt.SetFocus
    CheckBeforeSaving = False
    ElseIf CInt(Me.TxtStacksBuilt) > 25 Or CInt(Me.TxtStacksBuilt) = 0 Then
    intStackResponse = MsgBox(strNumStacks, vbYesNo)
        If intStackResponse = vbYes Then
        CheckBeforeSaving = True
        Else
        Me.TxtStacksBuilt.SetFocus
        CheckBeforeSaving = False
        End If
    Else
    CheckBeforeSaving = True
    End If
    End Function

  2. #2
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    I have used this code before, and it works well in other applications.
    I am bit stumped.

    i also tried replacing Isnull() with

    if me.txtNAME="" then

    but no success. it would then skip ALL the steps except the Cint() step.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Have you step debugged? What values show in code for those fields?

    Why would you need to check fields that are auto-populated? Are you saving this data? Is this duplicating data?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    I use this code to check if any required to be filled text boxes are empty, if they are set the label color to red
    keep looping until they are or they have to close the form without saving the record.
    the check1...2 are on another page of my tabed form and can only be changed using a password to open the tab page
    if unchecked the field may be enpty.

    I am curious to know if anybody else may have another way to do something like this


    If Not (IsNull(Me.From_Company) And Me.Check1 = True) Then Me.From_Label.ForeColor = 16777215
    If Not (IsNull(Me.To_Company) And Me.Check2 = True) Then Me.Label47.ForeColor = 16777215
    If Not (IsNull(Me.addressed_to) And Me.Check3 = True) Then Me.Label45.ForeColor = 16777215
    If Not (IsNull(Me.Text67) And Me.Check4 = True) Then Me.Label68.ForeColor = 16777215
    If Not (IsNull(Me.Text13) And Me.Check5 = True) Then Me.Label14.ForeColor = 16777215
    If Not (IsNull(Me.Consignment_Note_Number) And Me.Check6 = True) Then Me.Consignment_Note_Number_Label.ForeColor = 16777215
    If Not (IsNull(Me.Courier) And Me.Check7 = True) Then Me.Label22.ForeColor = 16777215
    If Not (IsNull(Me.Date_Received) And Me.Check8 = True) Then Me.Date_Received_Label.ForeColor = 16777215
    If Not (IsNull(Me.Number_of_items_listed) And Me.Check9 = True) Then Me.Label70.ForeColor = 16777215
    If Not (IsNull(Me.Number_of_Items_Received) And Me.Check10 = True) Then Me.Number_of_Items_Label.ForeColor = 16777215
    If Not (IsNull(Me.Weight) And Me.Check11 = True) Then Me.Label24.ForeColor = 16777215
    If Not (IsNull(Me.Consignment_Type) And Me.Check12 = True) Then Me.Consignment_Type_Label.ForeColor = 16777215
    If Not (IsNull(Me.Description_of_Goods) And Me.Check13 = True) Then Me.Description_of_Goods_Label.ForeColor = 16777215
    If (IsNull(Me.Consignment_Note_Number) And Me.Check6 = True) Or (IsNull(Me.From_Company) And Me.Check1 = True) _
    Or (IsNull(Me.To_Company) And Me.Check2 = True) Or (IsNull(Me.Courier) And Me.Check7 = True) Or (IsNull(Me.addressed_to) And Me.Check3 = True) _
    Or (IsNull(Me.Text67) And Me.Check4 = True) Or (IsNull(Me.Text13) And Me.Check5 = True) Or (IsNull(Me.Date_Received) And Me.Check8 = True) _
    Or (IsNull(Me.Courier) And Me.Check7 = True) Or (IsNull(Me.Number_of_items_listed) And Me.Check9 = True) Or (IsNull(Me.Number_of_Items_Received) And Me.Check10 = True) _
    Or (IsNull(Me.Weight) And Me.Check11 = True) Or (IsNull(Me.Consignment_Type) And Me.Check12 = True) Or (IsNull(Me.Description_of_Goods) And Me.Check13 = True) Then
    t = MsgBox("A required field has not been completed !" & vbCrLf & vbCrLf & " Fields shown in red require an entry", vbOKOnly, "Required feilds are empty !")
    If (IsNull(Me.From_Company) And Me.Check1 = True) Then Me.From_Label.ForeColor = 255
    If (IsNull(Me.To_Company) And Me.Check2 = True) Then Me.Label47.ForeColor = 255
    If (IsNull(Me.addressed_to) And Me.Check3 = True) Then Me.Label45.ForeColor = 255
    If (IsNull(Me.Text67) And Me.Check4 = True) Then Me.Label68.ForeColor = 255
    If (IsNull(Me.Text13) And Me.Check5 = True) Then Me.Label14.ForeColor = 255
    If (IsNull(Me.Consignment_Note_Number) And Me.Check6 = True) Then Me.Consignment_Note_Number_Label.ForeColor = 255
    If (IsNull(Me.Courier) And Me.Check7 = True) Then Me.Label22.ForeColor = 255
    If (IsNull(Me.Date_Received) And Me.Check8 = True) Then Me.Date_Received_Label.ForeColor = 255
    If (IsNull(Me.Number_of_items_listed) And Me.Check9 = True) Then Me.Label70.ForeColor = 255
    If (IsNull(Me.Number_of_Items_Received) And Me.Check10 = True) Then Me.Number_of_Items_Label.ForeColor = 255
    If (IsNull(Me.Weight) And Me.Check11 = True) Then Me.Label24.ForeColor = 255
    If (IsNull(Me.Consignment_Type) And Me.Check12 = True) Then Me.Consignment_Type_Label.ForeColor = 255
    If (IsNull(Me.Description_of_Goods) And Me.Check13 = True) Then Me.Description_of_Goods_Label.ForeColor = 255
    Exit Sub
    Else
    End If

  5. #5
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    I have used the tag property of control.

    Code:
    Dim ctl As Control,cnt As Integer
    For Each ctl In Me.Controls
      If ctl.Tag = "XYZ" Then
          If IsNull(ctl.Value) = True Then
           ctl.BackColor = vbYellow
               cnt = cnt + 1
           End If
      End If
    Next ctl
    
    If cnt > 0 Then
      MsgBox "Highlighted fields are mandatory", vbInformation
    Else
     'proceed normally
    EndIf

  6. #6
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60

    Post

    First, I would use else statements instead of an additional if statement for the opposite condition of the first if statements, like
    If (conditionToCheckIfNull) Then
    ForeColor = vbYellow
    Else
    ForColor = vbGreen
    End If

    Next, I might iterate over a collection of the controls, or since each seem to be matched with a checkbox, a lightweight class that has a control and a check box and a function that tests the one control against the checkbox, and then alters the color using a function within the class itself. This might not be worth the time for a small project, especially if this pattern of tests doesn't show up anywhere else.

    Lastly, I would name all of those checkboxes, although that might be a slight pain now, I think it would be worth it. Name it to where they are linked to if anything else.

  7. #7
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60
    Quote Originally Posted by amrut View Post
    I have used the tag property of control.

    Code:
    Dim ctl As Control,cnt As Integer
    For Each ctl In Me.Controls
      If ctl.Tag = "XYZ" Then
          If IsNull(ctl.Value) = True Then
           ctl.BackColor = vbYellow
               cnt = cnt + 1
           End If
      End If
    Next ctl
    
    If cnt > 0 Then
      MsgBox "Highlighted fields are mandatory", vbInformation
    Else
     'proceed normally
    EndIf
    The checkboxes seem like a part of the equation though. If you use the checkboxes name as part of the tag though, you could refer to the checkbox as Controls(textbox1.tag).value or something like that.

  8. #8
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    For mejia.88:

    You may have designed it this way intentionally, but your code will only give you an error message for the first field it finds with an error - the other checks will be skipped (it is how you are using If-then-ElseIf). You would have to call CheckBeforeSaving after each correction you made until there were no errors.

    If that is how you want it, then could could put a msgbox in the code to show you what the current contents of the problem fields are, something like this, for example:

    msgbox "IsNull(Me.txtEvaporationLot) = " & IsNull(Me.txtEvaporationLot) or
    msgbox "IsNull(Me.TxtDiffusionLot) = " & IsNull(Me.TxtDiffusionLot)

    which shows you the expressions and their current values.

    I don't see anything else in the code that might cause it not to work.

    HTH

    John

  9. #9
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    For John_G and June7,

    First, thank you both for your help!

    As I debugged I found that after auto-populating, if there was no information to auto-populate, it would populate with "", not null.
    since Isnull does not consider "" as null, it would skip over that code.
    i have changed from if Isnull(me.name) then to if me.name="" then.

    June7,
    i am not duplicating any data. i have added this code because we are changing manufacturing scheduling systems, and there could be some data loss (according to the IT guys). i added this check just to make sure all information is present for processing. it is also a double check for the person scheduling. with a new system, things can be rough while the scheduler gets used to the new software.

    JohnG,

    i did intend the code to find the first empty text box; i did try your method for evaluating the contents of a box, pretty nifty!

    regards,
    J

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Loop skips a large number of records unless stepped through
    By Monterey_Manzer in forum Programming
    Replies: 1
    Last Post: 10-04-2013, 05:01 PM
  2. help with next steps mysql
    By Hockeyguy1924 in forum SQL Server
    Replies: 1
    Last Post: 06-15-2012, 05:13 PM
  3. Guiding Through Steps (Opinion Please)
    By Dalagrath in forum Forms
    Replies: 1
    Last Post: 04-01-2011, 05:02 PM
  4. New to access, looking for steps to complete
    By iamyourdemize in forum Access
    Replies: 1
    Last Post: 05-21-2010, 02:19 PM
  5. Replies: 0
    Last Post: 10-14-2009, 02:49 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums