Results 1 to 5 of 5
  1. #1
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43

    code to validate data in 4 related tables in access

    There are 4 tables in my database related each other using a primary key. I want to validate whether these 4 tables have data before processing. I create a button and when this button is clicked data in these 4 tables are processed and generate a report. If in any of the underlying table has no data the processing is not done. In this case I want to inform the user in which table has no data and asked to enter data in it. I want to display a message for the same. But Iam not fully succeeded in making the perfect code for the same. Will any one help me with a sample code.

  2. #2
    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
    Please post
    -a copy of your relationships window (tables and relationships) showing all fields in each table, and
    -the current validation code you have.

    Preferably post a copy of your database in zip format.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Get a record count of each,then alert user or run

    t1= dcount(....)
    t2= dcount(.....)

    if t1 =0 then MsgBox "table1 has no data"
    etc...

  4. #4
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43
    Thank you for the help. The Dcount is used by me. But if all the three tables have no data all the 4 messages appeared simultaneously. I want to appear the second message after the user has given time to rectify the defect pointed out in the first message so on. How can I achieve it. So many ways are tried but vain. Please give me a sample code to achieve the same.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You could try putting the line DoEvents after each message box to give the processor time to complete each task.

    However I would suggest doing all 4 DCount checks and building up a text string for one message
    Air code....

    Code:
    Dim strText as String
    StrText="" 'not really needed here
    
    If DCount(for table 1.....)=0 Then strText =.......
    If DCount(for table 2 ....)=0 Then strText = strText & vbCrLf & ......
    
    Etc
    
    
    If strText<>"" Then MsgBox strText, vbExclamation, "Missing data"
    HTH
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 4
    Last Post: 01-11-2015, 08:17 AM
  2. Replies: 5
    Last Post: 12-14-2012, 04:21 PM
  3. Replies: 10
    Last Post: 07-18-2012, 03:42 PM
  4. Replies: 1
    Last Post: 07-16-2012, 02:10 PM
  5. How to validate data using MS Access
    By maniii in forum Import/Export Data
    Replies: 2
    Last Post: 09-01-2011, 07:29 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