INSERT INTO from form into table 2

[merlynsdad]

I know this is really simple, but I can't remember part of this SQL phrase. Call it a Senior Moment. I'm taking data that a user has input into a form and dumping it into an existing table. I have this so far

strSQL = "INSERT INTO tblMaster ([InputDt]),Issue" _
& "SELECT Date() AS [InputDt] " _
& "xxxxxxxxxxx;"
db.Execute strSQL

What I can't remember is what the next line is that pushes the Issue into the table! Issue is strIssue coming out of the form.



If the square peg won't fit in the round hole, sand off the corners.

 

[Andrzejek]

Google is your friend, but...

Basic INSERT syntax is:

strSQL = "INSERT INTO tblMyTable (Field1, Field2, Field3, ...) VALUES (123, 'abcd', #1/1/2012#, ...)"

for data type: number, string, date, etc (respectively)

So, what VALUES are you trying to INSERT into what Fields?

Have fun.

---- Andy

 

[merlynsdad]

The value is the current value of strIssue and I want to insert it into the Issue field.

If the square peg won't fit in the round hole, sand off the corners.

 

[merlynsdad]

If I substitute another variable for the date, then

 strSQL = "INSERT INTO tblMaster (SEID,Issue) VALUES (strSEID, strIssue)"

should work, no? I'm getting a 3061 too few parameters error.

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

Andy has provide the generic SQL syntax. The query processing engine doesn't understand variable names. You must change the SQL like:

strSQL = "INSERT INTO tblMaster (SEID,Issue) " & _
   "VALUES (""" & strSEID & """, """ & strIssue & """)"
   Debug.Print strSQL

If SEID is a date field then try:

strSQL = "INSERT INTO tblMaster (SEID,Issue) " & _
   "VALUES (#" & strSEID & "#, """ & strIssue & """)"
   Debug.Print strSQL

If your date format is not m/d/y, you may need to format the date value.

Always use Debug.Print to troubleshoot SQL Statements built in code.


Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

If you have just 2 fields in your table tblMaster, and both are text, I would try:

strSEID = "abcd"
strIssue = "xyz"

strSQL = "INSERT INTO tblMaster (SEID, Issue) VALUES ('[blue]" & strSEID & "[/blue]', '[blue]" & strIssue & "[/blue]')"

Debug.Print strSQL

You should get:[tt]
INSERT INTO tblMaster (SEID, Issue) VALUES ('[blue]abcd[/blue]', '[blue]xyz[/blue]')"
[/tt]



Have fun.

---- Andy

 

[merlynsdad]

I need to put "Date AS [InputDt]" in here somewhere. Date is not on the form, but InputDt is in the table. Right now I'm trying

 & "VALUES (Date As [InputDt],  '" & strSEID & "', '" & strIssue & "','" & strDescription & "','" & strCubicle & "');"

but I'm getting the dreaded missing operator syntax error.

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

The word "Date" has no meaning in your SQL. What do you get in the Debug.Print?

What date do you want to insert into the table? Have you considered setting the default of the InputDt to Date()?

This assumes there are 5 fields with the first being a date field and the other 4 are text. This code will break if any of the string variables contains a single quote in it.

& "VALUES (Date(),  '" & strSEID & "', '" & strIssue & "','" & strDescription & "','" & strCubicle & "');"

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

How about this. If your field in your tblMaster for a date is called InputDt and it is declared as Date and you want to insert today's date (BLUE portion of the code):

strSQL = "INSERT INTO tblMaster ([blue]InputDt[/blue], SEID, Issue, Desc, Cubicle) VALUES (#"[blue] & Date & [/blue]"#, '" & strSEID & "', '" & strIssue & "','" & [red]Replace([/red]strDescription[red], "'", "''")[/red] & "','" & strCubicle & "')"

dhookom mentioned single quote issue, taken care by RED portion of the code - simply replace one sigle quote with 2 single quotes.

It would be a lot easier - and faster - if you would mention all those problems at the start.

Have fun.

---- Andy