Inserting Records...
Aug 4, 2005
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?
View Replies
ADVERTISEMENT
Oct 31, 2006
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
View 2 Replies
View Related
Feb 5, 2008
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
View 2 Replies
View Related
Aug 31, 2005
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.
View 3 Replies
View Related
Jan 28, 2006
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....
View 7 Replies
View Related
Aug 1, 2006
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....
View 6 Replies
View Related
Jan 31, 2008
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.
View 8 Replies
View Related
Mar 4, 2015
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.
View 5 Replies
View Related
Jul 25, 2013
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 Related
Sep 6, 2012
I 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 Related
Jun 18, 2014
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 & "')"
View 12 Replies
View Related
Jul 16, 2015
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());
View 2 Replies
View Related
May 6, 2014
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
View 5 Replies
View Related
Jan 24, 2006
i got the atached message when i am trying to insert a new value, the wierd thing is when i do okayu, the value is being insrted...
View 5 Replies
View Related
Jul 31, 2006
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 Related
Feb 26, 2007
Hi
I really need to insert a movie in my database. I have tried inserting a Windows Media Player object in a form, but I dont know how to link it to a movie on my computer. Please help me with this. By the way I'm using DAO, if that helps at all.
Please help me as soon as possible, I'm in a hurry
Thanks in advance
View 4 Replies
View Related
Dec 30, 2004
i am working on my company's database which is in access 97, and has a backend. the normal entry way when you add a column it wont save as it says it can't save propertys to a linked table. i added the required fields to the backend without any problems and saved and exited. now when i go into the "frontend" the fields arn't showing up on the table. any ideas?
thanks
briar
View 6 Replies
View Related
Apr 23, 2007
just wondering if anyone knows how to insert about 10 lines of data into an already existing table?
View 3 Replies
View Related
Jan 23, 2007
Hi,
Can anyone tell me what i'm doing wrong with this insert statement?
INSERT INTO TBL_PROCESS_NAME
VALUES( ' & [Forms]![frmIntroduction]![txtProcess_Name] & ' )
the form is loaded when the query is run, i've tried taking out the & and the ' but to no avail, all i get in the table is [Forms]![frmIntroduction]![txtProcess_Name]
Thanks
Michael
View 7 Replies
View Related
Apr 5, 2007
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'
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
View 9 Replies
View Related
Sep 23, 2004
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!
View 7 Replies
View Related
Nov 16, 2006
HI,
I would like to insert values to differetn tables using a form, is that possible with just access? or does it need VB script or SQL, if so, is there any example to follow.
my form made of a combination of two tables attributes ad when I enter say:
name
tel number
address
car
car reg
booking time
then I would need that info to go to the table it belongs to, as there is three tables, one is the car, the other is customer and the last is booking.
thanks
View 1 Replies
View Related
Jan 8, 2007
Hi All,
This should be a simple thing however Im having some problems. Im trying to insert a record into access. I can insert everything except the date. I cant seem to get the right format. Below is what Im trying:
Code:strSQL ="INSERT INTO GISrequest (givenName, surName, email, division, title, purpose, audience, date) VALUES ('firstmname', "&strVal1&", 'anemail@somewhere.com', 'thedivision', 'thetitle', 'purposestring', 'audience',#" & AccessDate(NOW()) & "#)"
I think the error is in how I format the date, but Im not sure. Ive looked on the net for examples but I havent found anything that works... can someone suggest what may be wrong or perhaps provide (or point me to ) an example of inserting a date into an access database with ASP?
Many Thanks
View 3 Replies
View Related
Jan 10, 2007
hEYA all, ive got a problem, im trying to inserting data from an array to my access table- but cant do this due to this error:-Data type mismatch in criteria expression.
Heres my insert code- which its genereating the error at:
Code:sub InsertDBCmd( )'================================================= ========================================SET objRs = SERVER.createObject( "ADODB.recordSet" )dim sqlStatmentsqlStatment = "INSERT INTO Members "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "[A/C no], [Address_Code], "sqlStatment = sqlStatment & "[Managing Director], [Title], [Buying Group Member], [M D Sal], [Rebate Contact], " sqlStatment = sqlStatment & "[Rebate Title], [Rebate Sal], [Replied], [Member Name], [S/Holder 2], [S/Holder], " sqlStatment = sqlStatment & "[ALPHA TYPE], [Member Type], sqlStatment = sqlStatment & ") "sqlStatment = sqlStatment & "VALUES "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "'" & C & "', '" & No & "', " For n = 1 to 15 if n <> 15 then sqlStatment = sqlStatment & "'" & FieldsArray(n) & "', " else sqlStatment = sqlStatment & "'" & FieldsArray(n) & "' " end if Next sqlStatment = sqlStatment & ") "'rESPONSE.WRITE(sqlStatment)'RESPONSE.ENDobjRs.OPEN sqlStatment, objConn,1,3'================================================= ========================================end sub
Any code or suggestions pls??
View 1 Replies
View Related
Sep 17, 2005
I have two tables named datadistint and links
and i need to insert only some values in five fields of links from datadistint but before inputting values we need to ensure they are already not there in links.
Insert Into links (LinkTitle,LinkURL,LinkDescription,maincat,cat)
Select 1,3,2,4,5 From datadistint Where
1 Not in (Select LinkTitle From links) and 3 Not in (Select LinkURL From links)
and 2 Not in (Select LinkDescription From links)
and 4 Not in (Select maincat From links)
and 5 Not in (Select cat From links)
i ran the querry as above but it doesn't work what should i do or what's wrong with this querry,
because i need to compare all five fields and then insert only new values.
View 1 Replies
View Related
Oct 19, 2004
How do I add a new field to an existing table so that the same value is automatically entered for all the records in that new field.
View 1 Replies
View Related