How to Use results of text and combo boxes for new record in table

[dblack64]

I am working in Access 2007.
I have a form named Master Terms List that contains 4 bound text boxes and 7 cascading combo boxes. The bound text boxes have data sources from a table named Termslist. The record source for the 7 cascading combo boxes is also the table Termslist.
The names of the fields on form Master Terms List and the type of controls they are follows:
Termslistmonth - text box
DateReceived - text box
Coordinator - combo box
Customers - combo box
Vendor - combo box
shared - combo box
DBA - combo box
Brand - combo box
Grpnbr - combo box
vastart - text box
vaend - text box
When a new record is generated, the user enters the terms list month, date received, selects coordinator name from a dropdown list, selects customer, vendor, shared vendor, DBA(doing business as), brand name, group number, start date and end date. The cascading combo boxes work fine and are all synchronized.
I need to use the results of these selections as the base data for a new record that will be saved to a temporary table named temptermslist. I have attempted to write some code that defines the fields to be appended to the temptermslist table using the results in the form Master Terms List.
I have the code linked to a button named Savenewva. I am not really proficient in VB but have managed in the past to find enough code on help boards etc to get the job done.
The code is below.

Option Explicit

Private Sub BtnSaveNewVA_Click()

Insert Into(Temptermslist(TermsListMonth, DateVendorAgreementReceived, CoordinatorName, CustomerName, VendorName, SharedVdr, DBA, BrandNames, PRIMEShareGroupnbr, VAStartDate, VAEndDate))
"SELECT (TermsListMonth,DateVendorAgreementReceived,Coordi natorName,CustomerName,VendorName,SharedVdr,DBA,Br andNames,PRIMEShareGroupnbr,VAStartDate,VAEndDate) FROM" & _
"Forms![Master Terms List] WHERE (((TermsListMonth)=[forms]![Master Terms List].[txttermslistmonth]) AND ((DateVendorAgreementReceived)=[forms]![Master Terms List].[txtdatereceived]) AND ((CoordinatorName)=[forms]![Master Terms List].[cbocoordinator])AND ((CustomerName)=[forms]![Master Terms List].[cbocustomers]) AND ((VendorName)=[forms]![Master Terms List].[cbovendor]) AND ((SharedVdr)=[forms]![Master Terms List].[cboshared]) AND ((DBA)=[forms]![Master Terms List].[cbodba]) AND ((BrandNames)=[forms]![Master Terms List].[cbobrand]) AND ((PRIMEShareGroupnbr)=[forms]![Master Terms List].[cbogrpnbr]) AND ((VAStartDate)=[forms]![Master Terms List].[txtvastart]) AND ((VAEndDate)=[forms]![Master Terms List].[txtvaend]))"

End Sub

The code fails on the first line at the word temptermslist. The error message from access is "compile error: sub or function not defined".
I have tried to figure out what this means but am stuck and have been researching this issue for about 1 week. Any help would be appreciated.

[ChipR]

Here is an example so you can see the syntax. You want to build a string with the values from your form, then run it. This example takes the values from txtBoxes.

    Dim strSQL As String
 
    strSQL = "INSERT INTO MyTable VALUES (" & txtNumber _
           & ", " & txtAnotherNumber & ", """ & txtString _
           & """, """ & txtAnotherString & """)"
 
    'MsgBox strSQL  'so you can see that it is right
 
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL, 0
    DoCmd.SetWarnings True

[dblack64]

Chip,
Thanks for your help. The code I have written is below. I am getting a "compile error Variable not defined and strSql on the insert into line of code is highlighted. I'm not exactly sure how to define the variable. Any additional help would be appreciated.

Option Compare Database
Option Explicit


Private Sub BtnSaveNewVA_Click()

Dim strSQL As String

srtSQL = "Insert Into Temptermslist Values (" & txtTermsListMonth & "," & txtDateReceived & "," & cboCoordinator & "," & cbocustomers & "," & cboVendor & "," & cboShared & "," & cboDBA & "," & cboBrand & "," & cbogrpnbr & "," & txtvastart & "," & txtvaend & """)"

MsgBox strSQL("I hope this is right")
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL, 0
DoCmd.SetWarnings True


End Sub

[ChipR]

Your declaration of the strSQL variable is fine, but you seem to have a typo at

srtSQL = ... 

I don't think that MsgBox line will compile (when you get there). Just take out the part in the parenthesis for now.

For building your string, you will need to match up the quotation marks. Take a look at this post which will explain everything:
Quotes (') and Double-Quotes (") - Where and When to use them

We'll be happy to help if you still have issues afterward.