How Do I Lookup Something From A A Different Table On A Data Entry Form?
Aug 23, 2006
Okay, for simplicity's sake, I have a data entry form.
It is bound to tableData.
Inputs are:
ProductID
Customer Name
ProductID is a combo box.
There is another table called tableProduct. In this table, is ProductID and ProductName.
For convenience sake, when a user chooses a ProductID from the combo box, I want it to lookup that ID from tableProduct. However, how can I do this when everything is already bound to tableData?
Thanks in advance.
View Replies
ADVERTISEMENT
Aug 23, 2006
Okay, for simplicity's sake, I have a data entry form.
It is bound to tableData.
Inputs are:
ProductID
Customer Name
ProductID is a combo box on the form.
There is another table called tableProduct. In this table, is ProductID and ProductName.
For convenience sake, when a user chooses a ProductID from the combo box, I want a separate textbox to lookup that ID from tableProduct and display the ProductName.
How can I accomplish this?
Thanks in advance.
View 2 Replies
View Related
Oct 8, 2014
I am creating a table for data entry. Three fields in the table are going to be Firstname, Lastname, and Address. I want the choices for data entry to be read from a master table which contains first and last names and town of residence.
That being said I would like the choice of Lastname to be all last names from the master table, and the choices of Firstname to be those from the master table but are limited to having the Lastname as entered in the previous field, finally I want the Address field to be limited to those records which match the lastname and firstname. I have been playing around with lookup queries for each of the fields to no avail.
View 1 Replies
View Related
May 17, 2006
Hello,
New to access :eek:
Trying to create a simple data entry form to get information and populate fields from another table. i.e. type in info in one field and the other fields are displayed in the form with information from another table.
Any help apprecated :)
View 3 Replies
View Related
Sep 20, 2013
I have a form that looks up office names and will automatically populate a field called office number based upon their selection in the combo box. I have a submit button on click event set up to run a query.
Now, here's where I am running into issues: In this query, I need to pull selected columns of information based upon a multivalue lookup field. This multivalue lookup field is joined with the table that the values populate from.
The form will only have one value stored in the txt box field, and I need to be able to search for all records containing that one value.
This is what I have for code:
SELECT FilePlan.FPName, FilePlan.Description, FilePlan.[File Code], FilePlan.GRS, FilePlan.Schedule
FROM Offices INNER JOIN FilePlan ON Offices.[Office Number] = FilePlan.OfficeNumb.Value
WHERE (((FilePlan.OfficeNumb.Value) Like "*" & [Forms]![RetCutOff]![txtOffNumb] & "*"));
Do I need to string multiple queries together to make this work, or is it just not possible?
View 6 Replies
View Related
May 8, 2015
I have a simple data entry form based on a table. However I have a few fields that I do a lookup in a field on the form from a query, and yes I know I should not have a lookup in the control source however, this is the way that I will be doing it on this occasion.
=DLookUp("[Salary]","[Salary Query]")
How I get the value from this unbound field to enter into the actual field in the table. Do I bring the actual field into the form and hide, and do some sort of after update, as I have tried and it does not work.
I have called the unbound field with lookup "Salary Level Base" and the actual field in the table is "Salary Base".
View 5 Replies
View Related
Oct 21, 2014
I am creating a form for data entry into my main table. It shows all the fields including a name, date, id number etc.
I am trying to put a subform on the form containing a query. The query has two filter parameters fed from two unbound boxes on the main form (date and name). Their purpose is to filter records on the query to just show those for one person on one day. It also calculates a duration based on a start and end time. The query results are ok.
I can't get the query to display properly in the subform.
Subform Properties/data/source object is set to the query name. I'm told i have to link the master fields and child fields but when I try I get the error 'can't build a link between unbound forms'. I thought all that was necessary to bind forms was for there to be a common field between the form and query? Both contain fields from the main table including date, name, start time and end time etc.
View 4 Replies
View Related
Jan 31, 2014
What I am trying to do is create a data entry form to an "order table" using 2 cascading combo boxes. I have created a data entry form based on a query. I can't get the cascading combo boxes to work properly.Here is the code:
Private Sub Combo0_AfterUpdate()
Combo2.RowSource = "SELECT L2_ID,L4_Element_name,L5_Category FROM qry_ord WHERE L3_ID = Combo0.Value;"
Combo2.DefaultValue = [Combo2].[ItemData](0)
Command4.SetFocus
End Sub
[code]....
View 2 Replies
View Related
Jan 5, 2013
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded
121/2/201310:00:00 AM3:00:00 PM
221/3/20132:00:00 AM11:00:00 AM
321/4/201312:15:00 AM11:30:00 AM
421/5/201310:25:00 PM11:00:00 AM
531/2/201311:00:00 AM3:30:00 PM
631/3/201312:00:00 PM10:00:00 PM
731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
View 4 Replies
View Related
Oct 8, 2013
I'm making a database to track projects/opportunities. I have a Project Data table, a customer table, and a contact table. A customer can have multiple contacts. I have already figured out how to use a lookup in the Project Data table to be able to select a customer for the project but how do I then select a contact based on that customer? I want to be able to assign a "main contact" in the project data table based on the customer that is selected.how to use the customer selected in the project data table to provide only the contacts for that customer to be selected from a combo box in the project data table.
View 2 Replies
View Related
Mar 5, 2015
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
View 1 Replies
View Related
Feb 9, 2015
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
<code>
DoCmd.OpenForm "PO_Practice Data", , , , acFormEdit, acHidden
Forms![PO_Practice Data]!PO_Name.Enabled = True
Forms![PO_Practice Data]!PO_Name.Locked = False
Forms![PO_Practice Data]!Practice_Name.Enabled = True
Forms![PO_Practice Data]!Practice_Name.Locked = False
[code]...
View 3 Replies
View Related
Dec 18, 2014
I am trying to save specific data from a data entry form to a specific depending on what the user selects from a dropdown/combo box.
So if the user selects "test company from the combo box, that data (from that form) will save to the Test company data table.
View 5 Replies
View Related
Nov 24, 2007
Hi All
I am new to this, hope you can help!!
I have 2 tables,
Customers and Routes
I want to auto insert data from Routes when I select the route Number from Customers,
the tables to auto update are
CruiseNumber:
CruiseName:
CruiseDate:
from
RouteNumber:
RouteName:
RouteDate:
I have read HELP in Access but I am a layman when it comes jargon. 'from the many to the few'??
Hope you can help....in 'Plain English'
View 6 Replies
View Related
Nov 22, 2013
I have problems understanding lookup tables, especially how to populate them with data. I'm working on an exams database, and have many such tables...
Table for Students
Table for Subjects
and a lookuptable for studentsubjects
Table for Classes 1, 2, 3, 4
Table for Subjects Eng, Math, Geo, Chem... 11 subjects in all
and a lookuptable SubjectClasses
Having inserted data for all subjects and all classes, should I then go ahead and Insert data for SubjectClasses? There will be 44 items!
Even more appalling, studentsubjects table will have (no of students * 11) for my small school, it will be 1,760
View 8 Replies
View Related
Feb 18, 2015
My aim is to have my forms open to a new record, which I have done, but if my users need to then update or edit data in previous entries, they can click a button that allows this.
My thoughts were to add a button, then put in code so that the necessary properties changed the form from displaying a new record or records entered since opening it, to showing all records in the associated table....
As an example there is a table for purchase orders. When clicking on this from the main menu form, it opens up the purchase order form to create a brand new PO. At times though, we will need to revisit an order to attach a copy of an invoice, or update the cost of whatever was purchased.
View 5 Replies
View Related
Sep 28, 2014
My db has multiple linked tables. As time passes and work on a project continues data needs to be added to a separate table that is linked to the first table to be populated.
i.e., Initial design data is entered first, when drilling is complete more data is added to a linked table, when drilling is surveyed yet more data is added to the second table.
What I want to be able to do is to query for a particular set of existing design data, display this on a form and on the same line enable data entry to other fields from other tables which are linked to the existing design data.
View 7 Replies
View Related
Jul 6, 2005
This has most definitely been asked before but I really cant find anything on the forum! Ive created a data entry form which has a save button which the user must click each time to save the record. However how do I refresh the form i.e. empty all data from the previous record without exiting the form. Ive tried to combine the undo macro but that simply deletes the previous record. Any help please!!!
View 3 Replies
View Related
Jun 18, 2013
A field in a table can be populated by a lookup up but it has to be done manually or with a form.A "new" table can be created with a query that matches the data.
Is it possible to skip these steps and create a field that automatically populates with the data from another table based on other common data?I can do this in Excel but not sure it can be done in Access.
View 1 Replies
View Related
Oct 17, 2013
We have a lookup table that has a list of CLIN numbers and their costs. The contract that governs those CLIN numbers and costs will be changing to entirely new numbers. Unfortunately, I still need to have the old and new CLIN numbers linked to the other tables.
Will I need to merge all the CLIN numbers into one lookup table, or can I do it from two lookup tables?
View 5 Replies
View Related
Dec 18, 2007
Hello all,
I just wanted to run this by some experts before I go mucking around in my db. Basically, I am creating a data entry form to populate with information about car parts, and the vehicles they apply to. This is what I am working with:
tblParts tblModelsParts tblModels
PartID ModelPartID ModelID
PartNumber PartID ModelNameID
PartDesc ModelID ModelYearID
I am trying to build a form that will allow me to enter new part numbers and descriptions into the table, and then have the user select (through a listbox?) all the vehicles the parts apply to. Is there a way to populate the junction table with each combination of partID and ModelID automatically? NOTE: tblModels is already populated with data.
I am assuming there would be a way to do this... A query of the junction table (and tblModels?) as the recordsource for the form, and then a subform with the tblParts as its recordsource?
I would have posted this in the massive thread I have going already... but I thought it might be nice to give Craig and Adam a break from helping me :o
View 14 Replies
View Related
Aug 22, 2014
i have 2 tables, "Items" and "Locations" and i created one to many relation from each table to 1 conjunction table, and Added one extra field called quantity into the conjunction table, so now the conjunction table has 4 Fields, ID AutoNumber and "Item" "location" "quantity" Fields, ok and i turn it into query then put the fields in form and use it as a data entry
im looking for a way, where if I choose an item From the item combobox, the locations where this item has quantity in it, would be available in the locations combobox, so i can add on the existing quantities...or to make it eazier, when i choose an item, it would let me choose all the location names which have this item next to it, so i may ( edit and add) the previously set quantity field
View 5 Replies
View Related
Jul 17, 2014
I created a table in MS Access using a Lookup & Relationship data-type. This means that my record cells call upon a particular table for values. It creates a LIST of values for that cell.When I query a particular value in this table I get every possible combination of the value. My query and code are below:
OR,
SELECT MainT.Content.Value, MainT.Source, MainT.[Entities Impacted].Value, MainT.Update,
MainT.[Divisions Impacted].Value, MainT.Announced, MainT.Effective, MainT.Stakeholders.Value,
MainT.[Ref#ID], MainT.[Issuing Agencies].Value
FROM MainT
WHERE (((MainT.Content.Value) Like "*" & [Forms]![SearchF]![ContentCB] & "*" Or (MainT.Content.Value) Is Null)
[Code] .....
This means that if I query STATES: "California", I'll get back every possible combination that exists with the name California in that particular field.The issue with this is that I'll get MULTIPLE primary key values rather than just 1. So, if one record, under STATES has California in it, but the other fields in that record have the Lookup & Relationship data-type, then every possible combination of that record will query instead of the multiple field-values form that you'll see in the table I am querying.
notice that my primary key (REF#ID) is repeated numerous times! This obviously causes problems with generating records and forms concerning information for 1 particular primary key. Is there a way around this problem other than specifying search criteria down as much as possible?! Perhaps there is some SQL code.
View 1 Replies
View Related
Sep 21, 2005
This is going to sound really stupid but I urgently need to have one form, where people can enter a product code and the description automatically shows in another field.
I have two tables, one for the form and another with all product codes and descriptions.
Presumaby I would need to make a query, which i've tried and failed miserably.
it seems so simple when you read the access help but i've missed something fundamental.
HELP!
Thank you.
PS I should have said my boss expects to enter data in one field - the product code - and the description automatically and instantly pops up in another field on the same form before leaving that particular record.
View 3 Replies
View Related
Oct 8, 2007
I have a table that lists county names. On the data entry form only the county names are displayed. However, I have another form that is used to build a custom query based on various criteria, of which county is one.
To make the table compatible with both both forms, the data source for the dropdown list for data entry is: Select * FROM county WHERE county <> "No Selection"
For the SQL form the code is simply: Select * FROM county The default value of the dropdown list is "No Selection". When dropdown list for county is set to "No Selection" the program interprets it as "select all counties". Other dropdown lists provide other parameters such as the project year, nature of the project etc. Each of these other dropdown lists also have a default value of "No Selection". The SQL form thus gives me the ability to mix and match several criteria. For example, it will display all projects in a particular year for a particular county or all projects in all counties that involve the construction of a residence.
My question, instead of physically having the string "No Selection" in the table itself, is there a way to embed the phrase "No Selection" in the SQL expression itself and still have it as an option on the dropdown list?
View 1 Replies
View Related
Aug 15, 2007
I'm new to access so I thought this would be easier than it is. I have a list of cities in a simple table . I want use this list (or a query of it) to create a validation rule for a field in a second table "City". How, either with VBA or some other method do I use the Cities database to validate entry. These tables are both in the same project.
Any help is appreciated.
View 2 Replies
View Related
