What would be the best way to do the following scenario:
I have a form where users can select continents. Another combo box is autofilled to show the respective countries. In that same combo box there is also an "All" option. So for North America the combo box would be filled with U.S., Mexico, Canada, and All. There are many other text/combo boxes that the user fills in and then finally a table is update with the record.
I want to be able to insert into a table identical records for U.S., Mexico and Canada if the user selects "All" in the countries combo box.
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID) SELECT Person_ID, Department_ID FROM TBL_NEWDATA WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
I have a normalized DB with one to many relationships, using Primary and Foreign Keys.
I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.
How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.
I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details
I have 2 tables, tblName and tblPhone. 1 name can have many phones.
tblName has PKName. tblPhone has PKPhone, FKName.
I have a form where user enters a new Name and PhoneNumber.
Name gets inserted to tblName, assigned with PKName = 100
Phone should get inserted into tblPhone with PKName.
IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").
The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.
I have a question.....i am uing Access as the back-end database of a VB6 Front end app. Basically i have a recordset with about 300K records....i currently Insert 1 row at a time....which takes ALOT of time.....is there a faster way to do the insert? i saw some things on the internet about a BULK insert....but it sounds like it is only with SQL Server or ORACLE. Please let me know what you think.
I have a 2 identical databases. One named customerrent and the other customerhistory. Both databases contain related tables. tblcustomer tblrent What i would like is a button on my form to archive the current record, to the customerhistory database then delete that same record from the customerrent database. Can someone give me an example how to accomplish this?
I have a database that is tracking proposals and job orders. First the proposal goes out and then sometimes the customer what to change the order in some way which means i need to put a new row somewhere other then the end of the record. I know in Access you can not add a row in the middle of a table but everyone was use to doing this in Excel and was able to make such adjustments. I thought about adding a Sort column so the data entry person could add their own sort order but some proposals can be hundreds of lines and no one wants to do this, however they want to be able to make changes and adjustments to the existing proposal. Is there some magic I could create in the development of this database to make everyone happy?????
Let me explain my situation first. I have a form that has a complete wells information(I'm doing Gas Balancing)
Every month we get new gas balancing statements. When a new year starts, I need to copy most of the info from one record to another- but make it a new year.
I have a form that displays all of the information. I am trying to make it so they push a command button for a new year, input what year, and then it copies the required information over- then refreshes the current filter(so that after they add a new year, they can go to the new year they just created)
I have googled for about 3 hours, and found a ton of differnt formats for Insert Into statements. Here is my latest(that does not work)
Dim strYear As Integer
strYear = InputBox("What year would you like to add to this well?")
DoCmd.RunSQL "INSERT INTO Gas_Balances (Sec, Twn, Rng, [Interest Owner], [Well Name], Year, County, State, WI%, NRI%, [Follow up Date], Comments, Operator) VALUES ( Sec, Twn, Rng, [Interest Owner], [Well Name], strYear, County, State, WI%, NRI%, [Follow up Date], Comments, [Operator_Operator])"
The values I want input to the table are from the current form(which is brought from a query of the Gas_Balances table).
I'm sure this is an easy fix, and I hate to bother the forum with what is probably a stupid question, but I have just about given up(it is 2:30 in the morning, I have to be up in 3 hours, and have spent the last 5 hours just trying to get something to work).
If anyone has any ideas/an example, please let me know. Thanks a lot everyone.
On the creation of a reconrd in the main table I'm trying to auto fill a sub table with standard information for the user. This is what I'm using to insert into the sub table:
I need to do something in excel but I have never used excel before. I need to make a form with a box. When you click on the box it shhould promt you to add a picture to the box. It should also format the picture so that it is the same size as the box. So far we have the box, but we still need to figure out what code to input so that it prompts you to add an image and then resizes it to fit the box. This is the code we have so far:
With Application.FileDialog(msoFileDialogFilePicker) .Title = "Seleccione la imagen" .Filters.Add "All Files", "*.*" .Filters.Add "JPEGs", "*.jpg" .Filters.Add "Bitmaps", "*.bmp" .FilterIndex = 3 .AllowMultiSelect = False .InitialFileName = Application.Path result = .Show If (result <> 0) Then fileName = Trim(.SelectedItems.Item(1)) Image1.Picture = LoadPicture(fileName)
End If End With
I am not sure if this can be done in excel or if I have to use another application to do this. I need to figure this out within the next couple of days, so if you have ANY idea of how to do this please post a response.
Have a database that stores information about jewelry. The user wants each record to have a picture of the item. No matter what I try I can’t put a picture field in my table. All help is appreciated. tillessal@yahoo.com :confused:
I have a db which has a main switchboard for 3 seperate forms/data sheets. I want to enter a column in one datasheet, but am not able to. I have checked properties, allow edits, deletions, etc, and has no relationships other than what the wizard would place between switchboard and form. Any help would be appreciated.
I have a table SL with some fields, the first of them are: CODNOU and CODADR. I have a query test with some fields, the first of them are COD_NOU and COD_ADR. I made the following query:
INSERT INTO SL ( CODNOU, CODADR ) SELECT COD_NOU, COD_ADR FROM test;
which intends to insert the data from test qry into to the table SL. Unfortunatelly when I run the qry a message occures and invites me to input the parameter value for COD_NOU. If I avoid using the field COD_NOU in the wry, the qry works correctly. What`s wrong?
From a subform I'm opening a popup form to a new record. Except the new record must insert the CustFK value. Right now its just filtering the record set. How can I insert the CustID value in the DoCmd.GoToRecord , , acNewRec form?? see VBA code below.
Dim stDocName As String Dim stLinkCriteria As String
Hi, got a slight problem. As part of an ASP website I'm trying to insert into two linked tables in an access database using SQL. Standard SQL syntax woiuld be:
INSERT ALL INTO tblParent(name, Address) Values ('Dave','Home') INTO tblChild(age, phone) Values ('35','1234567');
Thing is, does Access SQL support the INSERT ALL INTO statement? I keep getting syntax errors and I can only guess it might be this. if so, how do you make multiple table inserts into Access through SQL?
is it possible to have an insert query to fill some of the parameters by select query from other table and rest of the parameters by passing the value.
ex: insert into table1(a,b,c) values (10,select b fom table2 where.... , 35);
i tried with the above syntax but was unable to succeed.
if this is not possible can anyone help me in solving the problem
I have one question regarding the use of an INSERT INTO query. I tried the following code, which I found on http://www.techonthenet.com/sql/insert.php, however unfortunately it does not work. The text on the website says: “The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.” However, it just produces an error code, which says that Jet cannot find the dual table. Why doesn’t it work?
INSERT INTO tbX ( Col1, Col2, Col3 ) SELECT 'Test', 15, #3/3/2007# FROM dual WHERE not exists (select * from tbX where tbX.Col1 = 'Test');
Additionally, I try to do the same thing via Excel. I use here I code, which I found in a forum, too. However, unfortunately it doesn’t work as well… any hints?
Option Explicit
Private adoCn As ADODB.Connection Private adoRs As ADODB.Recordset Private adoCmd As ADODB.Command
Sub OpenDatabase() Dim sDBPfad As String
sDBPfad = "C:TestQuestion.mdb"
Set adoCn = New ADODB.Connection
With adoCn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source=" & sDBPfad .Open End With End Sub
Sub InsertData() Call OpenDatabase
Set adoRs = New ADODB.Recordset
With adoRs .ActiveConnection = adoCn .Source = "tbX" .CursorLocation = adUseClient .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open .AddNew .Fields("Col1").Value = "TEST" .Fields("Col2").Value = 99 .Fields("Col3").Value = #3/3/2007# .Close End With End Sub
Can someone tell me how to insert a blank row after every 4th row of data?
Here is the query:
SELECT B.NAME, B.WORKER, C.[SUB SYS] INTO D FROM B, C;
data comes back like this NAMEWORKERSUB SYS GORDON N PAUL0000ACRT GORDON N PAUL0000AENF GORDON N PAUL0000AFIN GORDON N PAUL0000ARAP GORDON N PAUL000ATCRT GORDON N PAUL000ATENF GORDON N PAUL000ATFIN GORDON N PAUL000ATRAP
I want it like this
NAMEWORKERSUB SYS GORDON N PAUL0000ACRT GORDON N PAUL0000AENF GORDON N PAUL0000AFIN GORDON N PAUL0000ARAP
GORDON N PAUL000ATCRT GORDON N PAUL000ATENF GORDON N PAUL000ATFIN GORDON N PAUL000ATRAP
