Forms :: Populate Combobox With Data From 3 Tables
May 17, 2015
Is it possible to populate a single combobox with data from 3 different tables.
I have a table called observations (for observing deer), in that table is a field called Location, I have 3 other tables that list possible locations t_foodplots, t_stands and t_section.
I would like to have a combobox called location on my observations form that will allow me to select the proper location from one of those 3 tables, the choices would be something like this...
Section 1
Section 2
Section 3
Stand 1
Stand 2
Stand 3
Foodplot 1
Foodplot 2
Foodplot 3 etc....
The chosen value would be stored in the t_observations "location" field.
I have a table filled with company contacts and form with a combo box containing the contacts names.
When a contact is selected I want it to show their Telephone, Mobile and email address in text boxes below. But it only shows the telephone number and the other two fields stay blank.
what I'm doing wrong ?
I have this code assigned to the combobox:
Code: Private Sub MainContact_Change() Me.Text169 = Me.MainContact.Column(3) Me.Text167 = Me.MainContact.Column(2) Me.Text177 = Me.MainContact.Column(1) End Sub
And this is the row source:
Code: SELECT tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact, tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail FROM tbl_Contacts WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company]));
I've got an access front-end database running on Access Runtime on a Terminal Server with a back-end on a data server both of which are hosted by a remote company and accessed via Terminal Services. All is well and the database is working fine but because this is a cloud system I need a way that I can easily delve into the tables on the back-end without copying it down locally from the cloud each time (I've set up an "administrator" option so that this would be available to limited users). I've got the front-end forms working as I'd like but I have quite a few tables and I'd like to avoid creating a separate form for every table. I've been able to populate a combobox on my form with the list of linked tables and I'd like that when I choose a particular table from this list that the whole table is then visible within a subform below in case I need to manually intervene with the data for any reason.
I have a form based on a query.On the form I have 4 comboboxes.The combo boxes filter eachother without a problem (based on custom select query).Now I want after the fourth combobox value is selected, I want to populate a text field with a value from a different column from the master query (after the 4 selections only 1 value should be possible)I try to say this easy.Master query contains 5 columns:
- group - type - job - insurance - charge
combo1 selects group (and filters records) combo2 selects type (from remaining records and filters again) combo3 selects job (from remaining records and filters again) combo4 selects insurance (from remaining records and filters again)
[code]....
This works great and the dropbox only shows 1 OF EACH DIFFERENT record...If I add a text box and want to see the "charge" value, that I thought I could use the ME.text-code. But in order to do this, I have to add the charge column into the query of Combo4.the dropbox for insurance gives me multiple values that are the same. Is there any way to make this work?
I would like to create a combobox on a form in which a user has three selections: negative, positive, other.
If negative is chosen a textbox auto-populates with "none detected". If positive is chosen the text box populates with "positive" and If other is selected the text box populates with "unspecified".
In my quality control database QC techs will be entering in test results. Each quality control entry they put in will have a product and lot number it is related too.
My question is about making data entry easier for the QC techs. When two jobs are being run at once, the techs may have to alternate between which jobs they enter, meaning a different product and lot number. It would be frustrating for the QC techs to enter in a product code and lot number for every entry they make.
Can the lot number field be automatically populated when the tech selects a product? Basically, if the tech selects product A, can the last lot number value for product A automatically populate the field in the new entry?
Edit: Currently I have the "solution" of making two instances of the entry form with each lot number entered on the different one. This allows the QC Techs to use the CTRL+ ' to populate the last record, allowing them to quickly enter the product code, lot number, and date of the job they are working on.
Got a bit stuck in a database. I have a form based on a query. On the form I have 4 comboboxes.
The combo boxes filter eachother without a problem (based on custom select query)
Now I want after the fourth combobox value is selected, I want to populate a text field with a value from a different column from the master query (after the 4 selections only 1 value should be possible)
Master query contains 5 columns: - group - type - job - insurance - charge
combo1 selects group (and filters records) combo2 selects type (from remaining records and filters again) combo3 selects job (from remaining records and filters again) combo4 selects insurance (from remaining records and filters again)
Combo4 is based on following query: -column1 Insurance Total=Group by Show=yes -column2 Job Total=Where show=no Criteria [forms]![name].[combo]
This works great and the dropbox only shows 1 OF EACH DIFFERENT record
If I add a text box and want to see the "charge" value, that I thought I could use the ME.text-code. But in order to do this, I have to add the charge column into the query of Combo4.
If I do this, the dropbox for insurance gives me multiple values that are the same. Is there any way to make this work?
Trying to pull information from a text box on double click to populate a combobox on another form...
keeps coming up "type mismatch"
I call lngAccount as recognised on mouseover... ("Business Account" is the value I want)
I can't get it to work!
For info, the form I'm calling from is a subform located FrmMainMenu/FrmAccountsMainMenu and the control source is [Account]
the form I wish to populate in on FrmTransactions (it is a main form only) and the combobox is cboaccountselect - **I've seen the obvious mistake with no reference to this whatsoever, corrected it, and still not working**
need to create a database for work. there would be a way to select an id based on a previous table and have parts of it populate in this new table.
For example:
Table A - Webinars (Webinar ID, Webinar Title and Client) Table B - Sessions (Session ID, Date, Webinar Title, Time, Facilitator, Session Occurred, Reason, Conference Call) [Webinar Title I used a look up field and just select the corresponding Webinar Title and it works nicely] Table C - Users (User ID, Status, Prefix, Last Name, First Name, Email Address, Component, Role and State) Table D - Registrants (User ID, Session Date, Webinar Title, Attended).
So in Table D I would like to add Role and Component, so when I select User ID #1 the Role and Component Field populate with the info based on Table C.
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I am only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform.
I have 5 tables in access 2010, 4 of them have data in them and I need to populate the 5th table with all data from the 4 tables. I know you have to create a Union query, but i dont know the sql statement
Table 1 - data table 2 - data table 3 - data table 4 - data table 5 - combine data from table 1, table 2, table 3, table
I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.I have a table called Costs within which there are 2 fields
Purchase Currency Exchange Rate
I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency Exchange Rate
When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.I have tried the following and none work:
SQL Tried
1. UPDATE Costs SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
2. UPDATE Costs SET ExchangeRate = [Exchange Rate] FROM [Exchange Rates 13/14] INNER JOIN [Exchange Rates 13/14] ON Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
3. SELECT [Exchange Rate] FROM [Exchange Rates 13/14] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate.
Or is the alternative to put this into the calculation of GBP Unit Cost where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.
Both tables are joined with 1 to 1 relations based on apm_id as both of the fields are same. The table [APM] is pre filled with data. The idea is to list data from [APM] and collect data from user to be saved in [DATA] based on the info from [APM].
I created a form which list all the fields from both tables. The fields from table [APM] are only for displaying data and created a combo box based on [APM].apm_id to display the fields by chosing an APM ID from the drop down box.
A user adds a record and moves on to second record, completes the second record. Now if the user wants to list the first record by chosing the APM ID from the drop down box it only populates the fields from table [APM] but not the fields from table [DATA] which the user just added. Infect the fields related to table [APM] changes, but the fields from table [DATA] stays the same.
How ever if I click on the small arrows on the bottom which lets you cycle through records than it populates all the fields from both tables correctly.
How can I fix the issue so that all the fields would populate based on APM ID selected from drop down box.
I have form with an unbound combo box which is populated from a query
It should populate a field in a table which is related to another - however it only works when i query by id number (primary key) and not the text value i want - is it possible to query both so it returns say 1,option 1 - then when selected it will place a 1 in the table - which is related to option 1 in another table instead of just having to select '1' which will mean nothing to a user?
I have a form that I am populating a ComboBox via a Query. It works but sometimes it flakes out and does not populate the box.
What I want to do is run this through code.
I am a bit stuck. I think that I need to set the recordset, Create a SQL query, Run the query and push the results to the ComboBox. Think this should be on the Click event of the combobox.
Dont really know where to get started.
Does anyone have a rough example of: 1. Getting the recordset 2. A general SQL query (how to run it) 3. How to push the results to the ComboBox
I want to append/populate all data from one table to subform?
It is possible?
I managed to populate data from one table to another but my relations between form and subform isn't good, because record that is supposed to be filled as the "link master fields" is left blank.
Relationship: Customer ID is the primary key in Customer and is a foreign key in order table.
I need to create a Form with contains all the customer info from the customer table, as a drop down list. Once the customer is selected, all their orders should appear in a subform. At the minute I can get a form to work which shows all the orders but you have to go to the next record so see the order. For example it will say John smith and one order. The next record will be John smith again and their second order in the sub from.
I have used access in the past and I am fine with creating everything apart from the drop down. I am just a bit lost with the structure.
I'm working on a db that will track students for a summer camp. One of my fields in my student records (CamperInfo) is "School". Currently, the field is set up as a combobox in the CamperInfo Subform in the FamilyInfo form. I would like the combobox to be able to add schools if the school is not listed by a pop-up when the "add school" (or whatever) is selected.
Code:
Private Sub School_AfterUpdate() On Error GoTo Err_School_AfterUpdate Dim strForm As String If Me.School = "Schools" = True Then strForm = "Schools" DoCmd.OpenForm Me.School Err_School_AfterUpdate: MsgBox Err.Description End If End Sub
As you might have guessed, it doesn't work. no errors, it just doesn't do anything. I added the "Schools" data.
I have a list of staff that have a conflict of interest with a particular entity. As a result, these staff are not allowed to interview these entities.
I have a query that matches all staff with their respective entities that they have a conflict of interest with (CoI) and that is functioning correctly.
When the form loads to add an interview, there is a listbox that pulls all the people who are not allowed to do an interview with that particular entity. That is also working correctly.
I have a subform, that is a continuous form, which will allow the user to add staff, one at a time, via a drop down box. These people are stored in their own table with a FK Id to the interview table. This also works correctly.
How to filter the combobox on the subform to exclude the people in the listbox.
Here is what I have tried, loosely based on what I have found on Google and researching here. I am 100% sure it is not working correctly, but what I am missing.
The query the listbox is based on has 3 colums, the ID, the Name, and the business contract number.
Code: Private Sub Form_Load() Dim strSource As String Dim i As Integer For i = 0 To Me.lstCoI.ListCount - 1
[Code] ....
In the immediate window, I get the following result:
SELECT [staff] FROM lutStaff WHERE Staff <> name1 SELECT [staff] FROM lutStaff WHERE Staff <> name2 SELECT [staff] FROM lutStaff WHERE Staff <> name3 SELECT [staff] FROM lutStaff WHERE Staff <> name4 SELECT [staff] FROM lutStaff WHERE Staff <> name5 SELECT [staff] FROM lutStaff WHERE Staff <> name6
The issue is that the box is not filtering all the names out of the list it is built on. It is only filtering out the last name.
Obviously I need to save the results for comparison, but I am at a loss on how to do that.
I have a small form with a combobox that gets its data from a query.
I added code to this database to forward to me info about forms and controls in case they generate errors. Occasionally I get an "error = 0" on this combobox.
Since there are no events attached to this combobox then what could be causing this error?
I have a main form and a several subforms. The main form displays potential customer data and 1 of the subforms displays the advertisement that prompted that customer to call. 1 of the Advertisements is newspaper ads and for that one we want to track which newspaper.
The subform has 3 pieces of data. The advertisement type, advertisement date and a hidden field for the customer ID to link those to the appropriate customer. There is also a requirement for a field for the advertisement source for newspaper ads.
Data Structure: Customer Table CustomerID Customer demographic data as fields
sample data Customer 1 John Smith 1234 some st somecity somest somezip
Advtypes 1 google 2 Newspaper 3 Radio
specificAdv 2 7/7/2015 Daily Press
I have a combo box that gets the advtype Then another combo box that gets the adv date the issue I'm having is I also need the source and If I put it in the combo box it only displays when that combobox has the focus otherwise only the date shows. If I try to use a textbox to display the data I can only get the firstcolumn in the combobox and not the column I want. If I use the same query the combobox uses but only having the source in the select criteria I get the same data for every record. Since there are multiple advertisements and a customer could reply to any of them or all of them.
I need to pull data from a master project list to auto update other forms. When someone enters a project number I need it to pull the data for that project into another form so they dont need to keep typing details in. The other forms are trackers for our processes to complete the projects.
Let me know what you think...would a subform pull the info automatically somehow?
I have created a basic Volunteer Events and Hours Volunteered tracking database.
I created a form Volunteer Hours to allow an end user to use that form to record the volunteer and hours volunteered on a specific Event. There are two tables needed in the form Volunteer Hours to be used for the end users to record the Event, Volunteer, Date and Hours. The form has drill down fields from two tables:
I want to be able to select the Volunteer Event and have the event date automatically populate in the Volunteer Hours form date field when the Event is selected to eliminate the end user from having to enter the date in.
Also; I would like to be able to add more than one volunteer at a time to an event on the form, this would also decrease the data entry time.
