I have a form that using a combo box to select specific record, sometimes we need to see all records, i would like if the combo box if left empty to return all records. I typed this expression but it returns no records when combo box is empty.
If i use this expression i get the wrong results when a Loan Officer ID is enterd; LIke([Forms]![FrmReports]![LoanOfficer] & * Example of wrong results, if Loan officer Id is 1, then it returns loan officers 1,11, 111
I have a feeling this is a common and probably easy question, but I couldn't find anything on it in the forums.
I have a main customer form with multiple subforms on it to keep track of my clients insurance policies and claims. I enter their contact data in the main form, and the policy data on one of the subforms. Then when they have a claim, I enter the policy information on the claim subform as well.
How could I have the combo boxes in the claims subform (which is storing data in a claims table) only show the policy numbers (stored in a policies table) assigned to that particular person? I would assume I need to somehow filter the policies query so it only shows records for that particular person, but I'm not sure how to do that on the fly.
This sounds like a cascading combo box issue but it isn't quite.
I filter my form using 2 unbound combo boxes which can be used in any order, both of which define text strings using case select. These strings are concatenated into a filter text and applied by a routine called in the after update event of either combo. All works well.
My question: How can I show all the filtered records in a third combo box so that users can easily select the record of interest from the filtered set?
I attempted to define SQL for each combination of the 2 comboboxes but it was getting ludicrously complicated so that got the heave ho.
After that, I attempted to use a recordset clone, but I couldn't get this to work: 'Clone record set for combo box rowsource Dim R As Recordset Set R = Me.RecordsetClone R.Bookmark = Me.Bookmark
'Populate combo box with recordset With Me![cboProjectList] .RowSource = R .Requery End With
Reading the Access help, it seems that a combo box can only be populted via query, table or SQL. Is there any way of using a cloned recordset or the me.filter to show the filtered form records in a combo or list box?
I am attempting to run a query that only captures the fields that are null so that I can run a report that shows outstanding information still required.
Essentially, each record is a provider. I have reviewers that complete an Access Form to certify each provider. The form consists of approx. 120 checkbox criteria. If it is checked they meet that criteria...unchecked they do not. The form is based off of 4 tables of which I am querying from.
I'm trying to get a query to identify only those criteria that are unmet for each provider. In other words...only show the unmet fields/checkboxes. From that I'll make the report.
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
I am having problems with setting up a set of combo boxes.
What I am trying to do is if combo Productline is empty then in combo PartNumber would show all products but if combo Productline has a value selected then in the combo partnumber would only be able to select the partnumbers in that productline.
Hi, This query will allow me to view payments that are made between 2 dates. I would like to know how to flip the query around so that it gives me the payments that have not been made. I think this would be described as returning the null values?
The SQL code i have at the momnet is: SELECT download20060602.Date, download20060602.Description, download20060602.Amount, Members.FirstName, Members.[Mid Name], Members.Surname, Members.[Memb No] FROM Members LEFT JOIN download20060602 ON Members.description = download20060602.Description WHERE (((download20060602.Date) Between [Enter Start Date] And [Enter End Date]));
Help. Getting stuck on filtering records. Have set criteria to select only userid's for department but now only want to show 3 records per user id if there are 3 records in the database. Some users may have thousands of records others may have none or one but just want to randomly select 3 of these for report if 3 exist. Any help much appreciated.
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed] FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
I have field in my table, which is required (in access, or not allow null sql server), and have defaults of 0.
I have a form with a combo box bound (thru query) to this field - how do I let the user choose something from the list and then decide they actually didn't want to choose anything - and so clear the list.
Hear I get an error saying the field cannot accept null - how do I just force it to accept 0 - ie no choice??
I have the following code attached to "mouse down" on a combo box. The code works just fine. All the values are available in the list. However, when I select a value, the combo box will not show it! why?!
The code Private Sub List_Reports_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) Dim obj As AccessObject Dim dbs As Object
Set dbs = Application.CurrentProject For Each obj In dbs.AllReports If UCase(Mid(obj.Name, 1, 6)) = "REPORT" Then List_Reports.AddItem (obj.Name) End If Next obj End Sub
Looks like a grate forum for access topics and thats exactly what I need now! I'm new to Access and are going grazy!!
I'm trying to add some functions to an excisting database that someone else has done.
The Form creates an report from an db containing a one per day status information from water and electrical meters. Filling in start and stop dates gives a nice report over all meters and their status.
Now I want to add a combo box so the user can select one meter or leaving it empty to get all meters. I can make it work when selecting one meter in the list, but leaving it empty shows none. Or selecting any don't help all of the meters shows up anyway.
I used the search engine on this forum and found similar problem, but couldn't figure it out. Tried many combinations of the IIF statement, but didn't help.
I apologise if this is a common question, but I didn't really know how to word the search!
I have a form with a person selector combo, and an asset selector combo. I would like the person combo to show everyone, but the asset combo to ONLY show those that havn't already been assigned to someone else.
I have a combo box set up to select a product and put the price into a text box. I am getting the cost showing up in the text box Therapy_Cost just fine, but the combo box itself does not display anything after I select something. If I remove the AfterUpdate, I do get the item displaying in the combo box, but, of course, don't get the price in the text box. Here are the details. Can anyone help?
Combo Box Name: Therapy_Type Control Source: Therapy_Cost Row Source: SELECT Purchase_Items.ItemID, Purchase_Items.Item_Name, Purchase_Items.Item_Cost FROM Purchase_Items WHERE (((Purchase_Items.Item_Type)="2")); Number of Columns: 4 (In this order - ID, Type, Name, Cost) Bound Column: 1 Column Widths: 0";0";1";0" AfterUpdate: Private Sub Therapy_Type_AfterUpdate() Me!Therapy_Cost = Me!Therapy_Type.Column(3) End Sub
I have two combo boxes that exist on a form record (i.e. two combo boxes for every record on the continuous form): mainCategory and subCategory.
Currently, I can choose a mainCategory and depending on the mainCategory, I have certain values available for the subCategory.
What I want to do is only show the values that have not been used for the subCategory (the mainCategory can always show its default value).
An example of what I mean is below...
The comboboxes and their row data:
mainCategory (combobox) ------------- A (combobox rows) B C
subCategory (combobox) ----------- for A: 1, 2, 3 (row for the selected mainCategory) for B: I, II, III, IV for C: red, blue
Sample scenario: 1. For the first record on the form, a user selects "A" from mainCategory and "1" from the subCategory for "A". 2. The user adds a new record to the form 3. For this new (2nd) record on the form, a user selects "A" from mainCategory and can only select "2" or "3" from the subCategory combobox. The "1" from the subCategory combobox is no longer available for choosing, unless the user changes the first record's "1" in subCategory to another value.
I have made a few cascading combo-boxes that dynamically change based on the data entered into the field above them.. Example:
A SKU is entered, the combo-boxes pull the information for that sku from one or more tables (using SQL Query).
Now... I am having trouble getting these fields to show the first item in the list of possible item specifics. Everything comes in fine, however, I need to form to refresh and repopulate the fields one the SKU loses focus.
I have a combo box which is populated when a user enters a value in a textbox and then hits a button However unless you click on the combobox dropdown arrow you cannot see if any values have been populated into the combo box i.e. i would like a value to show at the top so that it doesnt look as if the combo box is blank
Im kinda new to Access and only been using the "Access 2003 for Dummies" for learning and making small databases. So far I can find out how to do the things I want, but recently got stuck with a combo box feature. What im trying to do is select entries in my combo box so that it will filter the table and show all its contents based on that filter. Im not sure how to link the combo boxes the right way and it seems some VB coding is needed which I dont normally use. If anyone has any idea how to do it, tell me how! Thanks. BTW im using Access 97.
I have a form that will be used to select what report the user wants to run. I have a list of the reports in an option group so that the user can select which one they want. However one of the reports needs parameters that are supplied via a combo box. I only want to show the combo box if the user checks that report but I am not sure how to link the combo box to the check box. I know I can set the combo box to visible = true but am not sure what event to use to accomplish this.
Hi i have a combo box and have more than 30,000 value .and speed go to dead when i look up a record in Main and Sub Form. Combo box attached with. Code: SELECT bill2.invoiceno FROM bill2;
how can i speed up of combo box which have thousand of records i am using this code to look up a records in main and sub form(Orders & OrdersDetai) Code: Dim strSearch As String strSearch = "[invoiceno] = " & Me![searchbill] 'Find the record that matches the control Me.Requery Me.RecordsetClone.FindFirst strSearch Me.Bookmark = Me.RecordsetClone.Bookmark