I have a database that has several tables, each table has account numbers in
them. I also have queries set for each table to bring in desired
information. Is it possible to set up a form so the user can input an
account number and depending on which table the account number is in, that
query will run?
Ok this might be basic stuff i'm a bit dumb
I have a query showing lots of records of equipment that is in different rooms. I would like to have a form where the user can bash in a room number and all the records from the query that have that room number in it pop up.
Please help the dumb
I have a database with the following information:
FTA # Date Attended Last name First name assigned person......
Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.
Is there an easy way to type in the date that I want the query to use as the criteria?
Thanks
:o
I am looking to find out if I can build a "on the fly" pass-through query based on user predefined data.
Basically I am linking to a massive database. Access link to DB2 table. In order to make the system useable for the users they need to edit the query before it's ran. If not the query over the network takes around 5 minutes every time it's accessed and options such as filters and sorting are chosen. So if a user sorts one column and filters on another the query has now ran 3 times. Once to open, once to filter, and once to sort. That just took 15 minutes.
So If there is a way to build macro or form that asks them for specific information first and then modify's the query so that it only gets ran once that would be fantastic.
Example:Table has 7 fields/columns. Usually they will do 3 things. Pull back data from either a month or quarter. Then filter a column by it's content. Then sort by date of another date/timestamp column.
I have a database table in which I'm trying to pull sales data and generate sales reports from. The problem I face is that the sales data is recorded into a table with this structure:
Year | Customer | Sales_Month_1 | Sales_Month_2 | Sales_Month_3
Rather than having a single field "Month" in which I could set criteria or prompt the user to select a month to derive sales data from, I need to write a query that through user input (or through some code within the query) the right field will be selected. An example of this would be generating a report for the 1st month of the year, obviously.
How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:
Code:
Dim NETWORKBOX As String
NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE")
sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;"
DoCmd.RunSQL sql
If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.
hi everybody, im have a database with table called "project". there are many column in this table. my user want to export this table to Excel, but only some of column, with particular order ( depend on him) to analyze in Excel.
he asked me to build a form with a list box, drop box,somthing like this, so he can choose what column to export in what order.
i try to make a query like this: " Select Forms!UserInput.combobox1.value , Forms!UserInput.combobox2.value,etc, From Project" but it wont work.
Dou you have any idea.
thanks in advance
I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).
I cannot seem to define a form based on these relationships to input the data.
I have a TABLE with the following data:
- EMPID
- LASTNAME
I have a form with the folliwing data:
- text box for user to enter EMPID, txtEMPID
- LASTNAME
I would like to create a button to initiate a query to do the following:
- once the user enters a EMPID in the form, it will search in the TABLE under the EMPID field...
- if the user enters an EMPID that is in the TABLE...display "Y"
- if the user enters an EMPID that is NOT in the table...display "N"
- a error message box needs to pop up if "Y" to alert the user that "the EMPID already exists and that duplicate entries are not allowed"
One of my main questions is how do I run a query based on the users input and search a table? would the following work in the query?
I need to be able to query the service dates for the bill. So I can query 1509 for the .billYYMM and .readdate and it will give me the TO Date. How do I in the same query tell the query to subtract 1 from the BillYYMM that I just entered and give me the read date for that field in that query? So Query 1 MBRHISTDETL.BILLMOYR and in the criteria box use [Enter the Bill Month Year YYMM you want to search for] will allow me to run the query at any time for any bill month year that I enter. So another field in that query is MBRHISTDETL.READDATE. So to get the FROM date and the TO date i need the following MRBHISTDETL.BILLMOYR (whatever I enter as input) and MBRHISTDETL.READDATE that corresponds and then I need [MRBHISTDETL.BILLMOYR]-1 and MBRHISTDETL.READDATE. The first gives me the TO date and the 2nd gives me the FROM date?
View 5 Replies View RelatedI have a form which uses a parameter based query to present an individual senior doctor with a list of names for of individual juniors to provide an assessment report on. When the first form opens the user enters their RespondentID.
Once senior has decided which names to comment on I have another form which has the questions to be answered which is opened by clicking a button on the first form.
How can I pass a parameter from the first form to the second so that only the records relevant to that senior doctor are displayed? The underlying table has 60 senior doctors and 20 junior doctors. The senior doctor is identified by the field RespondentID in the first form. I've tried putting a WhereClause in the FormOpen command but I still get a dialogue box asking for the parameter RespondentID when the second form is opened.
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 RelatedHello,
I need to create a field in an input form that is simply the concatenation of two other text fields. I have tried all sorts of things, but when I look at the data in the table that field.
I have a field called ID that I want to be created like this:
=Format([UniqueID],"00000") & "-" & [Mosque]
This works well in my output fields, but does not work the same way on the input form. It needs to be based on the currently input values from the current record. Anyone have any ideas?
Thanks in advance,
--Robert
Hi all,
stuck on this, not sure if it can be resolved....
I have a parameter query for which the parameter is based on a form field entry (i wanted to avoid the dialog box popping up).
No problems with this....however, I want to create a chart based on the same query however, i get the following error message because it does not recognise the query parameter.
Error Message: The Microsoft Jet database engine does not recognise '[Forms]![frmChooseDTDate]!DateOccured' as a valid field name or expression.
Can this be resolved?
Help most appreciated!
regards to all
K
Hi,
Is it possible to customise the input form for a query. At present I have to type in a start and end date manually, it would be far easier to use a popup calendar to select dates. I also have another query that requires you to type in a username, it would be preferential to have a drop down list to select a name from.
Are either of these things possible? or will I need to create a form that calls the query and passes the relevant data?
Many thanks
nit
Hi
This is simple im sure but i am a thicky
How do you allow a user to enter a value in a form; have access set that inputted value to a criteria in a query? Then ill have a button to run the query which i can do
thanks
thicky ste
Hey Folks,
I have a form that has two things on it:
A button to open a query
A list box with names in it
How do I get the query so that it will select all records where the name field matches the chosen name from the list box?
I am not a programmer and I don't know VBA/VB.
I am using this as a workaround to avoid VBA functions (since I don't know them).
I can't seem to find a simple? solution to this.
I have a query that does a radial search in decimal degrees. I have created an unbound form as a dialogue box that converts degrees-minutes-seconds to decimal degrees and displays the results in a calculated text box. What I want to do is have the query take the results in the calculated text box as its parameters (along with a third parameter- distance) without prompting the user. How do I get the query to take its results from the fields on the form? I have tried Like [Forms]![frmName]![SearchValue], but I can't seem to make it work.
Based on information from a earlier thread.... I created a Union query that pulls information from multiple tables and fields.
SELECT AG_B_R1 as Num FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R2 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R3 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R4 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R1 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R2 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R3 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R4 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R1 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R2 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R3 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL SELECT AG_e_R4 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll];
And then I created another query to get the STDEV of the above query
SELECT StDev([Num]) AS StDev
FROM Q_cals_ag_bme_STDEV_Union;
The result will be on a subform on my main page. How do I get my form to input the [roll] automatically and requery the subform, showing my result.
Thanks
I have this select query.
SELECT DISTINCTROW L160.Date, Avg(L160.Zinc) AS [Avg Of Zinc], Min(L160.Zinc) AS [Min Of Zinc], Max(L160.Zinc) AS [Max Of Zinc], Count(L160.Zinc) AS [Count of Zinc]
FROM L160
GROUP BY L160.Date
HAVING (((L160.Date)=[Forms]![L-160quarterfrm].[Date]));
How do I build a form that would ask the user to input a range of dates for the criteria?
Thanks! :o)
I maintain a grade book application that uses many queries whose results are determined by "school year". Most of these are reports and I have a combo box on the Print form for that allows the user to select the school year. The criteria field of the several queries derived by school year is:
[Forms]![Main Navigation]![Print Form]![SchoolYear]
SchoolYear being the combo box control.
They work fine.
I have now added a function to export data to Excel. This is done in a VBA module and I am using a query to select data for the record set I use to write to Excel:
Set objRst = Application.CurrentDb.OpenRecordset(strQueryName)
When I hard code the school year in the query criteria field (i.e. "2012-2013") the process works fine, but if I revert the query to point to the print form field as above, I get an empty recordset.
The Excel export is executed from a control on the Print Form, so the form is open and the combo has data showing, just as it is when a report is run whose data is derived from a query.
When I execute the query from the VBA module, the query is not getting the school year selected on the Print form passed to it properly.
I have a form set up and would like to have field update to a table when a policy number is input into the form. The fields are extracted from a linked table and are not viewed on the form but need to be written to a table to create a report. I'm confused on the sets to take to handle this. I think i need to use the "onchange" property and set up a macro that runs a query but how does the query write to the table?
View 1 Replies View RelatedI have a table which holds information on audits that have been carried out on staff member's actions. The 'Supervisor' field is populated via a combo box which is linked to a separate table (tblSupervisors).
I am now trying to build a query to allow me to extract all audits that have been carried out on a specific supervisor - rather than the criteria to be [Enter Supervisor Name] and allowing text entry, I thought it would be better to have form that pops up with a combo box that is used to select the supervisor (from tblSupervisors);
So far:
- Form "Supervisor_Select" is created, and has a combo box that looks up from tblSupervisors
- Macros as specified in the instructions are created (Open Dialog, Close Dialog, OK and Cancel)
- Query is done, all bar the criteria expression on the desired field.
- Module is created as described in the instructions, and is called "Supervisor_Select"
I have tried putting the following in the criteria;
[Forms]![Supervisor_Select]![cboSupervisor], however I think I am missing the bit where the query opens the "Supervisor_Select" form?? Will this only work from a button where the on click event runs the 'Open Dialog' macro and then runs the query?
I have two linked tables as follows:
Table 1
Main Service ID (primary key)
Main Service
Table 2
Sub Service ID (primary key)
Sub Service
Main Service ID (foreign key)
one-to-many relationship
I would like to query the tables, so you search on the Sub Service and it returns all of the Main Services it come under. I would like this query to run off an Input Box that will allow me to type in the Sub Service or a word.
I have managed to get the Input Box, by putting the following code into the criteria :InputBox («prompt», «title», «default», «xpos», «ypos»). But when I type something in the search fails. I have chopped and changed the fields I search on, but can't get it quite right.
Can anyone help me on what fields to choose and what I need to set in the criteria to get the search running properly from the input box?
Also is there any way to program Access or VB to return the results in a message box e.g. **** is a Sub Service of ***** - listing all main services it appears under?
Any help is most appreciated,
Thanks,
Luke
I am having trouble using a form as input for a query. The form uses multi select list boxes, with then intention being that if I select multiple fields then only records which include those fields will show (not only fields that contain those and no others).
I also have successfully worked up some keyword searches that I would like to have run on the same query. So say I want to search for two values in my list box, and it also needs to include keyword X... how would I run these all together, or is it possible?The form is "EVR Search Form"..The query is "EVR Query - Trending Filters" and I've also made a copy to test on, "Copy EVR Query - Trending Filters"
basically i have a textbox called
'mytext'
when my user types t1 in that textbox, and tabs to the next field, i want the value LHR - T1 to be placed inside 'mytext'
how can i do this?