472,345 Members | 1,518 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,345 software developers and data experts.

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 1432
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

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

Similar topics

by: Paolo | last post by:
I would like some help on the use of combo boxes. I have created a form and named it Customers. This form's record source is a table, also named...
by: James | last post by:
I am used to VB6 but need to develop something in .Net. I need to create several bound combo-boxes which will use lookup tables to get their...
by: Edwinah63 | last post by:
Hi everyone, Please let there be someone out there who can help. I have two BOUND combo boxes on a continuous form, the second being dependent...
by: GaryGreenberg | last post by:
I am developing a web page for order processing using Spring MVC. In my JSP I have a table populated from the list of orders that are passed in the...
by: Chrish1987 | last post by:
Dear All, I have the following Tables: Dealer: DealerID - AutoNumber DealerName - Text Business: Business ID - AutoNumber
by: Dave | last post by:
I want to put the information that the user selects in my combo boxes into a subform that lies on the same form as the combo boxes. Thanks for...
by: Dave | last post by:
Hello all, First I'd like to apologize...This post was meant to be put in my previous post, but I tried many times without success to reply...
by: deanndra | last post by:
First, I want to say thank you to Scott and the others who replied to my first post here. I had to put that database on hold for the moment when I...
by: PiCubed | last post by:
I'm trying to create a form that uses combo boxes to select a table and then from that table I need to create linked combo boxes that searches for...
by: doma23 | last post by:
I have 5 combo boxes which represent certain periods. The row source of these 5 combo boxes is table tblRefDate. What I want to do is to make a...
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.