Odd VBA Behavior

M

Matthew DeAngelis

I am having some trouble with a series of DoCmd.RunSQL statements in
code. Basically, if I have these statements running consecutively,
sometimes only the first will run. Here is a sample procedure,
although the erratic behavior has happened to my other procedures with
the same structure:

Private Sub MakeDead_Click()

Dim cnn As ADODB.Connection
Dim VbMsgBoxStatus, AppendStatusOnReject, UpdateStatusDeadNoAction,
UpdateDateNotifySQL As String

Set cnn = CurrentProject.Connection

AppendStatusOnReject = "INSERT INTO [tbl: Data - StatusOnReject] (
DealID, DateReceived, Company, StatusBeforeReject, Priority, Interest
)" & _
" SELECT [tbl: Data - Deals].DealID, [tbl: Data -
Deals].DateReceived, [tbl: Data - Companies].Company, [tbl: Data -
Deals].Status, [tbl: Data - Deals].Priority, [tbl: Data -
Deals].Interest" & _
" FROM [tbl: Data - Companies] INNER JOIN [tbl:
Data - Deals] ON [tbl: Data - Companies].CompanyID = [tbl: Data -
Deals].CompanyID" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateStatusDeadNoAction = "UPDATE [tbl: Data - Deals] INNER JOIN [tbl:
Data - StatusOnReject] ON [tbl: Data - Deals].DealID = [tbl: Data -
StatusOnReject].DealID SET [tbl: Data - Deals].Status = '6 - Dead/No
Action', [tbl: Data - StatusOnReject].StatusAfterReject = '6 - Dead/No
Action'" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateDateNotifySQL = "UPDATE [tbl: Data - StatusOnReject] SET [tbl:
Data - StatusOnReject].DateNotice = Date()" & _
" WHERE ((([tbl: Data - StatusOnReject].DealID) =
[Forms]![frm: SpecificDeal]![DealID]))" & _
" WITH OWNERACCESS OPTION;"

VbMsgBoxStatus = MsgBox("Would you like to update this deal's status to
'Dead/No Action'?", vbYesNo)
If VbMsgBoxStatus = vbYes Then
If [Forms]![frm: SpecificDeal]![Status] Like "5 - To Reject" And
[Forms]![frm: SpecificDeal]![Personal] = True Then
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL UpdateDateNotifySQL
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
Else
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL AppendStatusOnReject
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
End If
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

End Sub


With this particular statement, the Append query will run just fine,
meaning that it registers the change in the log, but then the Update
query will simply not run. I don't get any errors: the changes just
don't happen. It only happens sometimes, and going into the code and
back out without changing anything will sometimes fix it. Right now, I
have it so both queries run every time on my machine (Access 2002) and
seem to run every time on another machine (Access 2003), but only the
first one runs on a third machine (Access 2002). I do not know where
to begin in fixing this problem.

Any help would be much appreciated.


Matt
 
T

Tim Ferguson

I am having some trouble with a series of DoCmd.RunSQL statements in
code. Basically, if I have these statements running consecutively,
sometimes only the first will run.
Click to expand...

If you use the conn.Execute method instead, you can force them to run
synchronously (i.e. finish before returning control to the VBA). And you
can get rid of these nasty dangerous SetWarning actions too.

HTH


Tim F
 
G

Graham R Seach

Mathew,

I don't understand why you start off using ADO (declaring an ADO
connection), but then run your queries in DAO. If this lot works at all,
then you may as well forget about ADO altogether, and just use db.Execute
(where db=CurrentDb).

Tim's comment about SetWarnings will still hold true.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Matthew DeAngelis said:
I am having some trouble with a series of DoCmd.RunSQL statements in
code. Basically, if I have these statements running consecutively,
sometimes only the first will run. Here is a sample procedure,
although the erratic behavior has happened to my other procedures with
the same structure:

Private Sub MakeDead_Click()

Dim cnn As ADODB.Connection
Dim VbMsgBoxStatus, AppendStatusOnReject, UpdateStatusDeadNoAction,
UpdateDateNotifySQL As String

Set cnn = CurrentProject.Connection

AppendStatusOnReject = "INSERT INTO [tbl: Data - StatusOnReject] (
DealID, DateReceived, Company, StatusBeforeReject, Priority, Interest
)" & _
" SELECT [tbl: Data - Deals].DealID, [tbl: Data -
Deals].DateReceived, [tbl: Data - Companies].Company, [tbl: Data -
Deals].Status, [tbl: Data - Deals].Priority, [tbl: Data -
Deals].Interest" & _
" FROM [tbl: Data - Companies] INNER JOIN [tbl:
Data - Deals] ON [tbl: Data - Companies].CompanyID = [tbl: Data -
Deals].CompanyID" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateStatusDeadNoAction = "UPDATE [tbl: Data - Deals] INNER JOIN [tbl:
Data - StatusOnReject] ON [tbl: Data - Deals].DealID = [tbl: Data -
StatusOnReject].DealID SET [tbl: Data - Deals].Status = '6 - Dead/No
Action', [tbl: Data - StatusOnReject].StatusAfterReject = '6 - Dead/No
Action'" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateDateNotifySQL = "UPDATE [tbl: Data - StatusOnReject] SET [tbl:
Data - StatusOnReject].DateNotice = Date()" & _
" WHERE ((([tbl: Data - StatusOnReject].DealID) =
[Forms]![frm: SpecificDeal]![DealID]))" & _
" WITH OWNERACCESS OPTION;"

VbMsgBoxStatus = MsgBox("Would you like to update this deal's status to
'Dead/No Action'?", vbYesNo)
If VbMsgBoxStatus = vbYes Then
If [Forms]![frm: SpecificDeal]![Status] Like "5 - To Reject" And
[Forms]![frm: SpecificDeal]![Personal] = True Then
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL UpdateDateNotifySQL
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
Else
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL AppendStatusOnReject
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
End If
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

End Sub


With this particular statement, the Append query will run just fine,
meaning that it registers the change in the log, but then the Update
query will simply not run. I don't get any errors: the changes just
don't happen. It only happens sometimes, and going into the code and
back out without changing anything will sometimes fix it. Right now, I
have it so both queries run every time on my machine (Access 2002) and
seem to run every time on another machine (Access 2003), but only the
first one runs on a third machine (Access 2002). I do not know where
to begin in fixing this problem.

Any help would be much appreciated.


Matt
Click to expand...
 
M

Matthew DeAngelis

Graham said:
Mathew,

I don't understand why you start off using ADO (declaring an ADO
connection), but then run your queries in DAO. If this lot works at
all, then you may as well forget about ADO altogether, and just use
db.Execute (where db=CurrentDb).

Tim's comment about SetWarnings will still hold true.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Matthew DeAngelis said:
I am having some trouble with a series of DoCmd.RunSQL statements in
code. Basically, if I have these statements running consecutively,
sometimes only the first will run. Here is a sample procedure,
although the erratic behavior has happened to my other procedures
with the same structure:

Private Sub MakeDead_Click()

Dim cnn As ADODB.Connection
Dim VbMsgBoxStatus, AppendStatusOnReject, UpdateStatusDeadNoAction,
UpdateDateNotifySQL As String

Set cnn = CurrentProject.Connection

AppendStatusOnReject = "INSERT INTO [tbl: Data - StatusOnReject] (
DealID, DateReceived, Company, StatusBeforeReject, Priority,
Interest )" & _
" SELECT [tbl: Data - Deals].DealID, [tbl: Data
- Deals].DateReceived, [tbl: Data - Companies].Company, [tbl: Data -
Deals].Status, [tbl: Data - Deals].Priority, [tbl: Data -
Deals].Interest" & _
" FROM [tbl: Data - Companies] INNER JOIN [tbl:
Data - Deals] ON [tbl: Data - Companies].CompanyID = [tbl: Data -
Deals].CompanyID" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateStatusDeadNoAction = "UPDATE [tbl: Data - Deals] INNER JOIN
[tbl: Data - StatusOnReject] ON [tbl: Data - Deals].DealID = [tbl:
Data - StatusOnReject].DealID SET [tbl: Data - Deals].Status = '6 -
Dead/No Action', [tbl: Data - StatusOnReject].StatusAfterReject =
'6 - Dead/No Action'" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateDateNotifySQL = "UPDATE [tbl: Data - StatusOnReject] SET [tbl:
Data - StatusOnReject].DateNotice = Date()" & _
" WHERE ((([tbl: Data - StatusOnReject].DealID)
= [Forms]![frm: SpecificDeal]![DealID]))" & _
" WITH OWNERACCESS OPTION;"

VbMsgBoxStatus = MsgBox("Would you like to update this deal's
status to 'Dead/No Action'?", vbYesNo)
If VbMsgBoxStatus = vbYes Then
If [Forms]![frm: SpecificDeal]![Status] Like "5 - To Reject" And
[Forms]![frm: SpecificDeal]![Personal] = True Then
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL UpdateDateNotifySQL
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
Else
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL AppendStatusOnReject
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
End If
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

End Sub


With this particular statement, the Append query will run just fine,
meaning that it registers the change in the log, but then the Update
query will simply not run. I don't get any errors: the changes just
don't happen. It only happens sometimes, and going into the code
and back out without changing anything will sometimes fix it.
Right now, I have it so both queries run every time on my machine
(Access 2002) and seem to run every time on another machine (Access
2003), but only the first one runs on a third machine (Access
2002). I do not know where to begin in fixing this problem.

Any help would be much appreciated.


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

Thanks to both of you for your responses.

Graham, the declaration of the ADODB connection is actually leftover
from when I tried to make these statements into a transaction and had a
pretty miserable time of it (which is why I am a little hesitant to try
using the conn.Execute, Tim). This code block does work, so perhaps it
would be best if I simply use DAO. I am trying to implement the
db.Execute command, but it does not appear to be working. Here is what
I have changed:'

Private Sub RejectDeal_Click()

Dim db As Database
Set db = CurrentDb

....(SQL statements same as before)

If [Forms]![frm: SpecificDeal]![NoReject] = False Then
db.Execute (AppendStatusOnReject)
db.Execute (UpdateStatusToReject)
Else
MsgBox ("This deal is marked 'No Reject'.")
End If
AppendStatusOnReject = ""
UpdateStatusToReject = ""
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

When I hit the cmd button, I get the following error:

Run-time error '3061':

Too few parameters. Expected 1.


Since I am unfamiliar with this method, I am not surprised that I have
received an error message. Can you help me?


Thanks again,
Matt
 
G

George Nicholson

Try it without the parenthesis:
db.Execute AppendStatusOnReject
db.Execute UpdateStatusToReject

--
George Nicholson

Remove 'Junk' from return address.


Matthew DeAngelis said:
Graham said:
Mathew,

I don't understand why you start off using ADO (declaring an ADO
connection), but then run your queries in DAO. If this lot works at
all, then you may as well forget about ADO altogether, and just use
db.Execute (where db=CurrentDb).

Tim's comment about SetWarnings will still hold true.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Matthew DeAngelis said:
I am having some trouble with a series of DoCmd.RunSQL statements in
code. Basically, if I have these statements running consecutively,
sometimes only the first will run. Here is a sample procedure,
although the erratic behavior has happened to my other procedures
with the same structure:

Private Sub MakeDead_Click()

Dim cnn As ADODB.Connection
Dim VbMsgBoxStatus, AppendStatusOnReject, UpdateStatusDeadNoAction,
UpdateDateNotifySQL As String

Set cnn = CurrentProject.Connection

AppendStatusOnReject = "INSERT INTO [tbl: Data - StatusOnReject] (
DealID, DateReceived, Company, StatusBeforeReject, Priority,
Interest )" & _
" SELECT [tbl: Data - Deals].DealID, [tbl: Data
- Deals].DateReceived, [tbl: Data - Companies].Company, [tbl: Data -
Deals].Status, [tbl: Data - Deals].Priority, [tbl: Data -
Deals].Interest" & _
" FROM [tbl: Data - Companies] INNER JOIN [tbl:
Data - Deals] ON [tbl: Data - Companies].CompanyID = [tbl: Data -
Deals].CompanyID" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateStatusDeadNoAction = "UPDATE [tbl: Data - Deals] INNER JOIN
[tbl: Data - StatusOnReject] ON [tbl: Data - Deals].DealID = [tbl:
Data - StatusOnReject].DealID SET [tbl: Data - Deals].Status = '6 -
Dead/No Action', [tbl: Data - StatusOnReject].StatusAfterReject =
'6 - Dead/No Action'" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateDateNotifySQL = "UPDATE [tbl: Data - StatusOnReject] SET [tbl:
Data - StatusOnReject].DateNotice = Date()" & _
" WHERE ((([tbl: Data - StatusOnReject].DealID)
= [Forms]![frm: SpecificDeal]![DealID]))" & _
" WITH OWNERACCESS OPTION;"

VbMsgBoxStatus = MsgBox("Would you like to update this deal's
status to 'Dead/No Action'?", vbYesNo)
If VbMsgBoxStatus = vbYes Then
If [Forms]![frm: SpecificDeal]![Status] Like "5 - To Reject" And
[Forms]![frm: SpecificDeal]![Personal] = True Then
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL UpdateDateNotifySQL
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
Else
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL AppendStatusOnReject
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
End If
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

End Sub


With this particular statement, the Append query will run just fine,
meaning that it registers the change in the log, but then the Update
query will simply not run. I don't get any errors: the changes just
don't happen. It only happens sometimes, and going into the code
and back out without changing anything will sometimes fix it.
Right now, I have it so both queries run every time on my machine
(Access 2002) and seem to run every time on another machine (Access
2003), but only the first one runs on a third machine (Access
2002). I do not know where to begin in fixing this problem.

Any help would be much appreciated.


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

Thanks to both of you for your responses.

Graham, the declaration of the ADODB connection is actually leftover
from when I tried to make these statements into a transaction and had a
pretty miserable time of it (which is why I am a little hesitant to try
using the conn.Execute, Tim). This code block does work, so perhaps it
would be best if I simply use DAO. I am trying to implement the
db.Execute command, but it does not appear to be working. Here is what
I have changed:'

Private Sub RejectDeal_Click()

Dim db As Database
Set db = CurrentDb

...(SQL statements same as before)

If [Forms]![frm: SpecificDeal]![NoReject] = False Then
db.Execute (AppendStatusOnReject)
db.Execute (UpdateStatusToReject)
Else
MsgBox ("This deal is marked 'No Reject'.")
End If
AppendStatusOnReject = ""
UpdateStatusToReject = ""
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

When I hit the cmd button, I get the following error:

Run-time error '3061':

Too few parameters. Expected 1.


Since I am unfamiliar with this method, I am not surprised that I have
received an error message. Can you help me?


Thanks again,
Matt
Click to expand...
 
G

Graham R Seach

I'm more inclined to believe there's something wong with one or more
queries. Parameter errors usually indicate that the expression service is
not seeing oneof them for some reason. The most frequent reason for this is
when people treat text fields like numeric ones, and vice-versa.

Check your queries VERY CAREFULLY.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


George Nicholson said:
Try it without the parenthesis:
db.Execute AppendStatusOnReject
db.Execute UpdateStatusToReject

--
George Nicholson

Remove 'Junk' from return address.


Matthew DeAngelis said:
Graham said:
Mathew,

I don't understand why you start off using ADO (declaring an ADO
connection), but then run your queries in DAO. If this lot works at
all, then you may as well forget about ADO altogether, and just use
db.Execute (where db=CurrentDb).

Tim's comment about SetWarnings will still hold true.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


I am having some trouble with a series of DoCmd.RunSQL statements in
code. Basically, if I have these statements running consecutively,
sometimes only the first will run. Here is a sample procedure,
although the erratic behavior has happened to my other procedures
with the same structure:

Private Sub MakeDead_Click()

Dim cnn As ADODB.Connection
Dim VbMsgBoxStatus, AppendStatusOnReject, UpdateStatusDeadNoAction,
UpdateDateNotifySQL As String

Set cnn = CurrentProject.Connection

AppendStatusOnReject = "INSERT INTO [tbl: Data - StatusOnReject] (
DealID, DateReceived, Company, StatusBeforeReject, Priority,
Interest )" & _
" SELECT [tbl: Data - Deals].DealID, [tbl: Data
- Deals].DateReceived, [tbl: Data - Companies].Company, [tbl: Data -
Deals].Status, [tbl: Data - Deals].Priority, [tbl: Data -
Deals].Interest" & _
" FROM [tbl: Data - Companies] INNER JOIN [tbl:
Data - Deals] ON [tbl: Data - Companies].CompanyID = [tbl: Data -
Deals].CompanyID" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateStatusDeadNoAction = "UPDATE [tbl: Data - Deals] INNER JOIN
[tbl: Data - StatusOnReject] ON [tbl: Data - Deals].DealID = [tbl:
Data - StatusOnReject].DealID SET [tbl: Data - Deals].Status = '6 -
Dead/No Action', [tbl: Data - StatusOnReject].StatusAfterReject =
'6 - Dead/No Action'" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateDateNotifySQL = "UPDATE [tbl: Data - StatusOnReject] SET [tbl:
Data - StatusOnReject].DateNotice = Date()" & _
" WHERE ((([tbl: Data - StatusOnReject].DealID)
= [Forms]![frm: SpecificDeal]![DealID]))" & _
" WITH OWNERACCESS OPTION;"

VbMsgBoxStatus = MsgBox("Would you like to update this deal's
status to 'Dead/No Action'?", vbYesNo)
If VbMsgBoxStatus = vbYes Then
If [Forms]![frm: SpecificDeal]![Status] Like "5 - To Reject" And
[Forms]![frm: SpecificDeal]![Personal] = True Then
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL UpdateDateNotifySQL
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
Else
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL AppendStatusOnReject
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
End If
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

End Sub


With this particular statement, the Append query will run just fine,
meaning that it registers the change in the log, but then the Update
query will simply not run. I don't get any errors: the changes just
don't happen. It only happens sometimes, and going into the code
and back out without changing anything will sometimes fix it.
Right now, I have it so both queries run every time on my machine
(Access 2002) and seem to run every time on another machine (Access
2003), but only the first one runs on a third machine (Access
2002). I do not know where to begin in fixing this problem.

Any help would be much appreciated.


Matt
Click to expand...

Thanks to both of you for your responses.

Graham, the declaration of the ADODB connection is actually leftover
from when I tried to make these statements into a transaction and had a
pretty miserable time of it (which is why I am a little hesitant to try
using the conn.Execute, Tim). This code block does work, so perhaps it
would be best if I simply use DAO. I am trying to implement the
db.Execute command, but it does not appear to be working. Here is what
I have changed:'

Private Sub RejectDeal_Click()

Dim db As Database
Set db = CurrentDb

...(SQL statements same as before)

If [Forms]![frm: SpecificDeal]![NoReject] = False Then
db.Execute (AppendStatusOnReject)
db.Execute (UpdateStatusToReject)
Else
MsgBox ("This deal is marked 'No Reject'.")
End If
AppendStatusOnReject = ""
UpdateStatusToReject = ""
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

When I hit the cmd button, I get the following error:

Run-time error '3061':

Too few parameters. Expected 1.


Since I am unfamiliar with this method, I am not surprised that I have
received an error message. Can you help me?


Thanks again,
Matt
Click to expand...
Click to expand...
 
M

Matthew DeAngelis

Graham said:
I'm more inclined to believe there's something wong with one or more
queries. Parameter errors usually indicate that the expression
service is not seeing oneof them for some reason. The most frequent
reason for this is when people treat text fields like numeric ones,
and vice-versa.

Check your queries VERY CAREFULLY.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


George Nicholson said:
Try it without the parenthesis:
db.Execute AppendStatusOnReject
db.Execute UpdateStatusToReject

--
George Nicholson

Remove 'Junk' from return address.


Matthew DeAngelis said:
Graham R Seach wrote:

Mathew,

I don't understand why you start off using ADO (declaring an ADO
connection), but then run your queries in DAO. If this lot
works at all, then you may as well forget about ADO altogether,
and just use db.Execute (where db=CurrentDb).

Tim's comment about SetWarnings will still hold true.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.ht
ml


I am having some trouble with a series of DoCmd.RunSQL
statements in code. Basically, if I have these statements
running consecutively, sometimes only the first will run.
Here is a sample procedure, although the erratic behavior has
happened to my other procedures with the same structure:

Private Sub MakeDead_Click()

Dim cnn As ADODB.Connection
Dim VbMsgBoxStatus, AppendStatusOnReject,
UpdateStatusDeadNoAction, UpdateDateNotifySQL As String

Set cnn = CurrentProject.Connection

AppendStatusOnReject = "INSERT INTO [tbl: Data -
StatusOnReject] ( DealID, DateReceived, Company,
StatusBeforeReject, Priority, Interest )" & _
" SELECT [tbl: Data - Deals].DealID,
[tbl: Data - Deals].DateReceived, [tbl: Data -
Companies].Company, [tbl: Data - Deals].Status, [tbl: Data -
Deals].Priority, [tbl: Data - Deals].Interest" & _
" FROM [tbl: Data - Companies] INNER JOIN
[tbl: Data - Deals] ON [tbl: Data - Companies].CompanyID =
[tbl: Data - Deals].CompanyID" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateStatusDeadNoAction = "UPDATE [tbl: Data - Deals] INNER
JOIN [tbl: Data - StatusOnReject] ON [tbl: Data -
Deals].DealID = [tbl: Data - StatusOnReject].DealID SET
[tbl: Data - Deals].Status = '6 - Dead/No Action', [tbl: Data
- StatusOnReject].StatusAfterReject = '6 - Dead/No Action'" &
_ " WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateDateNotifySQL = "UPDATE [tbl: Data - StatusOnReject]
SET [tbl: Data - StatusOnReject].DateNotice = Date()" & _
" WHERE ((([tbl: Data -
StatusOnReject].DealID) = [Forms]![frm:
SpecificDeal]![DealID]))" & _ " WITH
OWNERACCESS OPTION;"

VbMsgBoxStatus = MsgBox("Would you like to update this deal's
status to 'Dead/No Action'?", vbYesNo)
If VbMsgBoxStatus = vbYes Then
If [Forms]![frm: SpecificDeal]![Status] Like "5 - To
Reject" And [Forms]![frm: SpecificDeal]![Personal] = True Then
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL UpdateDateNotifySQL
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
Else
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL AppendStatusOnReject
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
End If
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

End Sub


With this particular statement, the Append query will run
just fine, meaning that it registers the change in the log,
but then the Update query will simply not run. I don't get
any errors: the changes just don't happen. It only happens
sometimes, and going into the code and back out without
changing anything will sometimes fix it. Right now, I have
it so both queries run every time on my machine (Access 2002)
and seem to run every time on another machine (Access 2003),
but only the first one runs on a third machine (Access 2002).
I do not know where to begin in fixing this problem.

Any help would be much appreciated.


Matt

Thanks to both of you for your responses.

Graham, the declaration of the ADODB connection is actually
leftover from when I tried to make these statements into a
transaction and had a pretty miserable time of it (which is why I
am a little hesitant to try using the conn.Execute, Tim). This
code block does work, so perhaps it would be best if I simply use
DAO. I am trying to implement the db.Execute command, but it
does not appear to be working. Here is what I have changed:'

Private Sub RejectDeal_Click()

Dim db As Database
Set db = CurrentDb

...(SQL statements same as before)

If [Forms]![frm: SpecificDeal]![NoReject] = False Then
db.Execute (AppendStatusOnReject)
db.Execute (UpdateStatusToReject)
Else
MsgBox ("This deal is marked 'No Reject'.")
End If
AppendStatusOnReject = ""
UpdateStatusToReject = ""
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

When I hit the cmd button, I get the following error:

Run-time error '3061':

Too few parameters. Expected 1.


Since I am unfamiliar with this method, I am not surprised that I
have received an error message. Can you help me?


Thanks again,
Matt
Click to expand...
Click to expand...
Click to expand...


I tried it without the parentheses and checked my queries over again,
but I have not found anything wrong with them. They seem to run fine
when invoked; they just don't necessarily run as a part of the
procedure.

I will keep looking for other ways to structure the procedure that
might fix the problem. Thanks for pointing me in the right direction.


Matt
 
G

Graham R Seach

Matthew,

Well, I've looked at your queries, and although your naming scheme leaves
much to be desired, I don't see anything that would cause problems in that
regard.

Since you said you are getting a "Too few parameters" error, and the only
parameters you're using are:
[Forms]![frm: SpecificDeal]![DealID]
and
'6 - Dead/No Action '

These look fine to me, but I'd suggest moving the form reference out of the
string in all of the queries, like so:
" WHERE (((D.DealID)=" & Forms![frm: SpecificDeal]!DealID & "));"

Try that, and see how you go. If no joy, then try stepping through the code
one line at-a-time.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Matthew DeAngelis said:
Graham said:
I'm more inclined to believe there's something wong with one or more
queries. Parameter errors usually indicate that the expression
service is not seeing oneof them for some reason. The most frequent
reason for this is when people treat text fields like numeric ones,
and vice-versa.

Check your queries VERY CAREFULLY.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


George Nicholson said:
Try it without the parenthesis:
db.Execute AppendStatusOnReject
db.Execute UpdateStatusToReject

--
George Nicholson

Remove 'Junk' from return address.


Graham R Seach wrote:

Mathew,

I don't understand why you start off using ADO (declaring an ADO
connection), but then run your queries in DAO. If this lot
works at all, then you may as well forget about ADO altogether,
and just use db.Execute (where db=CurrentDb).

Tim's comment about SetWarnings will still hold true.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.ht
ml


I am having some trouble with a series of DoCmd.RunSQL
statements in code. Basically, if I have these statements
running consecutively, sometimes only the first will run.
Here is a sample procedure, although the erratic behavior has
happened to my other procedures with the same structure:

Private Sub MakeDead_Click()

Dim cnn As ADODB.Connection
Dim VbMsgBoxStatus, AppendStatusOnReject,
UpdateStatusDeadNoAction, UpdateDateNotifySQL As String

Set cnn = CurrentProject.Connection

AppendStatusOnReject = "INSERT INTO [tbl: Data -
StatusOnReject] ( DealID, DateReceived, Company,
StatusBeforeReject, Priority, Interest )" & _
" SELECT [tbl: Data - Deals].DealID,
[tbl: Data - Deals].DateReceived, [tbl: Data -
Companies].Company, [tbl: Data - Deals].Status, [tbl: Data -
Deals].Priority, [tbl: Data - Deals].Interest" & _
" FROM [tbl: Data - Companies] INNER JOIN
[tbl: Data - Deals] ON [tbl: Data - Companies].CompanyID =
[tbl: Data - Deals].CompanyID" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateStatusDeadNoAction = "UPDATE [tbl: Data - Deals] INNER
JOIN [tbl: Data - StatusOnReject] ON [tbl: Data -
Deals].DealID = [tbl: Data - StatusOnReject].DealID SET
[tbl: Data - Deals].Status = '6 - Dead/No Action', [tbl: Data
- StatusOnReject].StatusAfterReject = '6 - Dead/No Action'" &
_ " WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateDateNotifySQL = "UPDATE [tbl: Data - StatusOnReject]
SET [tbl: Data - StatusOnReject].DateNotice = Date()" & _
" WHERE ((([tbl: Data -
StatusOnReject].DealID) = [Forms]![frm:
SpecificDeal]![DealID]))" & _ " WITH
OWNERACCESS OPTION;"

VbMsgBoxStatus = MsgBox("Would you like to update this deal's
status to 'Dead/No Action'?", vbYesNo)
If VbMsgBoxStatus = vbYes Then
If [Forms]![frm: SpecificDeal]![Status] Like "5 - To
Reject" And [Forms]![frm: SpecificDeal]![Personal] = True Then
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL UpdateDateNotifySQL
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
Else
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL AppendStatusOnReject
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
End If
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

End Sub


With this particular statement, the Append query will run
just fine, meaning that it registers the change in the log,
but then the Update query will simply not run. I don't get
any errors: the changes just don't happen. It only happens
sometimes, and going into the code and back out without
changing anything will sometimes fix it. Right now, I have
it so both queries run every time on my machine (Access 2002)
and seem to run every time on another machine (Access 2003),
but only the first one runs on a third machine (Access 2002).
I do not know where to begin in fixing this problem.

Any help would be much appreciated.


Matt

Thanks to both of you for your responses.

Graham, the declaration of the ADODB connection is actually
leftover from when I tried to make these statements into a
transaction and had a pretty miserable time of it (which is why I
am a little hesitant to try using the conn.Execute, Tim). This
code block does work, so perhaps it would be best if I simply use
DAO. I am trying to implement the db.Execute command, but it
does not appear to be working. Here is what I have changed:'

Private Sub RejectDeal_Click()

Dim db As Database
Set db = CurrentDb

...(SQL statements same as before)

If [Forms]![frm: SpecificDeal]![NoReject] = False Then
db.Execute (AppendStatusOnReject)
db.Execute (UpdateStatusToReject)
Else
MsgBox ("This deal is marked 'No Reject'.")
End If
AppendStatusOnReject = ""
UpdateStatusToReject = ""
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

When I hit the cmd button, I get the following error:

Run-time error '3061':

Too few parameters. Expected 1.


Since I am unfamiliar with this method, I am not surprised that I
have received an error message. Can you help me?


Thanks again,
Matt
Click to expand...
Click to expand...


I tried it without the parentheses and checked my queries over again,
but I have not found anything wrong with them. They seem to run fine
when invoked; they just don't necessarily run as a part of the
procedure.

I will keep looking for other ways to structure the procedure that
might fix the problem. Thanks for pointing me in the right direction.


Matt
Click to expand...
 
M

Matthew DeAngelis

Graham said:
Matthew,

Well, I've looked at your queries, and although your naming scheme
leaves much to be desired, I don't see anything that would cause
problems in that regard.

Since you said you are getting a "Too few parameters" error, and the
only parameters you're using are:
[Forms]![frm: SpecificDeal]![DealID]
and
'6 - Dead/No Action '

These look fine to me, but I'd suggest moving the form reference out
of the string in all of the queries, like so:
" WHERE (((D.DealID)=" & Forms![frm: SpecificDeal]!DealID & "));"

Try that, and see how you go. If no joy, then try stepping through
the code one line at-a-time.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Matthew DeAngelis said:
Graham said:
I'm more inclined to believe there's something wong with one or
more queries. Parameter errors usually indicate that the
expression service is not seeing oneof them for some reason. The
most frequent reason for this is when people treat text fields
like numeric ones, and vice-versa.

Check your queries VERY CAREFULLY.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Try it without the parenthesis:
db.Execute AppendStatusOnReject
db.Execute UpdateStatusToReject

--
George Nicholson

Remove 'Junk' from return address.


Graham R Seach wrote:

Mathew,

I don't understand why you start off using ADO (declaring
an ADO connection), but then run your queries in DAO. If
this lot works at all, then you may as well forget about
ADO altogether, and just use db.Execute (where
db=CurrentDb).

Tim's comment about SetWarnings will still hold true.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-076455903
6.ht ml


I am having some trouble with a series of DoCmd.RunSQL
statements in code. Basically, if I have these statements
running consecutively, sometimes only the first will run.
Here is a sample procedure, although the erratic behavior
has happened to my other procedures with the same
structure:

Private Sub MakeDead_Click()

Dim cnn As ADODB.Connection
Dim VbMsgBoxStatus, AppendStatusOnReject,
UpdateStatusDeadNoAction, UpdateDateNotifySQL As String

Set cnn = CurrentProject.Connection

AppendStatusOnReject = "INSERT INTO [tbl: Data -
StatusOnReject] ( DealID, DateReceived, Company,
StatusBeforeReject, Priority, Interest )" & _
" SELECT [tbl: Data - Deals].DealID,
[tbl: Data - Deals].DateReceived, [tbl: Data -
Companies].Company, [tbl: Data - Deals].Status, [tbl:
Data - Deals].Priority, [tbl: Data - Deals].Interest" & _
" FROM [tbl: Data - Companies] INNER
JOIN [tbl: Data - Deals] ON [tbl: Data -
Companies].CompanyID = [tbl: Data - Deals].CompanyID" & _
" WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateStatusDeadNoAction = "UPDATE [tbl: Data - Deals]
INNER JOIN [tbl: Data - StatusOnReject] ON [tbl: Data -
Deals].DealID = [tbl: Data - StatusOnReject].DealID SET
[tbl: Data - Deals].Status = '6 - Dead/No Action', [tbl:
Data - StatusOnReject].StatusAfterReject = '6 - Dead/No
Action'" & _ " WHERE ((([tbl: Data -
Deals].DealID)=[Forms]![frm: SpecificDeal]![DealID]));"
UpdateDateNotifySQL = "UPDATE [tbl: Data - StatusOnReject]
SET [tbl: Data - StatusOnReject].DateNotice = Date()" & _
" WHERE ((([tbl: Data -
StatusOnReject].DealID) = [Forms]![frm:
SpecificDeal]![DealID]))" & _ " WITH
OWNERACCESS OPTION;"

VbMsgBoxStatus = MsgBox("Would you like to update this
deal's status to 'Dead/No Action'?", vbYesNo)
If VbMsgBoxStatus = vbYes Then
If [Forms]![frm: SpecificDeal]![Status] Like "5 - To
Reject" And [Forms]![frm: SpecificDeal]![Personal] = True
Then DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL UpdateDateNotifySQL
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
Else
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL AppendStatusOnReject
DoCmd.RunSQL UpdateStatusDeadNoAction
DoCmd.SetWarnings WarningsOn
AppendStatusOnReject = ""
UpdateStatusDeadNoAction = ""
End If
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

End Sub


With this particular statement, the Append query will run
just fine, meaning that it registers the change in the
log, but then the Update query will simply not run. I
don't get any errors: the changes just don't happen. It
only happens sometimes, and going into the code and back
out without changing anything will sometimes fix it.
Right now, I have it so both queries run every time on my
machine (Access 2002) and seem to run every time on
another machine (Access 2003), but only the first one
runs on a third machine (Access 2002). I do not know
where to begin in fixing this problem.

Any help would be much appreciated.


Matt

Thanks to both of you for your responses.

Graham, the declaration of the ADODB connection is actually
leftover from when I tried to make these statements into a
transaction and had a pretty miserable time of it (which is
why I am a little hesitant to try using the conn.Execute,
Tim). This code block does work, so perhaps it would be best
if I simply use DAO. I am trying to implement the db.Execute
command, but it does not appear to be working. Here is what
I have changed:'

Private Sub RejectDeal_Click()

Dim db As Database
Set db = CurrentDb

...(SQL statements same as before)

If [Forms]![frm: SpecificDeal]![NoReject] = False Then
db.Execute (AppendStatusOnReject)
db.Execute (UpdateStatusToReject)
Else
MsgBox ("This deal is marked 'No Reject'.")
End If
AppendStatusOnReject = ""
UpdateStatusToReject = ""
End If

If VbMsgBoxStatus = vbNo Then
Exit Sub
End If

When I hit the cmd button, I get the following error:

Run-time error '3061':

Too few parameters. Expected 1.


Since I am unfamiliar with this method, I am not surprised
that I have received an error message. Can you help me?


Thanks again,
Matt
Click to expand...


I tried it without the parentheses and checked my queries over
again, but I have not found anything wrong with them. They seem to
run fine when invoked; they just don't necessarily run as a part of
the procedure.

I will keep looking for other ways to structure the procedure that
might fix the problem. Thanks for pointing me in the right
direction.


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

Moving the form references to the outside resulted in a perfect
execution of my db.execute commands :) It is too soon for me to say
that the erratic query behavior has stopped, but I must be closer to
solving it than I was.


Many thanks for your assistance,
Matt
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mail Merge Transaction 1
Update query giving runtime error 3417 action query cannot be used 5
Filtration of a form by data in the subform? 6
First Effort Using VBA Code on ComboBox 3
how to re-write this Sub to improve the performance? 1
UPDATE/APPEND QUERIES IN ACCESS 2K3 3
FWT Newsletter - Weekly - August 23, 2004 6

Top