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?
I have a question on the Control Box X Button which is right on the top right hand corner of Access database. I'd like to customise it in a way that if user by accident, click it, it'd ask the user if they want to quit or not. If yes, then close the database or else, leave everything as they are.
I have successfully performed enabling and disabling the X button. However, i'd still like to be able to let it enabled while having the feature as i described above.
I was wondering if anyone might be able to give me some advice.
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?
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
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.
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?
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?
I 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 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.
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"
I have a query that uses the input from a form as criteria, which is then used in a report. The form input is a drop down based on another table. This is a sales pipeline report, and the list is a list of sales people. The report works perfect for all sales people except one. When I run it for the one, I get the following error:
"This expression is is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".
I DO NOT get the same error when running the query by itself - so assuming there is something in the report causing this. I do have some sum formulas in the report.
Again, no other salespersons selected cause this error -- so I am assuming there is something in the dataset for this person that is causing the error.
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID UNION SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet) Set db = wrkJet.OpenDatabase("DW", _ dbDriverNoPrompt, True, _ "ODBC;DATABASE=DW;DSN=DW2") 'Set rs1 = db.OpenRecordset(strSQL)
All I'm trying to do is a make table (table name to overwrite: "TBL_5_7_RFCs") query via VBA so I can loop through a set of VARs in a table. Here is my code:
Code:
Public Function test_sql() Dim Conn As ADODB.Connection Dim RS As New ADODB.Recordset Dim MySQL As String Set Conn = CurrentProject.Connection RS.ActiveConnection = Conn
[code]...
I'll add the loop and array once I get this working but I'm stuck.
in my form, which exists of cbo's and text fields, I want to put in new data in order to update my source table. In the menu properties, which is related to my form, I chose the right options to update this table, etc. Here is my problem; when I go to the form display and making some attempts to put in my data, the following message appears in my status-bar: "Can't update this Record-set object."
I just wanna know how I can configure my form in a way that I shall be able to put in my data.
I have an command button, when once selected brings up a inputbox to enter a date is it possible to :
1 - add an Calender to the Inputbox to be able enale the user to select a date instaed of typing it in. I am able to add a date picker calender to a date text field, however i do not know or if it is possible to do this with a input box 2- Once the inputbox appears a message is displayed "Do you wish to fill a cylinder for works order number:-0003102647000001
However i need to split the two numbers 003102647 and 000001. It should say "Do you wish to fill a cylinder for Works Order Number 003102647 and For Line Number 000001". How would i amend the following code to do just that, the coding i am using is
My Code: InputBox("Do You Wish To Fill A Cylinder For Works Order Number:-" & [Works Order Number] & [Line Number], "Please Enter/Scan Cylinder Serial Number")
3- Also as the inputbox is asking for a cylinder number, the user has to therefore type an appropriate cylinder number to be filled, at the moment the individual can type in any cylinder number, but i would like the cylinder number entered to be validated against the cylinder master table, which is a list of all the cylinders avialable, how is this possible, would it be a select statment.
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?
I am looking for the vba to have a query cycle through using records from another query as the parameter run and export for each of the records on the second query.
Table 1 has information with all clients query 1 has x, y, z being pulled > client used as parameter query 2 has the list of clients
Currently I have the coding to run the query and export however I am not sure how to get it to repeat for each client.
Code: Private Sub CmdInternalReports_Click() Dim xlApp As Excel.Application Dim rstDetails As DAO.Recordset Dim strTab As String Dim strDir As String Set xlApp = Nothing Set rstDetails = Nothing
[code]...
There can be upto 70 different clients or as little as 2 depending on the day. So the Query I have takes a list of the clients that receive the report and runs it against the imported table to only give the ones with data day.
PHP Code:
tblClientReport.Distro
Is where I have the report via client name linked the the distro list that should be pulled.
[Enter oner1, such as m20] Or [Enter oner2, such as m20] Or [Enter oner3, such as m20] Or [Enter oner4, such as m20]
Basically, it pops up one window after another as you input your info.' Then after the four pop ups it will go and retrieve the needed information. It works great for me....
Is there a way for me to take this query script and if I leave one pop up blank it would just go and find what I put in without having to press "enter" for the additional inputs that I did not want... It's a pain when I increase this to 10 pop ups and one need one...
Example :
Lets say I only want to input two and then go find the information. right now I have to continue closing the pop ups until I have cycled through all four and then it will go. I want to eliminate the additional ones.
I'm not a genius at this so please make it as easy as you can if you have any ideas....
I have an InputBox date box created in a query, How would i change the title of the input box from "Enter parameter value" to "Works order Import selection". iS IT POSSIBLE
I am trying to make user input forms for each of the tables in my current database. I have made the forms, but I am required to have the forms come up blank first (so they can create a new record), but allow the user to edit existing records too. How do I do this?
i got a input query problem ! If input a Product model LU20XT-1V25DM , the report should list all the parts which need to assemble this model , and the problem is there are tables in this database , Finding LU in table 1 , 20 in table 2, Xin table 3, DM in table 10, something like that . so input LU20XT-1V25DM , THE Result should like this : No CODE DESCRIPTION SPEC QTY
