Modules & VBA :: Dynamic Query Change Field Selections
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.
Say for example, in two fields within my dropdown for Camera Status in the Maintenance table...
FIT FOR PURPOSE - Availability Checkbox = Yes
What expression do I need in expression builder, so that Selecting UNDER REPAIR or FAULTY/DAMAGED instead makes the checkbox availability turn to unchecked automatically within a form, without the need to manually change the checkbox?
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)
I have got an unbound multiple list box called List44 (Row Source: query based on table tblAircraftCategory, Multi Select - Extended) that needs to be passing parameters to my main query called AircraftSearch2. The multiple choice list box have the following fields:
1. Piston 2. Turbo Prop 3. Entry Level Jet 4. Light Jet 5. Super Light Jet 6. Midsize Jet 7. Super Midsize Jet 8. Heavy Jet 9. Ultra Long Range 10. Helicopter 11. Air Ambulance 12. Cargo 13. Vip Airliner 14. Airliner
The user will use the form for selecting search criteria (the form is called SearchForm2 and has 5 combo boxes, 3 text boxes and one multiple choice list box).
I'm very new to access and need to modify (or coding a separate module) my query to include my multiple choice list box in my query?
I'm still learning Access 2010 and having issues getting my buttons to work. I'm working on a simple address database.edit/save button. On form load, my fields are locked and my button will read "edit". After clicking, my fields are unlocked, my search features are locked, and my button reads "save" just how I need it to. The issue happens when I try and save the field edits, lock fields, return search features, and get button to read "edit" again. I know I need to add some code into what I already have, but I'm running into a wall as I have tried many options to get it to work. Here is the code for this button:
Code: Private Function Lockdown() 'locks controls at load Dim tb As Control Dim cb As Control Dim subf As Control
[code]...
Second issue is with my report button. I have not been able to get this to work once. I have done many searches on single record reports, and have found the same code every time. I added that code into my database, but can't seem to get it to work. In my database there are two address (shipping and work location) which I would like to print out together. I have the work location on the main form and the shipping on a subform. There are and upwards of 150 locations I will have in my database, Here is the code I'm working with:
Code: Private Sub cmdrptadd_Click() Dim strReportName As String Dim strCriteria As String
I am trying to copy a record as new record in vba in access so i make a button for the user so that they can copy a record each time and change a certain field if they wanted. How would i do that.
I have a form called frmNotInvoicedSearch and on that form i have an list box called listCompanyClient populated with our client's names.I then have a command button called cmdOK that brings up a search results form called frmNotInvoicedSearchResults.
Currently i can select one of the records and when i click ok it brings up the search results for that selected record.Is there a way that i can hold in the ctrl and select multiple options and the search results report as such?
I found a snippet of code online that I'm trying to use in an asset tagging database I'm developing, but I'm struggling to get it working. To start, I have a multi-column ComboBox that displays information in this format:
When you select an asset to be assigned to an employee, the ComboBox displays only the asset number (e.g., 1001). While that information is pertinent to our I.T. group, when Human Resources goes to collect an asset from an employee, they don't want to be taking cases off of phones or tablets to verify they have the correct asset number. They want to see the person has an iPhone5s and an iPad2 that they have to collect. So, what I'm trying to accomplish in my VBA is to have access read all the asset numbers and provide the descriptions of those items in another field.
The code I have so far is:
Dim ctl As ComboBox Dim varItm As Variant, str As String str = "" Set ctl = Me.Combo217 For Each varItm In ctl.ItemsSelected str = ctl.Column(2, varItm) & "," Next varItm Me.Text207.Value = str
Since this is code is something I found online, I'm not sure why I can't get it to work. I've never worked with the Variant declaration, but I think this may be where the code is breaking because whenever I remove the "For Each...Next", the code correctly assigns the value of column 2 of my very first row to my text box (Text207). Everything I've seen looks as though I don't have to declare varItm because it's function is to represent the rows that are checkmarked for ctl.ItemsSelected.
I'm using Access 2010. I'm passing a string into the OpenArgs of my report - works fine. In the report there are 3 rich text fields which may contain the text I passed in, and if so I want to change the color of that text to red so it stands out.
The value passed to the report changes so I'll need to use VBA in the detail's format section to check each of the 3 rich text fields.
I am trying to get my VBA to filter the subform, as currently it does nothing! I have copied it from a source on another forum and at the end of the VBA it originally opened up the query.
However I am trying to tailor this query so that instead of it opening the query I can have a datasheet on the form that displays the filtered records..
There are 3 multiselect listboxes
Here is my code
' This code uses ADO and ADOX and is suitable for Access 2000 (and later). ' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security. Private Sub cmdOK_Click() Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim qry As ADOX.View
I currently have a combo box that filters form based on specialist (an employee using the system), and this works fine:
Private Sub FilterSpecialist_AfterUpdate() Me.Filter = "SpecialistAssigned = '" & Me.FilterSpecialist & "'" Me.FilterOn = True End Sub
When Specialist Assigned name is selected, it only shows their cases (records of the form that are assigned to them).
However, now I'm trying to also filter based on only the selected Specialists Open cases. I have a combo box at the bottom of the form that saves whether the Case is Open or Closed, (creatively) called CaseOpenClosed:
Private Sub FilterSpecialist_AfterUpdate() Me.Filter = ("SpecialistAssigned = '" & Me.FilterSpecialist & "'" And CaseOpenClosed = "Open") Me.FilterOn = True End Sub
I've tried this based on me searching the forum but it doesnt filter at all anymore, and no error either. I've tried other ways but get errors. What is wrong with this?
I have a combo box in a sub form with three values, rate1, rate2, and rate3. I have another three fields in the sub form rate1, rate2, and rate3. The rates are dependent on the item. When I select the rate from the combo box I want a cost field to update on change to the relevant rate. I tried this to no avail:
I am new to MS Access, and am not sure if what I want to do is even possible. It is my understanding that comparing subsequent cells within a field in a database cannot be done- so I thought I'd see if there is a way to go around it.
I have a dataset for pedestrian activity, with over 3 million rows and 40 columns - too big for excel to handle.
I need to sort the entire dataset by 2 fields, following which I need to search down the field containing my pedestrian ids (numbered 1, 2, 3... till approx 10000), and when my ped id changes from one to the next, I need to check the value in a field showing the ped location, and if that matches with the ped's previous location, I need to copy out a cell corresponding to the previous cell's time stamp. If it doesn't match, I need to copy out another time stamp from another field.
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.
Hi, I’m trying to develop a form for coding data from historiocal documents into a dataset. The form is used to avoid errors when converting ‚questions’ in the codebook to the codes for later statistical analysis. Some questions allow for multiple anwers – but need to be stored to one field in the data-table, separated by semicolon. Ideally I would have a list-box with the questions and write the codes into one field after selection. Another difficulty is, that later the data need to be read from the table and the selections should be visible in the form (if the table contains „1;6“ items 1 and 6 in the listbox should be highlighted).
I’d be grateful for tips or links to relevant sources. Thanks!
I have a training matrix that lists employee names and certifications on various operations. The objective is to choose an operation and run a query to display everyone who is certified on that op. There are additional variables.
Code: Name EMP ID OP1 OP2 OP3 OP4 OP5 ----------------------------------------------------------------------------- John Doe 526261 C C C Bob Doe 555622 C C C Sheila Doe 066600 C C C
Okay that looks about right for the data itself. The listbox has all the ops, you choose an op and hit a button and it goes and finds everyone who has a 'C' in that op column and pulls their record.
I have a listbox which is populated by a query from a table.
I also have a sub which allows users to quickly select multiple items from the listbox (based on a pre-defined criteria) rather than scrolling through and selecting them manually / individually
The sub works fine but when it is finished, the listbox is automatically scrolled to the bottom (or, rather, to the last selected item)
I would like to have the listbox scroll back to the very top once all of the appropriate items have been selected.
(I could loop through the listbox items in reverse - or 'bottom-up' - in the code but that would only have the desired effect if the topmost item was selected. I want it scrolled to the top regardless of whether the topmost item is selected or not).
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]"
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.
Lets say I have a table with the following columns:
Chainname Storenumber Storename Revenue in value (sold goods in $) Revenue in volume (sold goods in pieces or SKUs) Month
I create an Append query where I use these fields from my table:
Chainname Storenumber Storename Revenue in value (sold goods in $) Month
What happends afterwards doesn't matter, but I what if I would like to change a field in my Append query to this:
Chainname Storenumber Storename Revenue in volume (sold goods in pieces/SKUs) Month
I can do that manually by opening the Append query and then change it in the field...But can I change it without actually opening the query. Lets say using a Form were I choose if I wanna use:
Is the a way of changing the fields selected in a query by a form.
I have a form showing how many people need to do each module. Each textbox is populuted by a count from the table.
The idea is simply to avoid having 26 queries based on each module. Is there a way to have the field name changed via the form.
I have used the on dbl click event to pass the field name to a hidden field on the record. I just need to pass the data in the hidden field to the select query.
Data Example: 123456, T43 R2 W5M, S, 6 123457, T43 R1 W5M, SE, 18, SW, 17 123456, T43 R1 W5M, E, 19, E, 30, SW, 29, E, 31, NE, 18
What I have done so far is to create a Union query to create a new record with the file and land description repeating for each row where there is quarter and section data with the following code:
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec1 AS Section, [LLD Import Table].Qtr1 AS Quarter FROM [LLD Import Table] WHERE ((([LLD Import Table].Sec1) Is Not Null)); UNION SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec2 AS Section, [LLD Import Table].Qtr2 AS Quarter FROM [LLD Import Table] WHERE ((([LLD Import Table].Sec2) Is Not Null)); UNION SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec3 AS Section, [LLD Import Table].Qtr3 AS Quarter FROM [LLD Import Table] WHERE ((([LLD Import Table].Sec3) Is Not Null)); UNION SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec4 AS Section, [LLD Import Table].Qtr4 AS Quarter FROM [LLD Import Table] WHERE ((([LLD Import Table].Sec4) Is Not Null)); UNION SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec5 AS Section, [LLD Import Table].Qtr5 AS Quarter FROM [LLD Import Table] WHERE ((([LLD Import Table].Sec5) Is Not Null));
To give this result:
FileNumber, LandDescription, Quarter, Section 123456, T 43 R 1 W5M, NE, 18 123456, T 43 R 1 W5M, E, 19 123456, T 43 R 1 W5M, SW, 29 123456, T 43 R 1 W5M, E, 30 123456, T 43 R 1 W5M, E, 31 123456, T 43 R 2 W5M, S, 6 123457, T 43 R 1 W5M, SW, 17 123457, T 43 R 1 W5M, SE, 18
However the number of Quarters and Sections under a file changes, so next time I my table could have fields up to Qtr20 and Sec20 What I’d like to do is to create a function that will automatically change the # behind the field names (to replace the * in the example below) so that I don’t have to rewrite the Union Query each time. I’ve seen some code examples that can change the value, but don’t quite understand them enough to write one.
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec* AS Section, [LLD Import Table].Qtr* AS Quarter FROM [LLD Import Table] WHERE ((([LLD Import Table].Sec*) Is Not Null));
Right now one of the tables in my query has multiple years across the top (as the fields), 2010, 2011, 2012, 2013. Each year contains different rates that get applied to labor hours.
I would like to know if there is a way to have a form change the field in my query. I have attached a word document showing print screens of what I mean.
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?
