Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    dhicks is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    36

    Message Box Display if Field is Null

    Hi there,

    In my main form, I have command buttons that allow a user to view individual records one at a time. The main form has some information on the item, but the subform has additional details about the item. I want to create a Message Box that would pop up if the record the user is on in the main form has a particular field in the subform that is null. Has anyone ever done this before?



    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if IsNull(txtbox) then msgbox "XXX field is null"

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As ranman256 shows, the code is simple. The real trick is figuring out what event to put the code into. Perhaps the form Current event.

    However, if field is required to have data, set it as required in table.
    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
    dhicks is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    36
    Yes, that part I have. I'm not being specific enough. Here is the scenario - I am viewing a record in the Main form, either by initially opening the Main form or moving to another record within the Main form. Upon either opening or moving, the subform needs to be checked to see if field Date1 is populated and field Date2 is empty. If this criteria is met, then the message box needs to appear. This needs to occur upon moving to any record in the form.

    The tricky part is that there are multiple records in the subform for each individual record in the Main form. I need the code to read each record in the subform (related to the record showing in the Main form only) to see if there is an occurrence of the criteria match described above.

    I can build some code on the first field in the main form so that when it changes (by moving to another record - I'm assuming the AfterUpdate event), but I am unsure how to get it to look at each associating subform record.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ...the subform needs to be checked to see if field Date1 is populated and field Date2 is empty. If this criteria is met, then the message box needs to appear
    So, as long as 1 related subform record meets the criteria, that's ok? Or every subform record must have both date fields populated?

    Perhaps a query along this line( for concept only) to find out
    select count(*) from subformRecordsource
    where isdate(date1) and not isdate(date2) and subformrecordID = mainformRecordid

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I'm assuming the AfterUpdate event)
    Use the OnCurrent Event if you want it to do something each time you move to a different record. If you run this from the Mainform, your subform field names below will need to be the full syntax like Forms![frmMain]![subForm![Date1]

    Maybe:
    If DCount("Date1","YourTable", MainFormID = Me.MainFormID AND Date1 <> null) > DCount("Date2","YourTable", MainFormID = Me.MainFormID AND Date2 <> null) then Msgbox "Date2 is null"

  7. #7
    dhicks is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    36
    There may be times when there is more than one record in the subform that meets the criteria. But the message box should pop up when it finds the first instance of said criteria.

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    There may be times when there is more than one record in the subform that meets the criteria. But the message box should pop up when it finds the first instance of said criteria.
    Above statement seems to contradict earlier statements. So you open your form and first Main record displays and all subform records display linked to that Main record. At that point if any subform records linked to that Main record have Date1 and no Date2, then you want a msgbox to pop up on the Main form saying something? And when you move to the next Main record, it will check again and pop a message if any of the new subform records meet that criteria?

    Or you select a main record and the subform records appear and you want to scroll through the subform records and when you reach one that has a Date1 and no Date2, you want a msgbox to pop up?

  9. #9
    dhicks is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    36
    The first scenario you described is exactly what I am trying to achieve.

  10. #10
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Then barring syntax errors, I think the line I suggested might work. If you have more records with a Date1 value then records with a Date2, then that triggers your msgbox no matter which subform record it is. This would go in the OnCurrent Event of the Main Form. Maybe create a query linking the main table to the detail table.

    If DCount("MainFormID","YourQuery", MainFormID = Me.MainFormID AND Date1 <> null) > DCount("MainFormID","YourQuery", MainFormID = Me.MainFormID AND Date2 <> null) Then Msgbox "Your msg here"

  11. #11
    dhicks is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    36
    Maybe it's because I am using Access 2016, but it seems there is no OnCurrent event for the Form or any of the Controls.Click image for larger version. 

Name:	event.PNG 
Views:	20 
Size:	5.7 KB 
ID:	29431

    Spoke too soon, found it!!

  12. #12
    dhicks is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    36
    Quote Originally Posted by Bulzie View Post
    If DCount("MainFormID","YourQuery", MainFormID = Me.MainFormID AND Date1 <> null) > DCount("MainFormID","YourQuery", MainFormID = Me.MainFormID AND Date2 <> null) Then Msgbox "Your msg here"
    Based on your line above, my code is:

    If DCount("frmLnrLckrInv", "qryMsgBoxCriteria", frmLnrLckrInv = Me.frmLnrLckrInv And Reservation_Date <> Null) > DCount("frmLnrLckrInv", "qryMsgBoxCriteria", frmLnrLckrInv = Me.frmLnrLckrInv And Actual_Pickup_Date <> Null) Then MsgBox "There is an active Reservation on this item, check for potential Date conflicts."
    End If

    This resulted in a compile error - Method or data member not found, highlighting the text I have in red above. But shouldn't that be addressing the subform instead of the main form? It is the subform that has the dates in question.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Cannot compare to Null, review: http://allenbrowne.com/casu-12.html

    Use the PK from the main form, assuming this value has been saved as FK in the dependent table. If there cannot be any records without a Reservation_Date, that criteria is not needed.

    Assume PK and FK are autonumber and number types.

    DCount("*", "qryMsgBoxCriteria", "name of FK field here = " & Me!PKfieldnameHere & " And IsNull(Actual_Pickup_Date)")
    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.

  14. #14
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Thanks June7, I usually have to play with it some when dealing with nulls and such. dhicks, you are not looking at data in the subform but rather getting all counts from the query based only on the Main record key field value.

  15. #15
    dhicks is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    36
    You're right, I'm tweaking the query now as I type!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-07-2017, 08:55 AM
  2. Replies: 2
    Last Post: 08-28-2014, 11:47 AM
  3. Replies: 3
    Last Post: 09-12-2013, 02:18 PM
  4. Display custom error if field is null or zero
    By steve87bg in forum Access
    Replies: 1
    Last Post: 01-14-2013, 02:33 AM
  5. Null field message
    By jpkeller55 in forum Access
    Replies: 3
    Last Post: 09-03-2010, 10:45 AM

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