Modules & VBA :: OpenForm Command Where Clause With 2 Criteria

Oct 31, 2013

I am having trouble opening Form 2 to the same record as the record in Form 1. Form 1 is a continuous form of questions. When certain response is given, I want to be able to add more information to the "additionalcomments" column for that record. I want to have the additional comments pop up in a new form.

Form 1 is based off of a query with no unique ID. I need the second form to open on Eval_Number and Question_Number. I have tried...

Code:
If Me.Response = 0 Then
DoCmd.OpenForm "frmadditionalcomments", acNormal, , "[Eval_Number] =" & Forms!ESVWL1Trainee!subfrmreponses.Eval_Number And [Question_Number] = " & Forms!ESVWL1Trainee!subfrmreponses.Question_Number "
End If

and this tells me the object doesn't support the method. Is the SQL incorrect? Is it the way its setup?

View Replies


ADVERTISEMENT

Where Condition Of OpenForm Command Not Working

Jan 19, 2005

The scenario is... also apologies if this seems a lot. Simple really.

An edit button on a subform is to open a separate form for editing, and I obviously want to apply a where condition to display the relevant record. So I have

DoCmd.OpenForm "editReviewPEST", acNormal, , ReviewID=Me.ReviewID, acFormEdit, acWindowNormal, False

The Record Source for editReviewPEST uses an INNER JOIN...

SELECT Reviews.Year, Reviews.ReviewID, Reviews.LesseeID, Lessee.DateOfOrigSanction, Reviews.Exposure, Reviews.ExpoCurrency, Reviews.SentToCredit, Reviews.Approved, Reviews.Completed, Reviews.Comments, Reviews.LevelOfLastSanction, Reviews.CreditManager, Reviews.TypeOfReview, Reviews.DateOfLastCreditReview, Reviews.DateOfNextCreditReview FROM Lessee INNER JOIN Reviews ON Lessee.LesseeID = Reviews.LesseeID WHERE (((Reviews.LesseeID)=[Forms]![Reviews]![SelectLessee])) ORDER BY Reviews.ReviewID DESC;

So I get the form to open with all the records relvant to the lessee, but to filter it further to exact ReviewID, I thought I could include ReviewID=Me.ReviewID (an Autonumber PK) as the Where Condition to the DoCmd.OpenForm. Instead I get a form which looks like Add Record Form.

View 4 Replies View Related

Openform With Criteria

Nov 8, 2005

I open a form with a criteria set but when the form opens it has every record, rather than restricting the records to those matching the criteria. I wonder if anyone has had this problem before? I use a query in the called to get the underlying recordset.

This is my code:

Dim CritText As String

CritText = "[ID1] = '" & Me.ID1 & "'"
DoCmd.OpenForm "frmTestAll", acFormDS, , stCritText

View 5 Replies View Related

Modules & VBA :: OpenForm Action Was Canceled

Apr 4, 2015

I have a form that opens when you initially open the MS Access file...

This first form posts session and user ID data to a sessions table, then closes itself and opens an end user form (i.e. the main form in the application)

It's work just fine for many weeks, up until a few minutes ago. Now, when I open the MS Access file I get this error message:

Run-time error '2501':

The OpenForm action was canceled.

When I choose "debug" from the error message, it's showing me that the error happens here:

Code:
Public Sub CloseMeAndOpenMain(frmMe As Form)
DoCmd.Close acForm, frmMe.Name
DoCmd.OpenForm "0100_0000_STRAT_AND_REQ_ASSEMBLY_ECs_LISTING"
End Sub

The thing is, I can then walk through the code with no issues (i.e. when I hit F8, it runs...)

Also when I open the first "sessions" form from inside MS Access (as opposed the file open feature) it runs just fine and opens the main end user file without any issue.

I only get the error when I initially open the first form from the file...

View 1 Replies View Related

Modules & VBA :: Openform Is Closing Form

Mar 20, 2015

I currently have 3 forms open and when I call a specific function and it gets to this line below it goes to the on close of 1 of my open forms closes that form before it opens the selected form in code (below) in design mode. It doesn't close all my forms....why does it do this?

DoCmd.OpenForm formname:=dynform, View:=acDesign, WindowMode:=acHidden

View 5 Replies View Related

Criteria Returning Wrong Results/WHERE Clause Length

Jul 19, 2006

Hi there,

I'm working on a select query that I have created through the visual query design window in Access 2003, I have multiple criteria but the query is not filtering the results properly.

I'm selecting only records :

where the DeliveryCountry field is within the EU - works
where the ProductID does not contain Repair, Upgrade or Rpr - doesn't work
where the Demo/Sale ID is 2 - works


I've viewed the code in SQL view but I still can't locate the problem, though I suspect it lies in the treatment of ANDs and ORs.

The SQL is pasted below (I have reformatted with indentations so I hope it is easier to read).

One thing to note: the reason the countries list is split in two is because Access did this automatically when I had one long list. Is there a maximum length for WHERE clauses?

Thanks in advance for your help!

Abby

SELECT Orders.ShipDate, Products.[Standard Tarriff Number], [Order Details].[Quantity]*[Order Details].[unitprice]*(1-[discount])*(1-[special discount]) AS [Line Total], [Order Details].Quantity, Orders.OrdDeliveryCountry, Orders.OrderID, [Order Details].ProductID

FROM Products RIGHT JOIN (([Demo/Sale] RIGHT JOIN Orders ON [Demo/Sale].[Demo/SaleID] = Orders.[Demo/SaleID]) LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID

WHERE (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Upgrade")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Repair")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Rpr")
AND ((Orders.[Demo/SaleID])=2))

OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Upgrade")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Repair")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Rpr")
AND ((Orders.[Demo/SaleID])=2))

ORDER BY Orders.ShipDate DESC;

View 5 Replies View Related

Modules & VBA :: Add WHERE Clause To Query (queryDef)

Dec 1, 2014

I have a TRANSFORMED query:

Code:
TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQAs
SELECT month(qaDate) AS QAmonth, Count(T_qa.qaQAPK) AS QAs
FROM Q_ALL_qa
GROUP BY Month(qaDate)
PIVOT month([qaDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);

This query is record source for a report, then this report show all calculations in a form.

I have 7 of each (query + report) all showing on the same form.

All those queries calculate data for all departments.

In the form, I have placed a combobox.

What I want is to create a vba code which will add clause WHERE to all queries at the same time and then run it.

Code:
WHERE qaDeptFK=Forms!F_CompLvl.cboDeptStats

However, if nothing has been selected in the combo, I want the queries to calculate data as normal, for all departments.

Where do I place the vba statement? Is it under combobox AfterUpdate event?

I am planning to use this: (As I never done it before)
[URL] ....
Modifying SQL On-The-Fly section

View 5 Replies View Related

Modules & VBA :: SQL Statement To Take A Value From Combo Box In WHERE LIKE Clause

Jun 6, 2013

I'm trying to get an SQL statement to take a value from a combo box in a WHERE LIKE clause.

For example:

INSERT INTO tblInspectionTempp (BuildingID, DoorNumber) SELECT tblDoorData.BuildingID, tblDoorData.DoorNumber FROM tblDoorData WHERE tblDoorData.BuildingID LIKE = '[Forms]![fmInspectionColumns]![cmboBuildingID].Value'"

The errors I'm receiving are either Missing Match or incorrect Syntax, depending on my trial and errors methods regarding the WHERE clause.

View 2 Replies View Related

Modules & VBA :: Update SQL With Multiple Where Clause

Sep 29, 2014

I have an update sql statement that isn't quite working properly.

My where clause has 3 criteria.
*print = -1
*stDocCriteria (project_num and client_id)

Below is what the code looks like.

Code:
updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print = -1 and " & stDocCriteria

Add watch: so you guys can see "stDocCriteria"

Code:
updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print =-1 and ([project_num]= '140012' And [client_id] = 87)"

I want to only updates records that meet all three criteria. The above sql not only updates all records that satisfy stDocCriteria regardless if print is -1(true) or 0(false).

View 10 Replies View Related

Modules & VBA :: Using Where Clause To Max Of A Table Field

Sep 18, 2014

I am trying to open a form using the where clause to max of a table field.I cannot get the syntax correct.

Code:

DoCmd.OpenForm "frmInput", , , Max(tblClients.ClientID) AS MaxOfClientID FROM tblClients

View 4 Replies View Related

Modules & VBA :: Manage IN Clause Using Parameters

Dec 10, 2014

I am trying to dynamically change the IN sql from within VBA using parameters. for some reason i have no luck, no errors shows up, but it's actually not picking up the criteria.

Code:
THE SQL IN STATEMENT

In (select RemID from [ReminderAssignees] Where RemDate between [Date1] and [Date2] And [sDismiss] )));

Code:
THE VBA CODE

qdf.Parameters("date1") = Date1
qdf.Parameters("date2") = Date2
Select Case iDismissed
Case 0, 1
qdf.Parameters("sDismiss") = "1=1"
Case 2
qdf.Parameters("sDismiss") = "(not isdate(Dismiss) or Dismiss > #" & Now & "#)"
Case 3
qdf.Parameters("sDismiss") = " isdate(Dismiss) and Dismiss < #" & Now & "#"
End Select

qdf.Execute

View 6 Replies View Related

Modules & VBA :: Open Form Where Clause With Date

Jul 21, 2015

I would like to open a form to a specific date that is taken from another form. Date field format is short date and looks like yyyy.mm.dd

I have figured it out that it only works if date format is yyyy/mm/dd

I know format function can do this, but i do not know how to write the code with correct syntax.

For example i have tried:
DoCmd.OpenForm "TreatmentsTB1", , , "DOV= #"& Format(2015.01.11,"yyyy/mm/dd")&"#"
it does not work.

View 3 Replies View Related

Modules & VBA :: Between Dates In Where Clause - Query Using Parameters

May 30, 2014

I have this code below which pulls a report based on the current date, I wanted to be able to pull the same report by entering between 2 dates as is done in a query using parameters.

Code:
reworkWhere = "ReworkTech = '" & Me.txt_tech_by_date_techid & "' And ReworkTimeOut = Date()"
repairWhere = "RepairTech = '" & Me.txt_tech_by_date_techid & "' And RepairTimeOut = Date()"
qcWhere = "QC_Tech = '" & Me.txt_tech_by_date_techid & "' And QC_TimeOut = Date()"
strWhere = reworkWhere & " Or " & repairWhere & " Or " & qcWhere
DoCmd.OpenReport "RPT_RF_TECH_REPORT_UNIT_DAILY", acViewReport, , strWhere

How to make a combo box with the months listed so they can pull this report by the month selected but is a side tangent.

View 3 Replies View Related

Modules & VBA :: InStr - Use Clause In Opening A Datasheet Form

Jun 16, 2015

I want to that the WHERE clause for a SQL statement that I am using options on a form to build. I intend to use the clause in opening a datasheet form.

This is the code I have for getting the substring

Code:

Dim intPos As Integer
Dim tempString As String
Dim BaseQueryFormStr As String
'BaseQueryFormStr is used to reopen the BaseMasterQueryFrm with the specified parameters
tempString = "WHERE"

intPos = InStr(1, strSQL, tempString, vbTextCompare)
BaseQueryFormStr = Left(strSQL, intPos - 1)
MsgBox (BaseQueryFormStr)

The value of intPos remains=0 and when the program hits the second to last line I get "run-time error 5"

View 10 Replies View Related

Modules & VBA :: Using A Custom Aggregate Function With GROUP BY Clause

Dec 20, 2013

I am trying to calculate annual percentiles of a large set of data and I have only been successful at retrieving the percentile of the entire data set (and not by the grouping). See provided example database for code/query. Query1 is what I want to happen to make the Percentiles table.

View 14 Replies View Related

Modules & VBA :: Invalid Column Error In SQL Server ODBC Where Clause

Jun 26, 2014

Connecting Access FE to SQL SERVER BE Connection is fine. I can open and close it and other queries work fine. I have only one problem with the SQL in one query

Code:
With rstRPT
If FirstRecord = True Then
.Open "SELECT Min([" & SourceTbl & "].[3Order ID]) AS MinOf3OrderID " & _

[Code].....

why it wants to see the variable as a column name?

View 1 Replies View Related

Modules & VBA :: SQL Where Clause - Open Form To Correct Work Order?

Aug 25, 2014

Windows 7
Access 2013

I've been trying to work up a where clause that is generated by a button click event on a report. The workflow that i'm trying to obtain is as follows:

1) A report is run to determine the remaining work orders that need to be processed.
2) A button that is placed on that report is to be clicked, taking the user to the form associated with that work order, so it can be processed.

What i've been able to do so far is capture the unique ID for the work order and then print that in a message box. I can then open the form.

What i haven't been able to accomplish thus far is to open the form to the correct work order.

Things I've tried : I started trying to use the macro with the search for record option and using the where clause. Not successful. I am a little more comfortable in using vba so i switched to that pretty quickly.

Code:
Private Sub btnJobEntry_Click()
'GOAL: open the work order form to the correct entry
'METHOD: store the uniqueID to a variable, then use that in the open command's where clause
Dim strJobID As String
'store the unique ID in the variable

[Code] ....

I've put the strJobID variable in both the filter and where clause sections of the DoCmd but it just opens the form to the first entry. I'm fairly confident i'm not applying the filter/where clause correctly by using the incorrect syntax.

View 3 Replies View Related

Hide Command Button If Criteria Is Met?

Feb 4, 2005

Hi Guys. I want to Display a command button on a form dependent on the value of another control.

I have a Main Form: frmSalesOrders and a Sub Form frmSalesOrderDetails

I have a command cmdSpecialInvoice on frmSalesOrders.
On Form Load I hide this.

However if the user selects a MakeID from frmSalesOrderDetails with an ID of 61 I want the button to be visible.

I attempted to put this on the After Update Event of control 'MakeID'


If Forms!frmSalesOrderDetails!MakeID = 61 Then
Forms!frmSalesOrders!cmdSpecialInvoice.Visible = True
End If


I got an error stating could not find frmSalesOrderDetails?

That aside is it possible to do what I want?

Note:
MakeId is a combo. Takes its value from qryMakes if tht makes a difference.

Any ideas?
Cheers,
Phil.

View 6 Replies View Related

Forms :: Multiple Criteria In DLookup Command

Nov 15, 2013

I have a form with combo box named "TEXT17" i have an another text box named(Basic Pay). How can I use dlookup creteria with two condition

EMPID= text17 value and
month in lbl9 value="mar"

I also paste my code here but show error

Me.Text43 = DLookup("Basicpay", "Salaryall", "[EMPID]='" & Me.Text17 & "'" And "[Month]='" & "Mar")

View 6 Replies View Related

Help With Openform

Oct 19, 2005

I am trying to open a second form that is linked to the first form but i cant get it to work it keeps coming up with a syntex error

DoCmd.OpenForm "frmstudentperformancepoorprevious", , , popUPID = '" & Me.StudentPerformanceID & "'"

Any ideas?? trying everything to get this form to link and everything is failing even the wizard which works if you just enter the record... but if you go back to it later and use the button it brings up a blank record

View 13 Replies View Related

OpenForm

Jan 22, 2006

Hello,

Im very new with access, and require a little help

what im trying to do is the following

I require a open form button, i can do this with no problems...

When the form opens, i need it to lookup a REP_ID value in a table to see if any records exist for the REP, if there is, i need it to display the form, with details.

If there are no details for REP_ID in the table, i require the form to open just blank, so input can be added!

How do i do this? Do i do it via code, macro?

Any help would be greatly appreciated

View 1 Replies View Related

OpenForm Filter

Nov 25, 2005

Hi All,

I have a problem and I can't figure out what I am doing wrong, I have a code:

Private Sub Button4_Click()
Dim compl As String
compl = name1
DoCmd.OpenForm "form1", , , "Field1=" & name1
DoCmd.Close acForm, "form2"
End Sub

Now everytime I click on this button and I enter a value in textbox(name1) it askes for a parameter! This is the only field in the form (form1) where I have this problem by the way. With the other fields it doesn't ask for a parameter. Does anyone know what might be the problem?

View 2 Replies View Related

OpenForm Filter

May 21, 2006

Access 2003
I'm sure this is incredibly simple, but I can't make it work, and can't find an answer in the forums.

:confused: I want to use OPENFORM to open a form showing only the records where a CHECKBOX is checked. I've tried using a query, using WHERE, using the OpenArgs, etc., but I'm clearly not formatting it right.

so... elements are:
btnActiveJobs (command button)
frmJob (form with job records)
chkActive (check box on frmJob that shows whether job is active)

The underlying Jobs table sets the Active field to yes/no

I'd like to do all this in the event code for the button, but I do also have a query (Query1) that produces the right jobs. Including this as the FilterName criterion has no effect.

View 4 Replies View Related

DoCmd.OpenForm Arguments

Jan 24, 2005

Hi,

I have got an unbound access form, and in this form I gather 2 dates (i.e. through txt fields). Once I have these fields, I want to open a form that has one list box on it. This list box should populate based on the dates that I gather on the previous form. How do I specify this through the DoCmd.OpenForm arguments? Currently I have the following code, but it is not working:

Form 1:
Dim whereClause As String
whereClause = "SELECT * FROM qryInvoice WHERE tblInv.InvDate Between #" & txtStartDate & "# And #" & txtEndDate & "#" & ";"
DoCmd.OpenForm "frmInvoiceFax", acNormal, , , , , whereClause

Form 2:
public Sub Form_Load(args As String)
MsgBox args
lstInvoice.Rowsource = whereClause
End Sub

I know I am not doing it the right way (because it is not working), but I can't actually find how to do it. Help!

View 2 Replies View Related

DoCmd.OpenForm Troubles

Mar 18, 2005

I have a form(frmGetRecord) with a subform(frmSubGetRecord). frmGetRecord has a combobox cmbCNO to choose the patient. The subform displays the admit date and discharge date(if there is one). There can be multiple admit/Discharge dates for a patient. Based on the values from fields on the form and subform, I would like to open a data entry form(frmEvents) for the particular record. I have a command button to run the code to open frmEvents. I am having trouble with the criteria in the where condition.

stLinkCriteria = "[CNo]=" & "'" & Me![cmbCNO] & "'" _
And "[AdmitDate]=" & "#" & Forms![frmGetRecord]![frmSubGetRecord].Form![AdmitDate] & "#" _
And "[DischDate]=" & "#" & Forms![frmGetRecord]![frmSubGetRecord].Form![DischDate] & "#"


DoCmd.OpenForm stDocName, , stQryName, stLinkCriteria, acFormEdit

When I click on the command button I get a type mismatch error. If I test the StLinkCriteria separately, the DoCmd.OpenForm will work for just the CNO field or just the AdmitDate field. If I test those two strings together, I get the type mismatch. Also, DischDate could be blank and I'm not sure how to add an IIf statement to the string to test for that in the where statement. I'm also not sure how to make sure it picks the correct record if there is more than one admit date. I would like to select the desired date record, then have the command button open the appropriate record.

Hope this makes sense. Thanks for any help.

View 10 Replies View Related

The OpenForm Action Was Cancelled

Aug 23, 2005

I am trying to open a form with a specified record using double clicked event (list box) but access is throwin "The OpenForm action was cancelled". My code is:

Private Sub lstSchool_DblClick(Cancel As Integer)
Dim test As String
Dim thisForm As String

thisForm = "frmRegister"
test = "ID = " & "'" & lstSchool & "'"

DoCmd.OpenForm thisForm, , , test

End Sub

ID is a AutoNumber field. I have used this code in my other dbz and it works fine ....

View 12 Replies View Related







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