Results 1 to 5 of 5

code to validate data in 4 related tables in access

  1. #1
    sanal is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    29

    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 online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,599
    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 offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,446
    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 Novice
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    29
    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
    ridders52's Avatar
    ridders52 is offline Long time standing
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    1,883
    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 (Mendip Data Systems), Website, email

    If this has helped, please click the star button and leave a comment

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

Similar Threads

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