Auto-fill / Update Field Simultaneously
Mar 11, 2013
What I have is two tables: Table A has one field (let's call it CustomerID). Table B has multiple fields about customer interactions (date, interaction type, etc.). What I want to do is this: have a form field that autofills the CustomerID field if that customer ID already exists and also allows the user to input a new CustomerID if it is a new customer (ergo updating Table A). The ability to have autofill activated for the subsequent record (e.g.: perform data entry on weekly basis and new customer arrived Monday then returned Wednesday) without saving the database.
I have the two tables connected: the CustomerID as primary key in Table A and it is related to a CustomerID field in Table B. For the autofill criteria I have set up a combo box lookup (Display Control setting, didn't limit to list) for the Table B CustomerID field. I've experimented with referential integrity as it would be a one-to-many relationship but that consistently gives me an error at data entry. I've spent 2-3 hours researching and tooling around and am convinced that it either isn't possible or is a ridiculously easy fix.
View Replies
ADVERTISEMENT
Jul 13, 2015
GOAL: select the IC number from the combo box and have 5 text boxes auto fill.
I have a table made for the IC number drop down. The columns represent the fields that need to be auto filled.
Making some kind of query to link the combo box entry to the text boxes? Some VBA where the control source is equal to my query? I have tried changing the text box control source to equal columns from my drop down but that did not work.
NOTE: the blue font in the first image represents the text boxes that need to auto fill when I select the IC number from the drop down. The second image is my table from which my combo box is generated. The acronyms are for the two tests on this form.
View 11 Replies
View Related
Mar 7, 2006
hi. this might be simple for others but i'm new with access. how can i automatically fill a YES/NO field referring to a Text field. which means, if the field is EMPTY, it will automatically say "No or False" in the Yes/No field, or vice versa.
thank you people of great minds and kind heart. more power to you all.
View 6 Replies
View Related
Mar 21, 2013
I've just returned to work after kids and started managing a large Access database related to health, back-tracking over many years.
Currently in filling a form we physically enter:
Apples 2.2
red apple 2.4
red apple cut 2.45
Oranges 5.6
Cucumbers 8.5
Is it possible to get field 2 to automatically fill with a number code due to the text typed in field 1?
FWIW, I'm confident at more basic Access e.g making follow on default value = Dlast("field""table") type stuff but the more complex stuff I haven't touched since Uni over a decade ago and you will need to be gentle while I blow away the cobwebs
View 3 Replies
View Related
Mar 6, 2006
Please help
I have 2 tables:-
1 Master Input Table.
1 Job Lookup table - (contains Job Number and Name)
When in-putting job details into the Master Input table i would like to enter the job number only and have the job name appear automatically without running a query, much the same as a 'vLookup' command in excel.
Is this possible?
Can anyone help?
View 3 Replies
View Related
Jan 2, 2013
I am trying to autofill a field based on another field but am not sure the best way to accompish this. I have a database that includes an employee table which has an Employee ID Number (EIN), name, address, etc. All my employees are listed in that table (full time as well as temps).
I also have an attendance table, which i have created a form to input the information. When a full time employee is off, they are covered by a temp. So, the form includes a field for the guard who is off, how many shifts they are off and which temp employee worked for the full time employee and for how many shifts. My payroll department needs the employee ID added to each employee (full time and temp) on the payroll report.
My Report is formated by Guard Off with a start date and end date to coincide with our pay periods. Multiple people enter info and then a bi-weekly report is sent to payroll. The report lists the employee off and then sorts by date and lists the employee working that date and shifts worked. (I hope that makes sense)
All the employee information is in one table. What I would like is when a name is selected on the form for Employee Off from a list box the EIN is automatically added to the EmployeeEIN field in the attendance table(the name in the list box is pulled from the Employee table). I also need the same for the Temp employee. When a name is selected from a list box for the temporary employee the TempEIN is automatically selected from the same Employee table based on the name selected.I can go into relationships and link the name in the Employee table with one name in the Attendance table, but not both.
View 9 Replies
View Related
Feb 17, 2014
I am new to using ACCESS. I have a database that has several tables which generate forms for my office to print for client files. I have these forms set to print based on a query that will only pull the ones that have a form printed field not selected, if that makes sense. What I would like to add is a field right next to the form printed field that allows us to enter the date the form was printed. I have added this field but now would like to take it a step further and have the date automatically fill once the form printed field is checked.
View 1 Replies
View Related
Jan 21, 2011
I am working on Access 2007.
I have two separate tables (called inventory and job log) and they have a few of the same field items (number, name, style 1, style 2, and area).
I would like the job log table to autofill the name, style 1, style 2, and area when you type in the number when logging a new job (pulling this information from the inventory table)..
View 3 Replies
View Related
Jan 2, 2013
Is it possible to look up 2 field to auto fill another field on my form?
Field 1 is "RiskLetter" this is autofill with "Risk" is user input.
I need to lookup RiskLetter and Risk and autofill from "Number" field. This to stop incorrect data being inputted.
So I need to look at the "Risk" & "RiskLetter" to come up with a "Number"
Would it be possible to use a Dlookup to look at RiskLetter and Risk to give me the score.
View 14 Replies
View Related
Jul 31, 2013
I have 2 tables named Candidates & Comments. CandidateID is the Primary key for Candidates and CommentID is the primary key for Comments.
I have CandidateID as a foreign key in the Comments table to link the two together.
I have a form "frm_CandidateMain" which lists a candidates details and a subform within it "frm_Comments" (in datasheet view) which lists comments that have been made for that candidate. These forms are linked by CandidateID as the Master/Child fields. This is working fine, and when double clicking the comment field, another form opens "frm_expanded" which is a pop up and has a larger field to that detailed comments can be written/read without the need to scroll along in datasheet view.
However, when double clicking on a blank line in the "frm_comments" field the "frm_expanded" form opens a blank form with no CandidateID selected, but I'd like it to auto populate the CandidateID from the main form that is currently open.
I've tried to do with with a Macro within Macro builder, but failed to do this.
View 3 Replies
View Related
May 7, 2014
I have been away for Access for a long time and having issue with making a query. I have 3 tables. "tablEmployee" has employee ID, first name, last name and sizer ID. "tblproduct" has product ID, Description and rate. I made a new table called tbldata which has date, product code, product description, product rate,employee id, employee sizer id, and lbs. I want a query when I enter the product id in tbldata, query auto fill the product description and rate. also same as employee.
View 3 Replies
View Related
Aug 21, 2013
I have a form that is populated by the User via a combo-box lookup. I'm trying to get an unbound field on the form populated after the combo-box is updated; the goal is that the field will have a value from a table (other than the form source) whose record can be found using the value from the combo-box.
Say for example, the User selects an OBJECTID of 100 and the form populates. There is another table in the database that has an OBJECTID field but that also has a different code or designation for the same asset. This is the value I want to pull into this field. I have tried the DoCmd.SearchForRecord approach, but Access doesn't seem to be buying it.
Is there a direct way to reference this value into the field, or do I need to consider making a query the source of the table and going that route?
View 3 Replies
View Related
Jul 3, 2013
In my form i have a field called DEPTH_KM and next to it a field called DEPTH_FT. Now i need it to auto convert the kilometers filled to feet and populate the feet field automaticlly for me so i don't have to keep manually converting it on every entry. I had it in excel (=SUM(A2*3280.8399)*-1)and it works but i need it to work in my Access database.
View 1 Replies
View Related
Mar 5, 2013
My database has four fields: ItemName, ItemNumber, Description, and RelatedItems. I would like to create a query that will search every Description in the database and return that data in the RelatedItems field. The query should fill the RelatedItems field with a list of every ItemName and ItemNumber that contains the ItemNumber of the current record.
View 2 Replies
View Related
Feb 16, 2013
I would like a field on a form to auto fill with a phrase after I have entered data into another field.
So if I type Yes and field A then Field B updates to Not Decided. If I type No in field A, then Field B updates to Not Applicable.
View 3 Replies
View Related
Feb 9, 2015
Access 2010. The first table is called Clients.The Primary Key field for the Clients table is an AutoNumber field called ClientID.The other two relevant fields in this table are ClientFirstName and ClientLastName. The second table is called Contacts.In the Contacts table is a foreign key field, bound to the ClientID field from the Clients table, and also named ClientID.
On the Contacts table, the ClientID field properties are set on the Lookup tab as follows:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT clients.ClientID, clients.ClientFirstName, clients.ClientLastName
Bound Column: 1
Further, in the Row Source's Query Builder, ClientFirstName and Client LastName both have Ascending selected as Sort criteria.
Everything works as intended: When entering data into the the Contacts table (actually, the data is entered via a form built from the Contacts table) the data entry person can click the drop-down on the combo box for the ClientID field, scroll through ClientID records, and see unique ID numbers sorted by first and last names. And the actual data for the field is stored properly as the unique ClientID number.
When entering ClientID data into the Contacts table (or form), is it possible to enable autofill based on ClientFirstName and ClientLastName, rather than ClientID? For instance, let's say we have a few clients named Joe Smith. It would be great to just start typing "Joe Smith," and have the field autofill the ClientID number for the first client named Joe Smith. The intention is to make data entry a bit quicker.
View 3 Replies
View Related
Mar 21, 2012
I am trying to auto-fill address info from a combo field. I'm using a select query on a table for the information. It appears to work for the first field (Firm Address1), but then stops working on all the other fields.
View 3 Replies
View Related
Dec 30, 2012
Is it possible to look up 2 field to auto fill another field?
Field 1 is "Exposure" this autofill with "Probability" is user input.
I need to lookup Exposure and Probability and autofill a score in to "Risk Rating". This to stop incorrect data being inputted.
I have a table with all the results combination in it.
Would it be possible to use a Dlookup to look at Exposure and Probability to give me the score
I tried a Combox with autofill. But the power to be would like it done without user input.
View 11 Replies
View Related
Apr 10, 2014
i have a user permission table.that consists of PermissionPK, UserFK, CompanyFK. I also want the username to be automatically filled in?So when a user ID is filled in on the table, it also fills in what that UserID's Username should be?As i need both the UserId and Username text for code that looks at the Environ username.
View 2 Replies
View Related
Aug 30, 2013
The table ProductInfo has an ItemID, and its corresponding Quantity.The table Orders has a PO column, an Item# column, a OrderQuantity column, and the column Item#CurrentStock which holds the Quantity of the selected item, for 3 items.I have created a form that has a ComboBox populated with the ItemID's from ProductInfo, that when selected populate the corresponding Item# in the Orders table.
I would love if the Quantity(stock) from ProductInfo would automatically fill the Item#CurrentStock field on the form when the ItemID is selected from the ComboBox.But all of the fields only relate to Item# from the first ComboBox!?!?I have tried =dlookup,
View 3 Replies
View Related
Feb 9, 2012
How do I get access to auto fill a text (supplier)? I realize that for a new supplier I would have to input the supplier name, however my question is what should I do for Access to enable auto-fill so that the next time I have to input that supplier it would auto fill for me. This is happening in a single column.
For example: In the supplier column, I typed Dell in the first row, Apple in the second row, HP in the third row. Now when I start typing 'Apple' in the fourth row, I want it to autofill 'Apple' because I have already input that once.
View 7 Replies
View Related
Jul 25, 2015
I am using a DB to, among other things, calculate the monthly salary of my service users. Obviously, I have to do it every moth. Most of the data from the previous month will be the same in the next month, except date. Even if some of it is different, having the previous data on sight calculate the new salaries.
Every month I have to select all records with the month date (say 07/2015) I want to copy and
(1) copy paste the records into the salaries table, and
(2) search and replace the new records' dates.
For example, records with 07/2015 with say 08/2015 (I have to replace downwards to ensure I don't change the record of the month I need to keep).
It can't be that difficult, but I have tried append and update queries to make this automatically (say by clicking a button) but so far no luck.
View 10 Replies
View Related
Aug 18, 2015
I am building a database to track contract of employees so that I can know which contracts are valid and which are expired.
My table has the following fields:
ID (Primary key)
Employee ID (Foreign key to link to the employee table)
Start_Date
End_Date
Status (Either valid or expired)
Challenge
I want when I enter the end date, the system checks the end date against the current date and fills in the status field with either valid or expired as appropriate. For instance if the contract end date is March 10,2016, the status must be filled in the word valid.
View 3 Replies
View Related
Jul 20, 2015
I've got one form that a user will start on, titled "Query Form" - Only one text box is here and it is titled "text0"
Upon hitting enter here, a new form is opened up titled "Time_IN_Form".
What I would like to do is have the value from text0 on the Query Form to automatically populate Emp_ID on Time_IN_Form so the user doesn't have to enter their employee number twice (only once via the first form).
What is the best way to do this? I have tried playing with global variables but didnt have any luck with that.
View 5 Replies
View Related
Apr 30, 2005
Hi after I searched the forum for a solution similar to my problem I did not find anything that worked.
I have a form that it is used for hiring DVDs.
There are fields among others like customer_id, copy_id and a hired field wich is true or false.
I also have a submit button which saves the record before you finish. I would like to make the hired field for this record updated to value "true" when I click the submit button.
View 3 Replies
View Related
Dec 27, 2005
Hi all
I've followed the instructions on this post:
http://www.access-programmers.co.uk/forums/showthread.php?t=85898&highlight=updating+field
But, unfortunately doesn't seem to be working for me.
I added the equivalant field to 'Hired' after I had previously made tables etc and started added code to forms etc.
When I type Me. a list of options available to type in appear, and 'Confirm' (my equivalant to Hired) does not appear, yet all of the other field names in the form associated to this table DO appear. Does anyone know how to get around this?
I've also set my field to be "Yes/No" - does this mean that the code is:
Me.Confirm = True
OR
Me.Confirm = -1
?
Many thanks
View 5 Replies
View Related
