Another SQL quandry

  • Thread starter Thread starter Stewart
  • Start date Start date
S

Stewart

I'm trying to convert(format) this sql from my query to a form I can use in
vba(or vb) and escape the DoCmd.OpenQuery "qryQuery" trap.

My query sql view:

INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date], [Ack/success] )
SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack
FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;


VBRSgen converts it to: 'was worth a shot...

Dim daoDB As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO "
strSQL = strSQL & "tblMessQ "
strSQL = strSQL & "("
strSQL = strSQL & "RecipID, "
strSQL = strSQL & "ConCode, "
strSQL = strSQL & "Message, "
strSQL = strSQL & "[Date], "
strSQL = strSQL & "[Ack/success]"
strSQL = strSQL & ") "
strSQL = strSQL & "("
strSQL = strSQL & "SELECT "
strSQL = strSQL & "INNER JOIN tblMessLog ON tblRecipients.ID =
tblMessLog.RecipID"
strSQL = strSQL & ""
strSQL = strSQL & ")"
Set daoDB = Workspaces(0).OpenDatabase("C:\Test_1.mdb")
MsgBox strSQL
daoDB.Execute (strSQL)
If Not (daoDB Is Nothing) Then
Set daoDB = Nothing
End If

** But has INSERT INTO error I can't find.

I also had no success with:

strSQL1 = "INSERT INTO tblMessQ ([RecipID], [ConCode], [Message], [Date],
[Ack/success])"
strSQL2 = "SELECT [tblMessLog].[RecipID], [tblRecipients].[Concode],
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS [Ack]"
strSQL3 = "FROM tblRecipients INNER JOIN tblRecipients.[ID] = '" &
[tblMessLog].[RecipID] & "' ; "
strSQL = strSQL1 & Chr(13) & strSQL2 & Chr(13) & strSQL3
MsgBox strSQL, , "SQL Contents here"

** Although it was nicely on three lines looking excatly like my original
query's sql view.

Any assistance appreciated.
 
Wow that's a great tool Allen.

I'm still receiving an error: Run Timer error 3061, Too few peramaters.
Expected 1

I've seen that a great deal of that lately.

Result sql is:
strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack " &
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

Allen Browne said:
Your final sample needs spaces added at the end of each line, so the words
don't run together.

This might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

That's what I actually use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Stewart said:
I'm trying to convert(format) this sql from my query to a form I can use
in
vba(or vb) and escape the DoCmd.OpenQuery "qryQuery" trap.

My query sql view:

INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date], [Ack/success] )
SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack
FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;


VBRSgen converts it to: 'was worth a shot...

Dim daoDB As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO "
strSQL = strSQL & "tblMessQ "
strSQL = strSQL & "("
strSQL = strSQL & "RecipID, "
strSQL = strSQL & "ConCode, "
strSQL = strSQL & "Message, "
strSQL = strSQL & "[Date], "
strSQL = strSQL & "[Ack/success]"
strSQL = strSQL & ") "
strSQL = strSQL & "("
strSQL = strSQL & "SELECT "
strSQL = strSQL & "INNER JOIN tblMessLog ON tblRecipients.ID =
tblMessLog.RecipID"
strSQL = strSQL & ""
strSQL = strSQL & ")"
Set daoDB = Workspaces(0).OpenDatabase("C:\Test_1.mdb")
MsgBox strSQL
daoDB.Execute (strSQL)
If Not (daoDB Is Nothing) Then
Set daoDB = Nothing
End If

** But has INSERT INTO error I can't find.

I also had no success with:

strSQL1 = "INSERT INTO tblMessQ ([RecipID], [ConCode], [Message], [Date],
[Ack/success])"
strSQL2 = "SELECT [tblMessLog].[RecipID], [tblRecipients].[Concode],
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS [Ack]"
strSQL3 = "FROM tblRecipients INNER JOIN tblRecipients.[ID] = '" &
[tblMessLog].[RecipID] & "' ; "
strSQL = strSQL1 & Chr(13) & strSQL2 & Chr(13) & strSQL3
MsgBox strSQL, , "SQL Contents here"

** Although it was nicely on three lines looking excatly like my original
query's sql view.

Any assistance appreciated.
Click to expand...
Click to expand...
 
Probably the reference to the control is causing the problem. Try
getting the value of the control and including that in the text string.
That may still have problems if txtTextMess is too long.

strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,""" & _
Forms!frmTestMess!txtTextMess & """, Now() AS [Date], 0 AS Ack " &
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Wow that's a great tool Allen.

I'm still receiving an error: Run Timer error 3061, Too few peramaters.
Expected 1

I've seen that a great deal of that lately.

Result sql is:
strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack " &
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

Allen Browne said:
Your final sample needs spaces added at the end of each line, so the words
don't run together.

This might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

That's what I actually use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Stewart said:
I'm trying to convert(format) this sql from my query to a form I can use
in
vba(or vb) and escape the DoCmd.OpenQuery "qryQuery" trap.

My query sql view:

INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date], [Ack/success] )
SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack
FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;


VBRSgen converts it to: 'was worth a shot...

Dim daoDB As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO "
strSQL = strSQL & "tblMessQ "
strSQL = strSQL & "("
strSQL = strSQL & "RecipID, "
strSQL = strSQL & "ConCode, "
strSQL = strSQL & "Message, "
strSQL = strSQL & "[Date], "
strSQL = strSQL & "[Ack/success]"
strSQL = strSQL & ") "
strSQL = strSQL & "("
strSQL = strSQL & "SELECT "
strSQL = strSQL & "INNER JOIN tblMessLog ON tblRecipients.ID =
tblMessLog.RecipID"
strSQL = strSQL & ""
strSQL = strSQL & ")"
Set daoDB = Workspaces(0).OpenDatabase("C:\Test_1.mdb")
MsgBox strSQL
daoDB.Execute (strSQL)
If Not (daoDB Is Nothing) Then
Set daoDB = Nothing
End If

** But has INSERT INTO error I can't find.

I also had no success with:

strSQL1 = "INSERT INTO tblMessQ ([RecipID], [ConCode], [Message], [Date],
[Ack/success])"
strSQL2 = "SELECT [tblMessLog].[RecipID], [tblRecipients].[Concode],
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS [Ack]"
strSQL3 = "FROM tblRecipients INNER JOIN tblRecipients.[ID] = '" &
[tblMessLog].[RecipID] & "' ; "
strSQL = strSQL1 & Chr(13) & strSQL2 & Chr(13) & strSQL3
MsgBox strSQL, , "SQL Contents here"

** Although it was nicely on three lines looking excatly like my original
query's sql view.

Any assistance appreciated.
Click to expand...
Click to expand...
Click to expand...
 
You are correct.

When I hard code text in place of the form reference it works perfect.

So I created a variable and set it = to the form reference. That causes the
same Too Few perametrs error.

John Spencer said:
Probably the reference to the control is causing the problem. Try
getting the value of the control and including that in the text string.
That may still have problems if txtTextMess is too long.

strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,""" & _
Forms!frmTestMess!txtTextMess & """, Now() AS [Date], 0 AS Ack " &
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Wow that's a great tool Allen.

I'm still receiving an error: Run Timer error 3061, Too few peramaters.
Expected 1

I've seen that a great deal of that lately.

Result sql is:
strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack " &
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

Allen Browne said:
Your final sample needs spaces added at the end of each line, so the words
don't run together.

This might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

That's what I actually use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to convert(format) this sql from my query to a form I can use
in
vba(or vb) and escape the DoCmd.OpenQuery "qryQuery" trap.

My query sql view:

INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date], [Ack/success] )
SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack
FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;


VBRSgen converts it to: 'was worth a shot...

Dim daoDB As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO "
strSQL = strSQL & "tblMessQ "
strSQL = strSQL & "("
strSQL = strSQL & "RecipID, "
strSQL = strSQL & "ConCode, "
strSQL = strSQL & "Message, "
strSQL = strSQL & "[Date], "
strSQL = strSQL & "[Ack/success]"
strSQL = strSQL & ") "
strSQL = strSQL & "("
strSQL = strSQL & "SELECT "
strSQL = strSQL & "INNER JOIN tblMessLog ON tblRecipients.ID =
tblMessLog.RecipID"
strSQL = strSQL & ""
strSQL = strSQL & ")"
Set daoDB = Workspaces(0).OpenDatabase("C:\Test_1.mdb")
MsgBox strSQL
daoDB.Execute (strSQL)
If Not (daoDB Is Nothing) Then
Set daoDB = Nothing
End If

** But has INSERT INTO error I can't find.

I also had no success with:

strSQL1 = "INSERT INTO tblMessQ ([RecipID], [ConCode], [Message], [Date],
[Ack/success])"
strSQL2 = "SELECT [tblMessLog].[RecipID], [tblRecipients].[Concode],
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS [Ack]"
strSQL3 = "FROM tblRecipients INNER JOIN tblRecipients.[ID] = '" &
[tblMessLog].[RecipID] & "' ; "
strSQL = strSQL1 & Chr(13) & strSQL2 & Chr(13) & strSQL3
MsgBox strSQL, , "SQL Contents here"

** Although it was nicely on three lines looking excatly like my original
query's sql view.

Any assistance appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Here is what did work!!

strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,""" & _
Forms!frmTestMess!txtTextMess & """ AS Message, Now() AS [Date], 0 AS Ack "
&
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

Stewart said:
You are correct.

When I hard code text in place of the form reference it works perfect.

So I created a variable and set it = to the form reference. That causes
the
same Too Few perametrs error.

John Spencer said:
Probably the reference to the control is causing the problem. Try
getting the value of the control and including that in the text string.
That may still have problems if txtTextMess is too long.

strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,""" & _
Forms!frmTestMess!txtTextMess & """, Now() AS [Date], 0 AS Ack " &
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Wow that's a great tool Allen.

I'm still receiving an error: Run Timer error 3061, Too few peramaters.
Expected 1

I've seen that a great deal of that lately.

Result sql is:
strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack " &
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

Your final sample needs spaces added at the end of each line, so the words
don't run together.

This might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

That's what I actually use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to convert(format) this sql from my query to a form I can use
in
vba(or vb) and escape the DoCmd.OpenQuery "qryQuery" trap.

My query sql view:

INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] )
SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack
FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;


VBRSgen converts it to: 'was worth a shot...

Dim daoDB As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO "
strSQL = strSQL & "tblMessQ "
strSQL = strSQL & "("
strSQL = strSQL & "RecipID, "
strSQL = strSQL & "ConCode, "
strSQL = strSQL & "Message, "
strSQL = strSQL & "[Date], "
strSQL = strSQL & "[Ack/success]"
strSQL = strSQL & ") "
strSQL = strSQL & "("
strSQL = strSQL & "SELECT "
strSQL = strSQL & "INNER JOIN tblMessLog ON tblRecipients.ID =
tblMessLog.RecipID"
strSQL = strSQL & ""
strSQL = strSQL & ")"
Set daoDB = Workspaces(0).OpenDatabase("C:\Test_1.mdb")
MsgBox strSQL
daoDB.Execute (strSQL)
If Not (daoDB Is Nothing) Then
Set daoDB = Nothing
End If

** But has INSERT INTO error I can't find.

I also had no success with:

strSQL1 = "INSERT INTO tblMessQ ([RecipID], [ConCode], [Message],
[Date],
[Ack/success])"
strSQL2 = "SELECT [tblMessLog].[RecipID], [tblRecipients].[Concode],
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS
[Ack]"
strSQL3 = "FROM tblRecipients INNER JOIN tblRecipients.[ID] = '" &
[tblMessLog].[RecipID] & "' ; "
strSQL = strSQL1 & Chr(13) & strSQL2 & Chr(13) & strSQL3
MsgBox strSQL, , "SQL Contents here"

** Although it was nicely on three lines looking excatly like my
original
query's sql view.

Any assistance appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Obviously I am not seeing something. How is what I posted different
from what you have come up with for a solution? The only thing I can
see is that I did not bother to alias the inserted value. Did that
cause an error? In my experience it hasn't, but ...

If my solution is faulty I would really like to know why so I don't give
the same bad advice to someone else.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Here is what did work!!

strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,""" & _
Forms!frmTestMess!txtTextMess & """ AS Message, Now() AS [Date], 0 AS Ack "
&
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

Stewart said:
You are correct.

When I hard code text in place of the form reference it works perfect.

So I created a variable and set it = to the form reference. That causes
the
same Too Few perametrs error.

John Spencer said:
Probably the reference to the control is causing the problem. Try
getting the value of the control and including that in the text string.
That may still have problems if txtTextMess is too long.

strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,""" & _
Forms!frmTestMess!txtTextMess & """, Now() AS [Date], 0 AS Ack " &
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Stewart wrote:
Wow that's a great tool Allen.

I'm still receiving an error: Run Timer error 3061, Too few peramaters.
Expected 1

I've seen that a great deal of that lately.

Result sql is:
strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack " &
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

Your final sample needs spaces added at the end of each line, so the words
don't run together.

This might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

That's what I actually use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to convert(format) this sql from my query to a form I can use
in
vba(or vb) and escape the DoCmd.OpenQuery "qryQuery" trap.

My query sql view:

INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] )
SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack
FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;


VBRSgen converts it to: 'was worth a shot...

Dim daoDB As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO "
strSQL = strSQL & "tblMessQ "
strSQL = strSQL & "("
strSQL = strSQL & "RecipID, "
strSQL = strSQL & "ConCode, "
strSQL = strSQL & "Message, "
strSQL = strSQL & "[Date], "
strSQL = strSQL & "[Ack/success]"
strSQL = strSQL & ") "
strSQL = strSQL & "("
strSQL = strSQL & "SELECT "
strSQL = strSQL & "INNER JOIN tblMessLog ON tblRecipients.ID =
tblMessLog.RecipID"
strSQL = strSQL & ""
strSQL = strSQL & ")"
Set daoDB = Workspaces(0).OpenDatabase("C:\Test_1.mdb")
MsgBox strSQL
daoDB.Execute (strSQL)
If Not (daoDB Is Nothing) Then
Set daoDB = Nothing
End If

** But has INSERT INTO error I can't find.

I also had no success with:

strSQL1 = "INSERT INTO tblMessQ ([RecipID], [ConCode], [Message],
[Date],
[Ack/success])"
strSQL2 = "SELECT [tblMessLog].[RecipID], [tblRecipients].[Concode],
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS
[Ack]"
strSQL3 = "FROM tblRecipients INNER JOIN tblRecipients.[ID] = '" &
[tblMessLog].[RecipID] & "' ; "
strSQL = strSQL1 & Chr(13) & strSQL2 & Chr(13) & strSQL3
MsgBox strSQL, , "SQL Contents here"

** Although it was nicely on three lines looking excatly like my
original
query's sql view.

Any assistance appreciated.
Click to expand...
Click to expand...
Click to expand...
 
John

Your last suggestion was:
"SELECT tblMessLog.RecipID, tblRecipients.Concode,""" & _
Forms!frmTestMess!txtTextMess & """, Now() AS [Date], 0 AS Ack " &
vbCrLf & _

It gave me an error of aaaa(?), missing/expected element or something, I
wondered why every other element had the target field name, except this one.
I'm understanding more now, no field or Expr1, didn't look as specific as
the ither items, so I added the Message field name as follows.

"SELECT tblMessLog.RecipID, tblRecipients.Concode,""" & _
Forms!frmTestMess!txtTextMess & """ AS Message, Now() AS [Date], 0 AS Ack "
& vbCrLf & _

YOUR ADVICE IS NEVER BAD! Its tremendous, a bail-out, a good night's sleep
rather than agonizing over a vague error code for days. Its very
appreciated.

John Spencer said:
Obviously I am not seeing something. How is what I posted different from
what you have come up with for a solution? The only thing I can see is
that I did not bother to alias the inserted value. Did that cause an
error? In my experience it hasn't, but ...

If my solution is faulty I would really like to know why so I don't give
the same bad advice to someone else.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Here is what did work!!

strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,""" & _
Forms!frmTestMess!txtTextMess & """ AS Message, Now() AS [Date], 0 AS Ack
" &
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

Stewart said:
You are correct.

When I hard code text in place of the form reference it works perfect.

So I created a variable and set it = to the form reference. That causes
the
same Too Few perametrs error.

Probably the reference to the control is causing the problem. Try
getting the value of the control and including that in the text string.
That may still have problems if txtTextMess is too long.

strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,""" & _
Forms!frmTestMess!txtTextMess & """, Now() AS [Date], 0 AS Ack " &
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Stewart wrote:
Wow that's a great tool Allen.

I'm still receiving an error: Run Timer error 3061, Too few
peramaters.
Expected 1

I've seen that a great deal of that lately.

Result sql is:
strSql = "INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] ) " & vbCrLf & _
"SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack "
&
vbCrLf & _
"FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;"

Your final sample needs spaces added at the end of each line, so the
words
don't run together.

This might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

That's what I actually use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to convert(format) this sql from my query to a form I can
use
in
vba(or vb) and escape the DoCmd.OpenQuery "qryQuery" trap.

My query sql view:

INSERT INTO tblMessQ ( RecipID, ConCode, Message, [Date],
[Ack/success] )
SELECT tblMessLog.RecipID, tblRecipients.Concode,
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS Ack
FROM tblMessLog INNER JOIN tblRecipients ON tblMessLog.RecipID =
tblRecipients.ID;


VBRSgen converts it to: 'was worth a shot...

Dim daoDB As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO "
strSQL = strSQL & "tblMessQ "
strSQL = strSQL & "("
strSQL = strSQL & "RecipID, "
strSQL = strSQL & "ConCode, "
strSQL = strSQL & "Message, "
strSQL = strSQL & "[Date], "
strSQL = strSQL & "[Ack/success]"
strSQL = strSQL & ") "
strSQL = strSQL & "("
strSQL = strSQL & "SELECT "
strSQL = strSQL & "INNER JOIN tblMessLog ON tblRecipients.ID =
tblMessLog.RecipID"
strSQL = strSQL & ""
strSQL = strSQL & ")"
Set daoDB = Workspaces(0).OpenDatabase("C:\Test_1.mdb")
MsgBox strSQL
daoDB.Execute (strSQL)
If Not (daoDB Is Nothing) Then
Set daoDB = Nothing
End If

** But has INSERT INTO error I can't find.

I also had no success with:

strSQL1 = "INSERT INTO tblMessQ ([RecipID], [ConCode], [Message],
[Date],
[Ack/success])"
strSQL2 = "SELECT [tblMessLog].[RecipID], [tblRecipients].[Concode],
Forms!frmTestMess!txtTextMess AS Message, Now() AS [Date], 0 AS
[Ack]"
strSQL3 = "FROM tblRecipients INNER JOIN tblRecipients.[ID] = '" &
[tblMessLog].[RecipID] & "' ; "
strSQL = strSQL1 & Chr(13) & strSQL2 & Chr(13) & strSQL3
MsgBox strSQL, , "SQL Contents here"

** Although it was nicely on three lines looking excatly like my
original
query's sql view.

Any assistance appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top