I have a subform (this subform's source object can change to one of three diffrent forms) that allows the users to filter. I have a requirement to output the filtered data to excel. I am creating a query based on the subforms record source called qryExport. I now want to pass the subforms filter to qryExport. I have the string setup now I just need to know how to pass the data into the filter property of the query.
View RepliesI am able to filter a data on a continuous form using drop downs and then the following code attached to a cmdbutton to create a report of the filtered data.
Code:
Private Sub Command30_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "rptconveyorerrors", acViewReport, , strWhere
End Sub
On the same form where I filter the data i can sort it by clicking the headings aswell, however when i generate the report using the above VBA it doesn't take the sort with it and just generates it without the sort.
I am using the following VBA to sort my form
Code:
Me.OrderByOn = True
If Me.OrderBy = "[empname] DESC" Then
Me.OrderBy = "[empname] ASC"
Else
Me.OrderBy = "[empname] DESC"
End If
Me.Refresh
I thought it may be possible to use the following sort of VBA to pass the sort however i cant get it to work:
Code:
Private Sub Command30_Click()
Dim strOrder As String
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
If Me.OrderByOn Then strOrder=Me.OrderByOn
This is my first time writing a pass through query pinging sql tables using an input parameter from a form. I have gotten as far as executing the query but I can't seem to display the result to ensure it's pulling the right records. I also want to be able to append the records to a table. Below is the code I have written so far:
Sub GETRT()
Code:
Dim db As DAO.Database
Dim QDF As QueryDef
Dim STRSQL As String
Dim RS As Recordset
STRSQL = "SELECT * FROM LAB_MESR.ODM_RT_DAYS" & _
"WHERE LOCATION_ID=" & [Forms]![PARMS]![STR_NBR]
Set db = CurrentDb
Set QDF = db.QueryDefs("001:GET_LT")
QDF.SQL = STRSQL
End Sub
Table: DailyExport
Field: FailureGrouping (actually is offices)
I want to export all the fields from DailyExport each of our 9 offices to Excel, whetjer or not they have date in the DailyExport table. So if not, the exported workbook would only have columns headings.
I need to loop through the nine offices and export each office report to Excel.
Dim StrQry As String
Dim strfullpath As String
Dim SOffice as String
strSQL = "SELECT * FROM DailyExport WHERE FailureGrouping = " & SOffice
strfullpath = "Y:" & SOffice &" "& Format(Date,"mm-dd-yy") & "_Failures.xlsx"
DoCmd.TransferSpreadsheet acExport, , FailureGrouping, strfullpath, False
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)
Is it possible to have a macro button to pass query results to a listbox? I have a database where I have some fields with dates. What I need to do is to show a list of all cases that have a start date (and also the end date). So I will have 2 buttons, one that says "show started cases" and second one "show closed cases". If I press the first button, it will run a query and show only those cases that have a date filled in the started case field. I made a button that shows the results in a report, but I would like to have it show up in a listbox so I can double click it and go straight to the case.
View 14 Replies View RelatedSo what I have created is a form with 4 combo boxes which filters a subform with a click button by running a sql query.It was working great yesterday but then when I made the subform a pass through query it seems to no longer run (or just runs so slow it takes a large amount of time to query). To get a better sense of what I'm talking about I basically have a button and inside of the button it takes this query template:
SQL = "SELECT * FROM queryname WHERE 1=1"
concats with if statements to the end of the Where clause with the values in the combo box and then sets
subFormName.Form.RecordSource = SQL
how I can get this table to query...Also, for my second question, is it possible to make this pass through table editable after I filter it?
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
how i would filter a form using an option button. E.g i want to be able to click an option button that is called Filter user and it will filter the query based on who is logged in? and a Filter all option that will clear the filter and show all records?
View 3 Replies View RelatedGood 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 RelatedI have two subforms in a tab formation sitting on a main form. They can both open up a single popup form (via a button) and that popup form is opened via openargs with the autoID field.
What I need to do is pass back a value on the popup forms [On Unload] event to the subform which opened it.
As the popup form is Modal, the subform which opened it can't be changed.
Am I right in thinking I can pass back the value to the form which had previous focus?
Previously, to pass a value from a popup form to a single form I've been using
Code:
If CurrentProject.AllForms("MyForm").IsLoaded Then
do this
else do this
end if
But in this instance, how do I code the On Unload event in which to tell Access which form opened up the popup form, and pass a value back to it?
I'm guessing
Screen.PreviousControl.SetFocus
has something to do with it?
I have a after update event that will match the written record with any exist record in a table in the field "OrgName". If it doesn't find exact match, will call a function with a "soundex" algorithm to see if there is only a misspelling or another name altogether.
In the afterupdate event, I have a string called strOrg (wich is the name I want to compare).
I have the soundex function in a module, so I can use it for several form generally.
What I want is to pass the strOrg to the soundex function, however I don't know how to declare the variables. however I keep having this error:
"compile error: Argument not optional"
and goes to the line tagged as 1 in the private sub afterupdate
The afterupdate sub is the following:
Code:
Private Sub tOrgName_AfterUpdate()
Dim strOrg As String
strOrg = Me.tOrgName.value
If IsNull(DLookup("orgID", "torg", "OrgName = '" & strOrg & "'")) Then
resMsg = MsgBox("This organization name is not in the list. If you want to look for similar names press YES, if you want to register a new organization press NO.", vbYesNoCancel, "Organization not found")
[Code] ....
and the soundex function is declared as
Code:
Public Function Soundex(strOrg As String) As String
Dim Result As String, c As String * 1
Dim Location As Integer
Now the getrows is working fine at my end. Now I have a different problem at hand:
Sub Test2()
Dim myrset As Recordset
Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
myrset.MoveLast
[Code]....
Recordcount is coming fine
Manual Array is also giving the right result
But the Index method is giving the wrong value. Why?
(Holidays table is just having the values in the array only ie. #08/15/2014# and #08/29/2014#)
Is there a way to pass a variable to the GOTO statement?
View 14 Replies View RelatedI want to pass the Combobox value of one form to a textbox on another using the where condition. Using the break in VBA, the value of the combobox (in this instance "two") shows as null. the control of the combobox is [cboSubCategory] and I want it's value to pass to another form "tblCategoriesSub" (aware of the incorrect reference, as used wizard quickly - but will change if I can get it to work) to the "SubCategory" field.
View 5 Replies View RelatedI'm working on a database that will be used when maintenance needs to be performed on our test equipment. So far, I have a form set up for the associates to track when a maintenance action has been performed. The top section of the form contains basic data about the part and there is a subform that lists the actions that need to be taken on the piece of equipment. In the subform, the associate can mark whether the action is complete or not.
If the action is marked incomplete, I wanted a separate form to appear for them to track why an action cannot be performed. If possible, I would like this separate form to automatically show the equipment ID and the action that was missed. However, I'm having difficulty getting the data to copy from the subform to the Exception form. Is this something that is possible to do?
I am trying to pass the results of what is selected in a list box to sql through an ado statement, when i select one result i can pass the value with no issue, when i select multiple item i get a run time error incorrect syntax near ';' i am guessing it is passing the data as a csv to sql and the where statement does not like that as criteria but i am not sure how to handle that either in vba or on the sql side.here is my vba code from access
Private Sub Command49_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strItems As String
Dim intItem As Integer
For intItem = 0 To List45.ListCount - 1
If List45.Selected(intItem) Then
strItems = strItems & List45.Column(0, intItem) & ";"
End If
[code]....
I have a button on my main db, that opens a second db using hte following code:
----------------------
'Dealing with external objects, use inline error trapping
On Error Resume Next
Dim appAccess As Object
Dim db As Database
Dim strAppPathName As String
Dim strAppName As String
Dim strTimesheetPathName As String
[Code] ....
This code works great to open the other db, and handles wheter the other db is already open or not, but I cant seem to pass the variable to the other db using the startup switch /cmd.
I suspect if I used the shell method it would pass the cmd , but I havnt found any way to test if the db is already open with the shell method.
How can I pass a variable to the other db when opening it using VBA?
1. I have created a parameter query in access 2010. This parameter is on only one field.
2. I have created a multi-select list box in a form so that users can select one of more items.
I want to pass the selected items as parameters to the save access query.
I have code that will create an email and prepare it for sending.It will create a table inside the email and fill it in with some text and underscore characters to be replaced by data from the database. So far the data has to be done manually. I would like to know if it is possible to use the values from some fields inside the select record in the current form. So, if Me.Status would be "New" it should pass this to the table in the email.So far I have the below code:
Code:
Private Sub Command280_Click() 'send email with table
Dim objItem As Object
Dim oMail As Outlook.MailItem
Set oMail = objItem
Dim oApp As Object
[code]...
I want to pass a string aPath contains path of the active workbook to access vba module or sub. Below is my excel vba code that will open access db form. How do i pass the string value to access vba.
Code:
Dim aPath, aDbase, aDSource, aTable, exePath As String
Dim fileParam As String
aPath = ActiveWorkbook.Path
aDbase = "near_14.accdb"
aDSource = aPath & "" & aDbase
[Code]...
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.
I have tried:
Me.user2 = Me.slct_auditor.Column(0, 1)
Me.user3 = Me.slct_auditor.Column(0, 2)
Me.user4 = Me.slct_auditor.Column(0, 3)
Me.user5 = Me.slct_auditor.Column(0, 4)
Me.user6 = Me.slct_auditor.Column(0, 5)
Me.user7 = Me.slct_auditor.Column(0, 6)
Me.user8 = Me.slct_auditor.Column(0, 7)
but when skipping the first item in the listbox it is still passed as into the textbox.
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'
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 RelatedHi
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
I have an Access front-end connected to Oracle tables. When the user opens up the main form to add a new entry, I need to be able to pull the next sequential record number. However, I can't get this to happen, without filling out the entire record, saving, and then it will committ the number.
Can this be done with a pass-through query to the Oracle table, find the next sequential number, and then add it to my record id (ie, 06-1050, with 06 being the year and 1050 the next sequential number). I do have two fields in the Oracle table TXTYear and LNGNumber. This concatenated field is related to three additional tables that have more information. Each of these tables need to cascade a new record.
Confused? me too. any help would be appreciated.