How does one get multiple records inserted into a table at once, when you press the "Add Record" button? Resulting in the table below:
MAKE
MODEL
GAS / ELECTRIC
COUNT
YES / NO
Honda
Accord
Electric
1
YES
Honda
Accord
Gas
3
YES
Honda
Civic
Gas
0
NO
Ford
Mustang
Gas
4
YES
This is how it must be setup, all I need is to know how.
I have a tabular form with many rows of records. Users add some additional information and now I would like to insert it into a new table.
I tried to use below code and it works but it inserts only first row out of many. So I just wonder how to amend it to insert all data?
Code:
CurrentDb.Execute "INSERT INTO [PO Lines - Table] ([SKU], [SKU Description], [Barcode], [Qty], [Unit Price], [PO Number], [Range])" & _
" VALUES ('" & Me.SKU & "','" & Me.SKU_Description & "',''" & Me.Barcode & "''," & Me.Qty & "," & Me.Unit_Price & ",'" & Me.PONumber & "','" & Me.Range & "')"
I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table
the code I have started off with is
Code:
CurrentDb.Execute "INSERT INTO ColdTemperatures (ProductID, ColdTempDate, Temperature) VALUES (" & Lettuce & ", #" & Me.RealTime & "#, " & Me.Lettuce & ")"
which adds 1 successfully however if i repeat the code above for all 15 this Im assumming will create a potential bottleneck and slow the system down
is it possible to add all 15 records at once? do you think Im going at this the right way
Hi!
Please, please, please help me!
I have created a form that should get Date, Day inputed by a user. Then it must place 8 the same records in the table.
Any help will be appreciated
Thanks in advance
Hi all,
Really, I am very happy to be a member in this great forum with those great members....
I have a form with three text boxes and one button "Submit". This form is build based on a table with three columns. How can I insert records into this table through that form by clicking that button "Submit"....
Thank u and sorry 4 bothering u....
Hi all,
Really, I am very happy to be a member in this great forum with those great members....
I have a form with two text boxes and one button "Submit". And also, I have a table with two columns. How can I insert records into this table through that form by clicking that button "Submit"....
I think it is simple, but I am very beginner in Access....
See the attached file....
Thank u and sorry 4 bothering u....
I'm trying to insert 10% of a dataset from dbo_billing into another table Random_Temp. Another form is open when this query is to be ran that passess in the billyear and billmonth... I'm sure it's a syntax issue as I can isolate the random number part and it displays the appropriate data, I just can't re-write it to insert into the other table:
INSERT INTO Random_Temp ( indx, peopleId, audited )
SELECT TOP 10 PERCENT b.indx, b.peopleId, b.audited
FROM dbo_Billing AS b
WHERE (((b.billYear)=[Forms]![billing]![billyear]) AND ((b.billMonth)=[Forms]![billing]![billmonth]) AND ((b.recertifying)=-1))
ORDER BY Rnd(-(1000*b.indx)*Time());
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
I am using an unbound form to insert data into several tables, all related, at the same time. Please let me know if what I am trying to achieve is too ambitious!
I am developing a material sample library... any sample could be one material, two materials or three materials. Let us say that A, B, and C are three primary materials.
I have three Tables: tblX, tblY, tblZ.
tblX stores details for each individual material. Its structure is as follows:
tblX
XPK | ID | value1 | value2 |
01 | A | asdfas | asdfdf
02 | B | dfasdfa | sdfaf
03 | C | asdfffd | asdfg
(here, XPK is the primary key, ID is the name of the material and value1, vlaue2 are other descriptive fields...)
tblY stores materials that are combinations of two primary materials:
tblY
YPK | 1st | 2nd | ID | value1 |
101 | 01 | 03 | M | keiury |
102 | 02 | 03 | N | kjgeiih |
103 | 03 | 01 | P | djlkgoi |
(here, YPK is the unique ID for a sample, the '1st' and '2nd' fields are simply the primary keys from the tblX. ID is the name of this new material and value1 has descriptive values for samples M, N, etc.)
tblZ is the next level of material, made of combinations of materials from both tables tblX and tblY. it is like this:
tblZ
ZPK | 1st | 2nd | 3rd | 4th | ID | value1 | value2 |
1001 | 01 | 03 | -- | -- | R | asdprw | mnvd |
1002 | 01 | 102 | 02 | -- | S | adsfd | oirtyr |
1003 | 103 | 02 | -- | -- | T | werwq | pojfgr |
1004 | 02 | -- | -- | -- | U | alkfdp | uioite |
1005 | 01 | 02 | 103 | 102 | V | keqwei | oirewj |
(here, ZPK is the unique identifier, the primary key. as you can see, 1st, 2nd, 3rd, 4th are references to materials from either tblX or tblY or none, ID is the name of the material and so on.)
So, going back to the original primary materials, a sample could be a combination like A + (A+C) + B...
I hope I am clear enough till this point.
My unbound form (which is a very complicated one by now!) is where a user will start with such a sample and start putting in details for each of the primary materials. As he/she logs each individual material, I store that data in a temporary table (tbltemp). When all the primary materials are fed in, the user hits a 'Save' button, which is supposed to do the following:
1) save each primary material from tbltemp to tblX
2) extract their primary keys and then insert that into the next level table, either tblY or tblZ.
I have been working with ADO recordsets to 'addnew' data to tables and am adept with that. I have never simultaneously extracted data from one table and inserted into another. similarly, i have never extracted more than one record, and inserted them into a single record in another table, etc.
I will appreciate any guidance, both at conceptual level and at operational level, that I can get. If you would like to see my database, I could arrange to have it accessible; there is nothing confidential (only higly complicated, I think!).
If you are still reading, I already owe you a ton of thanks!!
Ok, I know this is already part of another thread, but my problem is a bit more complex...
The database I've been designing catalogs traffic signal equipment at every intersection in a 12-county area. There's about 480 intersections in the database. My boss pointed out the need to have the intersections listed in geographic order. For example, if I have three intersections on IL Rte. 5 - John Deere Rd, Bauer Parkway, and Industrial Park Rd, they need to be listed in the order in which they exist on the road (from east to west or north to south). Alphabetically, the intersections would be Bauer, Industrial, John Deere. But, as you drive down the road (from east to west), the intersections occur as Industrial, Bauer, and John Deere.
I can sort the intersections according to their "corridors" without any trouble, but I have to force the geographic order of the individual intersections by assigning each an index value (like "1", "2", and "3"). Better yet, I can assign the indices in multiples of 10, 100 or 1000, (1,2,3 becomes 10,20,30 or 100, 200, 300, etc.) and guarantee there will likely always be a sequential position available in the geographic index for a new intersection. Plus, I can always pick the "middle" of the range for each new insertion. For example, if Bauer has a geographic index of 200 and John Deere's is 300, then I can give Zebulon a value of 250 - leaving equal room for insertions before and after Zebulon in the future.
Pulling this off isn't hard - I know how to do it. The problem is running out of positions in the index. In the case of my database, it is unlikely we'll ever add more than two or three intersections between two existing ones, so using multiples 100 is probably best. In this case, I can add at least 6 intersections before I run into the possibility of having two intersections whose indices have a difference of only 1. Using 1000, the value goes up to 9. Each multiple of ten gives you 3 more entries (minimum) before encountering consecutive indices. I guess the inherent problem is figuring out a more "infinite" way of accomplishing this. I don't readily need it, but on the off chance that I do end up with consecutive intersections (that I have to insert between), it'll require manual re-numbering to fix the problem.
That's my solution, but there may be something simpler. Any thoughts?
Hi!
I hope this is the right section for posting this Q.
I use Access 2003 on WinXP pro as front-end & for back-end a MySql on a Linux server. I use MyODBC to connect to the back-end and all the tables are on the back-end. The workstation is connected to the server via VPN (so the server and the station are on different locations).
Quite often I get a problem that not all records are inserted into a table. E.g. I have like 5 - 15 records (up to 10 fields) in one table and I want to transfer/copy them to another:
strSql = "INSERT INTO tblDetailNakup " & _
"SELECT tblDetailNakupTemp.* " & _
"FROM tblDetailNakupTemp;"
docmd.runsql strSql
most of the time it works OK, but from time to time a couple of records are missing.
What could be the problem? Is there any way for somekind of a check, if all has been inserted otherwise the query is repeated?
TNX in advance,
Miha
Hey guys, got another question for you.
I have a table that has the follow fields (this is for a mailing schedule)
Job Name, Mail Date, Job Number, ID #, Material Due Date, Quantity To Mail
Here's the problem I'm having.
For each "Job" that we have, it can have multiple "Mail Date(s)". I'm trying to create a form that will allow a person to enter one "Job Name" and multiple "Mail Date(s)" and have it create a total number of records based on the number of "Mail Date(s)" (total possible is 4). Example below.
I'm creating a mailing for "Free Hot Coffee", I enter the "Job Name" once, and enter 4 "Mailing Date(s)", then click a button and have Access add 4 records for "Free Hot Coffee" each with a separate "Mail Date"
Is this possible? If so how? I was thinking of using a For loop but didn't know if it was possible.
Thanks in advance.
hi all,
I have created the database with multiple tables. I've created one main table to store all records from other tables. Then I have make several forms to enter record into several tables.
What I'd like to make is that when I enter new record to any one of those other tables, then it will be automitically add to the main table also. The record no of main table will be automatically increase by itself when the new record came in.
Can anyone help me with it?
thanks.
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
What is the best approach?
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I have a database consisting of two tables. One table is meant to be a running log of entries (Table A) and the other is simply used to house specific information relating to certain records (Table B). There is a form within the database that is used for data entry. Within that form there is a subform that has a button that will automatically insert a new record into the running log (Table A). This button currently carries over one value (a Doctor's ID) but I also need this button to begin carrying over my employee's name as well. I have tried numerous times and I can not get this second value to carry over.
View 1 Replies View RelatedI have created a tabel in access 2003 which has information about employees and i have to put even their photos as records so i can see them in a report that i have created too.
View 5 Replies View RelatedHi,
can someone please help me with this problem.
currently im working on a student management system with MS ACCESS. Im stuck on the create_assessment_form that allows you to query previous assessment results or to create a new assessment. It has a subform that shows the assessment results of all the students relating to a selected assessment_ID.
The query of the results are working properly,
But I need everyone's help for the creating new assessment part.....
When I create a new assessment, I have to choose a course_ID the assessment is relating to.
>>>Question<<<
How do I make it so that it will generate a list of assessment_results record for each of the student relating to the new assessment?
maybe my explaination is a bit messy. Please let me know if you do not understand my question. But I really need an answer to this question urgently!!!
Thanks in advance...
I've only started using Access 2010 since the beginning of January and have googled almost every problem to date.
My db is used to show the status of material through a manufacturing process. We start with one slab of material which gets cut up in to many parts.
The db works so far however I've come to the conclusion that the date was not normalized correctly. So I've created the following tables to fix this.
I have two tables the first of which holds data on the slab we start with and the second shows the status of the parts it has been cut up into.
Table 1 has the following fields
Cast Number
P/O
UST Status
UST Comment
Table 2 has the following fields
Cast Number
Blade ID (which will be 1 to 32)
Status
Comment
I would like table one to be displayed on a form with all fields. Have table two as a subform on the main form. But here is where I get lost!
I need the subform to show 32 text boxes to represent each part. Have each box assigned a default blade ID (1 to 32). Then depending which of the 32 text boxes get used to update those multiple records within Table 2.
I did have 32 status fields for every part but realized that meany were left null. I'm using, Access 2010 with Win 7...
I have a database that contains a few tables and queries etc. I made a copy of this database so that I could change some of the VB code with out affecting the database information. I have completed all the code and now I want to input those old tables into my finished verion. I am not sure how to do this. Any ideas on how I could do this would greatly be appreciated. Thanks.
View 8 Replies View Relatedjust wondering if anyone knows how to insert about 10 lines of data into an already existing table?
View 3 Replies View RelatedThe security table is made up of two primary keys: thing, personorgroup
When i run this statement to insert into the security table
INSERT INTO security (thing, personorgroup, accessrights)
SELECT '252600649', '4020', '255'
FROM PROFILE
WHERE not exists(select * from security
where security.thing = '252600649' and security.personorgroup = '4020');
I get this error:
Server: Msg 2627, Level 14, State 1, Line 25
Violation of PRIMARY KEY constraint 'PK_SECURITY'. Cannot insert duplicate key in object 'SECURITY'.
The statement has been terminated.
Anybody know how i can perform my insert successfully? :D
i am trying to achieve a simple thing (at least it seems so to me!)...
I am trying to add a new record to a table (LP_Product_Name) with only one field (Product_Name) as a part of a 'on Lost Focus' event of a text box (txb_ProductName).
Code:Private Sub txb_productname_LostFocus()' Declare Variables Dim db As Database Dim rs As DAO.Recordset Set db = Nothing Set rs = Nothing' Assign Values to Variables Set db = CurrentDb() Set rs = CurrentDb.OpenRecordset("LP_Product_Name")' Enter New Product name to the table With rs .AddNew .Fields("Product_Name") = Me.txb_productname End With ' Close variables Set db = Nothing Set rs = Nothing' Restore Visible formMe.cmb_productname.Visible = TrueMe.cmb_productname.SetFocusMe.txb_productname.Visible = FalseEnd Sub
When I type something in the textbox and shift focus to another field, nothing happens... no error message and no added value to the table! I dont know what is going on. It just doesnt work!
On the same form I also have a combo box that uses the same table (LP_Product_Name) as its rowsource. I want the Combo box to immediately show this added value.
Can someone please help!?!
Thanks!
I have the following tables:
tblProjects
ProjectsID (Primary key - exclusive)
ClientProjects
tblProjectsDetails
ProjectsDetailsID (Primary key)
ClientProjectsDetails (Secondary key)
tblQuotes
QuotesID (Primary key - exclusive)
ClientQuotes
Service
Rate
tblQuotesDetails
QuotesDetailsID (Primary key)
ClientQuotes (Secondary key)
Service
Rate
I have the following forms:
fmProjects
fmProjectsDetails (parent/child)
fmQuote
fmQuotesDetails (parent/child)
When I add a project I need to have data from the tblQuotes and tblQuotesDetails to populate tblProjects and tblProjectsDetails respectively.
It is very easy to populate tblProjects from tblQuotes since it is a single record. Therefore I use:
Me.ClientProjects = Me.ClientQuotes
:confused:
The question is: how to match the data of the 2 subforms? Since there will be more than one record per subform?
Any help is really appreciated.
I'm using Access 2007.I have 2 tables that are identical in structure.The tables are called [Workorder Parts] and [Workorder Parts Temp]. They both have the same structure:
WorkOrderPartID (Autonumber)
WorkorderID(Number)
PartID(Number)
Quantity(Number)
UnitPrice(Currency)
Notes(Memo)
KitID(Number).
[Workorder Parts] has 128 records in in and [Workorder Parts Temp] has 28 records in it that are a small subset of [Workorder Parts] therefore, the contents of the key field column in [Workorder Parts Temp] is the same as in [Workorder Parts].I tried the following SQL but get an error message saying "Cannot Update "WorkOrderPartID" Field not updateable":
DoCmd.RunSQL "INSERT INTO [WorkOrder Parts] SELECT * FROM [Workorder Parts Temp]"
My aim is that I want all of the records from [Workorder Parts Temp]to be copied to [Workorder Parts] and have their "WorkOrderPartID" fields correctly updated with a new value...