Solved

Access 2007 Verification Question on database tables

Posted on 2011-09-20
3
224 Views
Last Modified: 2012-05-12
I have attached a database that contains three Tables and three Data Entry Forms for the tables.  The Tables Are;  Point of Interest, Vehicles, and Weapons.  I need the capability to verify if a Driver License Number exists in the Point of Inrterest Table if the Driver License Number is entered in the Vehicle or Weapon Table.  I want an error message to appear when a user tries to enter a Driver License Number on the Vehicle or weapon table in the Driver License TextBox field.  If the Driver License Number exists allow the saveing of the record.  If the Driver License Number does not exist I want a mesage that one has entered a Driver License Number that does not exist.  How can this be done with VBA in ACcess and what property area should the coding be put on a form.  Should the arae be on an after update event or on a On-Click event?

I have attached a sample database I created from scratch real quickly.  CFould you maybe show me some coding how this can be done in VBA?

Thanks

jjc9809
Database2.accdb
0
Comment
Question by:jjc9809
[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
3 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 500 total points
ID: 36570318
In the DriverLicenseNumber field BeforeUpdate event procedure on the Vehicles and Weapons form, put the following code:

Dim knt As Integer
knt = DCount("*", "PointOfInterest", "DriverLicenseNumber = '" & Me!DriverLicenseNumber & "'")
If knt > 0 Then
    '* Driver License Number exists on Point of Interest table.
Else
    MsgBox "Driver license does not exist on Point of Interest table. " & vbCrLf & vbCrLf & "Please press <ESC> to undo your changes."
    Cancel = True
End If

Open in new window

0
 

Author Comment

by:jjc9809
ID: 36570667
I ca't get this to work.  See my attachments where I placed the code ad tried to run it.  See if you can get it to work.

jjc9809
RunCode.jpg
CODE-DEBUG.jpg
0
 

Author Comment

by:jjc9809
ID: 36570710
Pdebaets,
 
I found my problem.  I used an incorrect name in the coding for PointOfInterest Table.  I renamed the table and removed the space in PointOfInterest.  I named the table PointOf Interest as the tablename but failed to indicate the space there so the computer was unable to find the table.

Thanks

This works.  

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

751 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