Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

610 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