Use Combobox From Form In Query
Mar 11, 2008
Hello,
I have a form containing a combobox with the values yes (1) and no (2).
In my query I have a date field. When the value of the combo is yes only the dates should be displayed. is the value no the the empty fields should be shown. I nothing is selected all fields should be displayed.
I tried this:
IIf(IsNull([Forms]![Form1]![cboDate]);
IIf([Forms]![Form1]![cboDate]=1;[tblITEM]![date_ID] is not null; is null);[tblITEM]![date_ID])
But without success.
View Replies
ADVERTISEMENT
Dec 12, 2006
Folks,
can someone help me on this? I am not sure why the select query inside this procedure is not working. Here is the that routine:
Public Sub temp()
Dim strDocName As String
Dim strTableName As String
Dim strTbl As String
Dim aot As Access.AccessObject
Dim strSQL As String
Dim rpt As Report
Set rpt = CreateReport
strTbl = Forms!frmSearchBoilerGuar!cboTypeOfGuar
For Each aot In CurrentData.AllTables
If aot.Name = "strTbl" Then
strTableName = strTbl
End If
Next aot
strSQL = "SELECT tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, " & _
"strTablename.memGuranItem , strTableName.memLDs FROM tblProjts1 " & _
"FROM tblProjts1 LEFT JOIN strTableName ON" & _
"tblProjts1.intProjectId = strTableName.intProjectId"
rpt.RecordSource = strSQL
strDocName = "rpt"
DoCmd.OpenReport strDocName, acPreview
End Sub
Basically, I am trying to select a table name from the combobox and then use that table name for my query. Then I want to use that query as a recordsource for my report.
Any help is greatly apprecited.
Shan.
View 2 Replies
View Related
Jan 22, 2014
We have a ComboBox on a form with the months: Jan, Feb, Mar, Apr, etc.
We have fields in a budget table named: Jan, Feb, Mar, Apr, etc.
We want to create a query that pulls the correct field based on the value in the combo box.
This is what I have that doesn't work:
MyField: [Query1].[SumOf] & Forms![Main Menu]![test]
How to do this?
View 4 Replies
View Related
Jun 6, 2013
There is a command button on my form that calls a filtered query. I'm filtering the query based on a combo box within the form using
[Forms]![frmAssignDwgs]![Combo15]
It works beautifully until I put the form as a subform in the Navigation Form I'm using. When I execute it from within the Navigation form, it errors out looking for [Forms]![frmAssignDwgs]![Combo15].
Is there another syntax I can use so this will still work from within the Navigation Form? Or another way to approach this functionality?
frmNavigation is the main navigation form
frmAssignDwgToPkgs is the subform that holds Combo15
I found another thread (which I can't link to since I'm new) and tried various arrangements of the following to no avail.
[Forms]![frmNavigation]![NavigationSubform] .[Forms]![frmAssignDwgToPkgs]![Combo15]
View 3 Replies
View Related
Mar 24, 2013
I created a navigation form on which I put a form call [frmAnimal Setup].
I then placed one combo box on the Navigation frm Header. I have bound it to its source and it actually queries the tbl and show the right info. However when I select one, it will not let me.
In addition, [frmanimal setup] will not allow me to select a breed although it does query the tbl and shows the right information.
View 1 Replies
View Related
Mar 28, 2005
This should be fairly simple, but it is driving me nuts trying to get it to work right. I feel like I'm chasing my tail.
Anyway, I have a query with three fields: "PersonnelID", "QualificationID", and "QualificationStatus". This is a query off of my large table for tracking qualifications; however, I am using a form with three combo-boxes for the same information. The first two combo-boxes (personnelID and QualificationID) are used to filter the query. This gives the result of one and ONLY one row in the query. A unique personnelID number, a qualificationID number and the qualification status of the record in question. I WANT to be able to automatically retreive the last field (qualificationStatus) from the query and display in the form (and then save in my new table - I know, shouldn't save calculations and such).
I have it working somewhat. I can select the first two combo-box fields and then when I click the third combobox for qualificationstatus, I only have one item in the dropdown menu and then I select it. So far, so good, but this only works for the first record that I am working on. If I do another record without closing the form first, the combobox selection for qualificationstatus still shows the previous entry. However, if I switch the form to design mode and then back to form mode, the combobox shows the correct value. I just can't seem to get the form to refresh prior to selecting the third combobox. I have tried "DoCmd.RepaintObject" after update on the second box and on focus for the the third box, but it still doesn't work unless I switch the views back and forth.
Any suggestions would be ever so gratefully appreciated...
Brian :eek:
View 1 Replies
View Related
Apr 26, 2015
I am trying to use a combobox called Manufacturer to select which table the combobox called Model gets it's rowsource from using the code below.
Code:
Private Sub Manufacturer_AfterUpdate()
If (Me.Manufacturer.Value = "Siemens") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SeimensTable"
Me.Model.RowSource = "SELECT Model FROM SeimensTable"
Else
If (Me.Manufacturer.Value = "Samsung") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SamsungTable"
Me.Model.RowSource = "SELECT Model FROM SamsungTable"
End If
End If
End Sub
But when I run the form and select Manufacturer. Combobox Model remains empty. tell me what I'm doing wrong?
View 5 Replies
View Related
Jan 13, 2005
How would I modify multiple comboboxes in subforms at the same time. For example.. In main form ComboboxA user selects 1992, Combobox1 in subform1 is also changed to 1992, as well as Combobox2 in subform 2.
Whats the best way to do this?
View 2 Replies
View Related
Sep 25, 2007
Hallo.
I need Help. I have two tables Contacts and Orders. And have a Form with data from table Orders and one One ComboBox with data from Contacts. So, when i chhose in combobox for example contact name, i need to see all indormation about this contact from table Orders. How to make this?
View 1 Replies
View Related
Nov 24, 2005
I'm not entirely sure if this is possible, however I'm pretty sure I have seen it done in the past. But I've been racking my brains and scouring google for the solution with no luck what-so-ever.
What I'm trying to do is display a query, where one of the fields will have a drop down box that you can select from multiple values. I dont want to give away much information about the database structure (sensitive data, and all that fun stuff I'm sure most of you are familiar with). So I'm going to make up a hypothetical situation here.
Table: Person
Fields: pID, pFName, pLName, sID
Table: Status
Fields: sID, sDescription
Here is the basic query:
Select pID, pFName, pLName, sDescription
FROM Person, Status
WHERE Person.sID = Status.sID;
Then when the query is displayed, I'd like the status description to be capable of dropping down a list of the other possible selections. Now I know this wont work on a standard query. So what I did was create the query, then a form that displays in a query view. I have the one field as a combo box instead of a text box. However I'm just having trouble linking it up, it just shows what the current value is but not the other possible options.
If anyone could point me in the right direction, I would surely appreciate it.
Thanks
View 1 Replies
View Related
Feb 6, 2007
I have a form with a combobox called fraShipDay. How do I reference the value of the user's selection in a query? I've tried using Forms![Form1]![fraShipDay] but it's not working.
View 2 Replies
View Related
Jul 27, 2007
Hi people.
I am creating a database for my dad's business. I am nearly finished but there is one more function that he needs from it and I have scoured the forums here and the web to try to find an answer to no avail.
Basically I want to design a query which returns a total VAT amount for jobs invoiced by quarter of a year. Looking at the forums etc I have found a way to query using the combobox where the values in the combobox are the table values, and I found a way of the query prompting for the start-finish dates, but the combobox that I want to use won't relate to any existing field headings (if that makes sense) ie option1 would be q1 (ie Jan, Feb & March) option2 would be q2 (April, May & June) etc. In my Invoices table, the VAT figure is already worked out for each invoice and is stored in a seperate field, so I don't need the query to work out the figure, but I just need help to set up the query/combobox so that it filters by 3 month's worth of dates as selected in the combobox.
Any help would be much appreciated. I got it to work whereby the query prompts for dates, but remembering the start/finish dates of the quarter seems a bit daft when these could be selected from the combobox.
PS I am quite new to this programming malarky so start from the beginning with any help :D
Thanks in advance
Nik
View 1 Replies
View Related
Dec 14, 2005
Hello everybody,
I am new to this forum. Currently, I am designing a database for the company I work at. It is the first time I use ACCESS and it is ACCESS 97 (!).
Now my first ( of probably many following ) questions :
I have a table with a key data field. Now I want to set up a combobox which lists all entries of a data field in that table ( but it's not the key field ). But, I do not want duplicate entries.
The SQL query for the combobox as created by the wizard looks like this :
SELECT DISTINCTROW NEW_ID.[Project ID], NEW_ID.[Work ID]
FROM NEW_ID;
I only want to display the "Work ID" entries, without duplicates. "Project ID" is the key. I already tried to change DISTINCTROW into DISTINCT but that does not work because every entry in "Project ID" is unique, only the "Work ID" entries can repeat.
If I omit "NEW_ID.[Project ID]" in the query, nothing is displayed...
So, what should I do?! Please help, many thanks in advance!!
Victor
View 5 Replies
View Related
Jan 6, 2013
I am new to access and was going through some tutorials. I am not able to run a query that takes value from a combobox and display the results based on that.
I used [Forms]![CustomerF]![StateCombo] under the criteria but its not generating the results..
I have also attached the db i am using ....
View 1 Replies
View Related
Apr 22, 2005
I have 2 comboboxes, the first one is called "activity", whereby I have 3 options to choose from, and the second is called "level".
When I click an "activity", for example Drawings, I want the "level" combobox to list a unique set of options for that category. and if I click on a different "activity" for example Planning, I want the "level" combobox to show a completely different set of options.
If anyone can give me any help on how to do this then I would be very grateful!!
Thanks
Angela :eek:
View 2 Replies
View Related
Aug 6, 2006
We are struggling with the following thing. We already tried to explain it in this thread (http://www.access-programmers.co.uk/forums/showthread.php?t=111533) but we still don't get it :(.
We have a table, named "Tblapa".
This table contains: ID, UK, Area, Tf, Tt and Number
We have made a query, named "Qrychoosearea"
This query gets his input from the "Tblapa"
(and thus, the query results in: Area, UK, Tf, Tt and Number)
Now we would like to make a form, named "FrmArea" with a combobox.
This combobox lets people choose an Area of their interest out of the list of given Areas. Then they have the press an "OK" button
Upon clicking on the "OK" box we want to generate a REPORT based only on that specific Area (and the related UK, Tf, Tt and Number ofcourse) and thus not showing all Areas.
How do we have to do that?
As we are just beginners in Access and have no feeling for VBA (yet), a detailed explanation or a link to a website who explains how to do this (with images or so) would be much appreciated.
Greetz,
Math
View 10 Replies
View Related
Sep 14, 2006
Okay I have 3 forms. I have one form which is called frmEndOfTrial on this form there is a combo box which has two values--> Accepted and Return for credit.
I have two other forms called frmAccept and frmRFC. I have dragged those two forms onto the frmEndofTrial form and set there visibility to No.
What I want to happen is that when the user clicks on the 1st or 2nd option that corresponding form opens below the combo box?
I've tried a few things here but can't get it to work. I'm pretty limited in knowledge on access. Right now on hte combo box events I have this code for the After Update event
me.sfrmRFC.Visible = True
me.sfrmAccept.Visible = False
I know this is not what I want and not finished cause if I click on either accept or RFC it just brings up the sfrmRFC. How can I do this???
View 8 Replies
View Related
Nov 9, 2006
Hi
I have a minor problem.
I have a table and form with a carpool.
I would like to be able to display history by date with the help of the combobox which car you had last week.
But I don't know how to go about it.
I have attached a DB and would be glad if someone can but their magic touch to it.
Mikael
View 3 Replies
View Related
Dec 14, 2005
I have a form that is bound to a table of chemicals. On the form I have a combobox that displays records from another table of chromatograms for each chemical. There may be none, one, or multiple chromatograms for each chemical.
On the form I also have a graphic to display the chromatogram (.jpg file).
I want to have the graphic of the chromatogram update upon the change of chemical on the form. At the present time, I have the graphic display from the after_update event of the combobox. This works fine.
Is this the best way to proceed, or should I use a subform for what I'm trying to do.
Any help wouuld be greatly appreciated!
Thanks
View 1 Replies
View Related
Jun 6, 2005
:( I have such problem : my query works right, but in ComboBox I got results with wrong sort.
Query results is:
Street 8
Street 8a
Street 10
Street 10/12
But in ComboBox data views such:
Street 10/12
Street 10
Street 8a
Street 8
Please help me!
View 2 Replies
View Related
Sep 19, 2007
Hi all,
I am hoping this is a simple dumb mistake I am making. I set up a two tables and an append query. The two tables are: first a table of employees and second a table of terms. I am using the last column in the table of employees to send a list to the terms table with an append query.
With that everything works fine. I even created a form of the table of employees so it could be edited on the form instead of the table. It still worked fine. The problem came in when I change the text box on the form that governed the term field to a combobox. I used "With me.combo...." in VBA to add a yes or no selection to the combo box.
When I try to run the append query then I am told their are lock violations and validation rule violations.
Any thoughts?
View 1 Replies
View Related
Feb 26, 2005
I have a basic design question that I am not sure how to address.
I am trying to build a simple data entry database with a form to take input from the user, store the values in a table. Once the data is gathered into the table I want to use this table to print a report of each record (entered using the form before).
To achieve this objective, I made a form (frminput) with some text fields. Most of the fields on this form are Bound fields to a query (qrymaster). One of the field is a Combo box (whose value is shown from another table). I have designed the RecordSource of this Form to be a Query (qrymaster).
This is a basic Data Entry form where the user selects the Combo box item and based on what he selects, some of the fields in this Form gets pre-filled. The rest of the fields on this form are bound to the query "qrymaster" and the user has to type these fields manually.
Now, I want a Save button here that would save all the values on this form to the table "tblmaster". How do I do this efficiently keeping in mind all the normalization laws on the database?
At this time, behind the Save Button, I have included a SQL statement to insert all the field values into the table "tblmaster".
I am sure there is a better way to do this. Can someone point me in the right direction please?
Thanks.
View 3 Replies
View Related
Mar 4, 2005
Hi,
I am trying to build a form that allows the user to INPUT stock as it arrives. This is simple with a product form that shows all the products in the table. I just go to the quantity field and change it.
But, I want to create a 'search' function in the field. I want to allow the user to type in the Product number. Then the Product name, Product Price and Quantity is AUTOMATICALLY displayed.
The user can then change this value (items in stock).
Thats pretty much the jisst of what I wanna do.
Can anyone help?
Thanks in advance.
View 7 Replies
View Related
Jul 15, 2005
I have a combobox that displays information about a current record on a form (much like a subform would show all information related to a certain record). So for example, there is a user and the combobox would list all the users applications. Now, when the record is changed (via the navigation buttons) I want the combobox to requery, but I don't know where the query should go. I tried putting the requery under the primary key's afterupdate method, but no go.
Can anyone think of a solution? Do you understand my problem?
Thanks in advance.
View 3 Replies
View Related
Dec 14, 2005
I would like to have a form with a combo box open and then when a company is clicked on i would like for my report to open showng all those listings. I have the query and the report and the form but how do i get them to all work together they are not playing nice.
View 12 Replies
View Related
Feb 19, 2006
Hi everyone
I use Access 2003 and saw Ken Higg's sample database to filter records in a form using a combobox. I managed to replicate his settings and wondered about an extension to the principle, but I don't know if it's possible.
In the sample, a value is selected using a combobox and all matching records are filtered so it's possible to scroll through them. Is it possible to have a second (or more) combobox on the form so it is possible to filter on one or another criterion? This would mean one combobox filling the other one and I realise that the one that Ken provided is unbound.
The reason that I'd like to do this is, for instance, if there are records with Surname, 1st, 2nd and 3rd lines of the Address and Postcode, I may wish to filter records by entering the Surname or Postcode. Each of these fields would have to be displayed in a combobox. As the sample is set up at present, only one of the Surname or Postcode has to be selected via the combobox and the other is a textbox.
It may be that what I'd like to do isn't possible - or someone may have come up with some other idea. I'd prefer to have the search/filtering on the form, rather than using the buttons on the Form View toolbar.
Thanks for your patience.
Juan
View 7 Replies
View Related
