I have a Pupils database where I have a form with Pupil name and ID.I have a query with a calculated field and the ID and this works great.
I can display the associated calc on the form using a sub-form (I link the ID in the calc to the ID on the form) but I dont know how to get this value to display in a listbox.
I am trying to build a build calender to display customer orders on given days. I have the calender in place with intentions of having a list box on each day to display the orders.We have a daily report that is an excel file that lists these orders. I have linked this file to an access table in my db.
I am able to use this linked table to run a query and generate a report without any problems.When I try to use the information in this table to populate a list or combo box the only fields that will display are all numbers. If I use a field like "Customer Name" I get no information displayed. The data in excel and access are both set as text.
I am using MS Access 2010, I have three tables,
Policies (Id, Client Id, Address, Policy Amount, Paid)
Payments (Id, Client Id, Total Amount)
Payment Details (Payment Id, Policy Id, Paid Amount)
I have created a form "PAYMENTS" that include a sub form "PAYMENT DETAILS".
In the sub form control Policy Id is defined as combo box, and retrieving the list from Policies and Payments where Policies.Client=Payments.Client the query of Combo Box is as follows:
Code:
SELECT POLICIES.[POLICY ID]
FROM POLICIES
WHERE (((POLICIES.PAID)=False) AND ((POLICIES.CLIENT)=[Forms]![PAYMENTS]![ClientId]));
Only first time Combo Box display the Policy Ids according to the Client, but when I change the client and always displays the previous clients Policy Ids.
how to retrieve correct list of Policy Ids according to the Input Client in Payments.Client control
I have 2 forms. Information from one field links both forms (though it is not the primary key). Using a "open form" button I want the field on the second form to be populated with the contents from the same field on the first form, when a user selects the open form button. I imagine all of this is very doable in SQL, however I am using Access GUI to construct my database and have no time to learn SQL at this stage. Any ideas would be greatly appreciated.
View 1 Replies View RelatedI have an address box that I would like to auto populate with a double click event. Basically, if the location address is the same as the main company address, and there isn't any data already in the location field, then I would like the user to be able to just double click the location address and the field gets automatically populated with the main company address.
Private Sub Address_DblClick(Cancel As Integer)
If IsNull(Me.Address) Then
Me!Address = Me.CompanyName.Column(1)
End If
So this works fine if the main company address actually has an address in it. But if it doesn't then double clicking the location address produces a run time error 3315 for trying to populate a null value.
I have a combo box [Description] populated with items and a corresponding cost in [PriceRetail] within the same table. I require the price to be added into the price field once the item is chosen in the Description pull down. This is what i have, but the price field never populates.
---------------
Private Sub Description_AfterUpdate()
Me.Price = Me.Description.Column(3)
End Sub
---------------
The source table has 4 records in this order; ID, Category, Description, PriceRetail
In the Contacts Table there are two fields, the unique Contact ID key field numeric and the Contact Name. Like this:
Contact_ID; Name
1; Tom
2; Dick
3; Harry
In a form named "Selection" there is a Combo box that references the Contact table, and lists just the three names in the drop down (showing the Contact_ID is optional - I'm fine either way):
Tom
Dick
Harry
When the selection is made it will populate the field Contact_Ref the table called SelectedName.
Question: When the user selects the name I'd like the data populated in the SelectedName table to be the Contact_ID Value, and not the Name, so if Harry is selected the Value "3" would be saved.
I have got 2 tables:
TAT table with fields: ID, SPB, SET, DAYSIN, HRSIN
SETS table with fields: ID, SPB, SET, DAYSIN, HRSIN, plus a few other fields.
Is it possible to do the following:
If (SPB.TAT = SPB.SETS) AND (SET.TAT = SET.SETS)
then store
(DAYSIN.TAT in DAYSIN.SETS) AND (HRSIN.TAT in HRSIN.SETS)
Thanks for any help with this.
I've been trying to populate a field in a form with information, but only if the field is blank.
What I am trying to do is this.
I have a continuous form displaying all of our principals, however when our principals are on leave I want to populate the field with the "acting principal". I have set it up so the field grabs the acting principal first but it is blank for several schools. For these schools where there there is no "acting principal" I want it to grab the substantive principal automatically and populate the field.
I have tried Is Null, Is Not Null, NZ, IF, Where and Then statements but to no avail.
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
I have a report based on a query. I want to populate 6 Text Boxes with Dates from fields in another query. The date fields I want to add will be headings for columns that represent weeks (they change all the time so can�t be hard figures). The two queries are not really related by any common field. I am not able to get this working because the fields I want are not part of the query that is the Record Source for the Report.
Is there any way that I can do this? Can I change the record source of just the text boxes?
Hi,
I have a form with various list boxes that display options based on a query. When options are selected the text relating to them is populated in a seperate text box at the side.
The problem im having is when I select options from the list box I want it to store the selected options in the table that holds each record when the form has been filled out and submitted. At the minute it populates everything into the table apart from the options selected from the list boxes.
Does anyone know how to do this?
Thanks in advance
Edit: I have got the control souurce of the list boxes set to the correct field in the table.
created a main form called "frmPatientDemographics" that contains
txtFirstName (Text Box)
txtLastName (Text Box)
txtDOB (Text Box)
txtCountry (Text Box)
cboGender (Combo Box)
it's record source is a table called "tblPatientDemo" that are populated based
on the information place in the above text boxes
Here is my problem
I have a subform called "frmPatientLanguageSub"
Source Object......... frmPatientLanguge
link Child Fields..... PatientID
link Child Fields..... PatientID
When list box Multi Select is set to "Simple" and I add a new patient and click on
multiple selections in the list box it does not populate the "tblPatientLanguge"
But if I change the Multi Select is set to "None" it populates the "tblPatientLanguge"
for that patient
Any Ideas why access populates the "tblPatientLanguge" if I set the Multi Select property to "None" but not for "Simple"?
If so How do I make access poplulate when the table when muliple selections are selected in the list box?
how to auto populate list box based on two dependent combo boxes.
View 9 Replies View RelatedI’m developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc. There are 16 different “Events” in all.I currently have the following tables set up:
tblClaim
ClaimID
ClaimNumber
fkEmpID
tblEmployee
EmpID
EmpName
[code]....
What I need to do is create a form where management can choose two or more events, and calculate the average number of days between two of any of the events, for an employee, or all employees.I have created a crosstab query to change the values in the EventName field in tblEvents to field names, and the EventDate as values for the related EventNames. I created another query based on this query to do the DateDiff.
I created combo boxes on my form with the Row Source Type set to Field List, for a list of fields in my crosstab query. I’ve tried to use the following DateDiff function to get the days between the two fields selected in my combo boxes:
Code:
DateDiff("d",[Forms]![frmReportBuilder]![cboEvent1],[Forms]![frmReportBuilder]![cboEvent2])
But I get an error about unrecognized field name or expression for my combo boxes. So I added my combo boxes in the query parameter window, with a data type as both text and value, but with both I get an error “This expression is typed incorrectly or is too complex to be evaluated.” I also specified the column headings in the crosstab but I still am getting the “too complex” error.I’m pretty sure it’s trying to do a Datediff on the literal values in the comboboxes and not recognizing that I’m trying to specify field names.Is it possible to assign field names in DateDiff this way?
I had two fields in my table which I previously used in the form. I deleted the fields from the form itself but the fields are still listed under Field List and not even under any table...
Delete buttons doesn't work on it, and neither does backspace?
How do I remove it? Right click only gives me two options:
"Add Field to View" or "Edit Record Source" which returns an error saying this command cannot be used.
All,
I wish to create a custom form to link into my tblData table, however when I open a form in design view, the field list is not displayed. I have tried to select it from the toolbar but the button is not available ('greyed out'). I know I have done this before but can't remeber how.
Any help please would be great?
JB
i have created a form and its working correctly. however i have just been informed some of the fields needed deleting and different ones put in their place. so im just wondering if there is anyway to updated the field list so i can just add the new fields without having to recreate the whole form.
View 1 Replies View RelatedI (will have) a form which a user fills in to enter new data.
I have two fields,
"Branch" and "Branch ID" (Branch ID will not be on the form, just in the table)
Branch will be fed by a combo box with seven choices. I would like it so that when "Branch" is populated it autopopulates Branch ID with a code which relates to the branch, so for example
BRANCH BRANCH ID
Braintree BRA
Colchester COL
Is there a function that will populate a field with drop down menu based on two criteria?I want the the fields with first and last name to populate with drop down lists based on the employee code I have inputted in the form and job title from a query.
Path: looks at employee code from form > looks at specific job title from query > pulls out all first names in one field and all last names in another field with the same job title in drop down list from query
Employee Code: 100
Returns all employees' first names in first field with same job title:
Prince
Tina
Greg
Returns all employees' last names in second field of form with same job title:
Fey
William
Jones
Here's what the query looks like in datasheet view:
Code:
Location # First Name Last Name Job Title Employee Code
1 John Smith Technician 100
2 Jane Doe Manager 100
2 Greg Jones Engineer 100
1 Prince William Engineer 100
1 Tina Fey Engineer 100
I've been trying to get dlookup to work, but no luck. Here's one of my formula:
Code:
=DLookUp("[Last Name]", "[Employees tb]", _ "[Employee Code] = Form![Employee Code]" & "[Employees tb]", _ " [Manager]"
SELECT EmployeeCodeONLY.[Employee Code] FROM EmployeeCodeOnly;
SELECT [Employees tb].[First Name] FROM [Employees tb] WHERE ((([Employees tb].[First NAME])=[Forms]![Form1]![Employee Code]));
The first is linked to a separate table that only contains employee codes because query I am working with has duplicates due to multiple records.The second is trying to link both the table and query together to populate only first name.how to include the second criteria, job title, to refine it more.
I have inherited a database where technicians track equipment that has been repaired.In this database, there is a memo field where the technician lists all the part numbers he / she uses to repair the piece of equipment. This field was never reported against and was strictly used for reference. However, someone is coming to me now and asking that a report be generated with that information. Then she will separate those parts out on her end. because it is a free form field, the technician is not forced to put a comma between each part number. I was wondering if there is a way for the database to go through approximately 62,000 records and ensure that there is a comma between each part number? I was also wondering if there is way to program the database to automatically insert a comma after each part number.
View 5 Replies View RelatedMy table fields are as follows:
1. Issued? (This is a lookup field with the following lists "Yes"; "No"; "NA")
2. IssueDate
I created a subform bound to the table.I wish to accomplish the following on the form:
1. If "No" is selected on the [Issued?] field, I want the [IssueDate] to gray out.
2. If "Yes" is selected, I want [IssueDate] to be required.
I set Conditional Formatting as follows:
Expression is ([Issued?]="No"), condition is disabled.
When I tested it, [IssueDate] grayed out when "No" was selected. However, when I select "Yes", it still remained grayed out.
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'.
The main form has textbox & a Listbox with which I filtered a datasheet inside a sub form. Everything worked fine only the listbox [Discipline] is not working !! it cuase Run-time error: 3831. The multi-valued field "[Category]" cannot be used in a WHERE or HAVING clause.So how do I filter a multivalued Listbox field [Discipline] ?
Private Sub cmdSearch_Click()
'On erorr GoTo errr
Me.tblFLM_subform1.Form.RecordSource = "SELECT * FROM tblFLM " & BuildFilter
Me.tblFLM_subform1.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub
[code]....
:confused: I've tried everything I can possibly think of to get this to work. I've also looked at other threads and I still couldn't get this to work. The code for the list box in my form is Forms!Display_Engineering_Jobs_Report_Inputs!Repor t_Column1. I am trying to set the value in this list box as the input for the field on my query. This value also corresponds to a heading within a table I have. The information under the heading in this table is what I am trying to show in the query. Please help! Also, I'm pretty new to Access, so explain it as if you were writing a page in "Access for Dummies." Thanks!
View 9 Replies View RelatedI have a query with the following structure;
aDate(pkey) cost1 cost2 cost3 calc1 calc2 calc3
01/012014 ,,,,,,,,,1,,,,,, 2,,,,,,, 3,,,,,, 1,,,,,,, 2,,,,, 3
01/02/2014 ,,,,,,,,1,,,,,, 2,,,,,,,, 3,,,,,, 2,,,,,, 4,,,,,, 6
01/03/2014,,,,,,, 1 ,,,,, 2,,,,,,, 3,,,,,,, 3,,,,,, 6 ,,,,, 9
01/04/2014 ,,,,,, 1,,,,,,,, 2,,,,,,, 3 ,,,,, 4,,,,,, 8,,,,,, 12
fields calc1,2,3 are running totals of cost1,2,3
I expect/hope to first calculate the sum of a cost field and then minus the value of its corresponding calc field from a specific record.
result = sum(cost1) - calc1 selected record value
I want to select the calc1 record from a drop down list of the primary key. Which cost field is in the equation will static/defined as I intend to make a textbox for each field.i need to know the code to pick a field(and retrieve it's value) from a record selected via dropdown list.