Setting fields on exit???

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

Hi all

Wonder if you could help.

I have an address database which is used to print labels via a query that
looks for the field "printlabel" having a tick

----------CODE----------

SELECT MAIL.NAME, MAIL.POSITION, MAIL.ADDRESS_1, MAIL.ADDRESS_2,
MAIL.ADDRESS_3, MAIL.ADDRESS_4, MAIL.ADDRESS_5, MAIL.ADDRESS_6,
MAIL.PrintLabel
FROM MAIL
WHERE (((MAIL.PrintLabel)=Yes));

------END CODE------------

Then it uses a module to enter the number of "blank labels" and the number
of "labels to print"

----------CODE------------

'*********************************************************
'Declarations section of the module.
'**********************************************************

Option Compare Database
Option Explicit

Dim LabelBlanks&
Dim LabelCopies&
Dim BlankCount&
Dim CopyCount&

'==========================================================
' The following function will cause an input box to
' display when the report is run that prompts the user
' for the number of used labels to skip and how many
' copies of each label should be printed.
'===========================================================

Function LabelSetup()
LabelBlanks& = Val(InputBox$("Enter Number of blank labels to skip"))
LabelCopies& = Val(InputBox$("Enter Number of Copies to Print"))
If LabelBlanks& < 0 Then LabelBlanks& = 0
If LabelCopies& < 1 Then LabelCopies& = 1
End Function

'===========================================================
' The following function sets the variables to a zero
'===========================================================

Function LabelInitialize()
BlankCount& = 0
CopyCount& = 0
End Function

'===========================================================
' The following function is the main part of this code
' that allows the labels to print as the user desires.
'===========================================================

Function LabelLayout(R As Report)
If BlankCount& < LabelBlanks& Then
R.NextRecord = False
R.PrintSection = False
BlankCount& = BlankCount& + 1
Else
If CopyCount& < (LabelCopies& - 1) Then
R.NextRecord = False
CopyCount& = CopyCount& + 1
Else
CopyCount& = 0
End If
End If
End Function

----------END CODE--------------

Now what I would like to do is set the "printLabel" field when I exit access
to "no" so that all fields are set to "no" next time the database is opened.

I hope you follow what I mean here. I'm pulling out my hair as it took me
long enough to find out how to print the things in the first place.

Many thanks.

Sean
 
Hi Sean,

Pull you hair no longer. While there is no "exit Access" event, you
could use the On Close event of your main (menu) form. And in it execute an
update query:

update MAIL
set PrintLabel = False
where PrintLabel = True;

If it is named "qryClear Print-Label Flags", in code you could do:

DoCmd.OpenQuery "qryClear Print-Label Flags"

Alternatively, what I probably would do would be to have a "Clear
Print-Label Flags" button that the user can use whenever the user desires.
It would execute the above mentioned query. Then if for some reason the user
has selected some items for print, not yet printed them and then closes out
of Access (or the main form), the user's selections will still be there when
the user reopens the database.

Hope that helps,

Clifford Bass
 
Clifford,

Many many thanks.

Solved it. Went for the button option as the idea of a user not printing the
ones they want before closing if very likely :)

The little hair I have left is safe for a while.

Thanks again.

Sean.
 
Back
Top