Error with OnClose, linking to UpdateQuery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Me...again. :) I have a form where users can enter their initials to
identify records to print. The print report prompts for the initials to
return a specific subset of records. I have an UpdateQuery which prompts for
the initials to change the value to Null. Independently these work fine,
however, when I add to the OnClose property of the report I receive: "Runtime
error '3061': Too few parameters. Expected 1."

The goal is to temporarialy store the users initials so that only the
records they wanted to print are printed and after they are printed to clear
their initials from the underlying table.

Here is my code for the OnClose property:
Private Sub Report_Close()
CurrentDb().Execute "qryPortPrint", dbFailOnError
End Sub

Here is my code for the qryPortPrint:
UPDATE tblFiles SET tblFiles.Print = Null
WHERE (((tblFiles.Print) Like [Enter Initials]) AND
((tblFiles.Portfolio)=Yes));

Any ideas would be welcomed!
Thanks!
Ember
 
Ember:

Yes, you will get this error becuase the parameter is not supplied via code;
unlike when you use parameter query as a report's underlying query and it
will prompt you for the parameter, when you execute a parameter query,
there's no prompting, you have to gather the parameter and supply it to the
running action query. Here's how you could change your code:

Report_OnClose()
Dim objDb as DAO.Database
Dim strSQL as String
Dim strInitials as String

strInitials = InputBox ("Enter your intials to clear your report setting")
If len(strInitials) > 0 Then
Set objDb = CurrentDb()
strSQL = "UPDATE tblFiles SET tblFiles.Print = Null " & _
"WHERE (((tblFiles.Print) Like " & strInitials & "AND " & _
"((tblFiles.Portfolio)=Yes))"
objDb.Execute strSQL, dbfailonerror
Set objDb = Nothing
End if
 
Thanks! I did not realize there was a difference I assumed that if I told it
to run the query then the query would take over the process from there.

I will give this a try.

Ember

SA said:
Ember:

Yes, you will get this error becuase the parameter is not supplied via code;
unlike when you use parameter query as a report's underlying query and it
will prompt you for the parameter, when you execute a parameter query,
there's no prompting, you have to gather the parameter and supply it to the
running action query. Here's how you could change your code:

Report_OnClose()
Dim objDb as DAO.Database
Dim strSQL as String
Dim strInitials as String

strInitials = InputBox ("Enter your intials to clear your report setting")
If len(strInitials) > 0 Then
Set objDb = CurrentDb()
strSQL = "UPDATE tblFiles SET tblFiles.Print = Null " & _
"WHERE (((tblFiles.Print) Like " & strInitials & "AND " & _
"((tblFiles.Portfolio)=Yes))"
objDb.Execute strSQL, dbfailonerror
Set objDb = Nothing
End if
--
SA
ACG Soft
http://www.groupacg.com


Ember said:
Me...again. :) I have a form where users can enter their initials to
identify records to print. The print report prompts for the initials to
return a specific subset of records. I have an UpdateQuery which prompts
for
the initials to change the value to Null. Independently these work fine,
however, when I add to the OnClose property of the report I receive:
"Runtime
error '3061': Too few parameters. Expected 1."

The goal is to temporarialy store the users initials so that only the
records they wanted to print are printed and after they are printed to
clear
their initials from the underlying table.

Here is my code for the OnClose property:
Private Sub Report_Close()
CurrentDb().Execute "qryPortPrint", dbFailOnError
End Sub

Here is my code for the qryPortPrint:
UPDATE tblFiles SET tblFiles.Print = Null
WHERE (((tblFiles.Print) Like [Enter Initials]) AND
((tblFiles.Portfolio)=Yes));

Any ideas would be welcomed!
Thanks!
Ember
Click to expand...
Click to expand...
 
SA - tried the code but still getting "Runtime error '3061': Too few
parameters. Expected 1." When I go to "debug" it takes me to this line of
code:

objDb.Execute strSQL, dbFailOnError

I am however, now getting a chance to enter initals when the report closes!
:)

Since what users are entering are letters, should the "If Len" be greater
than a letter instead of "0"? Here is the full code:

Private Sub Report_Close()

Dim objDb As DAO.Database
Dim strSQL As String
Dim strInitials As String

strInitials = InputBox("Enter your intials to clear your report setting")
If Len(strInitials) > 0 Then
Set objDb = CurrentDb()
strSQL = "UPDATE tblFiles SET tblFiles.Print = Null WHERE
(((tblFiles.Print) Like strInitials) AND ((tblFiles.Portfolio)=Yes))"
objDb.Execute strSQL, dbFailOnError
Set objDb = Nothing
End If

End Sub

Thanks
Ember

SA said:
Ember:

Yes, you will get this error becuase the parameter is not supplied via code;
unlike when you use parameter query as a report's underlying query and it
will prompt you for the parameter, when you execute a parameter query,
there's no prompting, you have to gather the parameter and supply it to the
running action query. Here's how you could change your code:

Report_OnClose()
Dim objDb as DAO.Database
Dim strSQL as String
Dim strInitials as String

strInitials = InputBox ("Enter your intials to clear your report setting")
If len(strInitials) > 0 Then
Set objDb = CurrentDb()
strSQL = "UPDATE tblFiles SET tblFiles.Print = Null " & _
"WHERE (((tblFiles.Print) Like " & strInitials & "AND " & _
"((tblFiles.Portfolio)=Yes))"
objDb.Execute strSQL, dbfailonerror
Set objDb = Nothing
End if
--
SA
ACG Soft
http://www.groupacg.com


Ember said:
Me...again. :) I have a form where users can enter their initials to
identify records to print. The print report prompts for the initials to
return a specific subset of records. I have an UpdateQuery which prompts
for
the initials to change the value to Null. Independently these work fine,
however, when I add to the OnClose property of the report I receive:
"Runtime
error '3061': Too few parameters. Expected 1."

The goal is to temporarialy store the users initials so that only the
records they wanted to print are printed and after they are printed to
clear
their initials from the underlying table.

Here is my code for the OnClose property:
Private Sub Report_Close()
CurrentDb().Execute "qryPortPrint", dbFailOnError
End Sub

Here is my code for the qryPortPrint:
UPDATE tblFiles SET tblFiles.Print = Null
WHERE (((tblFiles.Print) Like [Enter Initials]) AND
((tblFiles.Portfolio)=Yes));

Any ideas would be welcomed!
Thanks!
Ember
Click to expand...
Click to expand...
 
Ember:

There was an error in our SQL statement it should read:

strSQL = "UPDATE tblFiles SET tblFiles.Print = Null WHERE
(((tblFiles.Print) Like " & strInitials & ") AND
((tblFiles.Portfolio)=Yes))"


--
SA
ACG Soft
http://www.groupacg.com

Ember said:
SA - tried the code but still getting "Runtime error '3061': Too few
parameters. Expected 1." When I go to "debug" it takes me to this line
of
code:

objDb.Execute strSQL, dbFailOnError

I am however, now getting a chance to enter initals when the report
closes!
:)

Since what users are entering are letters, should the "If Len" be greater
than a letter instead of "0"? Here is the full code:

Private Sub Report_Close()

Dim objDb As DAO.Database
Dim strSQL As String
Dim strInitials As String

strInitials = InputBox("Enter your intials to clear your report setting")
If Len(strInitials) > 0 Then
Set objDb = CurrentDb()
strSQL = "UPDATE tblFiles SET tblFiles.Print = Null WHERE
(((tblFiles.Print) Like strInitials) AND ((tblFiles.Portfolio)=Yes))"
objDb.Execute strSQL, dbFailOnError
Set objDb = Nothing
End If

End Sub

Thanks
Ember

SA said:
Ember:

Yes, you will get this error becuase the parameter is not supplied via
code;
unlike when you use parameter query as a report's underlying query and it
will prompt you for the parameter, when you execute a parameter query,
there's no prompting, you have to gather the parameter and supply it to
the
running action query. Here's how you could change your code:

Report_OnClose()
Dim objDb as DAO.Database
Dim strSQL as String
Dim strInitials as String

strInitials = InputBox ("Enter your intials to clear your report
setting")
If len(strInitials) > 0 Then
Set objDb = CurrentDb()
strSQL = "UPDATE tblFiles SET tblFiles.Print = Null " & _
"WHERE (((tblFiles.Print) Like " & strInitials & "AND " & _
"((tblFiles.Portfolio)=Yes))"
objDb.Execute strSQL, dbfailonerror
Set objDb = Nothing
End if
--
SA
ACG Soft
http://www.groupacg.com


Ember said:
Me...again. :) I have a form where users can enter their initials to
identify records to print. The print report prompts for the initials
to
return a specific subset of records. I have an UpdateQuery which
prompts
for
the initials to change the value to Null. Independently these work
fine,
however, when I add to the OnClose property of the report I receive:
"Runtime
error '3061': Too few parameters. Expected 1."

The goal is to temporarialy store the users initials so that only the
records they wanted to print are printed and after they are printed to
clear
their initials from the underlying table.

Here is my code for the OnClose property:
Private Sub Report_Close()
CurrentDb().Execute "qryPortPrint", dbFailOnError
End Sub

Here is my code for the qryPortPrint:
UPDATE tblFiles SET tblFiles.Print = Null
WHERE (((tblFiles.Print) Like [Enter Initials]) AND
((tblFiles.Portfolio)=Yes));

Any ideas would be welcomed!
Thanks!
Ember
Click to expand...
Click to expand...
Click to expand...
 
No luck, still getting runtime error 3061.

Thanks!
Ember

SA said:
Ember:

There was an error in our SQL statement it should read:

strSQL = "UPDATE tblFiles SET tblFiles.Print = Null WHERE
(((tblFiles.Print) Like " & strInitials & ") AND
((tblFiles.Portfolio)=Yes))"


--
SA
ACG Soft
http://www.groupacg.com

Ember said:
SA - tried the code but still getting "Runtime error '3061': Too few
parameters. Expected 1." When I go to "debug" it takes me to this line
of
code:

objDb.Execute strSQL, dbFailOnError

I am however, now getting a chance to enter initals when the report
closes!
:)

Since what users are entering are letters, should the "If Len" be greater
than a letter instead of "0"? Here is the full code:

Private Sub Report_Close()

Dim objDb As DAO.Database
Dim strSQL As String
Dim strInitials As String

strInitials = InputBox("Enter your intials to clear your report setting")
If Len(strInitials) > 0 Then
Set objDb = CurrentDb()
strSQL = "UPDATE tblFiles SET tblFiles.Print = Null WHERE
(((tblFiles.Print) Like strInitials) AND ((tblFiles.Portfolio)=Yes))"
objDb.Execute strSQL, dbFailOnError
Set objDb = Nothing
End If

End Sub

Thanks
Ember

SA said:
Ember:

Yes, you will get this error becuase the parameter is not supplied via
code;
unlike when you use parameter query as a report's underlying query and it
will prompt you for the parameter, when you execute a parameter query,
there's no prompting, you have to gather the parameter and supply it to
the
running action query. Here's how you could change your code:

Report_OnClose()
Dim objDb as DAO.Database
Dim strSQL as String
Dim strInitials as String

strInitials = InputBox ("Enter your intials to clear your report
setting")
If len(strInitials) > 0 Then
Set objDb = CurrentDb()
strSQL = "UPDATE tblFiles SET tblFiles.Print = Null " & _
"WHERE (((tblFiles.Print) Like " & strInitials & "AND " & _
"((tblFiles.Portfolio)=Yes))"
objDb.Execute strSQL, dbfailonerror
Set objDb = Nothing
End if
--
SA
ACG Soft
http://www.groupacg.com


Me...again. :) I have a form where users can enter their initials to
identify records to print. The print report prompts for the initials
to
return a specific subset of records. I have an UpdateQuery which
prompts
for
the initials to change the value to Null. Independently these work
fine,
however, when I add to the OnClose property of the report I receive:
"Runtime
error '3061': Too few parameters. Expected 1."

The goal is to temporarialy store the users initials so that only the
records they wanted to print are printed and after they are printed to
clear
their initials from the underlying table.

Here is my code for the OnClose property:
Private Sub Report_Close()
CurrentDb().Execute "qryPortPrint", dbFailOnError
End Sub

Here is my code for the qryPortPrint:
UPDATE tblFiles SET tblFiles.Print = Null
WHERE (((tblFiles.Print) Like [Enter Initials]) AND
((tblFiles.Portfolio)=Yes));

Any ideas would be welcomed!
Thanks!
Ember
Click to expand...
Click to expand...
Click to expand...
 
I was able to modify your suggestion enough to not get a runtime error. Here
is the new code:

Private Sub Report_Close()

Dim strSQL As String
Dim strInitials As String

strInitials = InputBox("Enter initals to clear records")
If Len(strInitials) > 0 Then
strSQL = "UPDATE tblFiles SET tblFiles.Print = Null WHERE
(((tblFiles.Print) Like " & strInitials & ") AND ((tblFiles.Portfolio)=Yes))"
DoCmd.RunSQL (strSQL)
End If
End Sub

This is the process by which records are selected for printing - the record
is located in the database and the user places their initials into the "Print
file label?" text box. They do this for as many records they would like to
print. When ready to print, they select the "Print file labels" comand
button (which is linked to a report which is created from a query). The
query is set to ask for the user initials (to ensure only that users records
are returned) and the report is opened with this filter applied. This all
works well and good...

The trick is I would like for the users initials to be cleared from the
"Print file label?" field when they close the report. This is where the code
above comes in, and works to some degree. OnClose the user is asked for
their initials, which is fine, but they are asked a second time as well. The
first time they are asked is through the use of the InputBox. The second
time is through a different box (which looks similar to a parameters box)
whose "heading" are the initials entered into the InputBox.

My question is, why am I being asked twice to enter my initials?

Thanks!
Ember

SA said:
Ember:

There was an error in our SQL statement it should read:

strSQL = "UPDATE tblFiles SET tblFiles.Print = Null WHERE
(((tblFiles.Print) Like " & strInitials & ") AND
((tblFiles.Portfolio)=Yes))"


--
SA
ACG Soft
http://www.groupacg.com

Ember said:
SA - tried the code but still getting "Runtime error '3061': Too few
parameters. Expected 1." When I go to "debug" it takes me to this line
of
code:

objDb.Execute strSQL, dbFailOnError

I am however, now getting a chance to enter initals when the report
closes!
:)

Since what users are entering are letters, should the "If Len" be greater
than a letter instead of "0"? Here is the full code:

Private Sub Report_Close()

Dim objDb As DAO.Database
Dim strSQL As String
Dim strInitials As String

strInitials = InputBox("Enter your intials to clear your report setting")
If Len(strInitials) > 0 Then
Set objDb = CurrentDb()
strSQL = "UPDATE tblFiles SET tblFiles.Print = Null WHERE
(((tblFiles.Print) Like strInitials) AND ((tblFiles.Portfolio)=Yes))"
objDb.Execute strSQL, dbFailOnError
Set objDb = Nothing
End If

End Sub

Thanks
Ember

SA said:
Ember:

Yes, you will get this error becuase the parameter is not supplied via
code;
unlike when you use parameter query as a report's underlying query and it
will prompt you for the parameter, when you execute a parameter query,
there's no prompting, you have to gather the parameter and supply it to
the
running action query. Here's how you could change your code:

Report_OnClose()
Dim objDb as DAO.Database
Dim strSQL as String
Dim strInitials as String

strInitials = InputBox ("Enter your intials to clear your report
setting")
If len(strInitials) > 0 Then
Set objDb = CurrentDb()
strSQL = "UPDATE tblFiles SET tblFiles.Print = Null " & _
"WHERE (((tblFiles.Print) Like " & strInitials & "AND " & _
"((tblFiles.Portfolio)=Yes))"
objDb.Execute strSQL, dbfailonerror
Set objDb = Nothing
End if
--
SA
ACG Soft
http://www.groupacg.com


Me...again. :) I have a form where users can enter their initials to
identify records to print. The print report prompts for the initials
to
return a specific subset of records. I have an UpdateQuery which
prompts
for
the initials to change the value to Null. Independently these work
fine,
however, when I add to the OnClose property of the report I receive:
"Runtime
error '3061': Too few parameters. Expected 1."

The goal is to temporarialy store the users initials so that only the
records they wanted to print are printed and after they are printed to
clear
their initials from the underlying table.

Here is my code for the OnClose property:
Private Sub Report_Close()
CurrentDb().Execute "qryPortPrint", dbFailOnError
End Sub

Here is my code for the qryPortPrint:
UPDATE tblFiles SET tblFiles.Print = Null
WHERE (((tblFiles.Print) Like [Enter Initials]) AND
((tblFiles.Portfolio)=Yes));

Any ideas would be welcomed!
Thanks!
Ember
Click to expand...
Click to expand...
Click to expand...
 
Back
Top