Modules & VBA :: Listed Box Pass Selected To A Sql Where Statement
Sep 11, 2013
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]....
View Replies
ADVERTISEMENT
Jul 3, 2013
Is there a way to pass a variable to the GOTO statement?
View 14 Replies
View Related
Dec 4, 2006
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?
Hope this makes sense :)
Michael
View 3 Replies
View Related
Jan 30, 2008
All -
For the life of me I can't get the Multiselect Listbox to correctly pass along all of the item selections to a Query which a form is based on.
I've been up and down the forum, and I can't figure out what piece of code to use and how to use it successfully.
I've been able to get a string created using the example posted here (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47909) and it's in the format of "54,67,89,100" etc.
Public Function
Public Function fMultiSelect(ctlRef As ListBox) As Variant
Dim Criteria As String
Dim i As Variant
' Build criteria string from selected items in list box.
Criteria = ""
For Each i In ctlRef.ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & ","
End If
Criteria = Criteria & Format(ctlRef.ItemData(i), "0000000")
Next i
fMultiSelect = Criteria
gMultiSelect = Criteria
End Function
Call:
Call fMultiSelect(Forms!frmPreSPIPComp!lstProjects)
I now need to pass that string to a Query. Once it's been passed to the query, I can open the report based on it.
Essentially I have a button that will perform the string creation, and I would then like to open a report. I want to base the report off of a query and then have the query use Criteria to dwindle down the report.
Am I missing something here?
The long explanation:
I have a single form that allows for the selection of the report. Once the report is selected, certain fields appear that allow for certain criteria to be selected (ie. Class Name, Multi-Select Class Name, Student Names, Multi select Student Names, Dates, etc.)
Once the report has been selected and the criteria set, a user hits a single button that runs the specific report.
Any better ideas of how to set this up. The reports will ultimately be basing their criteria on what the form has in all of it's unbound fields.
I also have a table that specifies the Display Name, actual Report Name for the button to figure out what report to run.
Bottom Line. I want to use a Listbox to filter a report. If I can use a query to base the report off of even better. I don't want to create the SQL in VBA.
Any ideas? Thanks!
View 5 Replies
View Related
May 23, 2013
I'm trying to create a query that supplies a form with data. I want to pass a TempVar to the query that is selected a from previously opened form.The TempVar is setting correctly and I can see if this if I place a textbox (NewCID) on the form showing the TempVar. The problem I have is displaying on the records according to that TempVar. If I set the query manually, i.e. "|Test|" then records are displayed but if I use the TempVar, which also displays |Test| then no records are brought back.
think it's something do with the vertical bar and that fact the field I'm searching on is a memo field, both of which I've no control over. I also have to use the Like statement because of this.Here's the query that works...
SELECT *
FROM dbo_ASSETS
WHERE ASSET_CID Like "|Test|"
and the one that I want to use, that doesn't...
SELECT *
FROM dbo_ASSETS
WHERE ASSET_CID Like [TempVars]![tmpvarCID]
I've even tried referring to the textbox instead of the TempVar, i.e.
SELECT *
FROM dbo_ASSETS
WHERE ASSET_CID Like [Forms]![AssetsCID]![NewCID]
but that doesn't work either.
View 2 Replies
View Related
Jul 20, 2015
I have a folder that gets updated with a few new xml files every day. I need to import the data in these files into a table. The names of the files to import is in a field (FName) in a table (tblFiles).
The code that loads up the files is this:
Application.ImportXML DataSource:="C:ImportXML FILES" & FileName & ".xml", _
ImportOptions:=acAppendData
Up until now I specify the name of the file in an inputbox, so I have to enter a bunch of names to get it done.
I am looking for a way to get the filename from the table and then load up the file automatically.
View 1 Replies
View Related
Mar 17, 2006
Hi all, i'm currently working a MDB project that aims to develop a front end access solution for users of the sql database. The system is used only to made adjustments to the WHERE clause of the SQL pass-through statement. The SELECT and FROM statements are pre-determined and users won't need to update this. The result is a read only. You might be wondering why i don't use ADP instead, well one of my limitations is that i won't have write access to the database. They are afraid i will corrupt data...
I want to use a series of text and combo boxes to build my WHERE statement, which will be added to the main SQL statement and then pass-through to the server, making use of its much beta processing capabilities. However as i'm trying to read up on VBA, i still have very little knowledge on how this can be done. Its actually similar to the Filter By Form option in datasheet view when i open a pass-through query. However, conditions applied through here means the processing is done on the user's computer, through testing i found this to be unreliable as it causes access to hang frequently. I would love to have this filter by form view available up front to the user before he even executes the query. As i mentioned, he keys in the criteria/conditions and access builds an sql where statement and appends it to the main SQL statement.
any idea how to do this? how do i capture user input and make access construct a WHERE statement from it For example
textbox daterange
Enter range of dates in here: date 1 and date 2
the where statement would then be "WHERE date BETWEEN date1 AND date2
Or is there other more efficient alternatives? sorry i'm very new to VBA and form development, would appreciate lots of advice and answers.
Many Thanks
Eric
View 1 Replies
View Related
Jan 7, 2015
What is the correct syntax that would delete a file when the record is deleted. The file's path is listed in a record field, MailLocation. Every time I try this code, I receive an error!
I've tried the below, and number of iterations, including calling the killfile differently (me![MailLocation], me.MailLocation).
Code:
Private Sub Form_AfterDelConfirm(Status As Integer)
Dim KillFile As String
KillFile = me!MailLocation
Kill KillFile
End Sub
View 6 Replies
View Related
Oct 11, 2013
I have no experience with Access Modules which I think I need to find all the queries that reference a field, in this example: LAWSON_LHSEMPDEMO.R_STATUS
I would like for the module to list all the queries for me that reference this table and field of "LAWSON_LHSEMPDEMO.R_STATUS"
Is there an easy way to do this?
View 4 Replies
View Related
Jan 22, 2015
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
View 5 Replies
View Related
Feb 17, 2014
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 3 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
May 9, 2015
I 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?
View 14 Replies
View Related
Apr 22, 2014
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
View 3 Replies
View Related
Jul 31, 2014
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#)
View 7 Replies
View Related
Feb 12, 2014
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
View 1 Replies
View Related
Mar 25, 2015
I 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 Related
Sep 26, 2013
I'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?
View 2 Replies
View Related
Feb 19, 2014
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?
View 10 Replies
View Related
Sep 24, 2014
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.
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
Feb 16, 2015
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]...
View 5 Replies
View Related
Mar 20, 2014
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]...
View 4 Replies
View Related
Aug 16, 2014
I 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
View 1 Replies
View Related
Sep 30, 2013
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 Related
Oct 16, 2014
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.
View 4 Replies
View Related
