Can You Silently Pass A Parameter Without VBA?
Jun 20, 2007
Hello,
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.
Any thoughts are appreciated!
Thanks,
Scott
View Replies
ADVERTISEMENT
Aug 18, 2006
Hi there
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!
Dave
View 3 Replies
View Related
Dec 6, 2007
Hi
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.
Thanks,
B
View 4 Replies
View Related
Dec 27, 2007
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
openexcel strFilePath & strFileName
ExportData "qryHoeqDotApproved", "HOEQ DOT APPROVED"
ExportData "qryHoeqDotReceived", "HOEQ DOT RECEIVED"
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
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
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")
'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 qd = dbs.QueryDefs("" & strQuery & "")
qd.Parameters![txtStartDate] = [Forms]![frmLar]![txtStartDate]
qd.Parameters![txtEndDate] = [Forms]![frmLar]![txtEndDate]
Set rs = qd.OpenRecordset
'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.
xl.Cells(intR + 3, 1).Value = rs.Fields(0)
xl.Cells(intR + 3, 2).Value = rs.Fields(1)
xl.Cells(intR + 3, 3).Value = rs.Fields(2)
xl.Cells(intR + 3, 4).Value = rs.Fields(3)
'Moves to the next record
rs.MoveNext
Next intR 'Loops back to For and enters data for the next row
'Once the export is done, this just puts the cursor to A1 on each sheet
xl.range("A1").Select
'Clears the recordset
rs.Close
Set rs = Nothing
vStatusBar = SysCmd(acSysCmdClearStatus)
End If
End Function
View 2 Replies
View Related
Sep 11, 2005
Hi
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?
Many thanks
Stuck21
View 1 Replies
View Related
Apr 16, 2014
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:
DoCmd.OpenReport "rpt_12MonthlyInvoices", acViewPreview, , "StartDate=" & Me.txtStartDate
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?
View 1 Replies
View Related
Mar 5, 2014
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.
View 6 Replies
View Related
Jun 29, 2005
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!
View 3 Replies
View Related
Feb 23, 2006
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?
Thanks in advance for any help...
View 1 Replies
View Related
Apr 18, 2013
I have a report that is based on a query.
The query has two fields. Start and End Dates.
When I run the query the Parameter box asks for the dates by using <[Date1] and >[Date2]
What I want is to have 2 fields at the top of the report, that display the values I enter in these boxes?
View 1 Replies
View Related
Aug 20, 2014
The following code throws an error:
DoCmd.SetParameter "Region", "apac"
Name of the Parameter: Region
Value for the Parameter: APAC
Location of the Parameter: Query_Form
How to pass a parameter for a Parameter Query in Run-time? (Without using form or something)
View 1 Replies
View Related
Oct 4, 2014
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.
View 14 Replies
View Related
Jan 28, 2015
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
Is this possible .
View 1 Replies
View Related
Apr 14, 2015
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.
View 9 Replies
View Related
Nov 19, 2006
:) 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.)
View 1 Replies
View Related
Jul 15, 2005
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.
View 8 Replies
View Related
Jul 12, 2005
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
View 2 Replies
View Related
Oct 24, 2006
Hi,
I want to pass a value between two forms. I created a text field in one of the forms and tried to access it using:
aVariable = frmSearch.txtQryName.Text
It's not working for me. Is this correct?
Thank you,
B
View 5 Replies
View Related
Jul 13, 2007
I'm trying to create a PTQ and just cant seem to find the source table.
The name of the source table is PRM1_ORG_MTRX3_N
If I write my query like this, I get an error code that states username.PRM1_ORG_MTRX3_N is an undefined name. It adds my userid to the beginning of the table name.
SELECT
T128.AS_OF_DATE,
T128.LEVEL8,
T128.LEVEL8_ORG_NAME,
T128.LEVEL9,
T128.LEVEL9_ORG_NAME
FROM
PRM1_ORG_MTRX3_N as T128
WHERE
T128.AS_OF_DATE like '1/1/2007'
Then I rewrote the query like this, and I got and error code that said PRM1.ORG_MTRX3_N is an undefined name. Does anyone know what else I can try here to get this query going? Thanks
SELECT
T128.AS_OF_DATE,
T128.LEVEL8,
T128.LEVEL8_ORG_NAME,
T128.LEVEL9,
T128.LEVEL9_ORG_NAME
FROM
PRM1.ORG_MTRX3_N as T128
WHERE
T128.AS_OF_DATE like '1/1/2007'
View 2 Replies
View Related
Feb 11, 2005
Hello!!!
I have a big problem. I have form A which display list of user errors. I need to pass selected error from that form A to another form B which contain user data in main form and error data in subform (this is error which I want to pass from A).
Form B tells me which error was reported from particular user. Errors are predefined in separet Form (A).
I hope you will understand me. I need help, because I am losing my maind with this one.....
View 3 Replies
View Related
May 25, 2005
How to pass the values from the first page to the second page? The first page contains the lists of Area_Code. After selecting the area_code and the button being clicked the second page automatically call the Query1 (where the area_code's Criteria is Forms!MainForm!SubForm!txtAreaCode) and get the data back on the DataSheetView on the second page.
I found many people use the comboBox and AfterUpdate() to requery the data. But my case is different. Because on the first page, I have a list of area code which has the button to check the authorisation before continuing to the second page.
Thank you very much!
View 3 Replies
View Related
Aug 30, 2005
I need some help please on passing parameters such
as a recordsource to a report but not using OpenArgs
I heard that this can be done using a hidden form.
I have a calling form (form1) which opens a preview snapshot
form (form2) which in turn brings up a rptCurrentRecord (form3)
I can't get the openargs RecordSource SQL from form1 to form3
For arguments sake, Form3 in this case is really the report itself.
Hope this is not too confusing.
Thanks for any help
View 1 Replies
View Related
Dec 21, 2004
So I have a function (TradeRoof_AfterUpdate) inside my form (frmCustomers) that calls one of 2 functions (ActivateConditions or DeactivateConditions) in a module (modRender). ActivateConditions is then supposed to call on one of 2 functions, Activate or Deactivate, also inside modRender. None of these functions are supposed to return a value, just change visibility of a control and true to false or vice versa.
i don't know why, but for some reason, i can't pass a control from ActivateConditions to Activate or Deactivate. Here are the functions (DeactivateConditions and Deactivate are nearly identical to their counterparts):
Code: Public Sub ActivateConditions(Sticker1 As Control, Condition1 As Control, Optional Condition2 As Control, _Optional Condition3 As Control, Optional Condition4 As Control, Optional Sticker2 As Control) Sticker1.Visible = TrueIf Not IsMissing(Sticker2) ThenSticker2.Visible = TrueEnd IfIf IsMissing(Condition2) ThenActivate Condition1ElseIf IsMissing(Condition3) ThenActivate Condition1Activate Condition2ElseIf IsMissing(Condition4) ThenActivate Condition1Activate Condition2Activate Condition3ElseActivate Condition1Activate Condition2Activate Condition3Activate Condition4End IfEnd Sub
Code: Private Sub Activate(ActiveCondition As Control) ActiveCondition.Visible = TrueActiveCondition = FalseEnd Sub
when i hit the trigger on my form, i get this error: "Run-time error '91': Object variable or With block variable not set," and then it highlights "ActiveCondition.Visible = True" in my Activate function.
Why can't I perform this action? Any help is greatly appreciated!
-Jason
View 1 Replies
View Related
Jan 6, 2005
Is it possible within Access to use a pass through query that joins tables from two different servers?
If not I have a workaround solution though this is a more intensive effort.
As always, you are all great here and any help is appreciated.
View 1 Replies
View Related
Jun 30, 2006
Can you do a pass through Query to a pivot table when your query has parameters? I'm reading like you can't, but nothing has been said concretely yet. When I do it, it gives me an error "Trouble Obtaining Data" when I try and set the layout.
View 1 Replies
View Related
Nov 26, 2013
So I have 2 forms, an Edit Lot Form, and an Add Run Form. Each lot will contain several runs. What I want to do is be able to add a run associated with the specific lot by pressing a button "Add Run" in the lower right corner of the edit lot form (see attached images). So the information for the lot will carry over to the run form and autofill the fields pertaining to the Lot in that form.
View 2 Replies
View Related
