On Event Procedures

  • Thread starter Thread starter Steve G
  • Start date Start date
S

Steve G

Hi All:

Well, I have managed to stump myself again.

I have a form that prints a report when a command button is clicked.

Now I need to update a field in a table in the database when this button is
clicked, as well as printing the report. This is the code I am using to
print the report:

Private Sub Please_Print_This_Form_Click()
On Error GoTo Err_prntDispForm_Click
Me.Dirty = False
Dim stDocName As String
Dim strFilter As String
stDocName = "rptDispForm"
strFilter = "ID = Forms!frmDelivery!ID"
DoCmd.OpenReport stDocName, acNormal, , strFilter
Exit_prntDispForm_Click:
Exit Sub
Err_prntDispForm_Click:
MsgBox Err.Description
Resume Exit_prntDispForm_Click
End Sub

I need to do this as well:
UPDATE Orders LEFT JOIN Printed ON Orders.ID = Printed.OrderID SET
Printed.Printed = "Yes"
WHERE (((Orders.ID)=[ID]));

Any ideas would, as always, be much appreciated.

Steve G
 
What you want is easy, but what happens if the printer ink runs out, or the
papger jams and the prinout does not work? This is goin gto be a common
occurance,a nd you need to take that fact into account. (otherwise, your
osftware will be consider NOT user friendlty).
Private Sub Please_Print_This_Form_Click()
Click to expand...
On Error GoTo Err_prntDispForm_Click
Me.Dirty = False
Dim stDocName As String
Dim strFilter As String dim strSql as String
stDocName = "rptDispForm"
strFilter = "ID = Forms!frmDelivery!ID"
DoCmd.OpenReport stDocName, acNormal, , strFilter
Click to expand...

strSql = "UPDATE Orders LEFT JOIN Printed ON Orders.ID =
Printed.OrderID " & _
"SET Printed.Printed = "Yes" WHERE Orders.ID = " & me!Id
currentdb.Execute strSql

The above assumes you are in frmDelivery. If you are, then you can also
change:
strFilter = "ID = Forms!frmDelivery!ID"
to
strFilter = "ID = " & me!id
 
Modified code below:

Private Sub Please_Print_This_Form_Click()
Dim strSQL As String
Dim dbs As DAO.Database
On Error GoTo Err_prntDispForm_Click
Me.Dirty = False
Dim stDocName As String
Dim strFilter As String
stDocName = "rptDispForm"
strFilter = "ID = Forms!frmDelivery!ID"
DoCmd.OpenReport stDocName, acNormal, , strFilter

strSQL = "UPDATE Orders LEFT JOIN Printed ON " & _
"Orders.ID = Printed.OrderID SET Printed.Printed " & _
"= ""Yes"" WHERE (((Orders.ID)=[ID]));"
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
dsb.Close
Set dbs = Nothing

Exit_prntDispForm_Click:
Exit Sub
Err_prntDispForm_Click:
MsgBox Err.Description
Resume Exit_prntDispForm_Click
End Sub
 
Thanks for your reply, Albert.

Steve G

Albert D. Kallal said:
What you want is easy, but what happens if the printer ink runs out, or the
papger jams and the prinout does not work? This is goin gto be a common
occurance,a nd you need to take that fact into account. (otherwise, your
osftware will be consider NOT user friendlty).

On Error GoTo Err_prntDispForm_Click
Me.Dirty = False

strSql = "UPDATE Orders LEFT JOIN Printed ON Orders.ID =
Printed.OrderID " & _
"SET Printed.Printed = "Yes" WHERE Orders.ID = " & me!Id
currentdb.Execute strSql

The above assumes you are in frmDelivery. If you are, then you can also
change:
strFilter = "ID = Forms!frmDelivery!ID"
to
strFilter = "ID = " & me!id


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn
Click to expand...
 
Thanks, Ken

Steve G

Ken Snell said:
Modified code below:

Private Sub Please_Print_This_Form_Click()
Dim strSQL As String
Dim dbs As DAO.Database
On Error GoTo Err_prntDispForm_Click
Me.Dirty = False
Dim stDocName As String
Dim strFilter As String
stDocName = "rptDispForm"
strFilter = "ID = Forms!frmDelivery!ID"
DoCmd.OpenReport stDocName, acNormal, , strFilter

strSQL = "UPDATE Orders LEFT JOIN Printed ON " & _
"Orders.ID = Printed.OrderID SET Printed.Printed " & _
"= ""Yes"" WHERE (((Orders.ID)=[ID]));"
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
dsb.Close
Set dbs = Nothing

Exit_prntDispForm_Click:
Exit Sub
Err_prntDispForm_Click:
MsgBox Err.Description
Resume Exit_prntDispForm_Click
End Sub


--
Ken Snell
<MS ACCESS MVP>



Steve G said:
Hi All:

Well, I have managed to stump myself again.

I have a form that prints a report when a command button is clicked.

Now I need to update a field in a table in the database when this button is
clicked, as well as printing the report. This is the code I am using to
print the report:

Private Sub Please_Print_This_Form_Click()
On Error GoTo Err_prntDispForm_Click
Me.Dirty = False
Dim stDocName As String
Dim strFilter As String
stDocName = "rptDispForm"
strFilter = "ID = Forms!frmDelivery!ID"
DoCmd.OpenReport stDocName, acNormal, , strFilter
Exit_prntDispForm_Click:
Exit Sub
Err_prntDispForm_Click:
MsgBox Err.Description
Resume Exit_prntDispForm_Click
End Sub

I need to do this as well:
UPDATE Orders LEFT JOIN Printed ON Orders.ID = Printed.OrderID SET
Printed.Printed = "Yes"
WHERE (((Orders.ID)=[ID]));

Any ideas would, as always, be much appreciated.

Steve G
Click to expand...
Click to expand...
 
To add to Albert's reply, what I've done in databases is to create a form
that allows the user to select a previously created record (such as an
invoice number) and then to print a copy of the report for that record.
Allows the user to reprint a report that "jammed" or to make a copy of an
earlier, already printed report.
 
Thanks, Ken.

I am having one problem with this stuff. If the patient has more than one
order, it is updating the printed field for all orders related to that
patient, instead of just the order that we are printing. I thought this was
probably due to the fact that we were opening the recordset by pateint
number, instead of by order number. However, when I switch the criteria to
order number instead of patient number, it is continuing to update all
orders for that patient. I've also done away with the table Printed, and
added a field named Printed to the orders table, to try to simplify things a
bit. However, if I use this, it updates all orders.printed to yes:
strSQL = "UPDATE Orders SET Orders.Printed " & _
"= ""Yes"" WHERE (((Orders.ID)= [ID]));" //Which, as I read the
code, actually makes sense to me
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing

And if I use this:
strSQL = "UPDATE Orders SET Orders.Printed " & _
"= ""Yes"" WHERE (((Orders.ID)= [strFilter]));" //the variable
strFilter is set earlier in the code, I think-see below
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing

It tells me that it is missing a parameter.

Steve
 
Back
Top