Modules & VBA :: Multiple Selection List / Comma Separated String - Run Query And Create Report
Jun 18, 2013
I'm using Access 2007.
So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.
I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter.
Here is some of my code:
Code:
Option Compare Database
Option Explicit
Private Sub Form_Current()
Dim oItem As Variant
Dim bFound As Boolean
[Code] ....
Here's my current query in SQL:
Then finally how to I get the query to execute and create a report based on all of this?
I would like update a table column values separated by ; For example
1. ColumA has no data, I want to do an update and place a comma at the end 2. If ColumA has already value do not delete add new valued at the end where ; if found.
My start attempt as follow:
Code: qry_test= "UPDATE Table1 SET ColumA =" & "Enter Entered ;" & & _ " WHERE ID=" & 1 Set RS = cnnDB.Execute(qry_test)
What do I need to do to add more stuff where ; is found without delete existing value if values exist.
I created a multivalue list in my table. It contains a list of names and a checkbox. I designed the form based on this and am able to check off multiple names. When I go back to look at the table, it stores in each name separated by a comma. So far so good.
When I create a report to display the data, it displays it as 1,2,3..etc., instead of the names separated by a comma.
I have an access table for SampleTests. Each sample may have multiple tests carried out on it. Each test is one record in the access table and a sample may have multiple records therefore depending on how many tests are to be carried out on this sample. I need to create a query that would have sampleID as one of the fields which it gets from the SampleTests table and then I need the second column to have all the tests associated with that sample ID in the format test1,test2, etc. but I cannot see how I can get access to put these records together separated by a comma.
I have a single 'text' box on a form that captures test scores. The person inputting the test scores puts in all of the scores separated by commas (77, 85, 100, 75.5).. Up to 6 test scores can be placed in this single text box. The field is formatted for text. What I propose to do is take the numbers in the text box and average them out and then display the test scores average in a number field named 'avescores' next to this field in the form. Once the inputer puts the series of scores into the text box, they would hit an 'update' button next to this box to 'update' the 'avescores' box. Right now they use a number pad and add up the numbers manually then place the average into the avescores box....
I've current got two tables, one with Products and the other with Suppliers. The products table has a column in which I may indicate Supplier IDs that carry the product. I'd like to make a report that groups the Products by the Supplier that carries them and I'm having some trouble. The way I see this working is to relate the Supplier ID found in the Product table with the auto generated Supplier ID in the supplier table.
My trouble is this, when the field is set to text I'm able to enter values separated by commas no problem but I can't report on them because I'd be relating a number to a string. How may I overcome this?
I use Access 2003. I am trying to populate a list box with the names of files found in specific folder in my computer. The problem is that if the file name has a comma (,) in it, so the name is being separated in two parts - first what is before a comma and second what is after. I need them exactly as they appear in directory in order to open the files from this list box.
If I have a text box (Text1) on my form with comma separated values entered i.e. 100,120,250,300 what would be the easiest way to enter these into a table column with a button click event. I would like to enter these values into my table tb_test under column Values.
so i have two listboxes that have the values i want for the query parameters. I slightly modified one code i found so that i can query using one of them, however i cannot figure out how to get the second listbox to put criteria into another field. The working code i'm using is:
Private Sub Command_Click() On Error GoTo Err_Command_Click
On Error GoTo Err_Handler Dim varItem As Variant Dim strCriteria As String Dim strSQL As String
For Each varItem In Me.PartyBox.ItemsSelected strCriteria = strCriteria & "counterparties.counterparty =" & Chr(34) & Me.PartyBox.ItemData(varItem) & Chr(34) & " Or " Next varItem
Err_Handler: If Err.Number = 5 Then MsgBox "Must Make A Selection First", , "Make A Selection First" Exit Sub Else MsgBox Err.Number & " " & Err.Description Resume Exit_Handler End If
Dim stDocName As String stDocName = "combqry" DoCmd.OpenQuery stDocName, acNormal, acEdit
INSERT INTO 1_2_06 ( Issue_ID, Assigned_To, ListType ) SELECT Issue_ID, Assigned_To, 'QPT' FROM QPTActions WHERE QPTActions.Entry_Date<=DateValue('1/2/2006') And QPTActions.Close_Date>DateValue('1/2/2006');
The Assigned_To values are a Lastname, Firstname format as they come out of the first database. But I need the comma stripped before they get entered into the INSERT database. Is it possible to do this on the fly?
Dear all, is it possible to run a query (or ideally a report) based on a selection from a drop down list in a form? (in accsess 97). The reson being is i have a big list of of codes for different absence reasons & at present the user has to type in the correct code to display the data in a report based on the code they enter, however its not easy for users to remember which code to type in to run a report.
Idealy what i would like is a drop down list with the absence codes & the absence desciption next to each code, then when the user selects a code from the list it displays the corresponding data in the report?
Is this possible?, any help would be much appreciated, thanks.
I have a table (let's call it "tableA) with about 7 fields. One of the fields contains information that I need to do a query on so that I may find specific payees, for example:
The field is called "Payee" and in that field for each record, the information could be "At&T", "A T and T" or "Abercrombie & Fi", but there is also other information in that field along with At&T, etc., so the field for a record may look like this: A T AND T MOBILI A T AND T MOBILI 10 90034 Bill Pay or ABERCROMBIE & FI 3042 PP 10 9111 DIRECT
Is there a way to set up another table (let's call it tableB) to have all the Payees names (such as AT&T or whatever) in it and by the push of a button (maybe macro), have a query go out and find all matches from tableB in tablea?
So, basically have the query look in tableA and find all the matches from TableB. Please understand that tableA could have records that contain the same name, but spelled a number of different ways, for example, AT&T or A T and T or ATandT or ATT. Again these names may or may not be embedded in with other information in a field.
Or is there a way that an Access query can look into an external list and find the records, example:
Have a text (file) list with all the names (separated by a comma or space - AT&T, AT & T, Amercrombie and FI, Abercom&Fitch, etc. Now have a query go to that list and find all records that match in my tableA?
I would like to remove the comma, but only one that is at the end of my string(there is a space after the comma, should be also removed).How should I do that in vba?
I created a button that when pressed concatenates any items selected in a list box and places them in a text box on the form with the format of "itemselected1" or "itemselected2" or "itemselected3"
However, I need to set the criteria in a query to look at this text box and return the results based on that selection. When I do that I get no results even is the query should run fine with that type of format.
Attached is a copy of the Code for the button and the result Dim txtValue As String Dim varItem As Variant Dim strlnameselect As String Dim intCount as Integer 'Cycle through selected rows in listbox
For Each varItem In Me.Queueselect.ItemsSelected intCount = intCount + 1 Select Case Len(txtValue) Case 0 txtValue = Chr(34) & Me.Queueselect.ItemData(varItem) Case Else txtValue = txtValue & Chr(34) & " Or " & Chr(34) & Me.Queueselect.ItemData(varItem) End Select If intCount=Me.Queueselect.ItemsSelected.Count Then txtValue = txtValue & Chr(34) End If Next 'Assign variable value to textbox Me.Queuetorun.Value = txtValue
End Sub
the result in the text box is "A" or "B" or "C" or "D"
Hi! I hate to ask for so much help on this, but I'm lost.
I have a budget database that is used to track equipment for purchase. Many times, the same item (a desk, or chalkboard) is needed for more than one room, so I need to buy more than one. The way my form is set up, I put the detail about the chalkboard in and select the room ID for the room it is going into. The problem with this is that I don't want to have to do it for every single chalkboard location in the college.
So, I want to use a multiple selection list box to add the records all at once. In other words, I want to enter the item detail in my form, and then select all of the rooms that item is going into, and then click OK! and have it create new records for each room that item is going into. So rather than having one new record created (as would be with a combo box), if I have three rooms selected, I want the OK button to add the record to the table three times with a different room number for each record. Any ideas????? Thanks!
I am trying to resolve a problem with selecting multiple records.
I have a table called T_user and bound form called F_user.
This form displays all 3 fields from that table.
When I want to add a new user, I enter a name and select a department.
Some of the users work for few departments, so here is the question:
How to select multiple departments for one user?
The department field in F_user is set to LISTBOX, and the multi select option in properties is set to SIMPLE.
I can select multiple departments, but as we know it won't save in the table, it will leave it as NULL value.
How can I save the record then? In another form I need to select users from specific department, so lets say A.Smith, need to be visible in those few selected ones.
I have a form where I add a new event, and it gets added to a list box in another form, I was intending to select the most up-to-date record within the list box but it seems to keep selecting the old latest record and not the new record that ive added. I had tried to select the first record but then move to the last record hoping it would refresh but that still doesn't work.
This is the code I used below:-
Quote:
'Select last event record added to event list box Me.lstPatientEvents = Me.lstPatientEvents.ItemData(Me.lstPatientEvents.L istCount - 1) lstPatientEvents.Selected(lstPatientEvents.ListCou nt - 1) = True
I have a form that allows me to change the information for a list of personnel. On this form I have a list box that displays all the personnel in the list. There should never be more than 12-15 people in this list so it's an effective way of selecting records, however I can't figure out how to make the form select the record that is tied to that person from the list.
In VBA I've tried creating an "On Click" command for the list that, when a name is selected, is brings up the corresponding record. I thought I could use a DoCmd.FindRecord but I guess I don't quite know what I'm doing. I didn't want to use SQL to limit the records to just the one that matched I wanted to keep it simple if possible.
Code: Private Sub Personnel_List_Click() DoCmd.FindRecord Personnel_List, , True, , True End Sub
The Personnel_List box is tied to the Personnel_Table, and the table only displays the names available.
I have got an unbound multiple list box called List44 (Row Source: query based on table tblAircraftCategory, Multi Select - Extended) that needs to be passing parameters to my main query called AircraftSearch2. The multiple choice list box have the following fields:
1. Piston 2. Turbo Prop 3. Entry Level Jet 4. Light Jet 5. Super Light Jet 6. Midsize Jet 7. Super Midsize Jet 8. Heavy Jet 9. Ultra Long Range 10. Helicopter 11. Air Ambulance 12. Cargo 13. Vip Airliner 14. Airliner
The user will use the form for selecting search criteria (the form is called SearchForm2 and has 5 combo boxes, 3 text boxes and one multiple choice list box).
I'm very new to access and need to modify (or coding a separate module) my query to include my multiple choice list box in my query?
I have 1 combo box contains 2 columns look-up directly from the properties(Not VBA)
Now i want to select record from the list
Example: i want to select PM-1234-1111 so i dont want to type starting letter PM to select but i want to type 234 or 123 or somewhere in the middle or end to filter that contained text in all the items is it possible?
I am trying to get my list box to filter based on the selection of a combo box.
My Combo box cboOrgRole is on my main form and is bound to OrgRoleID - on form - frmOrgEntry
The unbound list box lstRoleList is in a tabbed subform - frmPersonnel
This is my data pull for lstRoleList
Code: SELECT tbl00PersonRole.PersonRole, tbl01Orgs.OrgRoleID FROM tbl00PersonRole INNER JOIN tbl01Orgs ON tbl00PersonRole.OrgRoleID = tbl01Orgs.OrgRoleID WHERE (((tbl01Orgs.OrgRoleID)=[Forms]![frmOrgEntry]![OrgRoleID]));
This is the code I have on cboOrgRole AfterUpdate
Private Sub cboOrgRole_AfterUpdate() Me.frmPersonnel.lstRoleList.Requery End Sub
I have also tried Recalc - both throw a Compile error: Method or data member not found
My goal is to be able to select the role of the organization, (General Contractor, Architect, Engineer, or Client) and have it list the available titles for the specific organization type.
