Queries :: Can Run A Query Based On A List Box Selection
Jul 31, 2013
I have a query that allows the user to put in a date range - this works.He also wants to limit what he prints.
For example at the moment he gets
OrderA rest of line 1 for orderA
OrderA rest of line 2 for orderA
OrderA rest of line 3 for orderA
OrderB rest of line 1 for orderB
OrderC rest of line 1 for orderC
OrderC rest of line 2 for orderC
OrderD rest of line 1 for orderD
.
.
.OrderZ etc
He only wants to say print lines for OrderA and OrderD. It's he's choice and not based on anything else already in the table.
I see that in the criteria on the design for the query it allows me to bbuild an expression and I can select a list box on the form such as
[Forms]![name of form where list box is]![name of list box].[EXPRESSION VALUES]
I don't know which expression value to choose. I have tried afterupdate and beforeupdate but none of the rest look like they are the ones I should be using.
View Replies
ADVERTISEMENT
Feb 23, 2014
When creating a query I have set up a parameter query as instructed (in this case searching for a name) but when I run the query it returns the error 'this expression is typed incorrectly etc.'
I have worked out that this must be due to the fact that the names are not typed in by a front-end user but need to be chosen from a dropdown list (which i have stored in a separate table) - So, the query seems to fail based on that.
View 10 Replies
View Related
Feb 10, 2006
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.
View 4 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
Jun 25, 2013
I am currently working on an existing database (not created by me) which contains several queries. Each query relates to a particular product.
I am wanting to create a form which has a combo box so that a user can choose a particular product from the combox options and then click on the command button to run the correct query.
How to do this. I have created the form and the combox (together with the list of products). I just need to know what Event Procedure code I need to enter to programme it to look for the correct query and then run it.
For example.
I have the following queries
product1query
product2query
product3query
I have the following options in the combo box
product1
product2
product3
How do I get the command button to look at the product1 option in the combo box and then find and run the product1query.
View 9 Replies
View Related
Apr 15, 2013
I created 2 combobox that list specific table in a data base. I want to create an unmatched query based on the selection from those two combobox.
All field in those table are the same.
View 3 Replies
View Related
Jun 20, 2005
I have a form that contains a list box with 11 separate options. Within this list is the option named "None". My desire is to have a message box displayed when any option is selected other than "None". I have searched various threads, however I have been unsucessful nailing down my issue. Thanks in advance for the assistance.
View 1 Replies
View Related
Apr 8, 2014
I'm building a customer form using a list box of summary customer info on the left side of the form (CustId and Names) and customer details on the right hand side. I want the details shown on the right to be those for the customer selected in the list box on the left. I'm having problems getting the details to update when a different record in the list box is selected or clicked.
I originally set the data source for the detail records to the base customer table but couldn't work out how to update them when the list box was clicked!
I then tried creating a query with the select clause conditional on the list box (CustId = Me.qryContactDetails) and set the data control source for the detail record to the query (=[qryContactDetails]![FirstName]). The query works as expected but the field in the form just shows '#Name?'.
I tried adding a field requery on the List31_Click() event using Me.[FieldName].ReQuery but that didn't seem to change anything
View 5 Replies
View Related
Jul 6, 2013
I have a form whereby a user for my database can be created. The fields required are listed below:
Payroll ID
Forename
Surname
Location
Manager
The last two fields are related to two other tables; tblLocation and tblManager, which are related to one another.Each location (there are three) can have more than one manager, however, a manager can be associated with only one location.I would like whomever is creating the user to only be able to select a Manager that works at a particular location, currently, I can select LocationID 1 and ManagerID 3, but ManagerID 3 works at LocationID 2.
How can I base the 'Manager' field in my form on the previous field's selection, e.g. 'Location'.Also would need to restrict the order in which the form's fields are enter, i.e., 'Location' before 'Manager'.
View 13 Replies
View Related
Nov 21, 2013
Upon the results which populate my list box what I am trying to do is allow the user to select various values from the list box and then hit a button which deletes the values from the table. My code is currently:
Code:
Private Sub Del_TsCorrection_tbox_Click()
Dim lngID As Long
Dim strSQL As String
[code]...
But it doesn't delete, infact it doesn't do anything. The list box values are still in the list box and if I look in the table they are still there also.
View 10 Replies
View Related
Oct 3, 2014
I have a list box (List25) that is generated from a query (qryThisBox). One of the fields in that query uses the criteria [Forms]![MyForm]![SelectedField] to filter.I also have combo boxes in the form that List25 displays in. Unfortunately, I want to requery the list box in the form, using a combo box that calls the same underlying table field, SelectedField.
How can I do this? Can I somehow dynamically change the criteria in qryThisBox to change when it gets requeried?How do I stack combo box requeries, eg: Combo Box 1 gets selected - does a requery
and/or
Combo Box 2 gets selected - and filters the results of the requery for Combo Box 1.
View 1 Replies
View Related
Aug 14, 2014
I have a table x where the field value is selected via a combo box in a form that is populated from another table z.
When I look in x, it appears to have correctly stored the text from z, not the ID number.
I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.
Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.
How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search.
View 5 Replies
View Related
Sep 15, 2013
Is there a way in which someone can select a table from a drop down list and then search that table for a record then move that record to another table.
For example.
User selects 'Mikey's_table' searchs for a record then move this selected record to 'Mandy's_table'
(all the tables have the same structure etc. identical apart from the name of the table and records within)
I have the list of tables that all the records will be on and the users will know which table the record is in, i basically need to know if there is an ability to search for a record over multiple tables then edit that record and move it to another Table.
I have tried to use a Union Query which works when searching but i cannot edit or move the record ...
View 1 Replies
View Related
Jul 20, 2014
I can't quite get my code right for populating one combo box based on the selection in another. I didn't create this Access database but I've been asked to add in a second combo box that is based on the selection of the first combo box.
My first combo box is for Rohe (the location of the Maori tribe) and the second is Iwi (the tribe name). So my first combo box (Rohe) has Dvic_ROHE written in the control source and its row source code is:
SELECT tblLookUpValues.DatabaseFieldValue
FROM tblLookUpValues INNER JOIN tblFormQuestionLookUp ON tblLookUpValues.LookUpID = tblFormQuestionLookUp.LookUpID
WHERE (((tblFormQuestionLookUp.FormID)=2) AND ((tblFormQuestionLookUp.QuestionNumber)=75))
ORDER BY tblLookUpValues.DisplayOrder;
The combo box for Rohe (tribe location) is populated by a table named tblLookUpValues. The second combo box for Iwi (tribe) has Dvic_Iwi in the control source. I created a table named tbl_Rohe_Iwi_lookup that contains ROHE (a list of tribe locations) and IWI (the tribes). There are 12 tribal locations and another 10-14 tribes in each location.
The form that the Rohe and Iwi combo boxes are located on is called frmDeceased. This is the code that I attempted to write so that the second combo box (Iwi) is populated with only the tribes that are in the Rohe (location) that was selected from the Rohe combo box. But when I run the query it comes back with no results.
SELECT distinct tbl_Rohe_Iwi_lookup.Rohe, tbl_Rohe_Iwi_lookup.Iwi
FROM tbl_Rohe_Iwi_lookup
WHERE (((tbl_Rohe_Iwi_lookup.Rohe)=[forms]![frmDeceased]![Dvic_Rohe])) UNION select distinct null, null
FROM tbl_Rohe_Iwi_lookup
ORDER BY tbl_Rohe_Iwi_lookup.Iwi;
View 5 Replies
View Related
May 20, 2014
I am trying to write a query that gets all the telephone hours from a worker done under supervision in a town (qry_svhoursbytown). I have a table that has the workers details, including town (Crisis_support_workers), then another table that has what hours each worker has done (Supervision_Hours). The town is selected from a combo box on the form (frmSearch), under the heading �total hours by location�.
I try and select Bathurst then press recalculate results and in the text box next to supervision hours, it says 66, but if you look in the Supervision_Hours table, there is only 11 hours for the workers that are in Bathurst.
In the query, I have the sum total of the hours field in the Supervision_Hours table, the Town field from the Crisis_support_workers table with the total selected as Where and in that criteria I have [forms]![frmSearch]![ComboTown], then I just have the town field displayed.
What do I need to change to get it to sum correctly?
I have attached the database below so people can take a look.
View 1 Replies
View Related
Sep 17, 2013
I have a tblCommets that has 2 columns in it. The first is an identifier (1,2,3) and the second column is a comment associated with that identifier. Is it possible to use a query to pull that comment based on a user selection? So, if the user selects 1 then the comment associated with 1 is pulled, but if the user selects 2 then that comment results.
Code : =Select[tblComments].Column(2)
View 1 Replies
View Related
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
Sep 25, 2014
I have a combo box which gets its values from sql server using a query which is called "get_query_reason", which works fine. Now I want to update combo box values based on a user selection, st string. Have written the code, but does not work:
Dim qDef As QueryDef
Dim Query As String
Dim st As String
Dim rs As Recordset
st = "SOV"
Set qDef = CurrentDb.QueryDefs("get_query_reason")
[Code]....
View 3 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
Feb 4, 2005
I want to be able to choose an item from a drop down list (based on a query i.e. distinct products) and have the item chosen to be automatically used as a parameter in another query.
Thank you
View 7 Replies
View Related
Jun 23, 2015
I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error
ODBC-- call failed ODBC Driver SQLBase.....
Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.
Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.
View 2 Replies
View Related
Jan 8, 2006
right, struggling here.
Basically I have continuous form listing all the standard letters sent out when I double click on a letter, I need to to run a query for me. The code I have put behind the button is:
If ([StandardLetterType] = "Homework") Then
DoCmd.OpenQuery ("qryHomeworkClearMailMerge")
DoCmd.OpenQuery ("MergeHomework"), , , , , acDialog, Me.[StandardLetterID]
DoCmd.OpenReport ("rptHomework"), acViewPreview
Else
'
end if
and in the query I am first clearing the mailmerge yes/no field out which works fine and then running the merge to put the yes back in the current record so the query looks like this
Its an update query with update to yes for mailmerge and under the standard letter ID i have the criteria [Me].[OpenArgs]
is this completely wrong tried the [Forms!][frmFullStudentReport].[standardletterID] but it kept popping the box up asking for the number.
On the form I have the standardletterID and its hidden but there.
Any help would be greatly appreciated
View 1 Replies
View Related
Dec 1, 2004
I have a form on which i make a selection. This selection then populates a query which in turn generates a report. The problem that i have is that when i go back into my form and make another selection the query, and therefore the report, holds the original data.
My question is how can i refresh the selection and therefore produce different reports based on this selection without exiting the application?
Many thanks for any help provided in advance.
View 4 Replies
View Related
May 1, 2015
I have a list box that is correctly listing resources from a table (tblResource). I would like to select one of the list box entry and run a query against the tblResource to show the information for that resource in a form. I have tried to use the lstindex with the control (lstResource) in the where clause of a query to accomplish this with no success.
View 5 Replies
View Related
Jun 3, 2015
I have a dashboard-style form that has a list box with tasks connected to a query with line items of those tasks.
I have another query that will only show the line item selected from the list box. This means the query will only have one line item.
I want to have a command button below the list box that, when clicked, will modify the solo item in the second query to change a yes/no field from no to yes.
How can I accomplish this? Also, how can I make a button that deletes the selected line item?
View 3 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
