?
Solved

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

Posted on 2016-09-20
2
Medium Priority
?
191 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 51

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

762 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