SQL Insert Value

[Gus Chuch]

I using a SQL INSERT INTO to add new customers from a mailing list table to
my customer table and it works OK. But I have a field in my customer table
called STATUS that I need to insert a value of ‘ACTIVE’ for the new customer.
Any ideals on how would I modify this SQL statement to insert this valve?

strSQL = "INSERT INTO tblCustomer ( Name, Address, City, State, Zip,
Telephone_Number, County, Customer_Type )" & _
"SELECT tblMailingList.Name, tblMailingList.Address1,
tblMailingList.CITY, tblMailingList.STATE, tblMailingList.ZIP,
tblMailingList.[Telephone Number], tblMailingList.County,
tblMailingList.[Location Type]" & _
"FROM tblMailingList " & _
"WHERE
(((tblMailingList.Name)=[Forms]![frmMailingList]![txtName]));"

 

[Ofer Cohen]

Try

strSQL = "INSERT INTO tblCustomer ( Name, Address, City, State, Zip,
Telephone_Number, County, Customer_Type, Status )" & _
"SELECT tblMailingList.Name, tblMailingList.Address1,
tblMailingList.CITY, tblMailingList.STATE, tblMailingList.ZIP,
tblMailingList.[Telephone Number], tblMailingList.County,
tblMailingList.[Location Type] , 'ACTIVE' As NewStatus " & _
"FROM tblMailingList " & _
"WHERE
(((tblMailingList.Name)=[Forms]![frmMailingList]![txtName]));"

 

[Allen Browne]

You can include a literal value in the Sql string:

strSQL = "INSERT INTO tblCustomer ( Name, Address, City, State,
Zip, Telephone_Number, County, Customer_Type, Status )" & _
"SELECT tblMailingList.Name, tblMailingList.Address1,
tblMailingList.CITY, tblMailingList.STATE, tblMailingList.ZIP,
tblMailingList.[Telephone Number], tblMailingList.County,
tblMailingList.[Location Type], True As Status" & _
"FROM tblMailingList " & _
"WHERE tblMailingList.Name= """ & [Forms]![frmMailingList]![txtName] &
""";"

BTW, Name is not a good name for a field. Since nearly everything in Access
(forms, reports, ...) has a Name property, code gets confused between the
Name of the form/report, and the field called name. Consider renaming the
field to CustomerName or something, and then changing the affected queries,
forms, report, macros, and code.

Make sure Name AutoCorrect is off:
http://allenbrowne.com/bug-03.html

List of field names to avoid:
http://allenbrowne.com/AppIssueBadWord.html