Insert row, table to table - where append goes to correct record

Hi,

As I cannot use INSERT INTO with values specified to WHERE.
I have created an interim temporary table where the record contains two text 
strings.Now I want to be able to take those text strings and append to the 
correct  table where the record is set by an ID number.

First_Name = Fred
Last_Name = Jones

Its a new contact so it is too be added to a customer with an ID = 1375

Now I can't use the following sql statement
strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _
& " VALUES (""" & strFirst & """, """ & strLast &
""")" _
& " WHERE [CustID] = '" & Me![CustID] & "';" 

because "When you have a VALUES list for an append query, you cannot have a 
WHERE
clause. SQL engine has no idea which table's records are to be filtered
because you've provided the value list for the fields as the data source." 
from Ken Snell

so I took the text and Inserted into a temp table
strsql_Temp = "INSERT INTO [tbltempSERContact] ( [FIRST_NAME], [LAST_NAME] ) 
" _
                         & " VALUES (""" & strFirst & """, """ & strLast & 
""");"

now I want to be able to take this and append to the correct customer

'now append row from tbltempSERContact into tblCustomers
             strsql = "INSERT INTO [tblCUSTCONTACTS] [FIRST_NAME], 
[LAST_NAME]  " _
                         & " SELECT [FIRST_NAME], [LAST_NAME]" _
                         & " FROM [tbltempSERContact]" _
                         & " WHERE CustID = " & Me!CustID
Debug.Print strsql
which gives a print out of 
INSERT INTO [tblCUSTCONTACTS] [FIRST_NAME], [LAST_NAME]   SELECT 
[FIRST_NAME], [LAST_NAME] FROM [tbltempSERContact] WHERE CustID = 1391

And the error
Run-Time 3134
Syntax error in INSERT INTO

I'm getting lost here - I have text derived via VBA = values which I cannot 
append to a correct customer - HELP assitance would really be appreciated

0
Utf
12/16/2009 1:21:02 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

2 Replies
1170 Views

Similar Articles

[PageSpeed] 31

Billp wrote:
>As I cannot use INSERT INTO with values specified to WHERE.
>I have created an interim temporary table where the record contains two text 
>strings.Now I want to be able to take those text strings and append to the 
>correct  table where the record is set by an ID number.
>
>First_Name = Fred
>Last_Name = Jones
>
>Its a new contact so it is too be added to a customer with an ID = 1375
>
>Now I can't use the following sql statement
>strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _
>& " VALUES (""" & strFirst & """, """ & strLast &
>""")" _
>& " WHERE [CustID] = '" & Me![CustID] & "';" 
>
>because "When you have a VALUES list for an append query, you cannot have a 
>WHERE
>clause. SQL engine has no idea which table's records are to be filtered
>because you've provided the value list for the fields as the data source." 
>from Ken Snell
>
>so I took the text and Inserted into a temp table
[snip the stuff that went totally off the rails]

If you want to insert a new contact into the contacts table
with a foreign key to the current customer record, then use
the current customer record's primary key value in the value
list, not in a where clause.

"INSERT INTO [tblCUSTCONTACTS] " _
		& "(CustID, [FIRST_NAME], [LAST_NAME]) " _
		& " VALUES (" & Me.CustID & ",""" & strFirst & """, """
& strLast & """)"

-- 
Marsh
MVP [MS Access]
0
Marshall
12/16/2009 6:36:20 AM
Thank you Marrshall

"Marshall Barton" wrote:

> Billp wrote:
> >As I cannot use INSERT INTO with values specified to WHERE.
> >I have created an interim temporary table where the record contains two text 
> >strings.Now I want to be able to take those text strings and append to the 
> >correct  table where the record is set by an ID number.
> >
> >First_Name = Fred
> >Last_Name = Jones
> >
> >Its a new contact so it is too be added to a customer with an ID = 1375
> >
> >Now I can't use the following sql statement
> >strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _
> >& " VALUES (""" & strFirst & """, """ & strLast &
> >""")" _
> >& " WHERE [CustID] = '" & Me![CustID] & "';" 
> >
> >because "When you have a VALUES list for an append query, you cannot have a 
> >WHERE
> >clause. SQL engine has no idea which table's records are to be filtered
> >because you've provided the value list for the fields as the data source." 
> >from Ken Snell
> >
> >so I took the text and Inserted into a temp table
> [snip the stuff that went totally off the rails]
> 
> If you want to insert a new contact into the contacts table
> with a foreign key to the current customer record, then use
> the current customer record's primary key value in the value
> list, not in a where clause.
> 
> "INSERT INTO [tblCUSTCONTACTS] " _
> 		& "(CustID, [FIRST_NAME], [LAST_NAME]) " _
> 		& " VALUES (" & Me.CustID & ",""" & strFirst & """, """
> & strLast & """)"
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 
0
Utf
12/16/2009 8:01:01 PM
Reply:

Similar Artilces:

Can't edit publisher_address_list after appending records with Acc
With Office 2000 if I create a publisher_address_list and then use Access to append records to it, I cannot use the Edit publisher_address_list feature in Publisher to edit or add records. Is there a setting I need to change? ...

SOP Returns: how to void correct?
I'm looking for step-by-step instructions on how to void a Sales return document in GP correctly please. I'm currently using GPv8 (but this shouldn't matter). I've create and posted a sales return document in SOP. Now, I discover its incorrectly so I want to void the document. I dont want to apply the document against any other SOP documents - I just want to get rid of it. How do I go about this correctly so that the SOP document in Inquiry (Inquiry > Sales > Sales documents) is updated to be a voided document? In which table does this updated informatio...

correct formulation of expression in control source
How can I combine 2 expressions that use the DCount function? For example, in control source, =DCount("[Field1]","qryX","[Field1]=True") will give me a value Y which I need to add to another value obtained by =DCount("[Field1]",qryZ","[Field1]=True"). I have tried various bits such as =DCount("[Field1]","qryX" And "qryZ","[Field1]=True") but that does not work. Can anyone help? Regards Have you tried =DCount("[Field1]","qryX","[Field1]=True") + DCount("[Field1...

Excel 12, insert chart does not work. Exel restart. Why?
When I insert chart and select chart data, MS Office encounters a problem and need to close. Another thing: On chart tools the desings are empty. Any solution? In article <A678D3D8-566D-46A1-8A19-5F85D34BB2C8@microsoft.com>, tujauja@discussions.microsoft.com says... > When I insert chart and select chart data, MS Office encounters a problem and > need to close. > Another thing: > On chart tools the desings are empty. > Any solution? > Office 12 is still in beta. You should have received information from MS about where and how to post questions about it. -- Rega...

Item record in RM00101
Does anyone know how an item record is entered into RM00101? I only have one in there and I can't figure out how it got there. Thanks. Chris, Do you mean there is an inventory item in there? If so, most likely there was either a data entry error or an import direct to table error. If that's not what you mean, can you please elaborate? -- Victoria Yudin Dynamics GP MVP Want to use Crystal Reports with GP? http://www.flex-solutions.com/gpreports.html blog: http://www.victoriayudin.com "Chris" <Chris@discussions.microsoft.com> wrote in message news:AEA12C29-07...

Unhiding rows
In Excel 2000, someone has hid the first 45 rows of the worksheet. I can't unhide them. The sheet is NOT protected. I tried various things including Edit/Go to/A1/Format/Row/Unhide but nothing works. Any clues? .......Baffled in Ontario row height? -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Connie" <marverncon@hotmail.com> wrote in message news:0b4001c35c24$077545d0$a601280a@phx.gbl... > In Excel 2000, someone has hid the first 45 rows of the > worksheet. I can't unhide them. The sheet is NOT > protected. I t...

Insert random number only if cell empty
Hello. I'd like my spreadsheet to place a 7 digit random number into a cell only the cell is empty, otherwise leave the contents as is. I simply don't want the number to change if one is already present. I currently keep the number static by pressing F9 when I enter the RAND formula, but that forces me to manually change the number when I save the spreadsheet to another name. I'd like the spreadsheet to automatically calculate a new number in the cell as soon as I erase the previous number in that cell. Any help on this issue will be appreciated. Thanks. "M.L.&qu...

delete empy rows
Hi, I have a sheet with 3000 lines, I am using only thre coomns A, B, C How can I delete all rows that has empty cells in colomn C i.e. row 5 contains Sam in colomn A , 2 in colomns B , nothing in colomn C. I want to delete this row row 6 contains 3 in colomn A, nothing in colomns B, 34 in colomn C. I want to keep this row. can this be done Khalil Try this on a *spare* copy .. Select col C Press F5 > Special > Blanks > OK Right-click on the selection > Delete > Entire row > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>y...

selecting rows using a variable
I am using excel 2003 and I need to select rows using a variable. Here is what I have now: Dim name As String name = Range("e800") ActiveWorkbook.Sheets("individual stats").Range("a1:a1540").Select Selection.EntireRow.Hidden = True Dim Start As Integer Dim finish As Integer Start = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("t800:t881")) finish = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("u800:u881")) ActiveWorkbook.Sheets("individual stats&q...

select and delete all blank rows
I could save a lot of time if I knew how to select and delete all blank rows from a document, and also separate things. An example of separating things would be a list contains fruits, vegetables and meat. I could separate all the fruits from the list easily. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then clic...

Pivot Tables #32
Hi - I have got to learn fast how to make pivot tables from a database in Excel 2003. I know how to set up the database, however I just don't seem to grasp the logic or procedure to set up the table. Is there somewhere on the web that I can go do get almost a step by step explanations and examples to make them clearer? Thank You There are instructions and links for pivot tables on Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivotstart.htm sue t wrote: > Hi - I have got to learn fast how to make pivot tables > from a database in Excel 2003. I know how ...

Rename Multiple Tables at once
I have an Access 2007 database that has a lot of linked tables and I want to 'batch' rename them. For example, they all start with "dbo_" and I want to rename all of them to "PM_". How can I make that happen? I've been searching and cannot find a solution. Any help that anyone can provide is greatly appreciated. Dim dbCurr As DAO.Database Dim tdfCurr As DAO.TableDef Set dbCurr = CurrentDb() For Each tdfCurr In dbCurr.TableDefs If Left(tdfCurr.Name, 4) = "dbo_" Then tdfCurr.Name = "PM_" & Mid(tdfCurr.Name, 5)...

65536 Rows for how long????
Hi there! I was wondering: is Microsoft planning on expanding the 65536 row limit? They most deffinitely should! Best regards, Albert "Albert" <Albert@discussions.microsoft.com> wrote > I was wondering: is Microsoft planning on expanding the 65536 row limit? > They most deffinitely should! Why "certainly"? -- Bob http://www.kanyak.com Well, not "certainly", but its certainly my opinion. I think 65536 is not enough. I believe that, among other things, Excel was made for managing large amounts of information efficiently. I think that in this d...

How can I color every other row
Help please. I want to make it easier to use a large spreadsheet where two rows are used for each record. Filling in the background color of every second row prevents mistakes when entering data. I want to color only the used range, not the entire row. I recorded a macro and got the following: TheRange.Activate With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With I really appreciate the help. On way: Dim iCtr As Long With ActiveSheet.UsedRange For iCtr = .Row To .Rows(.Rows.Count)....

Rows to Columns
I have data arranged in rows across a spread sheet, for example B2, C2, D2, etc. How can I change that to a column, for example A1, A2, A3, etc without reentering all the data? Thank you --Select B2:D2. Copy the range --Select A1. Right click>PasteSpecial>Transpose>OK If this post helps click Yes --------------- Jacob Skaria "Walter Seaton" wrote: > I have data arranged in rows across a spread sheet, for example B2, C2, D2, > etc. > How can I change that to a column, for example A1, A2, A3, etc without > reentering all the data? >...

Pivot Tables #16
What are the point of Pivot Tables? I was asked about it at work, and I have no idea what they are and how useful they are? Very useful, you could have a look here to make a start, http://www.ozgrid.com/Excel/excel-pivot-tables.htm Regards, Alan. "Jason Woolsey" <jasonmwoolsey@me.com> wrote in message news:72F80D21-15FD-4B00-B934-FE2A46EACC3B@microsoft.com... > What are the point of Pivot Tables? I was asked about it at work, and I > have no idea what they are and how useful they are? Hi Jason They are the hidden gem in Excel. A fantastically easy way of ana...

m_strFilter and multiple tables.
Hi, Can I specify a table list in a m_strFilter statement? ie: How do I accomplish the following using m_strFilter: SELECT * FROM RS1,RS2 WHERE RS1.DOCN = RS2.DOCN Thanks. ...

Table Design Issue
I have a database the has a Parts Table and a Vendors Table. what needs to happen is i need a form that will allow someon to view all the information about that parts itself and the vendors that supply the part but every part is available by all 5 vendors for different cost, and i need to construct tables that when part information is undated or deleted it will reflect across the board and on the form itself be able to see the vendor name and the part information for that vendor. what type of relationships will be involved here Answered in tablesdbdesign. ...

Editing Table of Authorities Categories
Heres a good one to chew on... There are occasions when we want to separate out the Statutes category into two separate categories, "federal statutes" and "state statutes", because Word doesn't recognize that federal statutes must appear before state statutes, and we don't want to run the risk of manually moving the federal up before the state and then updating the table of authorities and forgetting to do the manual work again. Here's my issue: I have macro that reassigns categories 2 through 8, essentially renaming Statutes and Other Authoriti...

How To Align Decimals In A PP 2007 Table
I have financial data with one decimal point where I want to align to the decimal point in a PowerPoint table. How do I select the full column and align to the decimals? Thanks! (B^>)-]=[ You can't. I've asked for this feature ever since about PowerPoint 2000, but it's still not there. Frustrating. You have to do this individually in each cell. Basically, choose the decimal tab option to the left of the ruler, then add a tab (Ctrl+Tab) before the text in each cell as necessary. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT ...

Using Min to Calculate across a table
I am attempting to massage data so I can see what the maximum number of pieces I can produce based off of various components. My query to figure out how many of each component is available per unit is complete and the output is: Part_Number, MaxComponent1, MaxComponent2,MaxComponent3, MaxComponent4 Does anyone have any suggestions as to how I could find the MIN of the components and group by part number? Cheers See the MinOfList() function here: http://allenbrowne.com/func-09.html A better solution would be to create a related table with many records for the combinations that are va...

Entering the same value into two table fields at the same time
Say that there is a table which has 2 fields (A, B) There is a form [Form1] (data entry type) to enter data for A by `TextBoxA`. There is also one more `TextBoxB` which is Not Visible. My target is to record the value of (entered thtough `TextBoxA`) into B field of the table as well. So I write down a code "Before Insert" of the Form1 that: " Form_Form1.TextBoxB = Form_Form1.TextBoxA " It is not recording A values into the field B... Where am I wrong? Can you help me please? Sory !!! When I write down the same code into `OnExit` of TextBoxA, it is working...!! yes, ...

Pivot Table Report: Getting average of difference of two date columns
Hello I have data in excel sheet as: WORK_ID PROJECT START_DT END_DT 1001 P1 1/1/2006 1/10/2006 1002 P1 1/25/2006 2/20/2006 1003 P1 3/15/2006 3/20/2006 I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...

Filtering rows with tracked changes
Hi I am trying to work out how to filter the rows that have been updated over a period of a couple of days. I understand that you can highlight records with tracked changes in a certain time period � but I would like to be able to just have those rows that have highlighted cells to look at. Any ideas out there? You can list changes on a new sheet but it doesn't allow you to see the whole row. Cheers, Mike P.s � is it possible to highlight changes between dates � I can only see highlighting changes since � ...

Microsoft Word Table design in Access 2007
Is there a method for inserting a table (not an access table, but a word table with columns/rows/header, etc...) in a description field in a form or below the description field? I'm creating a database for development business rules and sometimes a business rule needs a table to explain or identify the heirarchy/relationship of items for the rule. I don't want this to link to anything else but the Business Rule ID that I am currently in. Thanks in advance! You could use a memo field but the data will not be separated by grid lines. You would need to add spacing and ...