Open Query If Condition Is True

Jun 15, 2006

I have a database with 3 tables. Each table has an account number field however the tables are completely different as are the account numbers that populate them. To go with the tables are queries that will bring in desired information. What i would like is for the user to input an account number on a form and have some code in the background that will find the account number in the table it is in and open the corresponding query. Is this possible? Thanks for the help. I have been trying to write code with DLookup but can't seem to get it to do what I want.

View Replies


ADVERTISEMENT

Queries :: Multiple Query Criteria IIF Statement - True Condition

Feb 24, 2015

I'm trying to have a single or multiple query criteria based on what the user checks on a form.

I can't get the True condition to work at all, I get no records. Here is what I'm using

IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])

If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.

View 14 Replies View Related

Cancel Adding New Record If Condition Is True

Oct 11, 2007

Hi

I wrote code that should validate a field when entering a new record and then if a condition is true, that new record should be cancelled and not entered into the table.

I managed to partially achieve this by writing the code below, but the new record does not get cancelled because the table will still create a PK for that record and leave the rest of the fields empty. I am using an autonumber for the PK that's why the table creates it automatically What I want to achieve is to cancel the creation of a new record at once, I don't want even PK created for that new record.

I used the CancelUpdate because I thought it would cancel the record creation, but it did not! When I read about it it said that I need to use it with either Edit or AddNew, (which i don't understand why!) but it still does not work.

Private Sub PlotNum_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_msg
Dim db As DAO.Database, rs As DAO.Recordset
Dim n As Integer, i As Integer
Dim vPlotNum As Integer
Dim vPhaseID As Integer

vPhaseID = Forms![frmHouse].Form![PhaseID]
vPlotNum = Forms![frmHouse].[qryHouse2].Form![PlotNum]

Set db = CurrentDb
Set rs = db.OpenRecordset("tblHouse")
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst
If n > 0 Then
For i = 1 To n
If rs![PhaseID] = vPhaseID Then
If rs![PlotNum] = vPlotNum Then
rs.Edit
rs.CancelUpdate
MsgBox "This plot number already exist in this particular phase." & vbCrLf & "Please choose a different Plot Number"
Forms![frmHouse].qryHouse2.Form![PlotNum].Text = ""
End If
End If
rs.MoveNext
Next i
End If
rs.Close
db.Close
Set db = Nothing
Set rs = Nothing

Exit_Err_msg:
Exit Sub

Err_msg:
MsgBox Err.Description
Resume Exit_Err_msg
End Sub


Any suggestions will be very much appreciated.
Thanks.
B

View 14 Replies View Related

Auto Fill Entry If Condition Is True

Apr 19, 2005

I have a table

(Not real names)

AutonumberPK
RequestType (Lookup to TypeTable)
RequestNumber
RequestName

This table then Relates to a request detail table.

The question here,

There are two types or request (T1 and T2)

In my form to enter new Requests I will have a dropdown box to select the type of request.

The next field is the request number. If the type of request is T1 then the person entering the request will have a 6 digit number to enter. But if the request is T2 then I need to auto populate the field.

Is this possible?
Or should I just build two forms, one for each type.

Also what would be the best way to auto fill in a number that follow this pattern.

05040001
05 = year
04 = month
0001 = the number of the request for this month.

Every month starts over at 0001

View 2 Replies View Related

Open Query If Field Is True

Jun 6, 2006

I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!

View 1 Replies View Related

Open Query If Field Is True

Jun 6, 2006

I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!

View 1 Replies View Related

Open Query If Field Is True

Jun 6, 2006

I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!

View 3 Replies View Related

Change Color Of Whole Line If Condition Yields True Of One Control

May 28, 2013

I know you can use a "Conditional format" to change a color of a control in a report. What I am trying to do is change the whole line if a condition yields "True" of one control. I did it once before, but oops I didn't save my work. I am using the format [Entity]![IT]. Where "Entity" is either the department or vendor, and "IT" is for the IT department. I want the control "Name" to change color if "Entity" is true if "IT" is the data.

View 3 Replies View Related

Problem To Open New Form With DataEntry=True.

Apr 24, 2005

Hi,
I have Add button. When i click on the button, it will open frmstaff for me to enter new staff record. Now, i have to set DataEntry=True. It means i have 2 forms, one for data entry and another form to view previous staff records. Is it possible for me to do like this?


Private Sub cmdAdd_Click()
Set Forms("frmstaff").DataEntry = True
End Sub


but it will give me error,
Invalid use of property.

any idea? thanx in advance..;)

View 1 Replies View Related

General :: Setting Open New Window Value To True

Jan 16, 2013

I was reading how to make a a button open a webpage. I found out how to and what to type in the Visual Basics. It worked but like another user said, when ever he clicks the button, the window opens for the website but the window is not maximized. He said "I set the open new window value to True".

View 1 Replies View Related

Open Form With Condition

Mar 25, 2006

I have searched, but I cant find a thread for this.

I have a calendar that I input when people are going to be off. If there is nothing for that date then the record is blank. I have a form that opens when the db opens attached to the date on the switchboard. If no one is off then the form opens blank because there is no date in the table to match. What I want to do is have a something built into the code that says if there is no match (null??) then dont open the form.

DoCmd.OpenForm "PopUpCalendar", acNormal, , "[Calendar]![Date] = Forms![Switchboard]![MainDate]"

View 1 Replies View Related

Using Where Condition In Open Form Command?

Oct 9, 2015

I have a Datasheet form which has a text field "Customer" which is hyperlinked to open another form to populate Customer details. I am using MacroBuilder with the OpenForm command with the Where condition which is something like this

WHERE ="[Customer]=" &[Customer]

But this does not seem to work for this field.

[The Customer field contains names that have text and also other special characters. Ex: "John Chemicals (U.S)"

View 11 Replies View Related

Forms :: How To Open Navigation Form Using If-then-Else Condition

Jul 29, 2013

I am pretty new to access so i am using a very basic function structure to open a navigation form using if-then-else condition, Below is my VB coding:

Option Compare Database
Private intLogonAttempts As Integer
Private Sub Emp_Exit_Click()
DoCmd.Quit
End Sub
Private Sub EmpLogin_Click()

[Code] ....

I am getting an run time error '2467': "The Expression you entered refers to an object that is closed or doesn't exist"...

View 14 Replies View Related

Modules & VBA :: How To Open Report From Msgbox With A Condition

Jan 13, 2014

i have a form to enter a new client if the client is existed then a message box appear and tell me that this client is existed and his number id is ## with two buttons yes and no.if i click yes i should go to a report that contains information about that user and this report take his data from a query

new client save code
---------------------
Dim MSG As Integer
Dim ExistentID As Long
ExistentID = Nz(DLookup("P_ID", "tbl_Personal_Information", "Full_Name = Forms!frm_New_Person!F_N"), 0)
If ExistentID > 0 Then

[code]....

when i click yes the report should open with the existent id but he open a input box to ente an id and his title is tbl_personal_information.PID it's the ID field that it's named P_ID in the query

View 4 Replies View Related

Forms :: Open Form With Where Condition - No Record Found

Apr 16, 2013

I try this code and it's not working

no record found

Code:
Private Sub openForm_but_Click()
Dim whr As String
whr = "get_date = #" & Me.gDate & "#"
DoCmd.OpenForm "searchResult_form", acNormal, "", whr, acFormPropertySettings, acDialog
End Sub

View 7 Replies View Related

General :: Macro To Email A Form Once Condition Met - Open Outlook From Access

Apr 10, 2013

I have a macro set to email a form to a person once a condition is met. This works fine but I've discovered that Outlook (2010) has to be open BEFORE this macro is invoked.

I wanted to add a step to the macro before the one that makes it email that basically says to open Outlook.

I have tried the RunApplication macro, but I apparently am not getting the path right. I don't understand what the problem is.

I accidentally dragged Outlook to my desktop and therefore created a shortcut from my Start Menu, but now when I go to Microsoft Office in my Start Menu, Microsoft Outlook isn't one of the options. I can't find the path to where it may reside now.

I am using the path to the shortcut on my desktop.

C:UsersJust MeDesktopMicrosoft Outlook 2010

What else am I supposed to do?

View 8 Replies View Related

True Or False Query Issue

May 12, 2006

I have a query that shows banned users (I work in a school). When a student's ban has ended, a tick box is checked in a form linked to tblBannedUsers to show they are no longer banned.

The query itself simply queries all records in tblBannedUsers with a username field (UserID) taking search criteria from a box on a form.

However, I want to filter out the students that are no longer banned (those with a tick in the checkbox). To do this, I thought it would be a simple case of editing the query, and in the Ban Lifted field criteria, use =False to say I only want those records with a tick.

This doesn't work and I still get all records given the combo box filter (which are just filtering for a username...if I leave it blank it gives all records via Is Null).

What should I be putting in the criteria to filter out those records with a ticked checkbox?

Thanks,

Steve Swallow

EDIT: I've just done a test query and <B>No</B> is the criteria to use, but when I use this criteria in my query which also take data from a form's combo box it ignore the <B>No</No> criteria.

View 4 Replies View Related

Query Similar To Vlookup (true)

Oct 9, 2006

Hello Everybody!!!

I need a help.
I have one table that has a field called "Initial ZIP CODE", another field called "Final ZIP CODE", and a field called "AREA".

I have a data base that will give me the specific "ZIP CODE", that will be between the "Initial ZIP CODE" and "Final ZIP CODE", and I want to get the "AREA".

Anybody knows how to to that???

Tks in advance.

Paulo

View 2 Replies View Related

Query Criteria True/false Field

Apr 25, 2006

I'm trying to limit the records on a subform via an option group selection. The group has 2 options: optionTrue (option value = 1) and optionFalse (optio value = 2). I have the following code in my query criteria of the true/false field.

IIf([Forms]![FrmHome]![frameProcessed]=1,-1,0)


The false part works, but the true part doesn't. I've tried many variations using true/false, using checkboxes, etc. and nothing works.

The database is SQL Server if that matters.

Any suggestions?

Also, is their a way to have an option for True or False or ALL?

Thanks,
Sup

View 5 Replies View Related

Returning True/False To A Form When Query Is Run?

Mar 9, 2007

I'm really not sure how to go about this. I'm creating a course booking system and when creating a booking I need to check for current bookings with the same employee and course id's (i.e. the employee is already booked on the course). The query takes the employee and course id's from a form, and is initialised when the 'book' button is pressed. It correctly selects if the person is already booked on the course but I want the query to return a value to the form i.e. if it returns null/false then the booking can be created but if it returns a record/true then the booking already exists and a message box can be displayed.

I'm not sure if I'm going about this the right way, can anyone suggest how this can be done as described above or suggest a better way of doing the task?

View 5 Replies View Related

Yes/no Condition For Query

Mar 1, 2006

i have a Yes/No checkbox to indicate whether a job has been done.. if it is done then i want it removed from the list.. so i think my query sql needs to be something like..

note..the bold bit is the bit i am questioning.

WHERE (((Booking_Main.Job_Date) Between Date() And 1+Date())) AND Booking_Confirm.Job_Done ="No"

or something like this??

View 3 Replies View Related

Format Query If Condition Is Met

Jun 21, 2005

hi ppl,
Could someone please help me with a query that I'm trying to create.
I need to create a query where once the DATE is overdue/expired how can i turn it into a red colour or something just to separate it from others.
e.g:

Length = 3
DateCleared = 31/01/2001
DateRenewal = 30/01/2004
--- obviously this date has expired and now i need to make it visible that it has expired. So I was thinking either turn it red.

thankyou.. :( :confused:
Ps. In order to create DateRenewal I used a formula by using the Length to calculate three years from the DateCleared.

View 3 Replies View Related

Unmatched Query With Condition

Jan 9, 2006

Hi there, I currently have the below tables:

tbl_stores
ID NAME
123 Store A
456 Store B

tbl_returns
ID NAME RETURN
123 Store A 9999
123 Store A 8888
789 Store C 9999

I want to find the stores that exist in the stores table, but not in the returns table WHERE the RETURN is equal to 9999. This would hopefully return 456 Store B

My problem is when I put a condition in my query it returns nothing as the condition is based on the left joined table.

SQL
SELECT tbl_stores.store, tbl_stores.name
FROM tbl_stores LEFT JOIN tbl_returns ON tbl_stores.store = tbl_returns.store
WHERE tbl_returns.store Is Null AND tbl_returns.survey_id=2
GROUP BY tbl_stores.store, tbl_stores.name, tbl_returns.store

Any ideas? Thanks for your help with this one!

Cheers

Tony

View 2 Replies View Related

Multiple Condition Query

Feb 4, 2008

Im trying to build a query that checks four fields for blanks. If I try to run the query with the criteria Is Null for all four fields, I only get 27 records. However, if I run the query with the condition only on one field I get the right number of records around 1000. I have been having to run four independant queries and then merge them in Excel. What am I doing wrong?

View 7 Replies View Related

Time Data In Query Condition

Mar 25, 2008

Hello,

I've faced a - for me - tricky problem when I've created a query from code. I have an MS SQL backend and in it there is a table which has a long time format field. I want to make an sql query where the condition is a time data. Unfortunately when I enter a time which is surely in the table, there is no result of the query. I've tried to solve it on several ways, time formatting, etc., but I had always the same result, to wit no result.
Does anyone idea, how could I solve it?
Any tips are appreciated very much!

Many thanks in advance!
VoiD:confused:

View 1 Replies View Related

Queries :: Append Query Where A Condition Has To Be Met

Feb 26, 2014

I am trying to copy notes from one table to another table where a condition has to be met and I can't figure out how to do that in an APPEND query.

Each record has a unique number that comes into my Initial Table in my Access database from a construction program I download. In this particular table both the [DNJTNo] and [DNVersionNmbr] fields can have duplicate numbers. However, when I run the query I want it to add a record to the second table only after the query checks that the [DNJTNo] in combination with the [DNVersionNmbr], is not already in the table. If it is, I do NOT want it appended to the second table. (I have attached a picture of the query in APPEND design stage).

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved