?
Solved

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

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

840 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