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
1154 Views

Similar Articles

[PageSpeed] 49

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:

How do I insert a clip art picture into a text box in word 2003
Trying to insert a picture into a text box in Word 2003 with very little success. Using Word as some students in the community do not have access to Publisher. Trying to create 4 invitations to a page. Want to use a text box to include text and graphics and then copy and paste four times. Help. Hi Lindas you'ld be better of posting to microsoft.public.word.newusers rather than an excel newsgroup personally, i would use a 2x2 (or 1x4) table in word for this rather than text boxes. Cheers JulieD "Lindas" <Lindas@discussions.microsoft.com> wrote in message news:...

Determine Number of Characters in a Row
I have a spreadsheet that contains network information. I am trying to determine the number of times a specific letter appears in a row. Is there a formula that will look for that letter, count the number of times it appears in that row and then input it into a specific cell? Thanks, Ted --- Message posted from http://www.ExcelForum.com/ "t2true" <t2true.yhfzy@excelforum-nospam.com> wrote in message news:t2true.yhfzy@excelforum-nospam.com... > I have a spreadsheet that contains network information. I am trying to > determine the number of times a specific letter appe...

How do I put information into a NEW journal table?
Having created a New table in Outlook 2003 Journal with different column headings (to record family tree history), how can I put information into each of the new headings, which do not appear on the Journal Entry form? You'll need to create a custom fields for the new information. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWS...

Look up tables
Hi all Sorry to interrupt your Excel forum, but does anyone know of a simila forum or website that deals with Microsoft Access? Also, how do you create Lookup tables in Excel? I've had a quick loo in the tips section but couldn't find anything Thanks Mar -- mark_b241 ----------------------------------------------------------------------- mark_b2410's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1684 View this thread: http://www.excelforum.com/showthread.php?threadid=47818 Hi Mark, you're on the right page, just 2 lines above excel is the Acces...

lookup in one sheet and insert in second if not found
I have created a workbook with two sheets in it. I enter all my order in sheet one and the sheet two totals them. The column on my sheets ar as follow�. Sheet one STYLE SMALL MEDIUM LARGE 100 1 1 1 101 1 1 1 100 1 1 1 102 Sheet two STYLE SMALL MEDIUM LARGE 100 2 2 2 101 1 1 1 I have used the SUMIF command in the second sheet, which is correct Therefore my totals are correct. The only problem I am having is thi ��.that I have to enter the style numbers in the second sheet as well. What I want to do is, to have some kind...

txt search box returns all records when wildcard is entered
I have used a simple textbox search originally created by Graham Thorpe [Event Procedure]. When I open my search form If I type in and abbreviated search text for example 206 vs. 206-030-987-101 It returns all of the records in the database. If I search on 206-030-987-101 it will find that specific record and display it, but all records are available. I would like help with using the textbox search to bring up only those records containing the exact match if one is available and not physically being able to view any other records in the current form. I would like to also be able to pe...

Pivot Table
I have 2 lists with approximately 100 items/rows, with 5 pieces of data in each row. Each list has a common element (account number). Many times the account number shows on both lists, and I want to combine How do I combine both lists to use as source data in a pivot table? ...

How to limit subform to one record?
I have certain instances where I need a subform to be able to accept one record, and no more. I've been trying setting allowadditions to false if dcount (records for that mainform record) >0, but I can't get the setup right in terms of which event to trigger it, etc. Any advice? On Wed, 30 Jan 2008 13:41:00 -0800, markmarko <markmarko@discussions.microsoft.com> wrote: >I have certain instances where I need a subform to be able to accept one >record, and no more. > >I've been trying setting allowadditions to false if dcount (records for that >mainf...

How do I add numbers across columns of a single record?
I have a column that lists items. I then have three columns next that list the number of items in each storage location. I would like to create a query that will add all three storage areas to give me a total of each item. I would even be pleased to create a new column on the worksheet that would total the storage areas. I can only seem to run totals down a column not across a record. Thank you!! jennyls, Columns in an Access table are ot like columns in an Excel Spreadsheet. To do what you want you would have to create a query and put: MyTotalsColumn: Nz([NameOfColum...

Unmatching row numbers
Okay, I'm trying to use this formula to tell me if there is a matching number in another worksheet IF(Sheet1!$E$1:$E$65536 = "Value from Sheet2 (D3)","Found","") But Excell will only tell me it is "found" when the row numbers match, how can I overcome this setback. Thanks Again, ~E~ ------------------------------------------------- Dear Creperum You dont mention if the row numbers need to match (eg sheet1 d1=sheet2 e1=Sheet3 e1 or whether you only want sheet1 a1 to look at both ranges to see if the value is present. depending on which way you w...

Editing an inserted Object in a Task
An Inserted Object (Excel spreadsheet) in a task could be opened and edited in Office 2000 with a double-click, but not in Office XP or 2003. Are there any work-arounds? ...

Lost stop recording toolbar
I lost the stop recording toolbar. The one with the Relative Reference button. How do I get it back? Any help appreciated! View / Toolbars / Stop Recording -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Irv" <i_leb@sympatico.ca> wrote in message news:OlVBdzh8DHA.632@TK2MSFTNGP12...

Reading one Record at a time till i reach EOF in VC++ using ODBC
Hi, i am working on Vc++6.0. I hve a req where in i need to read one record at a time till i reach EOF from the database.Can anyone tell me how to do this Very urgent Thanks Use CDatabase and CRecordSet classes. // Embed a CDatabase object // in your document class CDatabase m_dbCust; // Connect the object to a // read-only data source where // the ODBC connection dialog box // will always remain hidden m_dbCust.OpenEx( _T( "DSN=MYDATASOURCE;UID=JOES" ), CDatabase::openReadOnly | CDatabase::noOdbcDialog ); CRecordSet rs;rs.m_pDatabase=&m_db...

65000 Row Limitation from Access to Excel
This question has been asked a couple times, but the answer seems to be unclear so I will ask again. I have query results in Access that I would like to work with in Excel - I like Excel pivot tables better than Access pivot tables. If I highlight the 287K line Access table, right click on copy, then paste into the 1 million plus row spreadsheet capacity in Excel, it only pastes 65,000 lines. Exports from Access to the million row plus spreadsheet work similarly only exporting 65,000 lines of the much larger table. I can successfully export the data from Access to a text file, then impor...

CPropertySheet -- insert a page?
Hi, Is it possible to insert a page to CPropertySheet? I only foud teh AddPage() function, I assume it adds the page as the last page, but I need to insert a page. Thanks! WJ Why don't you add the 'rearraged' list of pages again to the sheet when you want to do this. "WJ" wrote: > Hi, > Is it possible to insert a page to CPropertySheet? I only foud teh AddPage() > function, I assume it adds the page as the last page, but I need to insert a > page. > Thanks! > > WJ Thanks. I have thought about this. Will the tab flash though? I guess I nee...

Printing reprot from a form for the record viewed
I would like to print a report (sort of an invoice) from a form where i am entering the final data for the invoice. but the report is generated for the entire table. for time being i am printing the form. but the actual report must have a different format and some more additional informations. So i want help to print the report only for the record viewed in the form and that i should be able to give the print commond from the form itself through the command button.thanks Try this link on "How To Print A Single Record from a Microsoft Access Form into a Report"http://www.databasedev.c...

Paste Linked Table
Hi Is there any way to paste an already linked table as another linked table as you can do manuualy but programatically? Thanks Martin wrote: >Is there any way to paste an already linked table as another linked table as >you can do manuualy but programatically? A linked table is just another TableDef object with something in its Connect property. That means you can use the DAO CreateTableDef method (see VBA Help) and copy the properties of the existing linked TableDef. That's essentially what Access does when you use Copy/Paste in the Database window. OTOH...

save record isn't available now
Hello, I have a table that collects data from about 150 spreadsheets each day, and then I refer to this table in a Report, without any problems. I now want to use the same data from this table, but in a Form but I get the message "save record isn't available now". From what I've read on previous messages from others, the problem is that it can't write to the table, thus it can't save. So assuming that is the problem, is there some way around this? If it involves VBA code, where would I put the code, would it be in one of the Form's Events? Thanks very mu...

unhide data in pivot table
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I saw that this has been raised before but did not see a solution posted. Has anyone found a workaround? The issue again, I have created a pivot table and in it I ahve e.g columns with data for different years. I want to hide the data on one year and select thehide functionin the fieldsettings and it does disappear neatly, however, when I want to turn this back on it is not possible. It does seem like the cmd button should do the trick when i click it witht he year marked in the fieldsettings, however, it has...

calculating a median in a pivot table?
Hi. I'm using version 8. I'm hoping to find out how to calculate a series of median values for different groups (of costs) in a pivot table. Hi not possible directly in the pivot table AFAIK -- Regards Frank Kabel Frankfurt, Germany "Peonies" <Peonies@discussions.microsoft.com> schrieb im Newsbeitrag news:25D2CACD-C75A-4F17-8DB9-64A3F456F79A@microsoft.com... > Hi. > I'm using version 8. > I'm hoping to find out how to calculate a series of median values for > different groups (of costs) in a pivot table. ...

Printing Pivot Table Pie Charts
I have 1400 names in a list each, person has their own data. I created a pivot pie chart to display that data. Can anyone tell me how to print the pivot table so I get a separate sheet for each name selected...without me having to click on the dropdown list each time? Also does anyone know how to hide a field, but have the data still be displayed in the pie chart? thanks ...

how do i narrow down the items in a pivot table drop down list?
I have business groups in one column and products for those business units in another column (with associated data in subsequent columns). When i create a pivot table and then select one particular business unit, on the drop down for the products i only want to see the products that relate to that business unit. Right now i still see all of the products and it is a big list with lots of different products. Is there a way to narrow down the drop down list when a higher order drop down in selected? Click in the down arrow for the Business Unit and you can select which business unit you...

Inserting pictures in a CHM file
With the HTML Help Workshop from MS, I'm trying to add pictures to some of my pages with mixed results. Small pictures seem to work ok but larger ones (>500 kb) don't. After compiling the help file, the frame is present with no picture. Can anyone help? Best regards, Fred Fred, > With the HTML Help Workshop from MS, I'm trying to add pictures to some of > my pages with mixed results. Small pictures seem to work ok but larger ones > (>500 kb) don't. After compiling the help file, the frame is present with > no picture. What tool are you using to ...

How to sort records by color?
I like to distinguish different kinds of records by different color.I also want to sort them by color so that the records of the same color can go side by side.Is it possible to do that? Any reply is appreciated. -- Welcome to My MSN Space: http://spaces.msn.com/members/rover317 http://www.cpearson.com/excel/SortByColor.htm -- Kind regards, Niek Otten "���˵�˫��" <rover317@hotmail.com.discuss> wrote in message news:u7FoYZ33FHA.3592@TK2MSFTNGP12.phx.gbl... >I like to distinguish different kinds of > records by different color.I also want to > sort them by colo...

Error when no records meet criteria
I posted this problem on 3/24/10 and received a reply that didn't work as needed. I've pasted the original messages below. I would appreciate any help in correcting code to prevent error from occurring. Thanks in advance, Pam ____________________ Subodh, Thank you for replying. I did as you suggested and now I'm getting the same error at the next section of code where B2=5 and there are records that match this criteria. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ > "=AND(RIGHT(D2)<>""p"",B2=5)" >...