Results 1 to 4 of 4
  1. #1
    wtucker is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    50

    Question If field is not null, check if other field is null - Please help me write this out

    Hey everyone,

    I've got a question on SQL. I'm a novice and it's too complicated for me to do on my own.



    I have a form with multiple fields and records (it's in datasheet view). Records are approved by entering a date in the [Approved] field. But I want to make sure users also fill in another field, [Number]. Sometimes it already has a value, but usually it's blank.

    So the logic would work as follows:

    for each record,
    if [approved] is not null,
    check [number]

    if [number] is null,
    msgbox "Please ensure that each approved record has a proper number associated with it."

    else go to next record.

    I realize this isn't crystal clear, and I'm hoping someone can help me put it together, or perhaps find an easier way to make this work.

    Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Advise naming controls different from fields, such as tbxApproved and tbxNum.

    A record is committed to table when 1. close table/query/form or 2. move to another record or 3. run code to save. How do you want to commit record? Do you have a custom button set up to close form or save record?

    The real trick is figuring out what event to put code into. The form BeforeUpdate event might be appropriate for your situation. Fairly common topic. Search web or forum.

    Number is a reserved word. Should avoid reserved words as names.
    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.

  3. #3
    wtucker is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    50
    I've changed [number] to [group number] so it's a little less confusing.
    There's no button to save. Records are committed when the form is closed.
    I'll look into the BeforeUpdate event, but it sounds like it will work the same as OnClose.
    I've also tried a message box. I could live with the user selecting ok/cancel every time just as a reminder to check. But so far in my attempts, when the user hits cancel, it still closes the form. If I can figure out how to prevent that, I'll be happy.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No, BeforeUpdate does not work quite same as OnClose. Post your OnClose code. Then there's the form UnLoad event. Unfortunately, doesn't trigger if navigate to another record.

    Advise no spaces or punctuation/special characters (underscore only exception) in names.
    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.

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: 1
    Last Post: 01-05-2017, 05:14 PM
  3. Replies: 7
    Last Post: 11-07-2016, 09:24 AM
  4. Replies: 3
    Last Post: 11-13-2013, 08:56 AM
  5. If/Then Null based on Null Value of Another Field
    By vbpeterson in forum Queries
    Replies: 11
    Last Post: 06-05-2012, 05:00 PM

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