Queries are not executed

  • Thread starter Thread starter MAC
  • Start date Start date
M

MAC

Hello,

I can't get the merge procedures (see below) to work in sequence. Am I
placing the conditions in the wrong place? It's only giving the 3 message
boxes for each of the procedures without executing each query for each merge
to Word. What am I doing wrong? Any help appreciated!

I have the following Sub:

Private Sub Command1_Click()
Dim strSql As String
Dim strSq2 As String
Dim strSq2 As String
Dim Update As String

strSq1 = "SELECT * From test WHERE test.Letter1 = true AND test.done =
False"
If Letter1 = True And done = False Then
[execute my "1st Merge procedure"]

Update = "update test set letter1 = True, Done = true where letter1 = True
and Done = False"
CurrentDb.Execute Update

Else
MsgBox ("No one fields to merge for Letter1")

strSq2 = "SELECT * From test WHERE test.Letter2 = true AND test.done =
False"
If Letter2 = True And done = False Then
[execute a "2nd Merge procedure"]
Update = "update test set letter2 = True, Done = true where letter1 = True
and Done = False"
CurrentDb.Execute Update

Else
MsgBox ("No one fields to merge for Letter2")

strSq3 = "SELECT * From test WHERE test.Letter3 = true AND test.done =
False"
If Letter3 = True And done = False Then

[execute a "3rd Merge procedure"]
Update = "update test set letter3 = True, Done = true where letter1 = True
and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter3")
End If
End If
End If
 
If you have supplied ALL your code [except for the bits in
square brackets] then the problem is with your IF
statements.

"If Letter1 = True And done = False Then"

The assumption is that "Letter1" and "Done" are supposed
to be recordset fields, but you haven't created a
recordset. All you have done is created the sql staement
for it.
If you create a recordset (RS) - how you do this depends
on whether you are using DAO or ADO - you would then
replace your IF statement with

If RS.Recordcount > 0 then...

or

If not RS.EOF then ...
 
Chris, thank you. Your assumptions are correct. I know this is basic, but
how do I create a record set so that I can refer to it in my code?

Thank you for your help!

Chris said:
If you have supplied ALL your code [except for the bits in
square brackets] then the problem is with your IF
statements.

"If Letter1 = True And done = False Then"

The assumption is that "Letter1" and "Done" are supposed
to be recordset fields, but you haven't created a
recordset. All you have done is created the sql staement
for it.
If you create a recordset (RS) - how you do this depends
on whether you are using DAO or ADO - you would then
replace your IF statement with

If RS.Recordcount > 0 then...

or

If not RS.EOF then ...


-----Original Message-----
Hello,

I can't get the merge procedures (see below) to work in sequence. Am I
placing the conditions in the wrong place? It's only giving the 3 message
boxes for each of the procedures without executing each query for each merge
to Word. What am I doing wrong? Any help appreciated!

I have the following Sub:

Private Sub Command1_Click()
Dim strSql As String
Dim strSq2 As String
Dim strSq2 As String
Dim Update As String

strSq1 = "SELECT * From test WHERE test.Letter1 = true AND test.done =
False"
If Letter1 = True And done = False Then
[execute my "1st Merge procedure"]

Update = "update test set letter1 = True, Done = true where letter1 = True
and Done = False"
CurrentDb.Execute Update

Else
MsgBox ("No one fields to merge for Letter1")

strSq2 = "SELECT * From test WHERE test.Letter2 = true AND test.done =
False"
If Letter2 = True And done = False Then
[execute a "2nd Merge procedure"]
Update = "update test set letter2 = True, Done = true where letter1 = True
and Done = False"
CurrentDb.Execute Update

Else
MsgBox ("No one fields to merge for Letter2")

strSq3 = "SELECT * From test WHERE test.Letter3 = true AND test.done =
False"
If Letter3 = True And done = False Then

[execute a "3rd Merge procedure"]
Update = "update test set letter3 = True, Done = true where letter1 = True
and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter3")
End If
End If
End If


.
Click to expand...
Click to expand...
 
Try this...

Private Sub Command1_Click()
Dim conDB As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSql As String
Dim strSq2 As String
Dim strSq2 As String
Dim Update As String

Set conDB = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset

strSq1 = "SELECT * From test WHERE test.Letter1 = true
AND test.done = False"

Set conDB = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open strSq1, conDB, adOpenForwardOnly

if not Rs.eof then
[execute my "1st Merge procedure"]
Update = "update test set letter1 = True, Done = true
where letter1 = True and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter1")
end if

strSq2 = "SELECT * From test WHERE test.Letter2 = true
AND test.done = False"
rs.Open strSq1, conDB, adOpenForwardOnly
if not Rs.eof then
[execute a "2nd Merge procedure"]
Update = "update test set letter2 = True, Done = true
where letter1 = True and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter2")
end if

strSq3 = "SELECT * From test WHERE test.Letter3 = true
AND test.done = False"
rs.Open strSq1, conDB, adOpenForwardOnly
if not Rs.eof then
[execute a "3rd Merge procedure"]
Update = "update test set letter3 = True, Done = true
where letter1 = True and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter3")
End If

rs.close
conDB.close


-----Original Message-----
Chris, thank you. Your assumptions are correct. I know this is basic, but
how do I create a record set so that I can refer to it in my code?

Thank you for your help!

If you have supplied ALL your code [except for the bits in
square brackets] then the problem is with your IF
statements.

"If Letter1 = True And done = False Then"

The assumption is that "Letter1" and "Done" are supposed
to be recordset fields, but you haven't created a
recordset. All you have done is created the sql staement
for it.
If you create a recordset (RS) - how you do this depends
on whether you are using DAO or ADO - you would then
replace your IF statement with

If RS.Recordcount > 0 then...

or

If not RS.EOF then ...


-----Original Message-----
Hello,

I can't get the merge procedures (see below) to work in sequence. Am I
placing the conditions in the wrong place? It's only giving the 3 message
boxes for each of the procedures without executing each query for each merge
to Word. What am I doing wrong? Any help appreciated!

I have the following Sub:

Private Sub Command1_Click()
Dim strSql As String
Dim strSq2 As String
Dim strSq2 As String
Dim Update As String

strSq1 = "SELECT * From test WHERE test.Letter1 = true AND test.done =
False"
If Letter1 = True And done = False Then
[execute my "1st Merge procedure"]

Update = "update test set letter1 = True, Done = true where letter1 = True
and Done = False"
CurrentDb.Execute Update

Else
MsgBox ("No one fields to merge for Letter1")

strSq2 = "SELECT * From test WHERE test.Letter2 = true AND test.done =
False"
If Letter2 = True And done = False Then
[execute a "2nd Merge procedure"]
Update = "update test set letter2 = True, Done = true where letter1 = True
and Done = False"
CurrentDb.Execute Update

Else
MsgBox ("No one fields to merge for Letter2")

strSq3 = "SELECT * From test WHERE test.Letter3 = true AND test.done =
False"
If Letter3 = True And done = False Then

[execute a "3rd Merge procedure"]
Update = "update test set letter3 = True, Done = true where letter1 = True
and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter3")
End If
End If
End If


.
Click to expand...
Click to expand...


.
Click to expand...
 
Chris,

I get the message "User-defined " right on the first line when I click on
the button to execute the procedure! What am I doing wrong now?

Thanks for ALL your help!


Chris said:
Try this...

Private Sub Command1_Click()
Dim conDB As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSql As String
Dim strSq2 As String
Dim strSq2 As String
Dim Update As String

Set conDB = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset

strSq1 = "SELECT * From test WHERE test.Letter1 = true
AND test.done = False"

Set conDB = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open strSq1, conDB, adOpenForwardOnly

if not Rs.eof then
[execute my "1st Merge procedure"]
Update = "update test set letter1 = True, Done = true
where letter1 = True and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter1")
end if

strSq2 = "SELECT * From test WHERE test.Letter2 = true
AND test.done = False"
rs.Open strSq1, conDB, adOpenForwardOnly
if not Rs.eof then
[execute a "2nd Merge procedure"]
Update = "update test set letter2 = True, Done = true
where letter1 = True and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter2")
end if

strSq3 = "SELECT * From test WHERE test.Letter3 = true
AND test.done = False"
rs.Open strSq1, conDB, adOpenForwardOnly
if not Rs.eof then
[execute a "3rd Merge procedure"]
Update = "update test set letter3 = True, Done = true
where letter1 = True and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter3")
End If

rs.close
conDB.close


-----Original Message-----
Chris, thank you. Your assumptions are correct. I know this is basic, but
how do I create a record set so that I can refer to it in my code?

Thank you for your help!

If you have supplied ALL your code [except for the bits in
square brackets] then the problem is with your IF
statements.

"If Letter1 = True And done = False Then"

The assumption is that "Letter1" and "Done" are supposed
to be recordset fields, but you haven't created a
recordset. All you have done is created the sql staement
for it.
If you create a recordset (RS) - how you do this depends
on whether you are using DAO or ADO - you would then
replace your IF statement with

If RS.Recordcount > 0 then...

or

If not RS.EOF then ...



-----Original Message-----
Hello,

I can't get the merge procedures (see below) to work in
sequence. Am I
placing the conditions in the wrong place? It's only
giving the 3 message
boxes for each of the procedures without executing each
query for each merge
to Word. What am I doing wrong? Any help appreciated!

I have the following Sub:

Private Sub Command1_Click()
Dim strSql As String
Dim strSq2 As String
Dim strSq2 As String
Dim Update As String

strSq1 = "SELECT * From test WHERE test.Letter1 = true
AND test.done =
False"
If Letter1 = True And done = False Then
[execute my "1st Merge procedure"]

Update = "update test set letter1 = True, Done = true
where letter1 = True
and Done = False"
CurrentDb.Execute Update

Else
MsgBox ("No one fields to merge for Letter1")

strSq2 = "SELECT * From test WHERE test.Letter2 = true
AND test.done =
False"
If Letter2 = True And done = False Then
[execute a "2nd Merge procedure"]
Update = "update test set letter2 = True, Done = true
where letter1 = True
and Done = False"
CurrentDb.Execute Update

Else
MsgBox ("No one fields to merge for Letter2")

strSq3 = "SELECT * From test WHERE test.Letter3 = true
AND test.done =
False"
If Letter3 = True And done = False Then

[execute a "3rd Merge procedure"]
Update = "update test set letter3 = True, Done = true
where letter1 = True
and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter3")
End If
End If
End If


.
Click to expand...


.
Click to expand...
Click to expand...
 
Sorry for the rush in last posting: the error message is: "User-defined type
not defined"




Chris said:
Try this...

Private Sub Command1_Click()
Dim conDB As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSql As String
Dim strSq2 As String
Dim strSq2 As String
Dim Update As String

Set conDB = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset

strSq1 = "SELECT * From test WHERE test.Letter1 = true
AND test.done = False"

Set conDB = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open strSq1, conDB, adOpenForwardOnly

if not Rs.eof then
[execute my "1st Merge procedure"]
Update = "update test set letter1 = True, Done = true
where letter1 = True and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter1")
end if

strSq2 = "SELECT * From test WHERE test.Letter2 = true
AND test.done = False"
rs.Open strSq1, conDB, adOpenForwardOnly
if not Rs.eof then
[execute a "2nd Merge procedure"]
Update = "update test set letter2 = True, Done = true
where letter1 = True and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter2")
end if

strSq3 = "SELECT * From test WHERE test.Letter3 = true
AND test.done = False"
rs.Open strSq1, conDB, adOpenForwardOnly
if not Rs.eof then
[execute a "3rd Merge procedure"]
Update = "update test set letter3 = True, Done = true
where letter1 = True and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter3")
End If

rs.close
conDB.close


-----Original Message-----
Chris, thank you. Your assumptions are correct. I know this is basic, but
how do I create a record set so that I can refer to it in my code?

Thank you for your help!

If you have supplied ALL your code [except for the bits in
square brackets] then the problem is with your IF
statements.

"If Letter1 = True And done = False Then"

The assumption is that "Letter1" and "Done" are supposed
to be recordset fields, but you haven't created a
recordset. All you have done is created the sql staement
for it.
If you create a recordset (RS) - how you do this depends
on whether you are using DAO or ADO - you would then
replace your IF statement with

If RS.Recordcount > 0 then...

or

If not RS.EOF then ...



-----Original Message-----
Hello,

I can't get the merge procedures (see below) to work in
sequence. Am I
placing the conditions in the wrong place? It's only
giving the 3 message
boxes for each of the procedures without executing each
query for each merge
to Word. What am I doing wrong? Any help appreciated!

I have the following Sub:

Private Sub Command1_Click()
Dim strSql As String
Dim strSq2 As String
Dim strSq2 As String
Dim Update As String

strSq1 = "SELECT * From test WHERE test.Letter1 = true
AND test.done =
False"
If Letter1 = True And done = False Then
[execute my "1st Merge procedure"]

Update = "update test set letter1 = True, Done = true
where letter1 = True
and Done = False"
CurrentDb.Execute Update

Else
MsgBox ("No one fields to merge for Letter1")

strSq2 = "SELECT * From test WHERE test.Letter2 = true
AND test.done =
False"
If Letter2 = True And done = False Then
[execute a "2nd Merge procedure"]
Update = "update test set letter2 = True, Done = true
where letter1 = True
and Done = False"
CurrentDb.Execute Update

Else
MsgBox ("No one fields to merge for Letter2")

strSq3 = "SELECT * From test WHERE test.Letter3 = true
AND test.done =
False"
If Letter3 = True And done = False Then

[execute a "3rd Merge procedure"]
Update = "update test set letter3 = True, Done = true
where letter1 = True
and Done = False"
CurrentDb.Execute Update
Else
MsgBox ("No one fields to merge for Letter3")
End If
End If
End If


.
Click to expand...


.
Click to expand...
Click to expand...
 
Chris, I referenced ADO 2.7 and it ran, but the merge procedure is NOT being
executed. This is my merge procedure:

If Not rs.EOF Then
Set App = New Word.Application
Set Doc = App.Documents.Open("C:\FOLDERNAME\WordTemplate.doc")

App.Visible = True
App.WindowState = wdWindowStateMaximize

With Doc.MailMerge
.Execute
Doc.Close False
End With

Should it be different for ADO?
 
Assuming the "IF" statement is True, there is nothing in
your merge procedure that uses DAO or ADO
If the IF statement is returning false, then there are no
matching records in your query.

What exactly is happening (or not happening)?

Please reply directly to

chris
at
mecury=projects
dot
co
dot
nz



If your code
 
Back
Top