Inserting Records Into A Table Through A Form
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....
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....
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
I am trying to add records into a table TblMain ... below is code i used but i get error saying
the form record source is not linked to that table or any table for that matter. Table has AutiID indexed autonum field ... if that matters (dont want to update that just regular fields in table).
Me.txtUID = [TblMain]![UID]
Me.txtTD.Value = [TblMain]![Todays Date]
How would i do this so that it adds new record every time i update the table using this vba code?
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.
very new to all this and need a form to add multiple records to a table, preferably by selecting the number of records to add (in Access 97!)
The idea is to add a list of labels in use and then later insert their related details. Ideally, 3 fields will make up the label code: 1 incremental 4 number field, and the other 2 accepting a default value.
Any ideas on how i can do this?
thanks for any help offerred!:
I am planning to use Access97 as my database for a windows based application. The application involves processing for millions of records (Example: 6 million records). Main operation of the application is to do logical operations like "AND" and "OR" within the records in the table.
I am processing the data from the binary file and inserting the same in the table. I am processing in the batch of 1 million records. Inserting 1 million records into access is taking more than 1 hr and hence the application has a very bad performance hit.
let me know if there is any option in access to optimize the insert query or any better method to perform the logical operations than going for access database?
Currently I am inserting each record by record and using a Jet 4.0 provider with Borland C++ as front end.
Thanks in advance.
Let me start by explaining what i am trying to do ....
i have some data that i have in an access db - (Table GECF1). I need to first search each record for text "submitted VMCoE" . Once i get that .. i need to get the date/time stamp from the field prior to this entry. I also need to then search in the same row backwrds now, for the first occurence of " submitted" and get the date/time stamp for that action . These serve as the start and end time for a task ... subtracting these i get the TAT for the activity.
This needs to happen till i reach the last record. I also need all these entries to flow into a separate table which would contain only my TAT data .....
ANy help would be welcome.
Am uploading the db if it helps.
I'm trying to insert the records from a recordset into a temp table, but I can't seem to get the syntax correct. Here is what I have:
Do Until rst.EOF = False
sSQL = "INSERT INTO TEMP_TABLE SELECT * FROM rst "
Ideally I wouldn't want to have the loop, I would just insert all the records using the query once. I'm open to any other ideas if anyone has any other suggestions on how to insert records from a recordset into a temp table.
Thanks for all your help.
Inserting records based on a lookup table
I have a TransferSpreadsheet method that imports records into the Sales_Rep_Staging table with no problems. What I want to accomplish is to load the Sales_Rep_Staging table into the Sales_Rep table based upon a lookup table.
The lookup table has Regions and State_Code columns. The Regions column will be compared with the Description column in the Sales_Rep_Staging table, once there is a match the process will insert records into the Sales_Rep table based on the State_Code column in the lookup table. If the State_code has multiple states code there will be multiple inserted records. Multiple state_codes is separated by “&” sign.
If the description column in the Sales_Rep_Staging table has southwest as a region it will insert two records into Sales_Rep table with the same data except for state column. The state column will have AZ for the first inserted record and NM for the second inserted record.
Below is the table structure with data.
SouthwestAZ & NM
Northwest/Southwest/Great LakesCO & NM & KS
Great lakesKY & MO & KS & NE & IA & IL
Gulf Coast/Southeast/South AtlanticLA & AR & OK & NW & AL
Great Lakes/MID-Atlantic/New EnglandOH & IN WY & PA & VT & NH & ME & MA & RI & CT
South Atlantic/Gulf CoastSC & NC & AL
MID-AtlanticVA & DC & MD & DE & NJ
Northwest/SouthwestWA & OR & ID & AZ & NM
DESCRIPTIONREP FIRST NAMEREP LAST NAMEImport_Date
Gulf Coast/Southeast/South AtlanticSTEPHANIEJONES5/4/2007
Great lakes/MID-Atlantic/New EnglandHALEYCRAMER5/4/2007
South Atlantic/Gulf CoastVICTORBOLTON5/4/2007
DESCRIPTIONStateREP FIRST NAMEREP LAST NAMEImport_Date
Any advice will be greatly appreciated. Thanks.
I have a working table called tblGenesisSummaryHours that I insert records into with the statement:
Code: strInsert = "INSERT INTO tblGenesisHoursSummary (" & _
"EmpNbr, Branch, Department, JobCode, EmpName, HrsReg01, HrsOT02, " & _
"HrsVac04, HrsSick05, HrsHol07, HrsJury14, HrsEmer15, HrsPers16) "
(I omitted the VALUES part) This part works fine.
On occasion I need to update a field in an existing record. I determine this by an On Error on the insert. When I get that, the code then goes to an update routine that finds the existing record, inserts the new data and then updates.
Code: ' Update Routine
SummaryHours.Find ("EmpNbr = '" & DetailHours!empno & "' AND" & _
"Branch = '" & DetailHours!Branch & "' AND" & _
"Department = '" & DetailHours!Department & "' AND" & _
"JobCode = '" & DetailHours!jobcode & "')")
' update fields
I'm erroring out at the SummaryHours.MoveFirst statement with a message that the file is at EOF or BOF and needs a current record. I can see that I have multiple records there andthe one I want to update is one of them. I'm opening the file with:
SummaryHours.Open "tblGenesisHoursSummary", MyDB, adOpenDynamic, adLockOptimistic, adCmdTable
Is this the wrong type of Open syntax?
i have a problem adding the data from the fiels on the form into a table. I know you can just click on the navigation arrows and it will save the record but i want to use a submit button as this is more user-friendly and suitable for the work i'm doing.
I have attached the database... the form that needs the code for submit button is 'frmNew_JobStatus' and the table i'm trying to insert the data into is 'tblJobStatus'
can anybody have a look at what i've done so far and suggest how i can fix this... sample code would be very usefull as i'm not an access expert.
thank you all
I want to design a form , please note I have beginners information regarding Access,
I have 2 tables , 1 table has Project No, Document No, DEpt NO. with all the information
There is another table-2 with Project No, Document No. and Resourcrce. There is not data
in this table
What I want to do is , I want to create a form where in I select the Project No. and
Dept No. When I select these items the form should list me the data in Table 1 for that
Project no and DEpt No. , after this data is listed , I will add the REsource data
and all this data has to be stored in table-2. I hope some one will be able to guide me on
how to do this.
I am new to access (using Access 2003) and am having trouble working with forms. Here's what I want my form to do:
-Use a combo box to select a specific system
-Given the selected system, pull up ID numbers and descriptions (in separate text boxes) from two separate tables corresponding to that one system
-Navigate through those ID numbers/descriptions from each table independently to find ones that match
-Store the ID numbers of the ones that match into another linking table
The biggest problem right now is being able to navigate through the different table ID numbers/descriptions and add both ID numbers to a row in a different table. I've tried using a combo box with the INSERT INTO statement into the code builder, but I keep getting syntax errors.
Does anyone have any suggestions on a better way to do this?
I am doing one project using access. I 've made two forms. In one form, lets say, i made two common buttons. Both buttons will load the same second form and will add new record to the same table. When I click first button, it will load second form with all fields blank and one field of record will autofill one value,lets say "a" to the table.That field shouldn't be appeared in the second form,just want to fill automatically. Then the other fields of new records will be filled by the user input from the second form.
Also, when I click second button, it will do similarly but only the autofill value will be different from the first one.
Does anyone know how to make it?
If don't understand what I am saying, I can explain it again.
thanks in advance.
Can someone please enlighten me on how to insert a record 'between' two records? With a Table open in Datasheet View, when I try to insert a new record between records, I get taken beyond the last record. Also, how would my request work on a Form??
Any suggestions would be greatly appriciated!!
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?
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.* " & _
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,
I have this scenario wherein I have a parent table called student and the child table called test. As soon as the student information is entered (through the form) I want 10 records to be inserted into the test table with the student information. Is there a way I can handle this in MS Access?
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?
I need to insert a hyperlink into a field of many many records. Is there a fast way in which I can do this or do I need to go record one by one and Insert Hyperlink?
I have a table with several fields and one field of them is quantity
not all records are set quantity to 1 as I want to,
(Because I'm going to delete that field when all have been repeated)
I need to repeat as many records as number in Quantity field says.
I guess that a pseudo code might be like.
Look for records whom it's quantity is > 1
Insert all fields same as source record in a new row, many times as quantity tells
Finally set source record quantity's field to 1 or may be deleted entire record.
Please help me develop that primitive pseudo code to pretty VBA
I have data in a table that someone else uses a program call GIS and that program shows does calculation with new data and fields added...the new data is given to me in Excel. I am going to import from Excel into Access and make that into a new table without the already fields. You can separate each 1-to-1 record with the Primary Keys...
The problem is that he does this often and he would gives me information in Excel. What I want to do is update the data if it's already in the database via Primary Key. Else, Insert new data via the Primary Key...i hope that makes sense.
Say for example, i created a database that supplies User's First Name, Last Name, Home Address, and Contact Number. The GIS will take the Home Address and gives me other information that I alone can't figure. Once done, he transfer the new data along with my database data into an Excel file. My job is to insert the new fields into a database.
Now, I know I can just create a separate table given the fact that it is 1-to-1 relationship and have it talk via the Primary key.
Say I inserted 50 records into this new table (tblGisData) and one day the GIS guy takes those 50 records again and found out some information needs to be corrected. He corrects them and gives it to me...how should I deal with it...also, say there's 60 records now....should I check for....??
if DLookup (primary key is in database of tblGisData) then
How would you do it?
The situation is this, I have a Form with a Subform in it, in my main form I have information about a new Student class, like Student ID, Language of the class, and comments, this main form is connected to a subform that have this records, idStudent, Date of Class, Hours, Professor, and Status.
I made a routine that when i click a button on my main form i want to automatically insert the Hours of the class to this subform, .
Set rsSub = Forms!frmAlumnosClases!sfrmDetalleClases.requerey
rsSub("Hours").Value = Chour (comes from a variable)
I made this routine , but every time it runs , althought the language of the class appears in my subform , when i click on that field the field changes to name#?.
How can I insert a recorset to a subform ??
I want to create a table called 'IssuedInvoices'. The table will simply contain the number of the invoice and the name of the sales rep to whom the invoice was issued.
What I would like to see happen is that the person issuing the invoices simply needs to enter the lower and upper invoice number together with the sales reps name on a form and the table updates to add all the additional numbers in between (the invoice numbers are consecutive).
I have an Excel template which people will be using to enter contact details. Each row contains contact name address, email address, spouse name, spouse email address, child 1 name, child 1 email address, child 2 name, child 2 email address etc.
I have an access database which has tables Contact, EMail Address and Child. I am using a macro to load the Excel file into a LoadData table. I need help in populating the Contact, EMail Address and Child tables reading from LoadData. I have a 1 to many relationship between Contact & Child, Contact and Email Address and Child and Email address?
I am fairly new to Access, so any help would be GREATLY appreciated!
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.
just wondering if anyone knows how to insert about 10 lines of data into an already existing table?
The 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'
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!?!
I have been inserting thumbnail pictures into my table for the last six months with no problem. The field in my table displays "Microsoft Photo Editor 3.0 Photo." Now when I enter my thumbnail by inserting an object, the field displays the word "package" and the photo does not appear in the form where the pictures are displayed.
What would cause the word "package" to appear when I am inserting an object just like I have been doing for months?
I have a strange problem, I have created this query:
INSERT INTO WEEKONE ( Assigned_To )
WHERE QPTActions.Entry_Date<=DateValue('1/2/2006') And QPTActions.Close_Date>DateValue('1/2/2006');
It works, but it inserts Numbers into the Assigned_To column in the table WEEKONE. When I looked in the QPTActions database the values are text but the type of column was originally set to Number. I changed this to Text but its still inserting Number values into the destination table. How can I fix this?
Hi, I am wondering If I can Sum the value of rows in one field of my Query Table and Inserting that Calculated value into the bottom of the summed Row in the VBA or through Query Design View.
Please let me know, Thank YOU!
Below I have attached the picture of the data that Im trying to sum and insert into a new row that Hopefully can be created through MS access Query.