I'm hoping someone here can help me out with existing code or at least point me in the right direction... In one of the databases I am developing, I need to set up the ability for one field in NEW records to be autofilled with the next value from a table or query.
Details
My database includes the following objects:
tblSupervisors - supervisor information (including an indicator if they are 'active')
tblCases - case information including name of supervisor assigned to review this case
frmNewCases - allows data entry of new information to tblCases
If someone enters a new case, the Supervisor field would (in an ideal world) automatically fill in with the name of the next active supervisor from tblSupervisors.
Example
The active supervisors in tblSupervisors are Joan, Mark, Lisa, and Gary.
Case #1 is entered and the Supervisor field is automatically pre-filled as Joan.
When Case #2 is entered later, the Supervisor field shows Mark.
When Case #3 is entered later, the Supervisor field shows Lisa. However, because the person entering the case knows Lisa is on vacation, s/he can click a button and Lisa's name is replaced with Gary.
Finally (for this example), Case #4 is entered and Joan is assigned as Supervisor again.
Whew!
Okay. That's it. (I hope.) Any and all help would be GREATLY appreciated!
First of all apologies for the lack of proper terminology I'm a novice Ms Access user and I like to thank everyone in advance for trying to help.
Ok here is the situation:
I have two tables, NewJobs and Contacts which have the following fields.
Newjobs -------- JobID (AutoNumber, Primary Key) JobName JobDate JobDescription JobOwner (Linked to table 'contacts' via LookUp)
Contacts --------- DisplayName EmailAddress Department Extension
Ok basically what I want is to have a form based on table NewJobs which will allow me to enter new jobs into the database. When I get to JobOwner a drop down list linked to 'Contacts' table will show me all the data from column 'DisplayName' and allow me to select it (saves time on typing). I have already done this and its not a problem.
Now I would also like in the same form to have additional fields from table 'contacts' such as EmailAddress, Department and Extension which will autofill with the right information soon after I select a JobOwner from the drop down list.
So for example if I select 'Joe Bloggs' Access will automatically fill the additional fields in the form with Joe's information (department, extension etc) from the Contacts table.
I hope all this makes sense. Thank you all for your support.
Hi! My first post here! I am quite an amateur at database programming.
I am developing a form to input data about paint samples into a database which represents a sample library.
When the user starts to input data about a new sample into the form, they first have to choose a 'base metal' from a combo box on the form. Based on this 'base metal' selected, after all other fields have been filled in, i want to generate a 'catalogue code' for that record (The 'catalogue code' is an alpha-numeric code). Both 'base metal' and 'catalogue code' values are entered in two fields in the table.
for this i want to have a command button 'Get Catalogue Code' which runs a code to compile the 'alpha-numeric' text using two different tables. I want this text to be inserted into the 'catalogue code' field in that particular record and a message box informing the user what that text is.
I can figure out the code to compile the text and the message box. What i dont know is how to insert the text into that field in the table.
I am willing to provide any more information, including my database to anybody who can help! Thanks in advance!
I have two tables tblCList and tblCode. The primary key of tblClist is an autogenerated number which is the foreign key to the tblCode.
The tblCList has another column names sClist.
whenever I import values from Excel into the tblCode, I need Access to look up the text values from the sClist in the tblClist and insert the appropriate number corresponding to the entry in tblCode.
I have a Form with a combo box that takes in formation from a table and auto fills it. But i cant get that autofilled info to enter into the main table.the one that the form is based off of. I tried to use an append query to move the info from one table to the other. It didnt work. does anyone know why this error pops up i cant see anything wrong...
Concrete Pumper Database Set 0 fieldsto Nulldue to a type conversionfailure, andit didnt add 3 records due to key violations, 0 records due to lock violations and 0 due to validation rule violations...
in detail what im trying to do is this. database is for remote control concrete pumpers.
I have a form based on a table. In this table i have fields such as model #, button 1 configuration-button 12 configuration date entered, date shipped etc.I created a form from the table and Instead of entering each of the 12 button configurations I would like to have that entered automatically when a model # is selected from a combo box. So i made a table with just model # and button configurations and set up a combo box on the form to match. now i need to get the button config from the button config table to be entered into the MAIN TABLE when entered into the form by combo box. oh man...i dont know if im too far gone or what...this may not make sence so please give it your best...I appreciate any input at all. I thought i could do it with an append query and a macro but now i dont think so.
Help. I know just enough about Access to get myself into trouble.
What I want to do is have a form where I enter a low work order # and a high work order # with a series of yes/no questions and then have that autofill a table with work order #s incrementally from the low value to the high value and attach all of the yes/no answers to each work order #.
How do I get the table to autofill from low to high work order #?
Thanks for any help in advance and type slowly. I'm not very bright.
Hi all,Sorry if this has been asked before, but I've used the Search function and can't find exactly what I'm trying to achieve.The Current SetupI have a database that I'm now trying to expand upon by adding extra features. One of these features I'd like to add is the ability to, firstly, know how many records in the entire datasheet have not been officially closed (i.e. have their 'rmaclosed' column empty), and, secondly, to have a list autogenerated that shows all of these records and allows the user to double click an item from the list and have that record open.Now, to make life easier, I should point out that I already have a successfully working search function in my database. Clicking a button from the switchboard opens up a form that allows the user to enter full or partial text into a box, then, at the click of another button, the main window of the form will automatically fill out with a list of all those records which match the entered text. The user can then double click anything from this generated list and the appropriate record will be opened.Working on the assumption that the code for what I want to achieve would be near-identical to this search function code, I have replicated both the form and code, and am now trying to adapt it; it is this adaption I'm having trouble with.The Current SituationTake a look at the attached image. That is how my form looks to the user when opened.Now what I really, really want to achieve is for that central window to be automatically filled out with the listed column headings of any record stored in the table maindata that has a Null value in the column rmaclosed when the form is opened. However, I don't see any option for 'OnLoad', so I'm willing to compromise and just have the user click a button.You'll see the button there for 'List RMAs'. When clicked, that should populate the main window with the records that have not had their 'rmaclosed' column filled out. The code behind the button - which is where I'm having the problem - is as follows:Private Sub cmdSearch_Click()Dim strSQL As String, strOrder As String, strWhere As StringDim dbNm As DatabaseDim qryDef As QueryDefSet dbNm = CurrentDb()strSQL = "SELECT maindata.ID, maindata.rmanumber, maindata.company, maindata.rmalogged, maindata.initials " & _"FROM maindata"strWhere = "WHERE"strOrder = "ORDER BY maindata.ID;"'THIS IS THE BIT I'M HAVING TROUBLE WITHIf IsNull(maindata.rmaclosed) ThenstrWhere = strWhere & " (maindata.rmaclosed) Like '*" & maindata.rmaclosed & "*' AND"End IfstrWhere = Mid(strWhere, 1, Len(strWhere) - 5)Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrderEnd SubThis code has been adapted from that which - successfully - runs my filtered search engine. The problem is that my search engine runs a query based on text entered onto the search form by the user, whilst the form I'm trying to write should simply list every record in the main table, filtered according to the state of the 'rmaclosed' column.As I said in the opening paragraph, although it's not essential it would be really nice to have a counter somewhere on the form that would show the total numebr of records in the filtered list.
I have 2 tables: A and B with field names: ID, Title, Link
and one table C with: ID, Title
The Link in each table "points" on another table.A points on B that points on C.(When you fill in column Link in table A or B, a Lookup table is used so that I can select one of the titles in the linked table).I want to add a new field name called Position in table A and B. The Position is supposed to describe the cells place in the tree.
Example: if A with ID 1 points on B with ID 2, and B with ID 2 points on C with ID 3 then in A Position data field it would say: "3.2.1".I would like it to autofill Position when I add a new title.
I'm creating a table with times of the day from 08:00 to 17:00 in 15 minute increments.
I'm going to use the table as a lookup to make it easier to enter an appointment time.
I don't really want to type them all by hand - does access have an equivalent to excel's drag and autofill a sequence? is it just as easy todo it in excel then cut and paste it?
I essentially created an account since I can't seem to find a straightforward answer much anywhere else (plenty of hints on auto-filling forms- but that isn't particularly conducive to my specific need on this).
I've attached a blank copy of my database.
The immediate concern I'm having is that I want to be able to autofill data in the invoice table based on the customer table and the products/service table.
*I want to be able to select a first name [or other primary key if necessary] to fill in the last name, company, address, city/state/zip, phone, fax, cell/alt phone, and email automatically... that is without multiple dropdown selections or input to those sections at all.
*I want prices to fill in to the 'cost of product/service X' so that I may use it for other calculations in the invoice table- as well as to make forms from it directly.
I have a table which includes several columns. Two of these columns are important for this update query I wish to do. In my table, I have two columns like this:
API# Current Operator --------------------------------- 1 | 1 | 2 | Bob 3 | Joe 1 | Mike 3 | 2 |
I am wondering if there is a way to run an update query which would automatically fill in the blank fields in column 2. The numbers in column 1 will always correspond to the same person in column 2.
I have an Access database with several tables and a multitude of subforms which are displayed on a single master form. The subforms are used to facilitate data entry. In several of the tables there are fields which are related and I would like to have some of these fields updated based on the results entered in the related field (i.e. the answer for one field depends on the other). Field 1Field 2 [facing][DISC_CODE] un3 up1 dn2
Field 1 is a simply a description of the basic dataset. This field is already set-up on form as a combo box that allows the user to choose one of three options. Field 2 is a code number used by another piece of software to identify a particular symbol. It is a new field being added to the database. There are 200 codes that identify a wide range of symbols for different types of data and I don’t want to have to look them up when I, or my assistants, are doing data entry.
My question is this; is there anyway to have the DISC_CODE value, Field 2, automatically entered in the table when the value for Field 1 is selected in the combo box on the form?
I am new to Access (using Access 2012) and All I am trying to achieve is to autofill the field [Frequency] from Table FullList based on the value of [Frequency] from Table Courses using a DLookup code used to update the FullList form. The code is not updating anything! It is frustrating! Frequency is a number (integer) and while Course is a text. The figures of my Access database are below.
I have a grades_table with data regarding a grade a person gets. It contains like Name, Date, Grade, Grader_Name, Month, Year...
I have another table (grader_table) that contains the Grader_Name, and how many Grades that grader needs done in a given month (i'm using two numbers to indicate month and year).
What I would like to do is I have a form where all info for the grades_table is entered. When the query is run, I would like to have the month and Year field use the date given to auto fill numbers.
So I picture it as like 6/5/07 is given, so the query records the date, but also stores the 5 and 7 in other columns. I tried this in my query for i think its title - Month: Month([Date]) but it says there one too many ")".
I have a feeling I may also need help with the actual retrieval of a months score since it will cross tables, but this is the big question for now. Any help is appreciated.
1. After my relationship table is built, is it possible to have some tables automatics fill in according to what another field in the table is inputed? EX: I have a Ordertable(orderID,itemID,Itemdesc), and a Inventory table(ItemID,Itemdesc), When you select an Itemid to put in the order form, the order Description from Inventory would appear in the Itemdesc of the order form.
Is this possible? If so how?
2. In tables, is it possible to make a cell = to something? So like if I wanted to add a subtotal and a final total after tax, I could use = * .08 or something?
Can I fill all cells in a column that dont contain any data with the same value?
I have a table with about 1000 records, I have a column called "brand", half of the records contain some data but the other half dont and I really the ones that dont to contain the text "none".
I am trying to self teach myself and I am sure I am going about his wrong.
I have a form with a fillable text box called ProcessCode, a drop down box called CostContainerSize, and a fillable text box called disposal cost. Their are dozens of possible process codes and container sizes of 55,30,15,5,P,Y etc... I want the disposal cost box to auto fill based on Process code and disposal cost. I wrote the following in the control source IIf([ProcessCode]="LF" and [CostContainerSize]="55","$65.00") THis works to auto fill, but obviously only for that particular set of parameters. I have tried adding "or " comments to include other sizes for that particular process code with limited success. I have tried IIf([ProcessCode]="LF" and [CostContainerSize]="55"or"30","$65.00"or"$50.00") among other variations.
I created a form from a table (maintbl) with a combo box. The combo box retreives information from another table (model#tbl)and autofills it in the fields below. the information that is autofilled does not automatically get entered into maintbl. Theres probably a good reason for this but i cant figure it out. Anyone?? Im not very good with code but if thats what it takes thats what it takes.
Hello everybody! Im new in Access as well as in this Forum! Glad to be here :) Im currently developping a small application in Access 2003 and here is my problem: I am trying to autofill the value of the field OrderID in this form : "FRM/SupplierID/CurrentYear/OrderNumber" Where the SupplierID is charged once the SupplierName is selected from a combobox and the OrderNumber entered from another textbox. Note: SupplierName values are from other table: Suppliers
Need some help with a table, or possible more to do with a form design..
I'm looking to autofill in a field depending on data that's in the previous field.
E.G. I have too fields.. One for employee and one for department.. I want the department field to automatically select the department whenever the employee is selected from the drop down list.
It's probably a simple query or something I need to do..
