Solved

Get a value from Table, check if fields are -1

Posted on 2016-10-31
1
30 Views
Last Modified: 2016-10-31
hello,

I want to build a SQL that will check the table based on a "permit #" then if within that it finds that 4 fields are -1 then deny using that permit#

OK, so on my form I have the field:  txtPermit

on the beforeupdate, or even the afterUpdate,  I want it to search the txtPermit field in a table:  tblHWWPass, pull 4 fields and check to see if those all have "-1" (they are checkboxes)

txtPermit:  1

my sql will be

("Permit", "tblHWWPass", me.txtPermit)

but I don't know how to see if
Tab1
Tab2
Tab3
Tab4

are all -1

?
0
Comment
Question by:Ernest Grogg
1 Comment
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 41867646
use the dcount to check the presence of the record that satisfy the criteria in the beforeupdate event of the form

private sub form_beforeUpdate(cancel as integer)
' if Permit is Number Data type use this
if dcount("*","tblHWWPass", "[Permit]=" & me.txtPermit & " and [tab1]=-1 and [tab2]=-1 and [tab3]=-1 and [tab4]=-1") >0 then

' if Permit is Text Data type use this
'if dcount("*","tblHWWPass", "[Permit]='" & me.txtPermit & "' and [tab1]=-1 and [tab2]=-1 and [tab3]=-1 and [tab4]=-1") >0 then


 msgbox "record already exists"
 cancel=true

end if

end sub
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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 …
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…

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now