Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Display a messagebox based on results of a query - MS Access

Posted on 2016-09-20
2
Medium Priority
?
222 Views
Last Modified: 2016-09-21
I have an Access database with a form to add records using a command button.  There are a number of textboxes on the form that require the user to enter data into.  One of the textboxes is used for entering a date.

I have a small table with a list of invalid dates.   When the user clicks the command button to attempt to add a record, I want the date value entered in the textbox to be checked against the table to see if the date entered is valid.  If the date exists in the table, it is an invalid date.

The basic query for that is as follows:

SELECT Count(*) AS Expr1
FROM MyDatesTable
WHERE (((MyDatesTable[invalid_date])=Forms![Form1]![txt_Date]));

When the above query is run, it returns a value of 1 (invalid date because the date selected exists in the table) or 0 (a valid date).

What I need is, rather than have a 1 or 0 returned, I want a messagebox to pop up indicating "invalid date selected" when the query result is 1 and no action when the value is 0.  So, I'm looking to have the query results that get returned to be replaced by a messagebox (for invalid dates) or nothing (when the user entered a valid date).

Right now, under the command button to add a record, I have the following to get the basic query to run:

DoCmd.OpenQuery "Valid_Date_query"

How do I modify it or what do I replace it with to get the action I'm looking for?  Thank you.
0
Comment
Question by:dbfromnewjersey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 41807549
You can use:

If CBool(DCount("*", "MyDatesTable", "[invalid_date] = #" & Format(Forms![Form1]![txt_Date].Value, "yyyy\/mm\/dd") & "#")) Then
     MsgBox "Date in use."
End If

Open in new window

/gustav
0
 

Author Comment

by:dbfromnewjersey
ID: 41807629
Perfect. Thank you very much.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question