Create Multiple Records / Fields Simultaneously
Jul 13, 2012
I am extremely handicapped in Access 2010. I am currently trying to create a database for a small company I am interning in. One of the things I would like to do is to update or create multiple fields in different tables simultaneously.
To explain, I have two tables, Production Activity in CY 2012 and Production Activity in $ 2012. Both tables have a field "Date of Activity." In addition to some other fields, Production Activity in CY has a field "Actual Production in CY" and Production Activity in $ has a field "Actual Production in $".
Now I had initially created two forms, each dedicated to one table, however my supervisor wants to use only one form to update both fields.
I also want to know how can I connect/link both of the Date of Activity so that if one changes, the other one changes too. And when I run queries, both "Date of Activity" fields should appear as one (when I want to run query of let's say the maximum or minimum production each month).
View Replies
ADVERTISEMENT
Nov 17, 2014
I currently have a pharmaceutical lot database set up in the following format:
MFGData (table w/Manufacturing Info)
QAData (table w/ Quality Assurance Info)
QCData (table w/ Quality Control Info)
PASData (table w/ Process & Analytical Science Info)
SCData (table w/ Supply Chain Info)
[Code] ....
Each table has a corresponding form for data entry in each area. The tables were subdivided in this way in order to limit each department's ability to edit the data of other departments. The only field common to each table is the drug lot number, or "Lot #" (which is the primary key of each table).
I wanted to make it so that when Manufacturing enters a new lot number on frmMFGData, it automatically creates that lot number in the other 4 tables. This process mirrors our actual real world business process, where drugs are manufactured and assigned new lot numbers by our manufacturing team, and then other departments simply reference those numbers when doing their part.
To accomplish this, I went ahead and set up 1 to 1 relationships between the various tables using their "Lot #" fields, establishing referential integrity and enabling cascading updates. However, when I attempted to enter a new lot number into frmMFGData (the manufacturing form), it didn't seem to appear in any of the other tables. If I edit an existing lot number and change it to something else, the change does carry over to the other tables, so I know that the cascading updates are working in some capacity.
If cascading updates cannot "cascade" new records, then is there any other way to accomplish this?
View 13 Replies
View Related
Apr 9, 2006
When I click the new record command button which I created I get a new record, but the form has a subform and this starts with no records. How can I program this button to not only create a new record and the form to which it relates, but also to create a new record on the subform contained within it as well??
View 3 Replies
View Related
Jun 25, 2013
I recently made a database that we will use to allocate appointments between a team of up to 30 in 2 different sites. I have tested it with 10 people in the same site using it and have had no issues so far.
One of our managers wants to know whether it is likely to cause issues with network usage/congestion. When we introduce it to the other site. I told her it is unlikely, but went to our IT department for their opinion. I got a reply simply stating that Access is not intended for this sort of task, and is only designed to have a couple of people using it at a time.
I'd like a second opinion, because I don't think that answer is even close to correct. I'll describe briefly how it works and what our set up is.
The database has 2 tables, one that stores the details of the clients we call (7 fields), and another that logs each contact attempt (6 field) and ensures no 2 users get the same entry. I have split the database, with the backend saved on one of our networked drives, and the frontend will be distributed in an email. (at present it is just an unsplit database stored on a network drive, but I don't think this will work well when 2 different sites are using it).
I think data throughput will be minimal, but I don't know how I'd go about checking that. Would you expect any issues with this being used by 30 people simultaneously?
View 3 Replies
View Related
Jan 16, 2015
I have three identical Combo Boxes which show the names of the classes in our school. They are just three copies of the same combo box. What I want is this: I choose three different classes from these three boxes. Then I want a button at the bottom which when pressed, should show the records of these three classes separately. Preferably, as three columns.I have made the Combo Boxes, but am stuck at the button to perform these actions.
View 11 Replies
View Related
Aug 14, 2013
i essentially have 2 table:
1. Table BIC
A list of codes that will be updated monthly, which will be the basis for querying the second table. Approx 100 rows of data.
2. Table Original
A data file obtained from IT where i'll need to sort it to find any codes that are including in Table1. This includes approx ~ 10,000 row of data.
** note, the "BIC" from "Table BIC" can appear in any of the 5 BIC columns in Table Original.
What i need to do is create a query that will:
1. Search the "BIC" from "Table BIC" in all 5 columns of "Table Original".
2. Where it has a hit, it will create new table - for example, the first row of table Original includes the BIC "ABC" in the "BIC 1" column. A query would create table "ABC" and place this whole record (all 8 fields) in new table "ABC". No modification needed.
3. Where two (or more) BIC's from "Table BIC" appear in one record in "Table Original" - the result will only need to be placed in one of the new tables (really doesn't matter which one). For example, Record #4 includes the BIC "ABC" in field "BIC1" and the BIC "DEF" in the field "BIC4". Therefore, a new table would be created (either ABC or DEF) to capture this information.
View 4 Replies
View Related
Jun 19, 2012
I have three tables:
tblClient (for basic client information),
tblDischarge (for client discharge information), and
tblAppointments (for appointments that are added to an outlook calender).
All three tables are linked using the IDNumber from the Client table. I have a form where a user inputs a discharge date for the client.
Once that date has been added I need to add 5 records to the Appointments table for different followup times based on that discharge date (such as one month, two months, three months, etc.).
I have the code working to add an individual appointment to outlook but not 5 different ones at one time. So the question I have is how to add the 5 records based on the one discharge date?
View 1 Replies
View Related
Jan 5, 2012
I am relatively new to Access and have a simple database which I use to generate Publication numbers for our new publications. I would like to have a text box and button on a form that will generate duplicate records of the current record the same number of times as specified in the text box.
E.g. If I need 6 copies of a record I would like to enter 6 in a text box, click a command button that will generate 6 duplicates of that record.
The code in order to do this 'on click'.
Table = Publications
Form = Publicationsfrm
Text Box = Lines
Command Button = Submit
Primary Key = PublicationNo
I have tried the following code but it is not working.....
Dim i as Long, myRS as Recordset
Set myRS = CurrentDB.OpenRecordset("Publications")
For i = 1 to Me![Lines]
With myRS
.AddNew
![PublicationNo] = Me![PublicationNo]
.Update
End With
Next
Alternatively if I have a duplicate record button that uses a macro is there a way to run that macro the specified number of times in the 'Lines' text box.
View 14 Replies
View Related
Mar 19, 2014
I've developing a QA/QC database for testing chemical products and I'm stuck on the best way to continue with adding multiple records by using one form.
I have two tables:
tblProductSpecs with two primary keys, "ProductName" and "TestName"
tblResults with three primary keys, "ProductName", "TestName", and "LotNumber" and a number field named "Value"
A product can have multiple tests associated with it, e.g:
ProductName - TestName
XXX - Density, pH
ZZZ - Density
YYY - % soluble, cloud point , freeze point
This is my tblProductSpecs table
I want to store the "Value" of each "TestName" of the "LotNumber" of that "ProductName" in tblResults by a form. (All TestName values are number values).
I want a form where I can select "ProductName" and have the "TestName"s displayed for that "ProductName". Then I want to store the value of that "TestName" for that "ProductName" and "LotNumber" in tblResults.
The problem is that each test per product per lot number is a record and I can't figure out a way to create multiple records from one form.
View 14 Replies
View Related
May 26, 2013
I'm trying to create a form using a combo box to populate multiple fields and tables.
I've created a text field to display the added information using this format:
=Comboboxname.Column(x)
in the text box control source field, and this works for display purposes.However, I need it to populate this data into a field on a table.
For example:
My combo box looks up data that has 2 columns, Part Number and Description.
The control source for the combo box is "Part Number". And that populates the part number in the "Main" table no problem.
The text box I created using the above format in the control source populates the field in the form, but not the "Main" table.
Is there a way for the other (description) field to also populate the "Main" table as well?
View 5 Replies
View Related
Jun 28, 2006
I have developed a Debt Management program for work but I am stuck on a feature that we need to add to it. Basically I have a table which holds "planned future transactions". These are ad hoc transactions that need to be added from time to time to the clients budget. For example the client might have $500 a quarter for school fees for the next two years or he might receive a bonus every six months for the next 5 years. Currently we are putting these in manually but it is tedious and is causing complaints. The feature I require is to be able to add a record such as the school fees, nominate that this fee is paid every three months for the next 8 quarters and get the system, say through an append query? to create 8 records, each with the appropriate date 3 months after the previous one.
Any suggestions or assistance would be appreciated.
Thankyou
View 1 Replies
View Related
May 21, 2013
I need to create some new records based on main form data and a selection of records from a sub form. The main form and sub form have different sources. I wanted to show the source fields in the sub form along with a check box to allow the users to select individual records. The record source for the sub form contains >1000 records, so the user will first enter data in the main form, use filters to find the records he wants to 'assign' to the main form data, click those he selects, then click a command button in the main form to create the record(s) based on the main form data and the selected records from the sub form. The new records will be appended to a new table.
View 6 Replies
View Related
Jul 22, 2015
I am looking for a way to enable a user to fill in a number of fields on a form press a button(CreateButton) and duplicate the records how ever many times is stated in a text box(TxtQty). I also need this to increase the serial number by the amount of times stated in the text box.
There will need to be another text box (TxtNextSerialNumber) stating the next first available serial number
For Example:
TxtNextSerialNumber = AD-Oracle-00010
TxtQty = 5
Press CreateButton to create 5 records
TxtNextSerialNumber = AD-Oracle-00014
5 new records created with the below fields duplicated and the above happening.
I will then need a message box informing the user of the serial numbers created:
'You have created serial numbers AD-Oracle-00010 to AD-Oracle-00014'
Table Name: ADOracle
Form Name: ADOracleTestData
Fields Names:
CustomerName: Duplicate
PartNumber: Duplicate
OrderNumber: Duplicate
OrderDate: Duplicate
HoseKit: Duplicate
Returns: Duplicate
Comments: Duplicate
SerialNumber: + the amount shown in TxtQty starting on next serial number available.
View 6 Replies
View Related
Jul 10, 2012
I think what I want is:
1 table(1): record of people & contact details
1 table(2): list of events with check box's with the names of people from the other table
1 report: listing how many events people have attended.
When I add a new person to table 1 I want a field to be added to table 2 in the form of a checkbox, also when I delete this person I want this field to be deleted in table 2.how to make this an automated process.
View 1 Replies
View Related
May 22, 2013
I am trying to create a database by compiling records from multiple excel sheets. however, since most of the clients still uses the excel sheets to enter records, I am thinking if it's possible to create a linked table in access from these excel sheets that are hosted on an online server.
also is it possible to merge multiple linked tables together? if they have the same field range, so they will append when the linked tables gets updated.
View 3 Replies
View Related
Jul 31, 2014
I work for a company that manufactures home appliances & electronics. When those products fail within the warranty period, we are obligated to repair the units for the customers.
We have around 200 factory technicians who make those repairs. Each tech is able to make approximately 8 repairs each day. We have a dispatching system that assigns the repairs to the techs based on their availability each day. But the problem is that we have to manually enter and adjust the schedule for all 200 techs every single day, and this takes a lot of time. And of course technicians get sick, take vacation, etc, so we have to adjust the schedule so no techs will be assigned calls when they're off.
The problem: For example, currently when a technician takes off for 2 weeks, the user has to enter 14 individual records for the tech, which is somewhat time consuming. What I need is to program a button click event to determine the two dates (startDate & endDate) and append multiple records from one single entry in the form for each date in between and including the two date fields.
I know I'll probably need to create a loop that will loop through the two dates on the form and append a record for each day, so I can then cross reference the dates to the master schedule dates to make sure that no availability is opened for the techs taking time off.
View 3 Replies
View Related
Sep 5, 2014
I have the following source data:
Code:
Item Revision Start End
332 B 9/2/11 9/15/11
332 C 9/21/11 9/22/11
332 E 11/2/12 11/29/12
2A9 A 1/13/13 1/14/13
2A9 C 1/16/13 1/18/13
I'm trying to make a select query to provide the following output (RevisionStart-PreviousRevisionEnd):
Code:
Item Revision Span
332 C 6
332 E 407
2A9 C 2
I've struck out with union queries and aggregating/sorting letters and don't know where else to look. Is there a specific name for this type of operation anyway?
View 4 Replies
View Related
Jul 17, 2015
I have this access "Database" that contains only one table that I'm trying to make a form for. I want it to allow the user to enter a value for field A and a value for field B then a list of values for field C (which is the key, so each would be a new record all with the same values for field A and field B). I tried to make a multiple item form but when I click on the new row for field C, fields A and B both disappear.
Disclaimer: I know that the creation of a one table database is a mortal sin but that's what my boss gave me and there are already >8000 records with a million mispelled words, so I don't think there's much I can do to make it a relational database without entering each record in again.
View 4 Replies
View Related
Nov 29, 2012
In Access 2010, I have a table with two fields. The first field (Investor Name) contains several occurrences of the same text (i.e. Investor A occurs 2 times, Investor B occurs 3 times, etc.). The second field (Investment) may or may not contain duplicates (i.e. more than one Investor may have invested in the same Investment). I'm trying to create a query that will show me, in one record, all the Investments that each Investor has made.
So for the following data...
Investor Name
Investment
Investor A
Investment 1
Investor A
Investment 2
Investor B
Investment 1
Investor B
Investment 3
Investor B
Investment 4
I want the query to report back the following...
Investor Name
1st Investment
2nd Investment
3rd Investment
Investor A
Investment 1
Investment 2
Investor B
Investment 1
Investment 3
Investment 4
To clarify further, I'm NOT looking to concatenate the results into one field. Instead, I want each Investment to appear in its own field, so that I can pull each one into a report later. Ideally, there should be no limit to the number of Investments that each Investor has made, although practically speaking, it likely won't exceed 10 or so.
View 14 Replies
View Related
Nov 15, 2005
how or what function (DLookup) should I use to prevent duplicate records based on multiple fields? I want to look at data in three fields that can't match existing data in those three fields. It's ok if one or two of the fields match but not all three.
View 2 Replies
View Related
Mar 12, 2013
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b
I would like my query to now display a third field and group field :
field1 field3
apple both
banana a
carrot both
dog b
elephant b
View 1 Replies
View Related
May 30, 2015
I'm creating a query from one table using two fields that require certain records to NOT bring up records that have the following text:
One table, two fields
First field ECO LifeCycle Status Criteria is Not "ERP UPDATE" or "CANCELLED"
Second field ECR LifeCycle Status Criteria is Not "COMPLETE"
When I put the Not Criteria in the first field only I get a result.
When I put the Not Criteria in both fields I get no results.
View 9 Replies
View Related
Nov 5, 2014
I have a form (form1) that will populate with records (table1) based on key fields of "Project Number" and at time-points we will update part of the record, and then we create a word document detailing the updates with a couple of other bits of information not in original record.
Basically I want it to go from the original form (form1) into another form (form2) (via a command button) taking the key field of "Project Number" where it will ask the bits of information that we don't need have already and the contact person.
I have got this to store in a different table (table2) using a key field of just "ID" from the original records (table1). A list of contact people are in a separate table (table3) using a key field of "contact name" which will include other details for them such as address and phone number.
So from "form2" I would like to produce a report that contains information form "table1" based on the "Project Number" and "table2" based on "ID" as just been generated and "table 3" which is based on "contact name".
So I want to pull information form the records based on the certain key fields but struggling for it to pull it successfully...
Got relationships between:
"contact name" fields in both "table2" and "table3"
"project number" fields in both "table1" and table2"
In a query for the report I have all the appropriate fields, but only have the "contact name" and "project number" from "table2" is this right? or do I need both in?
Or would it be easier to draw all this information into "form2" but don't really want to select that many drop down boxes or would it be able to pull it from the "contact name" and "project number" boxes????
View 6 Replies
View Related
Mar 23, 2015
My colleague has a multiple table database and uses lookups for some fields. The simple query was to extract 3 fields and display all records from those fields. The result of the query was that the selected fields were listed first followed by all other fields which are usually not displayed.
View 5 Replies
View Related
Jun 2, 2013
I am in the of designing an access 2010 database for data entry. Is it possible to create a button on a form in which a prompt asks a user for which records to export. Then depending on the entry export specific fields (First Name, Last Name, DOB) to a specific excel 2010 workbook (setup) and worksheet (template)?
For example, if the user entry is 1, only record 1: First Name, Last Name, DOB is exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx). However if 2,3,4 are entered then records,2,3,4: First Name, Last Name, DOB are exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx).
View 1 Replies
View Related
Dec 10, 2014
Create form to search multiple fields in table
Return records that match search
Open the record that you want in Form View for editing
View 1 Replies
View Related
