FORM Critera Problem
Mar 15, 2005
Hello,
I first want to thank the owners of this forum for keeping it up and the bright users who post replues I have gotten a lot of useful info thus far. I have a small issue I've been working on for a few days now. I just can't seem to find the correct syntax to accomplish it. Any I have a table which has customer contact info as well as sales numbers. I have a form which my users can search contact info. However I've been unable to allow the users to search for sales dollar volume. Basically the form sends the critera to the query. I've tried getting the between to work but I'm doing something wrong. Below is my VB code attached to the search button. The red bold line is the problem. If I just take the line out then open the query manually and type between "0" and "1000" it works fine. But if I type my form txt box names I get a message saying type mismatch in critera expressiong. So I think it's my syntax.
Option Compare Database
Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
strSQL = "SELECT tblCONSOLIDATED.ACCOUNT1, tblCONSOLIDATED.COMPANY_NAME, tblCONSOLIDATED.ADDRESS1, tblCONSOLIDATED.ADDRESS2, tblCONSOLIDATED.CITY, tblCONSOLIDATED.STATE, tblCONSOLIDATED.ZIP, tblCONSOLIDATED.CONTACT_NAME, tblCONSOLIDATED.TELEPHONE, tblCONSOLIDATED.FAX, tblCONSOLIDATED.CURRENT_YTD, tblCONSOLIDATED.PRIOR_YTD, tblCONSOLIDATED.PRIOR_TOTAL, tblCONSOLIDATED.YEAR2_TOTAL, tblCONSOLIDATED.YEAR3_TOTAL, tblCONSOLIDATED.YEAR4_TOTAL " & _
"FROM tblCONSOLIDATED"
strWhere = "WHERE"
strOrder = "ORDER BY tblCONSOLIDATED.COMPANY_NAME;"
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.txtCSONME) Then '<--If the textbox txtCSONME contains no data THEN do nothing
strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
If Not IsNull(Me.txtCSOSLD) Then
strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND"
End If
If Not IsNull(Me.txtCSOARN) Then
strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND"
End If
If Not IsNull(Me.txtCSOAD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.ADDRESS1) Like '*" & Me.txtCSOAD1 & "*' AND"
End If
If Not IsNull(Me.txtCSOSSM) Then
strWhere = strWhere & " (tblCONSOLIDATED.ADDRESS2) Like '*" & Me.txtCSOSSM & "*' AND"
End If
If Not IsNull(Me.txtCSOCTY) Then
strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND"
End If
If Not IsNull(Me.txtCSOST) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' AND"
End If
If Not IsNull(Me.txtCSOZIP) Then
strWhere = strWhere & " (tblCONSOLIDATED.ZIP) Like '*" & Me.txtCSOZIP & "*' AND"
End If
If Not IsNull(Me.txtSLCYYD Or Me.txtSLCYYD2) Then
strWhere = strWhere & " (tblCONSOLIDATED.CURRENT_YTD) BETWEEN '*" & Me.txtSLCYYD And Me.txtSLCYYD2 & "*' AND"
End If
'If Not IsNull(Me.txtSLLYYD) Then
'strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_YTD) Like '*" & Me.txtSLLYYD & "*' AND"
'End If
'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qrySALESDATA")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
'Open the Query
DoCmd.OpenQuery "qrySALESDATA", acViewNormal
End Sub
View Replies
ADVERTISEMENT
Oct 19, 2004
I am trying to create a form that searches the criteria entered and opens the query filter by the info entered.
Example:
Form opens, you type the last name parameter of client
Last Name: Smith
Click a button named Search- opens the query and filters it by the criteria (last name). You see the query with all records with the last name Smith. I may want to add the first name in the form along with the last name. Does anyone no how to do this?
thanks
Biz
View 10 Replies
View Related
Oct 26, 2004
Hi! I have a query setup that feeds off of three combo boxes on a form. It is setup to allow a user to search and calculate revenue for a selected time period by company, then department, then page. If all three or just company are selected, there are no issues running the data. However, if only company and department is selected I receive an 'ODBC--Call Failed' message. The issues seems to be stemming from the department field as I can link the department table to the main table and the query will run without error. The problem with that bandaid is that the query takes more than 15 minutes to run that way as opposed to about 2 minutes to pull more data at the company level.
Any suggestions on why this is happening or a fix that can be put in place? Any help is greatly appreciated.
I can provide more details if needed. Thanks!
View 6 Replies
View Related
Mar 17, 2008
Hi everyone
I have a database that I built myself and use on a daily bases for keeping track of customers and there service records for a small sized plumbing and drainage firm. The database has a function that is designed to automatically search for upcoming boiler services that are due or over due which works fine, However we use acronym's to describe some routine jobs such as B/S (Boiler Service) or H/R (Heating repairs) which speeds up data entry. The problem is that Access refuses to search for the acronym's, I have tried basic text search critera's which brings up full words fine such as "Smith".
Is there a way of searching for the acronym's like B/S?
Thanks in advance for any help.
View 4 Replies
View Related
Apr 7, 2006
Hi,
I am trying to set the criteria in a query, referencing a control on a form. The control is a combo box and I am trying to use the second column to filter data in the query.
EG. Forms!FromName!ComboBox.Column(1)
However the query doesn't accept it. I get an error saying:
Undefined Function 'Forms!FromName!ComboBox.Column' in expression.
How do I reference the control properly please.
Thanks
Red
View 2 Replies
View Related
Nov 15, 2007
im tryin to get this query to get data from current weeks inputs. ive googled and tried everything with no luck so far. so here i am. can someone tell me what im doing wrong? keep in mind i dont know what im doing.....
heres what i put for criteria under my date column.
Between Date() And Date()-7
doesnt work.... anybody? thanks in advance...
View 10 Replies
View Related
Nov 16, 2004
Please advise, how do I set up a form for this:
Based on one particular report style, have multi select criteria to produce user selected criteria
based on selections from a. Interest, b. State, c. Type
Report 1. sorts on Interest, 2. sorts by State, 3. sorts by Type ... and each report includes selections of the other 3 selections plus basic data common to all.
Plus, the Interests have a table where the main interest data is stored in a ContactInterest table, while the specific interest selections, per contact, are stored in the Contact table as Interest_1 through Interest_20.
In the Report, when it is sorted by Interest, while I have a concantated output for 5 of the selections, they also, want to sort alphabetically by the interest selections (which changes per Contact).
There is already a form for printing reports that are more static, and I wanted to add these selections on that form, perhaps as a subform, to allow printing of the reports, mentioned, and associated mail labels to the targeted addresses per group.
Any help, direction, guidance, or redirection would really help and is MUCH appreciated. I've tried many options, and as a newbie, have not yet found a solutions- or solutions.
Thank you so much. (time is of the essence and my skills are small so far.)
__________________
View 1 Replies
View Related
Mar 26, 2013
Basically I have a Customer Form, which I have a New party button on it,this button opens up the party form to a new party, what I would like it to do is open up a new party but make the new party for the customer I had selected in the previous form.I have tried the GoTo macro's but cannot seem to get it to work.
I am thinking on clicking the button it will need to get the Customer ID, and then open the party form, create new party, and paste in the Customer ID, which then updates the Name - Date - Address - Company Fields.
View 5 Replies
View Related
Apr 14, 2015
I have been tasked with creating a tool to analyse mobile phone bill data and present the analysis, and our recommendation, to a customers. Being new to Access (other than basic tuition) this has been a slow uphill task, which is finally nearing completion, however there is a problem which I have not yet been able to overcome.
The requirement is for the DB to open first on a splash screen (lets call it Form A) with fancy picture where our customer is selected from a combo box, the customer is then telephoned, a linked computer screen is established and our staff then click "Go" to proceed to a second form (Form B) showing an account overview and more details.
The problem I have is when "Go" is clicked, the second form loads via on click event, and even populates the correct customer in its combo box. Unfortunately that is as far as it gets - the combo does not look up the information. The customer needs to be selected again for the subforms and subreports to load with the customer overview. To clarify, form B just sits there blank until the customer is re-selcted from the combo box in form B.
View 9 Replies
View Related
Aug 27, 2014
On the form: User enters first name, then last name. Upon updating the last name field, I would like another form to pop-up and display all the people with that same first name and last name that the user just entered.
On the pop-up form: All of the matching first names/last names are listed with a button control beside each record that says 'Select'. The user clicks the select button beside the record he/she wants. This pop-up form closes and all of the data from this selected record is now showing on the original form.
So far, I have a query/form that pops up only showing the matching first/last names. I'm having a hard time getting my original form to auto-populate with that record that the user selects on the pop-up.
(Also my main form is actually a sub-sub form - so in my trials I could've been massacring my syntax trying to point to it.)
View 5 Replies
View Related
Aug 10, 2013
I am trying to create a user form with sub form using the form wizard. I have only 2 tables, Employee (main table) and Vacation (subform table). I pick the fields from Employee Table then fields fro Vacation, but the wizard treat the Vacation as main form and Employee as sub form.
View 4 Replies
View Related
May 7, 2005
I have a fault form which the user enters details of a fault. When the fault form is submitted the data is saved in the relevant table and the form is cleared so that the user can enter the next fault. On the fault form i have a subform which displays the faults previously entered by the user. I want the user to be able to click on an entry in the subform and open the orignal fault form and see the data that had been entered. How can i create this functionality??
View 9 Replies
View Related
Jul 24, 2013
how to make my form controls change size / position as my form is resized / loaded on a computer with a different resolution. Several of the tutorials out there suggest putting code on the "on resize" property of the form. When I looked at the Northwind database to try to mimic their code however, it looks like they must be doing something different as there is no on resize code under the form properties and I was unable to find the code they do use.
View 4 Replies
View Related
Aug 19, 2014
I have a bound form which is from tblEmployee, I'd like to have a dataset below (like a splitform but not a split as they have limits) so when i search in the box it gives me say all the smiths - i select for example david smith and it displays his information in the form objects above so they can be edited?
View 1 Replies
View Related
Jun 6, 2013
I have one table containing name of restaurant with its address etc. Then i created another table to list out the restaurant workers names and details. Just as an example,
Table:Restaurant
Restaurant name
Address line 1
Address line 2
Restaurant #
Website
Table:StaffContact
Staff Role
Name
speciality
email
phone
I have the main form that has all the restaurant details only. And i have another form containing the Staff information. Please note the two table have a relation and it works well.
Now to make it user friendly(basically easier for the lazy ones), I dragged the staff contact form on to my main form and displayed it as a datasheet(basically a sub form).
Now, my boss does not want users to add/delete on this sub form(datasheet). So,he wants me to create buttons to open new record of staff for each restaurant(new form)
My issue is with opening a new record to enter a new person to the staff list and give them a role as well in form view.The new form has
So i ran a Macro, with open form with Where condition
Code:
[Staffcontact]![Rest Name]=[Forms]![MainForm]![RestaurantName]
But, it does not work .
View 2 Replies
View Related
Mar 20, 2014
What I would like to do is create a list box that will only display the information from another form that is related to the current form.So I have a form call Equipment Catalog and that form is related to Equipment features 1 to M relationship and the Equipment Features is related to a Features form M to 1.
So what I want to do is display all the related equipment features in a listbox that is related to the current PK of that form.So if there is only one feature on one form the list box will only display that one item however is there is 6 features on another it will display all 6.I have been trying SQL and Queries but I still can't get it to work.
View 1 Replies
View Related
Jul 29, 2013
Can I look up and verify data on a "second" form based on a selected record from first (still open) form.
I am trying to allow users to select a User Name from a combo box list and then open "Change Password" form when they select "Change Password" for that selected user name.
My problem is that I can't figure out how to associate and verify the data tied to the user name selected on the previous (Login) form ( I am trying to validate the old password tied to that selected record).
I have the first login form created, and it's working just fine. I also have the change password form created (and it's displaying the user name selected from the first form using:
Code:
Private Sub Form_Load()
With Forms![frmLogin]![cboUserName]
Me.txtPwdChgUserID = .Column(2, .ListIndex)
End With
EndSub
I also have the code written to validate and confirm old password, new password and validate new password (when the save button is clicked). I have yet to update the password with the new password (still trying to figure that out).
Attached zip file has screen shots of the two forms.
View 3 Replies
View Related
Jun 24, 2013
When I right click a row on the data sheet side of a split form an select "New Record" I want the curser to go to the first field on the single record side. I've placed this in the OnCurrent but it did no good.
Code:
If Me.NewRecord Then
Me!Descrfiption.SetFocus.
End If
Any way to set the focus to the single form Side of a split form?
View 4 Replies
View Related
Feb 3, 2014
The user will be creating a new project that contains a bunch of releases. The releases have standard names which are stored in a table tbl_ReleaseNames It should be noted that the list of names is not static.
The user selects which of the releases pertains to their project and then based on their selections, new records would be created in tbl_RFP_Release and then a subsequent form would open where it would display each of these newly created releases where they could enter additional information. I thought of creating an unbound checkbox associated with each of the standard names, and then checking to see if the checkbox was checked and then creating the new records followed by opening up the new form.
View 6 Replies
View Related
Jun 13, 2006
I am very new at Microsoft Access. I have two forms, A and B, that are filled out by two different users. Form A gets filled out first than form B gets filled out. Each has a name box as well as a birthdate box and a few other similar text fields. However, each form also has a few distinctive fields. I was wondering how I could input the common information in Form A to Form B so the user of Form B does not have to spend time retyping the name and birthdates again. In other words, I want to synchronize the similar records between the two forms. I would like the values to appear in a table as well. I would greatly appreciate it if someone could help me with the visual basic code. Thank You.
View 1 Replies
View Related
Sep 11, 2014
I have a button on a form which calls a module to show a calendar form. The user picks date and time, and saves it. The calendar form closes and adds the date and time to a text box on the initial form.I want to save the record so that the underlying table/query is updated, but it's not working. The record is not saved until the initial form is closed.Here's the form vba...
Code:
Option Compare Database
Option Explicit
Private blnFlag As Boolean, blnSaveIt As Boolean
Private Sub btnDelete_Click()
On Error GoTo Err_btnDelete_Click
[code]...
View 3 Replies
View Related
Dec 27, 2014
When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.
Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.
View 2 Replies
View Related
Jul 23, 2013
I want to use buttons on a form to change the sort order on a continuous form. In the buttons click event I am using a public function (named Sort_1) to change the sort order. The first element of the event call is the name of a generic query (named Sort_1_Query1) and the query field to sort (LAST_NAME OR FRIST_NAME, depending on the button.)This is the Click Statement.
=Sort_1("Sort_1_Query1","LAST_NAME")
This is the Public Function
Public Function Sort_1(SortName As String, FieldName1 As String)
DoCmd.ApplyFilter SortName, FieldName1 & "between 'A' and 'Z'"
End Function
I think the problem is in the use of quotation marks or trying to pass the query field name to the Do Command or the use of an ampersand.
View 4 Replies
View Related
Sep 25, 2013
I've built a PostgreSQL database for some ecology data and am trying to use MS Access 2010 to make a front-end for it.
I've created a form to display site data; it includes such fields as site ID, site name, county, state, landscape, etc. (There are more, but if I can get the idea down with a couple of these, I should be good to handle the rest). One can click through the form and see 14 pages of site details, which is fine because there are 14 sites.
What I want to do now is make a search form in which one can enter values for site ID, site name, county, etc, and somehow get the pages of the display form that match those values. It's not terribly important to me how that output looks - whether it's a list of matches that allows for clicking on a match and showing just one page of the display form, or whether it's a filtered subset of the display form pages that one still clicks through to view them all.
if there is code to write, where do I type it? Do I start my search form by making a form with the search fields as values, or is there some other way? How do I make those search fields into search boxes rather than display boxes?
View 14 Replies
View Related
Jul 30, 2014
I have a continuous form in which I put a command button for each record called "detail". I would like to click on the "detail" button and make it open another form containing all (and only) the info on this record.
At first I refused to use an "id" to link both forms, but finally I added the "id" in the table... however still does not work.
continuous form: "04 - GASTOS_BUSQUEDA"
id field on continuous form: "Gastid"
pop-up (details) form: "GASTOS_EDITAR"
id on pop-up (details) form: "editar_id"
This is what I have tried on the "click" properties of the "details" button field (called "btn_editgs"):
1)
DoCmd.OpenForm "GASTOS_EDITAR", acNormal, , "[editar_id] = " & Me.Gastid
2)
DoCmd.OpenForm "GASTOS_EDITAR", , , "[editar_id]=" & Me.Gastid
3)
stLinkCriteria = "[editar_id]=" & Me![Gastid]
DoCmd.OpenForm "GASTOS_EDITAR", , , stLinkCriteria
4)
Private Sub btn_editgs_Click()
On Error GoTo btn_editgs_Click_Err
Dim strWhere As String
strWhere = "[editar_id] = " & Me.Gastid
DoCmd.OpenForm "GASTOS_EDITAR", , , strWhere
btn_editgs_Click_Exit:
Exit Sub
btn_editgs_Click_Err:
MsgBox Error$
Resume btn_editgs_Click_Exit
End Sub
View 9 Replies
View Related
Jun 21, 2013
I have modal form - frmZlecenia
I would like to copy one control, then close this form, open concrete form and pass value to control.
My code is
Private Sub Menu_Click()
DoCmd.OpenForm "frmZleceniaMarzenaNawigacjaPD"
Forms!frmZleceniaMarzenaNawigacjaPD.Form!Imie = Me.Imie
Forms!frmZleceniaMarzenaNawigacjaPD.Form!Imie.SetFocus
DoCmd.Close acForm, Me.name
End Sub
The problem is, still opens the previous form, not form "frmZlecenia"
e.g If I open form x then from this form I open my modal form "frmZlecenia" and then I will click "menu" button - now form "x" is open :/, but should be "frmZleceniaMarzenaNawigacjaPD"
There is some way to open concrete form from modal form?
View 1 Replies
View Related
