?
Solved

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

Posted on 2016-10-31
1
Medium Priority
?
50 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

580 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