I'm populating an unbound ComboBox with a Query via the QueryDesigner. The value of the Rowsource depends on the value of another ComboBox at the main Form.
After several hours of trial and error, I came to the solution showed at the attached image ("works"); my concern is.. why does my first approach does not work via the IIF clause? ("not work")...
Hi guys,
I have a form with an option group. It has 2 options. The first one has a value of 1, the second has a value of 2. This option group is named "category".
Separate from this I have a query with several fields. In one of these fields, I need to enter a criteria that enters "LOE*" if the option group value is 1, or if the value is 2, I need it to enter "IDC*" in the criteria. My form that the option group is on is named "SelectWell". This is what I have tried so far:
Like IIf([Forms]![SelectWell]![category]=1,"LOE *","IDC*")
This works correctly if the option group value is 2, but doesn't work for the 1 value (LOE*).
Thanks,
Chris
Hi, I have the following situation:
I have two tables: userdata and superusers and a function username() which can be used for acquiring the username of the active user.
Table userdate contains a column 'username'. With the following SQL query the active user can obtain the records from the userdata table belonging to himself with a query like:
SELECT * FROM userdata
WHERE userdata.username = username()
Now, what I want to do is that IF the username (obtained using the function call (username()) exists in table superusers ALL records have to be shown, just with:
SELECT * FROM userdata
I want to do everyting with only one query, so that if the query is executed either all records from userdata are shown (when username is in superusers table) or only the ones that belong to the users.
Is there something like a conditional statement to create a conditional WHERE statement, or maybe another solution to solve this problem?
Thanks in advance.
Hi all,
I have a query which populates a form called EditPatientFrm, which asks the user to enter the patient UniqueID in order to pull up the record they want to edit from the TblPatient. However, I want the user to now be able to input just one of three criteria in order to pull up the record to be edited:
UniqueID
ChartNumber
PatientLastName and PatientFirstName (two fields)
since they may not have the UniqueID readily available to them.
Can anyone show me how to do this? I have tried to find answer to this one under queries and forms- no avail. Sure it's a simple thing...
thanks!
vrpres
How to get my query to run from criteria based on a combobox from another table. I have a field "prac" that has entries such as JF, MG, PM, RJ etc.
My query has the criteria for prac as [Forms]![PracReportPrint]![PracChoice].
PracChoice is the name of the combobox.
If I specify a valuelist for the combobox the query works. I created a different table just for a list of practitioners that contains only an index key and practitioners (JF, MG, PM, RJ, and so forth). When I set the combobox to table/query and select practitioners from my other table I get the correct list,b ut the query doesn't seem to detect what is selected from the combobox.
Hi,
could anyone help me please, I want to populate a combobox on a form with the values retrieved froma dynamic array.
Thanks in advance.
I am trying to improve the performance (loading time) of my main form. The big tip seems to be use SQL in VBA to populate subforms and comboboxes when needed, rather than when the main form loads.
I have a combobox configured for 2 columns, bound column = 1 and column widths set at 2cm each. After a struggle, I have managed to get the combo box to populate with SQL, but only for the first column. My second column appears when I click the down arrow but there is no data showing.
How do I tell the SQL to place the 'Identifier' data in the second column of my combobox? :confused:
Here is the subroutine populates the combobox when the subform loads:
Private Sub Form_Load()
On Error GoTo ErrHandler
'================================================= ========================
'Populate cboEmployeeID with SQL when form loads
'This is done to reduce time taken to load parent form
'================================================= ========================
'Define the SQL text
Dim stSql As String
stSql = "Employee_ID, Identifier, Section_ID, Office_Phone_Ranking, Role FROM [tbl_Prj_Details]"
stSql = stSql & "WHERE (((tbl_Emp_Details.Section_ID)=2) AND ((tbl_Emp_Details.Role)='Technical'))"
stSql = stSql & " ORDER BY tbl_Emp_Details.Office_Phone_Ranking;"
'Populate combo box with SQL
With Me![cboEmployeeID]
.RowSource = stSql
.Requery
End With
ExitHere:
Exit Sub
ErrHandler:
MsgBox Err.Number & " - " & Err.Description & Chr(13) _
& Chr(13) & "Error in 'fsubPrjDet01EDT1': Err 003"
Resume ExitHere
End Sub
Any help/pointers gratefully received :) .
Hi, I have been reading through the forum and found alot of really interesting stuff but cannot seem to find the answer to my specific problem. I'm sure it's easy but i'm sick of trying to figure it out now.
I am trying to build a form to display a shift rota. My tables contain daily shift patterns for 6 staff members for 2007. I want to build a form that has a subform displaying the rota for a particular analyst and a control in the master form to select which of the 6 staff members you wish to view. I have built a master form with a combo box. The combo box holds the names of the six staff and gets this data with a select statement in the Control Source field from the employee table. I constructed this with the wizard. I have constructed a query to drive the sub form. The query builds the rota, getting data from my tables but gives all shifts for all dates for all staff . I want to restict this in my subform to all shifts for all dates but for 1 member of staff. The member of staff should be defined by the selection made in the combo box in the master form. I then have a statement in the criteria of the query driving the subform to restrict the results by name to the name selected in the master form combo box. I'm sure this should work as i have used similar methods in the past. The statement in the query criteria reads like this:
[forms]![frmRota].[cmbAnalystSelect]
this is in the field of analystName, so the criteria should find all instances where the value in the analystName field matches the value in the cmbAnalystSelect control. However, the query always returns no results.
When i run the quey i have already opened the form (frmRota) and selected a value for cmbAnalystSelect, so the quey should collect the value from the combo box and filter the result according to the criteria but it seems to filter out all the results. If i run the query without first opening the frmRota form then i am prompted by a dialog box to enter a value for [forms]![frmRota].[cmbAnalystSelect]. If i enter a corresponding correct value (a valid name for a member of staff) the query returns a correct set of results, i.e. all shift patterns for all dates for the selected member of staff. When i have the form already opened i am not prompted for a value by the dialog box which would suggest it is at least recognising the form is open and that the control is there.
The last thing is that my ComboBox control is not bound to anything, when i view the control in design view it says it is unbound. I have nothing in the control source for the ComboBox. In the row source i have a select statement to pick out the employee names from the employee table, this then poulates the list in the ComboBox.
I hope this is enough info for someone to understand my problem and offer some advice, i can elaborate, attach screen shots etc. if needed.
Thanks in advance for any advice you can offer.
I have three comboboxes on a form – Fabric, Degree, Color.
The row source type for each is Table/Query. The row source is a Select Distinct query. I’m using a query so the list will populate itself. The choices the user makes in the comboboxes are used to filter called forms.
Would there be a way to include the word “ALL” in a drop down list in addition to the data generated by my query. If the user chooses “ALL”, the called page will not be filtered on that particular choice. I just created a record with “ALL” in those particular fields. That seems to work, but I think there might be another way.
Thanks
I have a client table with a field called location. On a reports form that I have, I want to make a combobox for all of the locations, so it could show all the clients from a particular location and also it would reduce the errors due to someone spelling a place name wrong. I could set the source to the location field in the table, but that would show them all in the order they come out and there would be duplicates.
Any help would be cool. Cheers
Bob
I am a relative newbie to VBA, and not very familiar with loops, but I need to add a loop to my function that exports a query with criteria contained in a bound ComboBox on a form. I've gotten my code to work fine without the loop, but I would like to export one file for each item "Team_ID" contained in the ComboBox without the user having to manually select and re-run the function each time. Here is what my code currently looks like:
Code:
Option Compare Database
Option Explicit
Public Function CreateQCChartsforReports() As Boolean
Dim qdf As DAO.QueryDef
Dim strSQLStatic As String
Dim BookName As String
Dim BookName2 As String
Dim intCounter As Integer
Dim cboCode As ComboBox
[code]....
I have a table of raw materials that have two fields, their stock code and longer description.
I have another table that is tracking the usage of these materials. I want both the stock code and longer description to be recorded in the usage table.
I want the "Description" field in the usage table form to auto populate when the user selects the corresponding stockcode from a combobox.
i need to clean up a date field (I hate dates in access!). The field in the table (imported from a spreadsheet) has records where users have simply entered a date in the correct format and then others where users feel obliged to add comments after the date rather in the 'Comments' section. Therefore Cdate alone won't work.
So far i have tried:
Code:
IIf(Len([TabDL].[PROMISE]<>10),#1/1/1900#,CDate([TabDL].[PROMISE])) AS PromiseDt1
The intention being to insert a holding date 01/01/1900 if the field contains too many characters to be a date alone. I've tried multiple variations of this code but keep getting errors across the board. Is my logic or Syntax flawed?
how to conditionally format query results? I have read another forum post that says that query conditional formatting is possible.This database keeps track of projects and their associated tasks & statuses. I created different queries depending on task name( i.e. assignment date, approval date, etc.) Now, with some of the tasks, if it is overdue, it needs to be highlighted red. If it's cutting it close, should be yellow. I can't figure out how to set up a conditional formatting rule to address this. So instead I've had to create to separate queries for "red" and "yellow" rules, and display them separately in the Dashboard form.
View 4 Replies View RelatedSo I have two tables:
users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...
user_change
primary key = user_change_id (AutoNumber)
user_id (Number) which relates to the user_id from "users" table
change_type (Text)
action_date (Date/Time)
...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
I have a simple crosstab query:
FirstName and Surname as Row Headings.
Date as Column Heading.
OnShift as a Value (Count).
These are from the table DailyActivityLogs. Also in that table is a choice field called Weather that lets you choose the conditions that day (Weather, Work, Part Weather, Subbed).
This is the report the crosstab query generates:
What I would like to do is colour the Count fields depending on what the weather was that day. It seems possible, as Weather sits in the same table, but the field Weather isn't in the crosstab query.
Can I do this?
Hello,
I've got a continuous form containing more than one record (5 to 30). I present the data of these records in text boxes. I need to color the boxes according to the data that they contain. This means that the same field can have different colors in different records. The total number of colors that is about 8 (correspondending with 8 different codes that can be in the data fields behind the text boxes).
Normally I would use Access standard conditional make-up function to implement the functionality described above. However the limit on the conditionl make-up criteria for each field is three (also if you use the formatconditions object in VBA) so that is not sufficient in this case. Is there any way to add more than three conditional make-up criteria to a text field?
Any help would be very much appreciated.
I have a form in which I want to capture today's date when another field on that form is changed from active to Closed. In excel I would write an IF statement but can't get my head round what I would do in Access to achieve the same result. basically it's if (field name 'status')=(criteria 'closed') then populate field (date closed with 'today's date').
View 7 Replies View RelatedI am trying to design a delete query that has an additional criteria needed.
I want to delete identical IDs in one column only if there are identical values in an adjacent column.
So for example, in the table below I want to delete the last row where the duplicate ID is "2700023" because the Code field has identical values, "LRAC". I do not want to delete the second row because the Code field is different for the row.
ID Code
2699974 BCC
2699974 SRAC
2700023 LRAC
2700023 LRAC
I am trying to use a combobox to select a value which then activates a query to return results.
Here is some details
I have to tables, one called "Customers" and the other called "Calls". These two tables are linked.
The customers is literally a list of customers with their contact details, but all I am in interested in is the "Company" Field.
The Calls table has a field called "End User" which looks up the company from the Customers Table.
What I am trying to do is create a search by Customer query, furthermore, I would like to do is to create a form with a drop down that looks up from Customers table, select the company and it returns all the records with that company...
What I have done
I have created a query that has the customer and calls tables included, I have dragged down the [Company] from customer table and then all the fields from the Calls table.
Then i created a blank form, inserted a combobox - Combo7, linked the box to the Customers table.
Back to the query, under the [Company] I have put into the criteria the following
Forms!sc!Combo7
Back to the form, selected the combo box, built a macro in the AfterUpdate, to run the query.
Tested this and it does not bring anything back, however if I put into the criteria Like [Please Enter Company Name], then typed the company name, it brings back all the records for that customer.
Am I missing something?, do I need to set the form control to the query, or even the combobox....
I have been spending all my today to fill a combobox dynamically, but have not been able yet.
I have a combobox and a pass-through query in access, which is working fine and fill the details into the combobox via data source. Now what I am planning to do is to update the combobox source as soon as value in a text box changes.
Here is the code I am using, but it is not working:
Dim rs As Recordset
Dim qDef As QueryDef
Set qDef = CurrentDb.QueryDefs("get_data")
qDef.SQL = "SELECT Initial + ' (' + Name + ')' uws FROM EM.dbo.UW" _
& " WHERE lob = '" & addSingleQuotation(Me.CMB_LOB.Value) & "'"
Me.cmbUM.RowSource = qDef.SQL
Me.cmbUM.Requery
I also used Recordset, but did not work:
Set rs = CurrentDb.OpenRecordset("get_data")
Me.cmbUM.RowSource = rs!uws
I have a combobox that I use to combine the employees first name and id number. So when you pull down the list you can select the employee you want and it is saved in the form. Now I need to create a query so that I can create a report on each employee separately. The query will not let me get the Employee Frist Name/ID from that saved field. I thought that what is saves in that field, you can retrieve it in your queries or reports.
View 3 Replies View RelatedWe 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?
I have a combo box that I want to populate using data of another access database...how would I do it? I tried searching...but I cant find any..
View 5 Replies View RelatedI have a report which I would like to apply conditional formatting on multiple fields. I would like the conditional formatting to be based on two types of criteria
First criteria would be contracts that start with the year 2014. I used the following expression which worked - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Joseph Steinbok" or "Adam Godson")
To this expression I would also like to highlight fields which contracts start with 2014 AND have a particular Director assigned to it. For this I used the following expression - And [DirectorInCharge]="Name"
On their own, both expressions are working but I want to combine them. How do I do this? I've tried the following - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Name") but then nothing is highlighted. I also tried InStr(1,[ContractNr],"2014") AND [DirectorInCharge]= "Name" - in this instance EVERY record was highlighted.
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.