Solved

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

Posted on 2016-09-20
2
161 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 50

Accepted Solution

by:
Gustav Brock earned 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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