Modules & VBA :: Change Query Criteria And Run It From A Form
Oct 22, 2013
I have small data base with many tables, one of them a table for equipment wit related details, as below fields.
1-EqipmentID
2-SerialNO
3-Model
4-Coustmer
6-City
I created one normal method query of ACCESS "QueryEQ" on that table , but every time I want to change any criteria I have to edit manual direct to query design .
What I need to do now is to create a form with one combo box showing all "model" and after select any mode I have to press command bottom to change the criteria of the model of the existing query "QueryEQ" and run it and show the result in a subform in same form.
View Replies
ADVERTISEMENT
Feb 14, 2006
I have built a database with only my department in mind that tracks three types of documents; Which works fine. Now some of my fellow department heads desire to use what I have built.
This is not a problem as their data structure is the same. The difficulty lies in changing the 30 queries. In the queries I hard coded my department number in the receiver (department field) criteria.
Additionally, my HQ is requesting me to perform some analysis on the other department's data. So other than manually changing the department number each time; Can I use a form or report to modify these 30 queries? I attempted to use a combo box but it would not hold the value when I closed the form.
I saw this thread today, http://www.access-programmers.co.uk/forums/showthread.php?t=102036 , but am not sure it will do what I want.
Suggestions welcomed.
Gunner...:confused:
View 8 Replies
View Related
May 30, 2014
I have a form where customer data is entered. one field serves as a criteria for a query. If, for example, A is selected in the form the query uses the criteria "A" in the X field f query. However, if B is selected in the form, the query uses the criteria "B" in Y field of query.
I can easily do this by setting up two queries and having criteria A run in X field of query and similarly for B. However, it seems as if there should be a way for me to have one query and simply use the criteria in a different field.So, is there VBA that will update a given query's SQL to use one criteria in one case and another critiera in another case?
View 1 Replies
View Related
Jan 26, 2014
I'm working on a report called Open Orders and when the database loads, it takes you to a Navigation Form. You make some selections mostly from combo boxes, then click run report which runs a query then launches the report.
I want the user to be able to click a check box called "Ready Only". If the checkbox = True, then I would like the field "Ready Pieces" in the query to have the criteria ">0". If the checkbox = false, I want that field to show all values (*).
I have no problem setting the criteria of a query field to equal that of a combo box value (Warehouse Like ([Forms]![Process Form]![Warehouse] & "*") but have problems when the criteria isn't the exact same as the value of the control.
Things I have tried to no avail: Putting a Iif statement in the query criteria: gives an error that criteria is too complex Creating an invisible text box whose value is determined by the checkbox to ">0" or "" then basing the Ready Pieces criteria equal to this....doesn't work Trying to use the DoCmd.RunSQL with my SQL code that changes via VBA when the checkbox is changed...Get an error and the SQL doesn't run
View 2 Replies
View Related
May 29, 2014
I have a form where varying layers of information can be entered. In some cases, a user may know all information or only part. If i tie the query to the form, each field has to be populated for it to work. how do i set the query up to effectively use the information available and not require all information?
I have tried setting VBA code as follows:
If Me.Combo1 = Null Then Me.Combo1 = "*"
However, when i do this, it updates the form field with an asterick/wild card but does not include in query. yet, when i update the form fields with data (not asterisk/wild card) the query runs.
View 13 Replies
View Related
Aug 9, 2005
I have a database consisting of one table with 5 columns, called Name, Zip Code, Model, Serial #, and Reference #
I made a form with 5 text boxes (generically named Field 1 through 5), and then I built a query. I put all five of the table's fields in the query. In the criteria fields of those five columns, I have
Criteria: Like "*" & [Forms]![Search_form]![Field1] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field2] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field3] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field4] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field5] & "*"
However, here in lies the problem:
Some of the records don't have a reference field included, and others don't have serial numbers, and etc. Basically, some records have blank fields.
So to find all of "John"s records, we would put John in Field1, and then run the query.
The first criteria is now looking for *John*, which is correct (it might find Jack John, Johnny, John John, or anything else with j o h n in it).
The rest of the criteria are now looking for **.
However, what about the records with blank fields?
John ; _____ ; SDMS71 ; 1231234 ; REF9001 will NOT make it into the query, because that blank field for some reason doesn't meet the ** wildcard!
how can I accomodate this "blank field" problem? I want those records with blank fields to still be included in the query, because they still belong to "John", and that's what i wanted to search for! :(
View 8 Replies
View Related
Jan 18, 2006
I have a database with 20 queries designed specifically for my department (SAM). A friend would like to use my database i.e. queries, reports, etc., but her department is named different (PAM).
The the field names in the each of our imported 'data' is the same, EXCEPT, each record in her department data field has "PAM" mine "SAM". However, her department is not part of my data import nor mine of hers nor will they ever be.
How can I, except manually, change every instance of "SAM" to "PAM" in the 20 queries?
Thanks
Gunner...:confused:
View 2 Replies
View Related
Jul 6, 2006
Hi,
The query for my form has a boolean field with ' like "No" ' in the criteria. I want users to be able to use a command button to change this to ' like "Yes" ' then requery the form.
Basically, what I want to do is toggle between current records ("No" in the boolean) and Archived Records ("Yes" in the boolean).
Any ideas?
Regards,
View 2 Replies
View Related
Jun 24, 2005
Hello, I am trying to change some query critera with a script.
Basically I have a drop down list which I select and once selected it requeries a subform. I would like to change one of my query parameters, specifically the criteria in a column.
To change query "MyQuery" column "Model" criteria, I've tried:
Code:!Queries!MyQuery!Model.Criteria = "Ultra5"
but that doesn't work. Is that even close to being right?
Thanks in advance!
View 3 Replies
View Related
Jan 13, 2007
Hi there,
I have a search form with a text box (unassigned at the moment). The search I want to conduct, filters the product number by the users search string with the use of * as a wildcard option.
I also have a Query with all the product numbers listed, no criteria set. How can I link the the search string in the search form to the query so when the search button is pressed, it will filter the results.
Any help appreciated.
View 2 Replies
View Related
Mar 26, 2008
I have managed to build a macro that will run a query I have in Access but what I would like to do is be able to change some of the query criteria using the macro script or any other way possible.
Is this possible?
Thanks
View 1 Replies
View Related
Apr 5, 2013
I use expression on the query field, but after I run it, the field change its format from number to text, how to reformat on the query criteria to change the text to integer format?
View 8 Replies
View Related
Aug 14, 2015
Is it possible to run a query and have the background color of the query field be highlighted if a certain criteria is met?
I'd like to use the expresion builder if possible.
For example if the values in field A and field B do not match, then field C should be a yellow background.
If this is possible, how is it done?
View 1 Replies
View Related
May 21, 2014
I have an access 2007 database connect to sql server 2008.I am running a pass though query to search between two dates (this query has been fine for years)
If I now run any search using parameters from 26th March 2014 to date - the query takes 10+ minutes to run.If I then change the date to 25th March 2014 to date - it runs in a nano second.I have not changed the back tables and I have not changed the format the data is saved in.
View 2 Replies
View Related
Feb 26, 2015
I'm working with a form someone else created and am adding a delete button. I've managed to add the button and get some code working which will delete a record from a table. The problem I'm having is that after the delete operation occurs all fields in the form, and the corresponding items below the form, show #Deleted in all fields. How can I force a refresh using code?
View 11 Replies
View Related
Mar 2, 2015
I am trying to change form backcolor with a cmd button via VBA.
View 6 Replies
View Related
Dec 12, 2013
I want to change certain records of a query or table.Here I tried to change this in a query. Something is missing in my code.
Code:
Public Sub TNS_QUERY()
Dim strSQL As String
Dim x As Double
Dim qdf As QueryDef
strSQL = "SELECT TEST_TNS.[TestID],TEST_TNS.[Division],TEST_TNS.[Customer_Split],SUM([TOTAL_NET_SALES]) as [TNS] " & _
" FROM TEST_TNS " & _
" GROUP BY TEST_TNS.TestID,TEST_TNS.[Division],TEST_TNS.[Customer_Split]"
[code]....
View 7 Replies
View Related
Jan 25, 2015
I have a main form which does nothing except filter subforms through a cbo.
On the main form are two subforms. One shows top line data, the other shows a breakdown of the top line data, and are linked by an unbound textbox (it's how it works, and does so perfectly)
I'm running an append query to duplicate a record in the second form using vba/sql BUT... need to have one of the fields values changed based on a field on the parent table.
If Forms!frmmain!frmPost.Form.RecordsetClone.RecordCo unt > 0 Then
strSql = "INSERT INTO [tblposts] ( TopLineID, AccountID, TransDate, Cat, SubCat, Debit, Credit ) " & _
"SELECT " & lngID & " As NewID, AccountID, TransDate, Cat, SubCat, Credit, Debit " & _
"FROM [tblposts] WHERE TopLineID = " & Me.TopLineID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If
Credit and Debits are reversed as I want one to zero out the other.
In regards to the AccountID, I've tried allsorts and it's just not working... to the point I'm almost giving up and finding an alternative.
Whats the "Correct" syntax to attach a "WHERE" statement to the highlighted [AccountID]'s (which needs to be the value on AccountID on the Parent table)
View 1 Replies
View Related
Mar 5, 2014
I have a form (Form4) which has a a list box (list11) that allows multi select. When I select on the records I need I hit a button that opens a form up with the selected records. This form is a continuous form. I have an unbound combo box (Combo55). Its values come from a specific table. I want to hit a button and change the field "Assigned" on all the records showing to the value that I selected in Combo55.
The problem is it only changes the value of the currently selected record. How Do I get it to change the value of all records that are showing?
I am only testing this idea with default field names. Before I implement the database I have to get a working model and present it. So I am building this and naming at the moment isnt important.
View 4 Replies
View Related
Oct 23, 2013
How to Change focus to another form and show only certain records...
View 1 Replies
View Related
Sep 26, 2014
I currently use code in a module and code on each form in the on_load event to change the icon of the form.. the code i use is as follows..
in a module:
Code:
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
Private Const WM_SETICON = &H80
Private Const IMAGE_ICON = 1
Private Const LR_LOADFROMFILE = &H10
Private Const SM_CXSMICON As Long = 49
Private Const SM_CYSMICON As Long = 50
[Code] ....
And on each form on load:
Code:
SetFormIcon Me.hWnd, "k: est directoryhsicon.ico"
What I am wondering is would it be possible to store the .ico file within the DB file itself (i know access can store bitmaps) and reference the .ico in the form load event code?
Overtime the db file will probably move to its own dedicated storage so using a direct reference to the file wont work..
I have tried the following but get an error (it tries to reference the .ico file as to being in the root directory of the db file)
Code:
SetFormIcon Me.hWnd, Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "hsicon.ico"
View 2 Replies
View Related
Jun 25, 2014
I am trying to build a function that will create a dynamic query for a chart on a Subreport.I am not exactly sure I am going about this the right way, but I need the user to be able to change selected fields for use in the query. I have a form with 3 combobox controls for selecting options to change the SQL statement. So far my code only deals with one of these comboboxes for simplicity. There is a button to call my function. Currently, the function is setting hidden text box values based on the combo controls, but I'm not sure if this is redundant.
I am using this as my guide for building the sql, but I am having trouble picking up the values in my text boxes for use in the SQL. [URL] .....
Code:
Option Compare Database
Option Explicit
[code]...
how do I get a value from an unbound textbox on an unbound form into a string to use as sql? The value in the textbox is a number.
View 3 Replies
View Related
Jan 1, 2014
I tried this code to change the label to display the full name of the state on the form. Is there a shorter way than putting 49 more states after the if?
[CODE]Private Sub cmdStates_Click()
DoCmd.OpenForm "frmChurchesAll"
DoCmd.ApplyFilter "qryFindState"
lblTxtSt = txtState
If txtState = "FL" Then
lblTxtSt = "Florida"
End If
lblTxtSt.BackColor = 15658720
lblTxtSt.Visible = True
lblStatesof.Visible = True
lbAllChurches.Visible = False
End Sub /CODE]
D7
View 8 Replies
View Related
Sep 23, 2013
Had a combobox so the user could choose between two data entry formats.They don't want the combobox, they want the user to enter data into one or the other textbox - and that choice to trigger the format.Two groups of text box - group 1 has a single text box - group 2 has three text box. When the user selects a text box and types the first character into it. This triggers locking out the other group choice and enables the <Validate and Edit> command button.
If the user backs out (deletes) the text in a text box.It basically makes both groups available again and it disables the <Validate and Edit> command button.Basically, if the text box Change event counts a character, it changes the text box Tag to "Bust". Then it calls a common routine that checks both text boxes.The choice won't take place if the textbox gets focus. It changes if a text box has 1 or more character typed in.
Code:
Sub WhosOnFirst()
' two groups of Required field - if one textbox in a group has a character entered first
' that group becomes the Format - enables the <Validate and Accept> button that will take the next setp
[code]...
View 2 Replies
View Related
Feb 25, 2014
I have been trying to create a login form that allows the user to change his/her temporary password logging it to the proper table along with timestamp and who done it info.But, after spending the morning trying to find the proper syntax I am flummoxed.
I can get everything to work accept the update of the fields. I can get the command to work (writes to the location) but it does the pop-up what is the parameter thing when it works. I have all the information just need to get it in so the command recognizes it.
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE lut_TeamList SET Pass = txt_Password.value WHERE TeamListID = Me.cbo_UserName.Value"
DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedBy = Me.cbo_UserName.Value WHERE TeamListID = Me.cbo_UserName.Value"
DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedWhen = Now() WHERE TeamListID = Me.cbo_UserName.Value
DoCmd.SetWarnings True
View 5 Replies
View Related
Apr 16, 2015
I have a continuous form with a text field that says "Select". There are two other fields, one of which is Brand. When the Brand Combo box has nothing in it I want the text box to appear (instructing the user to select a Brand) But once the user selects a Brand and goes to the next records, I would like the "Select" in only that record to become not visible. I tried conditional formatting but can't apply that to an unbound control ( or at least it is grayed out when I select the text box) and any other possible solutions I have found changes all of the selects - not only the one in the changed record.
View 7 Replies
View Related
