using a query to change a yes/no field

[Don Seckler]

I have a database that is going to be used to do billing on orders. I
sent up a field for each record that is called Billed and is a yes/no
field. I want to do a query that will pull all the records that
haven't been billed, issue them an invoice, then mark the billed field
in those records to yes.

Any ideas?

Thanks in advance!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When you "issue" the invoices is when you'd mark each order record as
"billed." If you're doing this from a printed report you can use the
report's Detail_Print() subroutine. E.g.:

Create a report query like this:

SELECT * FROM Orders WHERE Billed = False

Create a query named "qryBillIt" like this:

PARAMETERS thisID Long;
UPDATE Orders SET Billed = True WHERE OrderID = thisID

Then set up the VBA behind the invoice report like this:

Private Sub Detail_Print(Ca ncel As Integer, PrintCount As Integer)

' Use the OrderID control on the report
' that holds the ID of the currently printing
' invoice order.
Billit Me!OrderID

End Sub

Private Sub BillIt(lngOrder ID as Long)
' Sets the Billed column in Orders to True
' for the indicated Order ID

const QRY_BILL = "qryBillIt"

dim db as dao.database
dim qd as dao.querydef
set db = currentdb
set qd = db.querydefs(qr y_bill)
qd.Parameters!t hisID = lngOrderID
qd.execute
qd.close
db.close

End Sub

One problem w/ this is some people Preview the report & don't send it
to the printer. This will cause the record to be set to Billed=True
without printing/sending the invoice! This can be solved by user
training.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP57pqoechKq OuFEgEQL/CQCguP8NwgEsOIB RWnxOLj9e8WPSoB wAn1nK
TBmReMtxJJbUcV9 TRmevwE2y
=5KGU
-----END PGP SIGNATURE-----

Don Seckler wrote:

I have a database that is going to be used to do billing on orders. I
sent up a field for each record that is called Billed and is a yes/no
field. I want to do a query that will pull all the records that
haven't been billed, issue them an invoice, then mark the billed field
in those records to yes.

Any ideas?

Thanks in advance!

[paii, Ron]

You may want to add a verify check box and have someone verify that the
invoice has been printed. Otherwise you may not notice until the invoice has
NOT been paid.

"MGFoster" <[e-mail] > wrote in message
news:FQ******** ********@newsre ad3.news.pas.ea rthlink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When you "issue" the invoices is when you'd mark each order record as
"billed." If you're doing this from a printed report you can use the
report's Detail_Print() subroutine. E.g.:

Create a report query like this:

SELECT * FROM Orders WHERE Billed = False

Create a query named "qryBillIt" like this:

PARAMETERS thisID Long;
UPDATE Orders SET Billed = True WHERE OrderID = thisID

Then set up the VBA behind the invoice report like this:

Private Sub Detail_Print(Ca ncel As Integer, PrintCount As Integer)

' Use the OrderID control on the report
' that holds the ID of the currently printing
' invoice order.
Billit Me!OrderID

End Sub

Private Sub BillIt(lngOrder ID as Long)
' Sets the Billed column in Orders to True
' for the indicated Order ID

const QRY_BILL = "qryBillIt"

dim db as dao.database
dim qd as dao.querydef
set db = currentdb
set qd = db.querydefs(qr y_bill)
qd.Parameters!t hisID = lngOrderID
qd.execute
qd.close
db.close

End Sub

One problem w/ this is some people Preview the report & don't send it
to the printer. This will cause the record to be set to Billed=True
without printing/sending the invoice! This can be solved by user
training.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP57pqoechKq OuFEgEQL/CQCguP8NwgEsOIB RWnxOLj9e8WPSoB wAn1nK
TBmReMtxJJbUcV9 TRmevwE2y
=5KGU
-----END PGP SIGNATURE-----

Don Seckler wrote:

I have a database that is going to be used to do billing on orders. I
sent up a field for each record that is called Billed and is a yes/no
field. I want to do a query that will pull all the records that
haven't been billed, issue them an invoice, then mark the billed field
in those records to yes.

Any ideas?

Thanks in advance!

[Don Seckler]

MG please excuse my ignorance, this is my first access project.

I do understand how to set up the VBA, I have done some in my forms.
But how do I activate the VBA code? In the forms I put the references
in the event properties, I looked in the form properties and didn't
see a print event...

Create a report query like this:

SELECT * FROM Orders WHERE Billed = False

Create a query named "qryBillIt" like this:

PARAMETERS thisID Long;
UPDATE Orders SET Billed = True WHERE OrderID = thisID

Then set up the VBA behind the invoice report like this:

Private Sub Detail_Print(Ca ncel As Integer, PrintCount As Integer)

' Use the OrderID control on the report
' that holds the ID of the currently printing
' invoice order.
Billit Me!OrderID

End Sub

Private Sub BillIt(lngOrder ID as Long)
' Sets the Billed column in Orders to True
' for the indicated Order ID

const QRY_BILL = "qryBillIt"

dim db as dao.database
dim qd as dao.querydef
set db = currentdb
set qd = db.querydefs(qr y_bill)
qd.Parameters!t hisID = lngOrderID
qd.execute
qd.close
db.close

End Sub

One problem w/ this is some people Preview the report & don't send it
to the printer. This will cause the record to be set to Billed=True
without printing/sending the invoice! This can be solved by user
training.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP57pqoechKq OuFEgEQL/CQCguP8NwgEsOIB RWnxOLj9e8WPSoB wAn1nK
TBmReMtxJJbUcV9 TRmevwE2y
=5KGU
-----END PGP SIGNATURE-----

Don Seckler wrote:

I have a database that is going to be used to do billing on orders. I
sent up a field for each record that is called Billed and is a yes/no
field. I want to do a query that will pull all the records that
haven't been billed, issue them an invoice, then mark the billed field
in those records to yes.

Any ideas?

Thanks in advance!

[Larry Linson]

"paii, Ron" wrote

You may want to add a verify check
box and have someone verify that the
invoice has been printed. Otherwise
you may not notice until the invoice has
NOT been paid.

I strongly endorse this suggestion. I have seen more than a few reports come
out of heavily-used, out of adjustment departmental or office printers as
nothing but an unreadable, crumpled smudge. To manually check all those that
_did_ print successfully and uncheck the ones that did not can be tiresome
drudgery, where taking a stack of invoices and checking off the ones that
printed successfully before mailing is a simple task that can be done by
low-skill clerical personnel.

Of course, if you have printers that _never_ jam, smudge, smear, etc., it'll
be wasted effort. The closest I have seen to that are local printers
attached directly to the user's computer and that isn't usually what is used
to print a batch of invoices.

Larry Linson
Microsoft Access MVP

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The VBA is for a Report. I was assuming that you are printing a
report as the invoice. Put the VBA code in the code section of a
report.

Also, please read the other posts (warnings) on this thread - the
authors bring up good points about printing invoices.

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6AyVoechKq OuFEgEQJXRwCfdo P38cL/zyfm3dJizVB0u7/nL8MAnja+
G1dokMi75sHsBGE 3QwWEG8tm
=5HaI
-----END PGP SIGNATURE-----

Don Seckler wrote:

MG please excuse my ignorance, this is my first access project.

I do understand how to set up the VBA, I have done some in my forms.
But how do I activate the VBA code? In the forms I put the references
in the event properties, I looked in the form properties and didn't
see a print event...

Create a report query like this:

<snip previous postings>

[Don Seckler]

>

The VBA is for a Report. I was assuming that you are printing a
report as the invoice. Put the VBA code in the code section of a
report.
I meant I couldn't find where to put the vba in the report, sorry
typo.
Also, please read the other posts (warnings) on this thread - the
authors bring up good points about printing invoices.
Do you think that it's better to use a query to pop up all the records
that are being billed when the invoice(report) is printed, and give
the user the chance to mark all the records billed once they've had a
chance to check the printed invoice?

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6AyVoechKq OuFEgEQJXRwCfdo P38cL/zyfm3dJizVB0u7/nL8MAnja+
G1dokMi75sHsBGE 3QwWEG8tm
=5HaI
-----END PGP SIGNATURE-----

Don Seckler wrote:

MG please excuse my ignorance, this is my first access project.

I do understand how to set up the VBA, I have done some in my forms.
But how do I activate the VBA code? In the forms I put the references
in the event properties, I looked in the form properties and didn't
see a print event...

Create a report query like this:

<snip previous postings>

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The VBA is put in what's known as the "code behind forms" or, more
officially, the report class module. How to get there:

1. Open the report in design view.
2. On the menu bar click: View > Code - the report's class module
will open.
3. Paste the VBA into the class module.
4. Compile & save the module.

Please note: my posted example VBA was "air code," meaning it is
untested & was meant only as an example. Your requirements may
indicate another solution.

==

I believe it is better for you to decide how you want to bill to be
mark. After all, you are the "man-on-the-scene" and will have the
best idea how your users & equipment work.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6FYnoechKq OuFEgEQJnDQCcCV t+npKOJUJlGap2r +jaHeYDSN4An05U
QNocxe3D7Vn+Mn+ U7mOCh3RG
=hclL
-----END PGP SIGNATURE-----

Don Seckler wrote:

The VBA is for a Report. I was assuming that you are printing a
report as the invoice. Put the VBA code in the code section of a
report.

I meant I couldn't find where to put the vba in the report, sorry
typo.

Also, please read the other posts (warnings) on this thread - the
authors bring up good points about printing invoices.

Do you think that it's better to use a query to pop up all the records
that are being billed when the invoice(report) is printed, and give
the user the chance to mark all the records billed once they've had a
chance to check the printed invoice?

- --
MGFoster:::mg f
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6AyVoechKq OuFEgEQJXRwCfdo P38cL/zyfm3dJizVB0u7/nL8MAnja+
G1dokMi75sHsB GE3QwWEG8tm
=5HaI
-----END PGP SIGNATURE-----

Don Seckler wrote:

MG please excuse my ignorance, this is my first access project.

I do understand how to set up the VBA, I have done some in my forms.
But how do I activate the VBA code? In the forms I put the references
in the event properties, I looked in the form properties and didn't
see a print event...


Create a report query like this:

<snip previous postings>