I am involved in a project that is using a SQL back end. I am having issues with "unable to save" messages when I quit the application. I have noticed that if I close a form to look at something, on many occasions the select query behind the form or subform is open. Is there a way to close all forms, subforms, and queries with VBA without having to identify the exact names, when I use either
DoCmd.Quit
or
Application.Quit?
Two forms have matching fields (ContractID), and each form has different data displayed from different tables (SQL views, actually).
I want Access 2007 to stay on the same ContractID when the user switches to a different form.
I have a form with 7 List boxes linked to 7 Query's which in turn are linked to a table. Each list box if for a particular trade.
I am trying to select a person or persons from each List box and then have them sent to a report. I have Code to do one list box, but do not know how to link all boxes with code to a 'Open report' button.
The code I am using is as follows:-
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
[code]....
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
Hi
First timer question
After searching for Nemours combinations I am now asking for some help
I have tried combinations to get a expression to give me a list of bookings staying tonight
The fields in the table I use to store the Start & End date of the booking are below
[CheckInDate]
[CheckOutDate]
I have lost my way with Between And <= etc
A wee pointer would be appreciated
Thanks
I am creating an absenteeism database which has the following tables:
tblEmp - PK - auto number, EmpId, First Name, Last name
tblFunction - PK - FID (autonumber) has departments
tblTL - PK TLID (autonumber) has list of team leaders
tblRelated - pK - Id (auto), EmpId, TLID, FId as long integeres
tblCodes - CodeId (auto), list of absenteeism codes like late, mia, etc
EmpLeave - pk - autonumber, empid, codeid, hours, date of absenteeism
I have a Qry_Master which just joins all the information together as it gets updated on a monthly basis
Now, I am trying to create a form where the user has option to select one or more tls and one or more codes and when they hit the button, it should come up with all emps that have those codes and report to the team leader selected.
In my form, I have made both my list boxes as multiselect and i have Qry_frm that is a parameter query but when i run the button nothing happens and i cant seem to figure it out. I have attached the sample database to this thread.
I have a form which runs specific queries and opens a report using combo boxes, these work perfectly fine if I just try to run them in the form. I have put the form into a navigation form and if I try to run the same query I get parameter queries popping up instead of just running the query and opening the report.
View 2 Replies View RelatedI have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
Let's say I have a query on a form that returns results. Is it possible to double-click a record in the recordset and open a form.
I am currently doing this just fine from a listbox, but am wondering if it is possible from a query.
I have a database with lists clients across the UK. I have now been asked to provide options where users can select clients grouped by geopraphical area e.g say clients in Scotland.
I can of course do this but having numerous identical forms where the source queries have different parameters depending on the regions required.
The only problem with this is that I would need numerous forms and queries. Additionally, there are options on the form to navigate to linked forms which would all need to be unique.
What I would like to have options on my main (Switchboard Type) Introduction Form to select the region. The code on the relevent command button would include the parameter. I would therefore not require the additional forms.
The open form codes includes:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmClients"
DoCmd.OpenForm stDocName, , , stLinkCriteria
I feel i need something after "frmClients" such as qryClients.ClientArea = "Not Scotland". Various attempts to incorporate something has created errors.
Any quidance on code would be appreciated.
Thank you...Paul
I am running MS Access 2010 on Windows XP and my access skills are limited.
So I have a db with tables, forms and reports and would like to give my users the option of opening a filtered report (from a form).
The form (frmSelectStatus) and report (rptStatus) have been created and both open correctly by normal selection from the 'All Access Objects' side bar.
frmSelectStatus has a multi-select list box (lstStatus) and a command button (cmdStatus). When I click cmdStatus i get the correct report opening but it is all status records and not filtered by the selection made in lstStatus - and i know this is a result of not referencing 'lstStatus' in the code. i also have a query (qryStatus) which does not contain anything.
My 'on-click' code for cmdStatus is
Private Sub cmdSelect_Click()
DoCmd.OpenQuery "qryStatus", acViewNormal, acEdit
DoCmd.OpenReport "rptStatus", acViewReport
DoCmd.Close acQuery, "qryStatus"
End Sub
I know I need to reference 'lstStatus' but am not sure where or how to do that in the code.
i have this small code to import excel data into mdb file:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"datapub", "D:datapubcolumnar.xls", True, "datapubcolumnar!"
But I want to improve by call a dialog box for user to choose excel file.
Could anyone can write this code for me.
Thanks in advance.
I am attempting to create a Macro that will export the COMPLAINT table to an Excel Spreadsheet. However Excel does not open and I am prompted to select the Program I want to use. What am I doing wrong?
View 1 Replies View RelatedI have a text box in a form (unbound) and when I go to add text into it, the text carries over from one record to another but I want it to be exclusively for that record.
View 8 Replies View RelatedI have some code for a button on click event to open a form and select the record which corresponds to a value in an unbound text box. The code is:
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOpenPatientRecord"
[Code] ....
This works fine when I put in a 10 digit NHS number but opens a blank record when I enter a four digit or six character/digit PatientNumber. Both patient number and NHS number are text fields in the underlying table.
Im trying to work on an db in Access 2007 that was migrated from Access 2003 (in fact its been migrated several times starting from Access 97). It executes and runs with no problems in both versions.
The problem is when trying to open some queries (not all) - Access is unable to open the query in design mode and gives me this error :
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
However, as I cant open it - I cant check it. Im pretty sure none of the fields have invalid characters (they do have spaces) and Im not sure how long is too long....
when i open a form i want to open another frm at the same time. i have a main form with some buttons in it. when i click on a button and a form opens then i am not able to click on a button to open other forms from the main form.
View 2 Replies View RelatedI currently have a button that opens a report. the report pulls from a query that has parameters set to "fromdate" and "todate". instead of using dates and parameters that pop up as blank text boxes, I would like to click the button, have a form pop up with a combo box to select all of the options available (currently 23 options) and then click a button to make a report that only displays the record (1-23) selected. I do not need any time constraints because as the databases get updated with more records, there would be more than 23 options to choose from.
View 1 Replies View RelatedI've taken over a database written in Access '97 so it's quite old and I don't want to rewrite the whole thing. I have a query as the record source for a report like this:
Code:
SELECT TOP 1 ID1Child5.ID, ID1Child5.CODE1, ID1Child5.CODE2, ID1Child5.CHARGE, ID1Child5.DEFAULTS FROM ID1Child5 WHERE (((ID1Child5.ID)=[Forms]![ID1MainForm_0]![ID])) ORDER BY ID1Child5.DEFAULTS;
Which gives me the first value.
Now I need to reverse that and create a query that gives me all the other results. Basically everything except the top row.
I've been wracking my brain and scouring the net for information on how to do SELECT queries in Access and I can't figure it out or find correct information. Some idiot had a website that said to use DoCmd.RunSQL to do SELECT queries, but failed to mention that only ACTION queries can be used in that method, so I'm back at ground zero. What I want to do is this:
I want to be able to run a select query from a table, and store all the information in some sort of object so that I can access all the individual fields returned in the query and do things with them. Is there anyway to do what I'm asking for?
Please help with the code below.
I am trying to select a different query based on the selection in the [Product] Field. I have 4 different products and 3 different queries to run. Each product is specific to a particular query.
What I would like is:
When Product 3 is chosen "Query 1" is ran.
When Product 1 is chosen "Query 1" is ran.
When Product 2 is chosen "Query 2" is ran.
When Product 4 is chosen "Query 3" is ran.
As it is set up now it appears that when choosing Product 1 I open Query 2 and when choosing Product 2 I open Query 1, but it is the opposite of that. I get the correct query with this code by choosing Product 1 and the Query that opens is actually Query 1, I don't know what is going on here.
When I open Products 3 & 4 it always opens Query 2, no matter what Query I specify.
Please help me sort this out. I am not very familiar with case select. Thanks
Private Sub run_query_Click()
On Error GoTo Err_run_query_Click
Dim stDocName As String
stDocName = "Query 1" (Should be for Product 1 & 3)
stDoc1Name = "Query 2"(Should be for Product 2)
stDoc2Name = "Query 3" (Should be for Product 4)Select Case stProtocolName
Case [Product] = "Product 1": DoCmd.OpenQuery stDoc1Name, acNormal, acEdit
Case [Product] = "Product 2": DoCmd.OpenQuery stDocName, acNormal, acEdit
Case [Product] = "Product 3": DoCmd.OpenQuery stDoc1Name, acNormal, acEdit
Case [Product] = "Product 4": DoCmd.OpenQuery stDoc2Name, acNormal, acEdit
Exit_run_query_Click:
End Select
Exit Sub
Err_run_query_Click:
MsgBox Err.Description
How I might select all the records within a query except for the most recent one? I have an AutoNumber ID field and a date stamp to use , but I can't figure out how to exclude just the biggest record.I am trying to run an update query on the After Insert Event, so that when the user inserts a new record it will mark down similar older records as 'superceded'.
At the moment I can select all the records, and I can select just the largest one, but I can't seem to take one away form the other. Here is the SQL in it's current state:
Code:
UPDATE [Personnel-Qualifications] SET [Personnel-Qualifications].[Superceded?] = True
WHERE ((([Personnel-Qualifications].PersonnelID)=[Forms]![Personnel]![PersonnelID]) AND (([Personnel-Qualifications].QualificationID)=[Forms]![Personnel]![Child21]![qualificationID]));
I'm trying to do a select statement and put it in a variable which i can then output to a text box. How do I get the value into a variable? I can't seem to get my syntax right
This is what I currently have
Maxvalue = "SELECT MAX[Record Num]FROM Joblog"
are you able to specify the location of another database that you want to select data from?
For example: I want to do something like this:
SELECT [field1], [field2] from C:DesktopFolderMyOtherDatabase.mdbTableName
Is it possible to change this query to search for either a Name or by an employee ID number? The query runs a report on after update. Currently I can only enter the name not the employee ID
Code:
SELECT DISTINCTROW First(Inventory.Employee) AS FirstOfEmployee, Inventory.[Employee ID]
FROM Inventory GROUP BY Inventory.[Employee ID] ORDER BY First(Inventory.Employee);
I've done some searching, and haven't found any threads that match what I am attempting...
I have a database that tracks expenses that are incurred under different ongoing projects. I currently have a report based on a query that asks the user for the project number, and shows all the expense invoices that have not been paid for that project. This report is basically an internal invoice that gets assigned to a department, and so it is assigned an internal "invoice" number. Currently, the user types in this internal number before the report is generated and it is displayed on the report for printing purposes.
Here's the tricky part (for me anyways): I would like to be able to filter the invoice records by project number, and then append the user-entered internal invoice number to the applicable records. There is already a field in the table for this data, and it is currently being entered on individual records after the report is printed. My goal is to be able to do this all in one step.
Any help will be greatly appreciated... Thanks guys!