:) 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 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.
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 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 created a search form which I would like to use to run a query (so the data is in a spreadsheet form and I can export it).
In this search form I have a multi select list box (simple) that list the states in the US. I need to be able to pass 1 or more states as a search criteria at the same time. Also I need it to pull ALL states if there is nothing selected. The search form has a bunch of fields on it that won't all be used.
I know once you select more than one thing from a list you can't reference it directly, is there a way to accomplish what I want to do?
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!
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.
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.
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 want to be able to choose an item from a drop down list (based on a query i.e. distinct products) and have the item chosen to be automatically used as a parameter in another query.
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.
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 and access form that has a list box with dates in it. I need to pick a date and then run a stored procedure in SQL based on that date. Can someone help me with the login to pass on the date?
So if I have listbox1 which is a list of dates from another table and my procedure is simply
select * from main where date = *******
How can I pass the list box value to the stored procedure? My procedure works with WHERE date = (SELECT(LEFT(GETDATE(),11))) but I have since learned that todays date will not always be used so I need to pass a value.
I have two forms - Notes and JobLookup Notes contains a number of text boxes for entering data, one of them being for Job No. Beside it is a link to the form JobLookup. This form contains a simple listbox that lists all the job no. Is it possible to pass the selected job no from this list to the text box on the Notes form?
I have an access project that I am working on and need to be able to select multiple items from a listbox and have the exact selections appear in a textbox on the same form. I have looked around and have not been able to find any code that works.
Somewhere within the deep recesses of my brain, I recall seeing a method of creating a parameter list. I have tried a number of sources, but have come up empty - probably not searching on the right words.
Right now I have a parameter prompt "Enter FILE NUMBER".
I want to be able to enter more than one number and the numbers won't necessarily be contiguous.
An example would be "Enter FILE NUMBER(S):"
The answer might be "10859, 10100, 10903". I've tried using a comma and a semi-colon to separate the list members, but I know I have to make a change in the original query.
I know you can create parameters in queries such as [Enter the Town for your report]
Is there away of this showing a combo box or list of all the distinct values for the user to pick from (so no spelling mistakes or typo or null values)
Good afternoon, I have a form with a subform and in the first txtbox of the subform in the GotFocus event I have a little procedure which checks the txtboxs on the parent to make sure that there is data in all four of the txtboxes. This works great the first time and it pops up a msgbox and it even setsfocus on the txtbox with no data in it, but if I tab into the subform a second time and there still is no data in one of the txtboxs on the parent form, nothing happens, no message and no setting focus on the txtbox with no data in it. Does anyone know of a way to get this procedure to re-set everytime a user tries to enter the subform? Thank you in advance to anyone offering and ideas and suggestions.
