Multiple Selection In A Query From A Form
Aug 1, 2006
Dear All
I've got an issue with a query/form combination that I'm working on. I have the following:
A query where one of the the fields is 'Name' (a text field). 'Name' can be one of 6 different values. I have a form which has 6 check boxes, one for each name. I'm using check boxes on the form rather than a multiple selection list box simply because I think it looks better.
When the user has checked one or more of the check boxes and hits the 'show results' button, I want the query to open up with the details for orders associated with the name(s) selected.
I am writing my query in 'design view' rather than in 'SQL view', and don't know what to put in the criteria box of the 'Name' field. Depending on which check boxes are checked, I'm building a string and storing it in a text box, called Text1. I think the best way to do the filter is to use the In operator, and I currently have a structure like this in the criteria of the query: In([Forms]![Form1]![Text1]). This, however, doesn't give any results.
The problem I have, therefore, is that I don't know the correct syntax for the text within Text1. If I have, for example "Rob","Dave" as the text in Text1, it doesn't work, but if I put In("Rob","Dave") in the criteria rather than referencing the text box, it works fine.
Do anyone know what I'm doing wrong?
View Replies
ADVERTISEMENT
Jul 24, 2007
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
strCriteria = Left(strCriteria, Len(strCriteria) - 4)
strSQL = "SELECT counterparties.[Counterparty Entity], Fund.[Fund Name], products.Product, combine.[Available?] " & _
"FROM products INNER JOIN (Fund INNER JOIN (counterparties INNER JOIN combine ON counterparties.[Counterparty ID] = combine.[company id]) ON Fund.[Fund ID] = combine.[fund id]) ON products.[Product ID] = combine.[product id] " & _
"WHERE " & strCriteria
CurrentDb.QueryDefs("1").SQL = strSQL
DoCmd.OpenQuery "1"
Exit_Handler:
Exit Sub
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
Exit_Command_Click:
Exit Sub
Err_Command_Click:
MsgBox Err.Description
Resume Exit_Command_Click
End Sub
I am trying to be able to search by Product and counterparty.
Any help is greatly appreciated, thanks!
View 8 Replies
View Related
Jun 16, 2005
Hello,
I am building a sort of property management database, and I have run into a bit of a problem. The database will store information about buildings, leases, maintenance etc..
My problem has to do with units in buildings. The tracking of units was requested near completion of the database, and now I have to somehow incorporate it into the database.
Previously, the user when filling out lease information, simply had to manually fill in the unit numbers. It was just a text box, and the user could input what ever he/she wanted. For example unit:1 or unit:3,4,5 etc.
Now since we want to monitor unit sizes, instead of having the user just manually type it in, we want the user to select actual units.
I have several tables but I think the ones that are of concer here are:
Buildings
Leases
Units
Tenants
Units table right now stores the following:
unitId
buildingName
unitNumber
size
(and others that aren't really important) the unit table will store unit information for all buildings and is related to the buildings by the buildingName
So when viewing unit information on the building form, the units are in a subform, and are filtered by the buildingName. Everything works here.
Now for my problem. What I am looking to do is when a user is entering lease information in, instead of choosing the buildingName from a drop downbox and then just typing in the unit numbers, I would like to have the user select the buildingName from the dropdown box, but then besides the unit field, press a button which will open a popup form, displaying all the unit numbers for that building. Then the user can select which units the lease pertains to (it can be more than one unit), and this will populate the unit field.
I want the popform to look something like this
□ unit 1 □ unit 2
□ unit 3 □ unit 4
□ unit 5 □ unit 5
etc.
I think I know how to filter the form, so that it only displays relavent units to the selected building, but I have no idea how to display multiple records on one page, and allow the selection of multiple records.
Sorry about the long post but I was trying to be really clear in what I am trying to achieve (hopefully it was clear not confusing). Any input you guys might have, is very much appreciated.
Eric
View 1 Replies
View Related
Oct 8, 2014
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 seen this [URL] .....
form this post [URL] .......
but unsure how can I make it work on my form.
View 5 Replies
View Related
May 19, 2005
I have a database which keeps track of a trucking business. Each truck has a trailer number. I have no problem having a query prompt the user to enter a single trailer number to query on. What can I do when the customer needs to select more than one trailer number to query on?
View 4 Replies
View Related
Feb 18, 2015
I have created form with multiple tables field in some of these fields I have assigned the combo box selection the total combo boxes in form is four and all are unique numbers but this form is incomplete.
1.When I select or update any of four combo box then remaining combo boxes and fields on form should be updated automatically with related records.
2.I need to bring calculated fields from multiple queries on to my current form .how I can insert query fields in to form...
View 1 Replies
View Related
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?
View 11 Replies
View Related
Oct 27, 2014
I am designing a nomination form (web database so no vba macros can be used).
The form has 3 combo boxes: cboStaffName, cboLevel, cboNominee.
The form is bound to the tblSubmit table where the submissions are populated.
I used a select statement:SELECT Staff_List.Staff_Name FROM Staff_List; to populate the combo box for the Staff Name selection.
This is the select statement to populate the job level combobox:
SELECT Staff_List.Level, Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Staff_Name)=[forms]![frmtest]![cboStaffName]));
The select statement to populate the combobox for the nominee combobox:
SELECT Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Level)=[forms]![frmtest]![cboLevel]));
The problem is that the staff_name in the first combobox is still found in the nominee combobox which should not be because a staff cannot nominate self. There is a field in the Staff_List called YesNo that should be activated for each staff that is selected so that the select statement on the nominee combobox can be updated accordingly to remove items with the field "Active"
How to get the checkbox selected for each corresponding staff.
Sample of the database has been attached.
View 4 Replies
View Related
Jun 7, 2006
I wonder if anyone can help with this:
I have put a selection query as a subform in a form which shows client details. When the form opens from the switchboard it asks the user to enter the clients unique number and then the query links the number to the client ID (autonumber) which then populates the form with all the details for that client. This is all great but I have 2 questions?
1. If I put in an incorrect number or press cancel the form loads anyway with no record. Is there a way to force the user to re-enter the number or if the number does not exist say so, and then return to the switchboard. The aim of this form is for reference and not data entry anyway.
2. Once in the form correctly is there a way to 'refresh' the form so that it asks for the card number again. My plan is to have a button "search for another client" and when it is pressed the query runs again and the input box comes up.
Cheers
View 1 Replies
View Related
Jul 27, 2005
Hello there,
perhaps it could be a fault in my PC;
though I designed a form which depends on a selection-query and when I fill in the criteria in the selction query to look for a certain record, my form display stays empty. Even empty records won't be shown.
Is this phenomenon occuring either normal in Access or is this problem related to the configurations of whether my query or relations.
It's already pissing me off the whole day,
so I would be very eager to know what's the solution,
grtz
View 1 Replies
View Related
Jan 11, 2006
I have a table called Prem. It contains three fields (Offer, MailCode, MktgProgam). I want to have a form where a user can select. the Offer (from the offer field) Once selected, it takes the MailCode and MktgProgam information from the Prem table and uses that info (MailCode, MktgProgram) to pull from the main table called "Cust" Is this doable?
Thanks in advance
View 4 Replies
View Related
Oct 24, 2013
I've inherited a database that has a table with a large number of Yes/No fields. Apart from normalizing the database, I was wondering if the following was possible:
ContractTable has Yes/No Fields for rights granted for each order (television, dvd, internet etc).
Using a form, the user selects the specific right they'd like to run a report on - selecting this right from a drop-down menu, this is labelled "RightsCombo21" on the form.
When the button is clicked, the right selected is now the "Field" section of the query.
The SQL query right now looks like this (shows all rights sold for the territory):
WHERE ((([Contract Table].[Contract Cancelled])=False) AND (([Contract Table].[Contract End Date])>Now()) AND (([Contract Table].[Contract Type])="License Agreement") AND ((ContractIDAndCountry.CountryName)=[Forms]![TerritorySpecificRightsChooser]![TerritoryCombo7]));
I'd like to add something to this WHERE statement:
AND (([Contract Table].RightsCombo21)=True)
Not sure if it's a syntax issue, or I'm attempting something that isn't possible.
View 4 Replies
View Related
Nov 25, 2013
I have a form that runs off a query that displays further details of a record in a datasheet when you double click on a row.
The query itself has criteria that looks at the open form and selects the correct record.
My question is can you change the results of the query either using vba or a built in feature of access without having to use a separate query.
The selection criteria in the query is:
Forms]![Main]![Ordering-Supplier]![Ordering-Order List].[Form]![suppOrderID]
Basically I want to override the resulting data with another record when I run an event on the form.
View 3 Replies
View Related
Nov 6, 2013
Is it possible to add a field (i.e., variable) to a query (or SQL programming) based on a form selection?For instance, if I use a form to allow an end-user to specify which fields they wish to include in the query (essentially creating a UI for the query builder), how would I go about creating the query/SQL or updating the query/SQL?
View 3 Replies
View Related
Jan 9, 2014
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
I'm using 2003 and have some VBA ability.
View 5 Replies
View Related
Dec 17, 2005
I'm building a database for a realtor friend. Part of his job is keeping track of where his clients want to live. I have added a field named "Areas". I need to populate that field with names of cities where his clients want to buy thier house. Sometimes there are only a couple of cities. Other times there could be more then 10. I don't want him to type these cities in. He is not a good typer, either am I, and he is prone to abbreviations and typos. Garbage in garbage out. I would like to provide him a drop down list, or something like that, of all the cities or areas and have him select each area and then either hit a command button or copy/paste it to that text box. Either way will work. The command button would be nifty. The result would give him the option of doing a form filter and being able to filter that text box for ex: "atlanta" and "syracuse". He then could cue these people when he has a property come available in either one of those cities.
I DON'T understand VBA code. I don't know how to write it or where to put it.
I tried to search this site and I could not find any threads like this, to my amazement. If there is a similiar post out there and I could not find it I apologize in advance.
View 1 Replies
View Related
Oct 17, 2006
In form "sendmail" their is a combobox "to" which gets ur email address.
i want we could be able to select multiple entries thruogh this combo box and it should be seprated by a semi colon
View 2 Replies
View Related
Jul 28, 2006
Hi
In my application, I am allowing multiple selection in a listbox.
The data is saved in table. While retrieving, the items that were selected for saving, show as selected. But on printing ListIndex, it prints -1. What could be wrong ? I need to resolve it. Any solution ?
View 1 Replies
View Related
Oct 17, 2006
In form "sendmail" their is a combobox "to" which gets ur email address.
i want we could be able to select multiple entries thruogh this combo box and it should be seprated by a semi colon
View 2 Replies
View Related
Mar 23, 2008
This is probably in the wrong forum, but I'm not sure what area the answer would cover, either queries, macros, VBA etc.
I have a form, on which is a listbox with multiple selection enabled getting it's data from a query. What I want to do is for the user to be able to select multiple products from the listbox and have some VBA code or query concatenate each id and insert them into a table, separated by commas so I can separate them again for reports etc.
Eg.
Listbox:
ID
1
2
3
User selects 1 and 2 and clicks submit. Selections are concatenated to 1,2 and inserted into the table.
Please bear in mind I'm not the best at this kind of advanced databasing, so a simple or at least easy to follow answer would be very appreciated.
Thank you all in advance.
Steve
View 8 Replies
View Related
Dec 15, 2005
I am designing a small database.
I have problem in selecting Mutiple list values from list box.
I have a table of Personal.With fields
SrNo ------------- autonumber
Name -------------- Text
Age ---------- Number
Interset ------------ (Text)
//In Interset Properties through lookup tag I have
Display Control -------ListBox
Row Source Type --------- Value List
Row Source ---------------- "Swimming","Football","Cricket",hockey"
When I made form I got all the List Box Items. For single Selection it has worked. But If I want Multiple selection. I changed the property List Box property Multi Select from "None" to Simple. Now It worked with Multiple Selection.But If one first record I select Swimming, Football.On second record it would be blank. And select my self. But the previous one is saved on all the next records.If two selction on first next all same selction with 2.
If three then three.
I want Individual selection. It must be saved.According to corresponding record.
Facillitate in this regard is much appreciated.
View 6 Replies
View Related
Jan 1, 2006
I’am a fairly new to access I have a Database with forms, on one form that is linked to the student table you can put in all the student details.
what I want to do for the post code is have a drop down box with two place names that have different formats for there post codes E.G London : LL0 0LL and Cardiff : LL00 0LL , depending on which one is selected the corresponding mask would be applied to the Post Code text box.
Any Suggestions will be much appreciated.
View 4 Replies
View Related
Jun 13, 2014
I have a database that contains different departments per office location with the dept. codes such as 100, 101, 102, 103 etc.
I like to have a criteria in a query that will give me all departments that are running from one office location OR if I do not want one dept. to show in my query to be excluded.
Also, the way I currently set the parameters is, it is asking for the office location by state, county, city, address and department code. I set the department criteria as Not [Department] which excludes the dept. that I do not want to see in the query, BUT I also want to have an option that when I run the query to SEE all the departments.
Is it possible to have a criteria like that?
View 3 Replies
View Related
Jan 11, 2007
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"
View 3 Replies
View Related
Aug 7, 2005
thank you in advance for all your BIG help. :)
I have 3 tables - SMaster, SI_map, ILookup
I need to create a form to Add/Update into SMaster and SI_map tables, which has one-to-many relationship, that's why I use a list box to show the values from SI_map.
in SMaster [ Sid, other fields ]
in SI_map [ primary key, Sid, ILookupid ]
in ILookup [ ILookupid, IName ]
questions:
1. How to get the multi-selected values from the list box?
2. insert these multiple records into SI_map table at the same time with SMaster (if I use the same form to populate all these fields)
3. how to populate the records into form for user to see and update the values?
Any suggestion is appreciated, it is very flexible to change any format such as SMaster and SI_map can be separate forms, as long as it works.
Thank you.
View 3 Replies
View Related
Aug 16, 2007
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!
KellyJo
View 8 Replies
View Related
