Is it possible to add a field (i.e., variable) to a query (or SQL programming) based on a form selection?For instance, if I use a form to allow an end-user to specify which fields they wish to include in the query (essentially creating a UI for the query builder), how would I go about creating the query/SQL or updating the query/SQL?
View RepliesI have a Customers table and an Orders table. My Customers table is a bit different than the norm because I couldn't figure out any other way to do this.
My client's customers are either businesses (companies) with a contact person, or individual customers. So, in the Customers table, I have the following fields (there are others but do not apply to my problem):
Customer ID
Company
Contact
Customer
So, if it is a company the data entry person would enter the company name and the full name (EX: Doe, John) in the contact field, but if it is an individual customer, then they would leave Company and Contact blank and enter just the Customer name (Ex: Doe, Jane).
So, in the Orders table, I have combo boxes for look ups for the fields Company and Customer.
That works okay, but I would really like for the Customer ID to post into a text box once the Company or Customer has been selected. In other words, the input person would enter the name, then it would show that Customer's ID #.
I run a charity pre-school and have invoices to send out. Some of the parents cant afford to pay the whole amount in one go, so we give them a payment plan. e.g. If an invoice is for a 6 week term we let them pay weekly. So I have a check box on my form to say "are you on a payment plan".
Then - I have an free form input box..."How many payments..." and the answer may for instance be '6'.
What I want to do now is dynamically create/display 6 date fields, to record what the agreed payment dates are.
but maybe the answer is 4, or 7, etc. So I need to create/display the correct number of payment date fields.
I have a subform which is a continuous form displaying a single combobox of trade names so there may be up to 4 or 5.i want to click on a button on the main form which will open a form based on a query that will display all personnel matching all or 1 of the trades in the subform?
View 2 Replies View RelatedI have a query that allows the user to put in a date range - this works.He also wants to limit what he prints.
For example at the moment he gets
OrderA rest of line 1 for orderA
OrderA rest of line 2 for orderA
OrderA rest of line 3 for orderA
OrderB rest of line 1 for orderB
OrderC rest of line 1 for orderC
OrderC rest of line 2 for orderC
OrderD rest of line 1 for orderD
.
.
.OrderZ etc
He only wants to say print lines for OrderA and OrderD. It's he's choice and not based on anything else already in the table.
I see that in the criteria on the design for the query it allows me to bbuild an expression and I can select a list box on the form such as
[Forms]![name of form where list box is]![name of list box].[EXPRESSION VALUES]
I don't know which expression value to choose. I have tried afterupdate and beforeupdate but none of the rest look like they are the ones I should be using.
I am currently working on an existing database (not created by me) which contains several queries. Each query relates to a particular product.
I am wanting to create a form which has a combo box so that a user can choose a particular product from the combox options and then click on the command button to run the correct query.
How to do this. I have created the form and the combox (together with the list of products). I just need to know what Event Procedure code I need to enter to programme it to look for the correct query and then run it.
For example.
I have the following queries
product1query
product2query
product3query
I have the following options in the combo box
product1
product2
product3
How do I get the command button to look at the product1 option in the combo box and then find and run the product1query.
I created 2 combobox that list specific table in a data base. I want to create an unmatched query based on the selection from those two combobox.
All field in those table are the same.
When creating a query I have set up a parameter query as instructed (in this case searching for a name) but when I run the query it returns the error 'this expression is typed incorrectly etc.'
I have worked out that this must be due to the fact that the names are not typed in by a front-end user but need to be chosen from a dropdown list (which i have stored in a separate table) - So, the query seems to fail based on that.
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
I'm using 2003 and have some VBA ability.
Hello all,
I have a temporary table with Yes/No fields (F101,F102,F103,....etc).
In another table i have a field called ProductCode with values (101,102,103,....etc)
Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
Thank you in advance.
Is it possible to have a form designate which FIELDS to include in a selection query? I know that I can determine criteria and such, but i want the user to be able to generate a report based on the query select a, b, c from blah, or if they wish, just select a, b from blah...
View 5 Replies View RelatedI'm building a customer form using a list box of summary customer info on the left side of the form (CustId and Names) and customer details on the right hand side. I want the details shown on the right to be those for the customer selected in the list box on the left. I'm having problems getting the details to update when a different record in the list box is selected or clicked.
I originally set the data source for the detail records to the base customer table but couldn't work out how to update them when the list box was clicked!
I then tried creating a query with the select clause conditional on the list box (CustId = Me.qryContactDetails) and set the data control source for the detail record to the query (=[qryContactDetails]![FirstName]). The query works as expected but the field in the form just shows '#Name?'.
I tried adding a field requery on the List31_Click() event using Me.[FieldName].ReQuery but that didn't seem to change anything
I have three tables with the following columns:
MoldReq
Mold_NoPart_NamePart_NoMaterialDateWork_Ord(Pri Key)
MoldLog
Log_IDMold_No(Pri Key)Part_NameLog_DateTime_InTime_OutHours
Molds
Mold_No(Pri Key)Part_NamePart_No
I am trying to build a form in the MoldReq section so when the user selects the mold number(combo box) it will auto populate the Part_Name and Part_No fields by pulling the data from the Molds table.
I have tried following this video where I create a change event:
Code:
Private Sub cboCombo31_Change()
Me.txtPart_Name.Value = Me.cboMold_No.Column(3)
Me.txtPart_No.Value = Me.cboMold_No.Column(4)
End Sub
I have a table containing the area, location and name of someone there is also a field contains a number from 0-10. I need to run a query that will count the number of times a number appears in this field and put this total in a field on its own.
this my table fields are as follows...
Area Location Name Number
And I would like the query to show....
Area Location Name 10's 9's 8's ect...
I want a help on this complicated issue:
On running a select query with "a*" of names in a table:
Results are : America, Argentina
Now I want this to dynamically create 2 text fileds in a form and fill America &".snp" in first and Argentina &".snp" in 2nd filed.
It is not regarding subform to display data of query result.
This I am going to use to add attchment for outlook email session as being discussed in my Email from access with attachment thread.
[URL] ....I am trying to dynamically search multiple fields from a combo box on a form that includes a subform. I am using code from the above referenced link within this forum.
It works without the "setFocus" for the Listbox except it jumps to a record after the very first character is entered instead of narrowing down as characters are typed. If I leave in the "setFocus" I get run-time error 2110.
I am making a parameter query that looks up quality data by lot number. For some of the lot numbers certain fields of data may be null. How can I omit these fields in the query if they are null?
OR automatically omit them when exporting them data to excel?
I have an excel data file that is updated monthly (and I am not able to have the source file format changed of course) that will be my linked table for Access 2007. The file reports monthly data and adds the new month to the right of the historic in a layout like this:
Tree | Orchard | Province | Jan % red | Jan % green | # Limbs | Feb % red | Feb % green... etc so each month it adds 3 new fields to the data.
I have queries (rightly or wrongly) that will calculate the number of apples that month and report on those higher than a certain percentage. I would like to do this without having to create a static table to append to each month as the new data file will always show the full YTD results anyway (and I don't like to store data in my database).
I have a query that counts the number of fields in the raw data file and am wondering if there is a way to have access only run the number of queries required based on the number of fields. i.e. if 11 fields then run queries 1 thru 5, if 14 run 1 thru 6 etc.
I've inherited a database that has a table with a large number of Yes/No fields. Apart from normalizing the database, I was wondering if the following was possible:
ContractTable has Yes/No Fields for rights granted for each order (television, dvd, internet etc).
Using a form, the user selects the specific right they'd like to run a report on - selecting this right from a drop-down menu, this is labelled "RightsCombo21" on the form.
When the button is clicked, the right selected is now the "Field" section of the query.
The SQL query right now looks like this (shows all rights sold for the territory):
WHERE ((([Contract Table].[Contract Cancelled])=False) AND (([Contract Table].[Contract End Date])>Now()) AND (([Contract Table].[Contract Type])="License Agreement") AND ((ContractIDAndCountry.CountryName)=[Forms]![TerritorySpecificRightsChooser]![TerritoryCombo7]));
I'd like to add something to this WHERE statement:
AND (([Contract Table].RightsCombo21)=True)
Not sure if it's a syntax issue, or I'm attempting something that isn't possible.
I have a table (ComponentT) of different device components a user can select from ( generators, gearboxes, etc.)
The table has 3 fields - ComponentName, ComponentType, ComponenentDetails
I have a query returning all the ComponentDetails values.
I then have a combobox that uses this query as a row source.
The user can select different components they want from the combobox. Once something is selected, it is added to a new table - UserSelectedComponentsT
As a device can have only one gearbox or one generator etc. I want a warning messagebox to be displayed if the user tries to select a generator from the combobox when one has been previously selected.
I am trying to create a query that will provide a field for each day of a month. However, I want the query to be able to work for any month that I want to run on based on a parameter. Basically I want this:
Day 1: Sum(IIF([ReleaseDate]=#[# of Month]/1/[# of Year]#,[GamesSold],0)
Day 2: Sum(IIF([ReleaseDate]=#[# of Month]/2/[# of Year]#,[GamesSold],0)
and so on for 31 fields.
This is not currently working.
I have the query below that return a table like:
PLOTNR; period,Value, ID, Basal_area/ha, Basal_area/ha, perc_BA_sp
What I want to is to add another field that rank the perc_BA_sp by PLOTNR descending (thus highest perc_BA_sp values rank one etc.)
Code:
SELECT[Q:INV1-Basal_area_plot-spp].PLOTNR,
1 AS period,
[Q:INV1-Basal_area_plot-spp].Value,
[Q:INV1-Basal_area_plot-spp].ID,
[Q:INV1-Basal_area_plot-spp].[Basal_area/ha],
[Q:INV1-Basal_area_plot].[Basal_area/ha],
([Q:INV1-Basal_area_plot-spp]![Basal_area/ha]/[Q:INV1-Basal_area_plot]![Basal_area/ha])*100 AS perc_BA_spFROM[Q:INV1-Basal_area_plot-spp]
[code]....
I have a form that runs off a query that displays further details of a record in a datasheet when you double click on a row.
The query itself has criteria that looks at the open form and selects the correct record.
My question is can you change the results of the query either using vba or a built in feature of access without having to use a separate query.
The selection criteria in the query is:
Forms]![Main]![Ordering-Supplier]![Ordering-Order List].[Form]![suppOrderID]
Basically I want to override the resulting data with another record when I run an event on the form.
I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies View RelatedI can't quite get my code right for populating one combo box based on the selection in another. I didn't create this Access database but I've been asked to add in a second combo box that is based on the selection of the first combo box.
My first combo box is for Rohe (the location of the Maori tribe) and the second is Iwi (the tribe name). So my first combo box (Rohe) has Dvic_ROHE written in the control source and its row source code is:
SELECT tblLookUpValues.DatabaseFieldValue
FROM tblLookUpValues INNER JOIN tblFormQuestionLookUp ON tblLookUpValues.LookUpID = tblFormQuestionLookUp.LookUpID
WHERE (((tblFormQuestionLookUp.FormID)=2) AND ((tblFormQuestionLookUp.QuestionNumber)=75))
ORDER BY tblLookUpValues.DisplayOrder;
The combo box for Rohe (tribe location) is populated by a table named tblLookUpValues. The second combo box for Iwi (tribe) has Dvic_Iwi in the control source. I created a table named tbl_Rohe_Iwi_lookup that contains ROHE (a list of tribe locations) and IWI (the tribes). There are 12 tribal locations and another 10-14 tribes in each location.
The form that the Rohe and Iwi combo boxes are located on is called frmDeceased. This is the code that I attempted to write so that the second combo box (Iwi) is populated with only the tribes that are in the Rohe (location) that was selected from the Rohe combo box. But when I run the query it comes back with no results.
SELECT distinct tbl_Rohe_Iwi_lookup.Rohe, tbl_Rohe_Iwi_lookup.Iwi
FROM tbl_Rohe_Iwi_lookup
WHERE (((tbl_Rohe_Iwi_lookup.Rohe)=[forms]![frmDeceased]![Dvic_Rohe])) UNION select distinct null, null
FROM tbl_Rohe_Iwi_lookup
ORDER BY tbl_Rohe_Iwi_lookup.Iwi;
I am trying to write a query that gets all the telephone hours from a worker done under supervision in a town (qry_svhoursbytown). I have a table that has the workers details, including town (Crisis_support_workers), then another table that has what hours each worker has done (Supervision_Hours). The town is selected from a combo box on the form (frmSearch), under the heading �total hours by location�.
I try and select Bathurst then press recalculate results and in the text box next to supervision hours, it says 66, but if you look in the Supervision_Hours table, there is only 11 hours for the workers that are in Bathurst.
In the query, I have the sum total of the hours field in the Supervision_Hours table, the Town field from the Crisis_support_workers table with the total selected as Where and in that criteria I have [forms]![frmSearch]![ComboTown], then I just have the town field displayed.
What do I need to change to get it to sum correctly?
I have attached the database below so people can take a look.