469,347 Members | 19,297 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,347 developers. It's quick & easy.

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

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.
Sep 16 '10 #1
3 1260
1,287 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1.     Dim strSQL As String
  3.     strSQL = "INSERT INTO MyTable VALUES (" & txtNumber _
  4.            & ", " & txtAnotherNumber & ", """ & txtString _
  5.            & """, """ & txtAnotherString & """)"
  7.     'MsgBox strSQL  'so you can see that it is right
  9.     DoCmd.SetWarnings False
  10.     DoCmd.RunSQL strSQL, 0
  11.     DoCmd.SetWarnings True
Sep 17 '10 #2
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
Sep 17 '10 #3
1,287 Expert 1GB
Your declaration of the strSQL variable is fine, but you seem to have a typo at
Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 17 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Paolo | last post: by
1 post views Thread by James | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.