Please can someone tell me how to go about adding to the code below. Currently the user enters the date criteria in a form. This works great and the data is exported to excel. But I can't seem to figure out how to get the input from the user to also be exported to Cell A1 in excel. Example: if the user enters starting date and end date, that information should be placed in the A1field in excel. Thanks for your help.
Public Function ExportDataExcel()
Dim strFilePath As String
Dim strFileName As String
Dim strFileTemplate As String
Dim strMacroName As String
If (MsgBox("You are about to generate the LAR Monthly Report. Are you sure you wish to continue? You cannot cancel this procedure once started.", vbOKCancel) = vbCancel) Then
Exit Function
End If
'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Fill in the following with your files and path
strFilePath = "R:Call CenterCall Center DepartmentsMortgage DeptMortgage Statistics & Tracking"
strFileName = "Output.xls"
strFileTemplate = "Template.xls"
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
'This deletes the old file
Kill strFilePath & strFileName
'This recreates your file with the template
FileCopy strFilePath & strFileTemplate, strFilePath & strFileName
xl.ActiveWorkbook.Save
'The Application.Run will run the Macro(s) that you saved in your spreadsheet
xl.Application.Run "'" & strFileName & "'!" & strMacroName
xl.ActiveWorkbook.Save
'Uncomment/Comment these to close out the workbook
xl.ActiveWorkbook.Close
xl.Quit
DoCmd.Close acForm, "frmLar"
Set xl = Nothing
End Function
Private Function ExportData(strQuery As String, strSheet As String)
Dim intR As Integer
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef
'After you open that Object/Workbook, you refer to that workbook now as 'xl'. You will
'use it later, but now you have to access your queries through this code and to do so
'you need to use a recordset.
'strQuery is the name of the Query that you passed with the Function. You can also
'use an SQL string.
Set dbs = CurrentDb
'QueryDefs (0)
'QueryDefs ("name")
'QueryDefs![name]
'Set rs = CurrentDb.OpenRecordset(strQuery)
rs.MoveLast 'moves to the last record
rs.MoveFirst 'moves back to the first record
'You can use record count to make sure there are records in your Query/Recordset
If rs.RecordCount < 1 Then
'There are no records
MsgBox "There are no records for " & strQuery
Else
'There are 1 or more records. Now Select the sheet that you will be exporting to
xl.Sheets(strSheet).Select
'Now you need to loop through the records. 'intR' was dimmed at beginning of this
'function and will now use it to create a loop or 'For, Next'
'Starts with record 1 and gets the count of records in the recordset so it knows where
'to stop.
For intR = 1 To rs.RecordCount
'Now we need to export the recordset/query to the workbook/object we opened earlier.
'Remember 'rs' refers to the recordset & 'xl' refers to the workbook
'xl.cells(ROW,COLUMN).VALUE = rs.fields(INDEX).
'This is how you will fill in the value of a cell on the workbook. For the ROW you
'will want to add + 1 if you have Headings on your sheet. The INDEX for rs.fields
'refers to the columns of the recordset/query. The first column of the recordset
'starts with the index of zero.
When using parameters from one form to the next I normally hide the form and then reference the parameter textboxes in the next form to the hidden form. Is there a better way of doing this as I saw threads here mentioning passing a parameter. How do I do this? Thanks!
I have created a simple query that is used as the record source of one of my forms. I want to pass the query a different criterion each time the form is opened.
Any suggestion/example on who to do that will be very much appreciated.
Can Access handle paramater-passing without getting into the object model and adding a "Parameter" datatype to a query and calling DoCmd.RunSQL etc, and also without prompting the user?
Is something like this possible (as it is in SQL Server I believe):
SELECT * FROM some_query_that requires_a_parameter(param_value='china')
I am trying to avoid the annoying bit where Access Prompts you for a parameter with the little popup, and I'm also trying to avoid the little bit called "programming it with VBA".
I'd like to see if this can be done with only a SQL query in Access.
I am struggling with what seems like should be a straightforward task. Unexpectedly however it has become an infuriatingly difficult one (no doubt due to my complete novice status).
I have a very basic d/base (3 tables) that I have been searching using basic SQL queries. I want to create a simple 'search' form that produces the results of my various queries without the need to work in SQL. Enter one or more search criteria, hit ENTER, results presented in datasheet perhaps?
Thought this would have been easy but I've had no luck. Can you please explain how I can pass a parameter from a form to a query?
I have a report that pulls data from a crosstab query. The report works perfectly and prompts for a "StartDate" when it is run.I need a form with a date field that can be selected. Then a command button which when pressed opens the report with the selected date passed as the parameter.the code I have so far is in the on click event of the button:
I was hoping that this would pass the txt.startDate field on the form to the report's "StartDate" when it is opened, but it is still prompting for the parameter when the report loads.Should I be using openArgs rather than the where clause? Or do I need to configure something in the "on load" event of the report also?
I've a method **querylistboxitems** and i want to call this method in several click events, only difference is listbox,dropdown values change based on the event i call.
Code:
Public Sub querylistboxitems(lstbox As listbox, dropdown As ComboBox) Dim drpdwnvalue As String drpdwnvalue = dropdown.Value With lstbox //do something End with End Sub
And I'm calling this in the buttion click event by passing the listbox names as **List_Compare** and **Select_CM_Compare**
Code: Private Sub Command_compare_Click() Call querylistboxitems_1(List_Compare, Select_CM_Compare) End Sub
But the values passing to the function are not control names, control values i.e corresponding control selected values. I want to use listbox name in **lstbox**, not the value.
I this is a simplifed version a saved query called "qryTest"
PARAMETERS [gUserID] Long; SELECT [Id],'Complaint' AS IncidentType FROM tblComplaints WHERE tblComplaints.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Complaint');
UNION ALL SELECT [Id],'Fall' AS IncidentType, FROM tblFalls tblFalls.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Fall');
how to i call the result from code or from the query manager and include the parameter so there is no prompt that comes up?
like Me.ListNew.RowSource = "qryTest " & gUserID &";" or Select * from qryTest , 31 - where 31 is the value fed to the parameter
??? does anyone know? I know it can be called in APS like this:
Dim Conn Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open strConn ' strconn is my connection string set rsP=createobject("adodb.recordset") conn.qryTest 31 .....
I can't believe I can't do it from the query string! Heeeellpppp!
I have developed some complex pass-thru queries in Access, looking at DB2 data. I am hoping to be able to make these queries 'prompted' for easy use for my less technical colleagues. If these were developed in IBM's QMF tool, I would build the prompt like &MemberID, for example, if I wanted the user to input a value for Member ID. Access pass-thrus don't recognize this, and it throws an error. Anyone have any ideas on how I can do this?
I have a form with option group (two option buttons) and date fields (to select a date range). The form should pull/pass parameters from the query. There's a form button that generates a report based on the query.
Issue: I can't figure it out how to link option buttons and date range to the query so when the button is clicked it generates the report with chosen criteria. The form is for the user to enter parameters.
I have a stored procedure created in SQL SERVER 2008r2
I have a form in access adp project with combo boxes, when I click the submit button I want the values chosen to be the parameters and the stored procedure called to generate a report
I have created a report that prints a transaction input via a form. All the data has been posted to tables while the document details are still on the form. The source for the report is a query that gets its "Document ID" from the current form as its CRITERIA. This works fine. I click a button and the report prints.
Now I've added a datasheet that lists all the "Document IDs" that have been posted within a given date range. I've added a Macro to open(reprint) the same report when any Document ID is double-clicked. This is working except that it prompts for a Parameter Value and references Forms!DocDataEntry!txtDocumentID. (Note: this is the name of the original data entry form which is no longer open)
If I manually type the Document ID (that I just double-clicked) in the parameter box, the report prints correctly. But this shouldn't be necessary.
I know I'm missing a WHERE clause on the Macro that opens the report but nothing I've input works. I can't even hard code a document number. Actually any Where clause provided prompts additional parameter boxes to open and they ALL require the SAME INFORMATION... the Document ID.
I'm thinking that the Criteria on the Document ID in the query should be changed to allow a Document ID from any active source.
I have a routine that exports the results of a query to an Excel file. Is it possible to input the formula into the query so that the Excel values calculate?
This is the formula I am trying to pass to the "AZ" column of the Data tab
I have wrote some code which calls a query using querydefs and then pulls all the records into a table. This table is then exported to excel, however I seem to be having some trouble with the export and am finding it difficult to identify where the problem is coming from. When I run my code in step by step, more of than not the code passes fine and exports to excel. However, most of the time when I run the code as a whole without step by step, then the excel application will load but the workbook will not. The application then continues to close and the code completes without error? The code for the opening of the excel file is below.
The pause is a function i created to see if it was a problem of giving the exel application some time to load!
Code:
Sub MiseEnForme1_Excel() Dim AppExcel As Excel.Application Dim WkbExcel As Excel.Workbook Dim WksExcel As Excel.Worksheet
I have a query that has a date/time field (mm/dd/yyyy hh:mm:ss), I have created another field using the following syntax " Sdate: Format([AgentCallDetailInCalls.StartTime],"Short Date") ", I prompt the user for a date using this sdate field I created. The prompt syntax is [Please Enter Start Date]. Currently you have to enter 2/22/2008 (with slashes), I would like to enter 2222008 (without the slashes) to extract the data. The mask I have setup, does nothing. If I put in the date without the slashes, I get nothing, if I put in the slashes, it runs perfectly. Does anyone know a way around this? Thanks in advance for your most knowledgeable response. :confused:
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 report which asks for the beginning date and ending date which they want. The report is generated using this information. I want to put the information entered in the parameter request into the heading of the report. How do I recover this information to put it in.
Every time I run a query that I have created it asks me to input Expr1 and Expr2 in an enter perameter value pop up box. I don't enter anything, just click okay and the query runs as expected.
Is there a way to get rid of these? (and maybe more importantly, why do they appear?)
:) I like to learn Access. This forum has helped. I would like to pass a value from a "list Box" as a parameter for a query. Is there a way to do this or will I have to do some coding to set the passing of the control.
I would prefer to pass it and use the creteria options which opens a window. I have not see this option as of yet....
Thanks Tom:)
The text below is copied from the help offored with the program on my desktop. Its clear but I need more info and steps ************************************************** ******
When a user selects a value in a list box, drop-down list box, or combo box, Microsoft Access can do one of two things: Access can store the selected value in a table (not the same table that the list gets its rows from), or Access can pass the value to another control. For example, for the Supplier list box in the preceding illustration, if a user selects "Pavlova, Ltd." , Access looks up the primary key value (SupplierID) for Pavlova, Ltd. in the Suppliers table and sets the SupplierID field (the foreign key) for the current record in the Products table to the same value. This is the value that is stored. Because Access is storing a value based on a selection in the list box, the list box is bound. (Note that the SupplierName value that comes from the Suppliers table is displayed in the list box but not stored.)
I'm using an Excel spreadsheet that is importing external data from an access database in which I've got a field where the name of a person checking materials out is entered. it is currently set up, and I cannot change it, as a free form field. So folks enter information in a variety of ways.
For example, Larry Martin might be entered as "Larry Martin" or "Martin, Larry" or "larry.martin@somewhere.com". I'm trying to run a query that would look in that field for any entry with the string I enter, such as "Martin."
I've tried setting the criteria like this:
Like "*" & [Which Last Name] & "*"
However, when I try and run the query I get a message telling me the system is expecting two parameters.
Does anyone have any idea what I'm doing wrong? I've been banging my head against this for awhile now and am thoroughly stumped! All assistance, as usual, is greatly appreciated!
I have to print a label quickly every time that the product hit the warehouse. The label has been created as a report linked to the query that will provide the info to the report. In order to make this report printing as quick as possible the idea is to scan the sample id from the product and once the label is printed scan the next sample and an on.
I'm not an expert on VBA but I have created the following scrip but the reports doesn't pop up.
Here is the code:
Dim SampleID As String SampleID = InputBox("Enter Sample ID") If SampleID > 0 Then DoCmd.OpenReport "rptGRM_QuickPrintLabelDymo", acViewPreview, , "[Sample]=" & SampleID Else DoCmd.Close End If End Sub
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?
