Data Entry - Auto Fill Data For Exisitng Accounts In Records
Mar 9, 2006
First I would like to give thanks to all the knowledgeable folks here who have helped me with my DB to date. It is working and every one is very happy and I have learned a lot.
So now I would like to add some more functionality to this existing project.
My DB is for data input of customers for a drawing. It has the following fields: Id, account number, first name, last name, date/time, score1, score2.
I t is taking a great deal of time for the users to enter in hundreds of entries a day. Most of the entries are customers who are already in the DB. I would like to get the fields to auto fill the data for existing customers say after the account number is entered. So after you put in the account the name and any other pertinent data would shows up saving users from typing it in again.
The first problem I am having is that this is still a data entry form and I can’t figure out how to be able to see the account information and still add new data to the record? The new data is a daily score they get.
Second I haven’t figured out how to call up the customers information from just the account field.
I’ve googled this and haven’t found anything terribly helpful.
Its been a long time since I did dB work - stopped back in the days of dBase IV.
Still, using Access 2007 is not that difficult - except right now. I have 2 tables; one with customer contacts, the other with project details. Heres what I want - hopefully someone can help:
Within the project table, select from a lookup into customer contacts a name, which then auto fills in the fields for phone and email in the project table. I want to do this in the table or form, but have the data deposited into the table for sure!
I created a database to track a group of records (people). I couldn't figure out how to auto number the primary key to a random unique number that was less than 10 digits so I generated a list of random numbers and just use the next number off the list as I enter the next person which works OK- but I have to put that number on each table as the ID number to relate back to the Primary key and it seems that I should be able to have those auto fill with the entered number. Is it possible to have the number typed in "table 1" (people) primary key auto fill onto each of the other tables as an ID number??
I am new to Access, and I need some help for this project I am working on. Please let me know if my question doesn't make sense.
I have created four tables: Clients, Tenants, Property, Contract
Clients Client ID | Client Name |
Tenants Tenants ID | Property ID | First Name | Last Name
Property Property ID | Client Name | Contract ID
Contract Contract ID | Clients ID | Client Name | Tenant ID | Tenant Name |Property ID |
Is there a way that when I enter Client Name in the Contract table, Client ID would automatically come up and check against other fields in the table if the information entered in Contract table is in sync and relevant against to data entered in Client, Tenants, and Property table?
Please let me know if this is not clear and I need explain more. Thank you.
Is there a way to auto populate a table or form in Access from an Outlook email? For instance, if I send a survey via email and the survey is taken and sent back via email is there a way to have that data auto fill into the database or will the info need to be manually data entered?
I am creating a Access Data Base for Product Complaint. I have created several tables that share an auto complaint number (Primary Key). How do I make that auto assign? Below is the format of my Complaint number;
A Complaint Number is assigned using the format: PCYYMMXXX Where: PC = Prefix indicating Product Complaint YY = Last two digits of the year when complaint originated MM = Two digits for the month when complaint originated XXX = Sequential number starting at 001 for each year
Although familiar with CR, SQL and other such things. I have never had cause to use Access all that much.... Until Now!
My problem is easiest explained by my requirements....
I have a db table called Source. The data for this Table is populated by a form called Technical. Technical containes a series of fields (SO No, SO Line No, WO No etc etc). Some of these fields are lookup fields linked directly to the SO_Header Table in our main ERP software via and ODBC connection. The result is the SO No field is populated with a list of Sales Order No's as they exist within the ERP software. Within the Technical Form I have also the Customer Name.
This is where the problems begin. Within the ERP software we have two tables which I need to use: SO_Header and Customer. The SO_Header contains all the Sales Order info and the Customer table contains all the customer info. What I need to do is populate the customer name field in by db with the relevant customer based on the SO No chossen. However the Customer Name does not exist in the SO_Header table. Instead both the SO_Header table and the Customer tables contain a field called Cust_Code.
Now if I wanted to link these in Crystal Reports I would link from the SO_Header table to the Customer table using the Cust_code field. This would give me the corresponding Customer name based on the Sales Order No.
How can I get the correct Customer Name to appear automatically in my Access form based on a SO No chossen by the user?
I would realy appreciate any help on this! If you need me to clarify anything please ask.
Is there an easy way in Data access pages to do like you do in forms as far as using a dropdown list and when you choose a name/acct#/rec# or whatever, that the rest of the fields are automatically updated to match? Just curious.
If you need an example, I can provide one.... basically, I'm trying to get a data-acess-page to act like a form so someone without access can do a simple search.
I have a combo box that autofills a text box, this has duplicate values and I want to fill the text box based on the selection of the combo box.
Let me explain:The combobox is Suburb, the text box is for Postcode, the data has multiple matches for example FRANKLIN has a postcode match of 2913 in ACT and also 7113 in TAS.
From the combo box I select the record that matches 7113 but 2913 enters into the text box.
This is my code: In Row source of the combo box I have - SELECT [Australian Postcodes].Locality, [Australian Postcodes].Pcode, [Australian Postcodes].State FROM [Australian Postcodes];
In Event on change I have -
Private Sub Suburb_Change() Me.Postcode = Me.Suburb.Column(1) End Sub
how to change this to be based on the selected record from the combo box?
I am new to Access and I am trying to update our existing database. Presently our database is used to report incidents that have multiple subjects per incident. Is there a way to have a field recognize the data ie. the last name of an individual, and automatically fill in the remaining fields for that individual without having to fill in each field over and over again?
AutonumberPK RequestType (Lookup to TypeTable) RequestNumber RequestName
This table then Relates to a request detail table.
The question here,
There are two types or request (T1 and T2)
In my form to enter new Requests I will have a dropdown box to select the type of request.
The next field is the request number. If the type of request is T1 then the person entering the request will have a 6 digit number to enter. But if the request is T2 then I need to auto populate the field.
Is this possible? Or should I just build two forms, one for each type.
Also what would be the best way to auto fill in a number that follow this pattern.
05040001 05 = year 04 = month 0001 = the number of the request for this month.
I have an unbound control in data input form requiring to input a 6-digit number. I have put a validation rule restricting more than 6 digits. Most users prefer to enter, say 123 and the system can enter the 3 leading zero for them.
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.
My situation is this. I have 3 tables that I have imported from my mainframe system, between these 3 tables I have the data of product code, description,supplier code, supplier name, order method, and ABC code.
I am trying to create another table that I can capture daily Out Of Stock data for products.
What I would like to do is to enter the product number in the first field of my new table, and then the remaining fields will auto populate with the correct details based on the product data stored in the parent tables that I have imported.
I have a combo box that pulls account name data from tblAcctInfo. the combo box has an OnChange event which updates a textbox, txtAcctAddr.
when i have a new account that i would like to be listed in the combo box, i use a form, frmAcctAdd, to add a record to tblAcctInfo.
what i would like to do, is: 1) when i type a new value in the combo box that isn't in the list, have that string value pre-populate in the frmAcctAdd. 2) when i have added the new account info into frmAcctAdd and then saved the record, i would like the new value to pre-populate in the combo box, with the txtAcctAddr textbox also updated via the OnChange event (or maybe a different event is more appropriate?).
I have created a long version of this which requires a lot more user interaction (1-user typing in a new value into the combo box, 2-user RE-typing the SAME value into a data entry form, frmAcctAdd, 3-user saving the new record, 4-user re-clicking the combo box and selecting the newly added value) but i am trying to streamline the data entry with auto-populated fields.
How to fill values in a new record with data from previous record?
I've total 11 columns in a table and values in 3 4 columns are repeating for few rows before it needs to be changed eventually. I want to fill these rows with values from previous record.
I have been searching all day for a solution. This is my first time using Access so a lot of this goes over my head. I'm currently running Access2010. I have a form that I am using for when people take office supplies and we can generate a cost per department. All of the Tables, Query and Reports are set up already and surprising work. My form fields are:
Date Created (automatic current date) Quantity Item Code (Combo Box that shows Item# and Item Description) Initials Department (Combo Box that shows Dept# and Dept Description) RefCode Remarks
I am trying to get it to where when I enter a new record it will autofill with all fields expect "Item Code" from the previous record until I change it.
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
I need to alert the user of the database in case he/she enters a record that already exists in the database. If a person enters a key type and a serial number combination that already exists in the system and has status "issued", I need a pop up message to show up.
Am I missing some quotation marks somewhere in that DCount?
Private Sub SerialNumber_AfterUpdate() If DCount("*", "tblIssuedKeys", "KeyType = '" & Me.KeyType & "' And "Status = 'Issued'" And SerialNumber = '" & Me.SerialNumber & "'") > 0 Then MsgBox "This key has already been issued" Cancel = True End If End Sub
How do I display a more meaningful message instead of the cryptic error about having to enter data into blah blah blah. How can I trap that error and provide them a more meaningful message about entering data. I have tried the following;
Main form name frmPatientRecords Sub form name DentalRecords Subform Private Sub Form_BeforeUpdate(Cancel As Integer) If Nz(tblPatientDetails!recordid, 0) = 0 Then MsgBox ("sorry. Please complete the main record entry") Parent.SetFocus End If End Sub
I have the main form open up where the Data entry is set to YES so it opens in New Field. I created a button to change the value of form's Data Entry to NO. It works. Changed the form so I can navigate through entered fields. I have created a drop down to filter a specific field "Claim#" and it will open that entry.
The drop down only works if I set the default form's Data Entry to NO. Dropdown works and I can select Claim#. But if I open the form with DataEntry set to Yes, and I click on the button to change the value to NO, the form changes but the drop down field does not pull up the proper information. The button code is.
Private Sub EditButton_Click() Form_MainForm.Form.DataEntry = False End Sub
The Claim drop down has this event after update
Private Sub ClaimSearch_AfterUpdate() Me.Requery End Sub
I'm missing something. Like I said, it works without having the button change the value.
I am trying to open TabCtl86 forms in AddData mode if there are no records in a table for a user chosen form cboClientSearch. The tab control has three pages on it. At the moment I am getting an error saying "the form named '0' is either misspelled or doesn't exist".
I am using this at the moment; DoCmd.OpenForm Controls!TabCtl86, acFormDS, , , acFormAdd, acDialog
I have a form for creating new entries in a table. In it there is a textbox for a City field that I would like to have some kind of auto-complete based on past records in the database--similar to how Excel provides auto-complete options within a spreadsheet based on entries in previous cells.
In other words if "Binghamton" has been entered in a past record and the user starts typing "Bing" in the textbox, then "Binghamton" will become an autofill option. Is there a way to set this up?
I have a main form with several continuous subforms. Each subform consists of several listbox controls. I would like to require the user to select an item from the listbox before being allowed to move to the next record in the subform, and upon reaching the last record in that continuous subform, to require an entry there in order to move to the first record in the next cont. subform.
As an added bonus, it would be nice, though not necessary, to automatically jump the focus from one record to the next after data is entered. But my basic goal is to avoid skipping records.
Caveat: I cannot use the "required" option in the field to which the control is bound because that field has a default value previously entered using an append query. (The default value basically means "not yet entered" and is not one of the options in the listbox. I am using this because this field is a foreign key in the table, thus it must have a value in order to have a record with which to populate the subform.)
My apologies if this has been asked and answered elsewhere, but I've searched and, while I found a few related threads, they don't quite answer my question. Here they are for reference:
Thanks for any help! Also, if you are going to recommend VB code, could you be specific about how and where I should use it? I'm not afraid of code, but I'm definitely new to it...
I have a data entry form to add new records to an Access database file called Claims. An auto-incrementing sequence number (SeqNbr) needs to be kept PER YEAR. If the user enters a date the sequence number pertaining to the year of this date needs to be incremented. The first record within a new year of course takes value 1.Records can be added at random for different years.
A simple SQL-statement can be made to determine the new sequence number:SELECT max(Claims.SeqNbr) + 1 from Claims where year(this.value) = year(Claims.EventDate)...this.value meaning the value of the date control in which the user entered the date.I need to return the new sequence number to another field on the form in which also the COMPANY CODE, YYYY and MM from the EventDate, the new sequence number and the USER INITIALS are concatenated.
