473,320 Members | 2,109 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

using a query to change a yes/no field

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!
Nov 12 '05 #1
7 14662
-----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(Cancel 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(lngOrderID 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(qry_bill)
qd.Parameters!thisID = 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/AwUBP57pqoechKqOuFEgEQL/CQCguP8NwgEsOIBRWnxOLj9e8WPSoBwAn1nK
TBmReMtxJJbUcV9TRmevwE2y
=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!


Nov 12 '05 #2
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" <me@privacy.com> wrote in message
news:FQ****************@newsread3.news.pas.earthli nk.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(Cancel 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(lngOrderID 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(qry_bill)
qd.Parameters!thisID = 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/AwUBP57pqoechKqOuFEgEQL/CQCguP8NwgEsOIBRWnxOLj9e8WPSoBwAn1nK
TBmReMtxJJbUcV9TRmevwE2y
=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!

Nov 12 '05 #3
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(Cancel 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(lngOrderID 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(qry_bill)
qd.Parameters!thisID = 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/AwUBP57pqoechKqOuFEgEQL/CQCguP8NwgEsOIBRWnxOLj9e8WPSoBwAn1nK
TBmReMtxJJbUcV9TRmevwE2y
=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!

Nov 12 '05 #4
"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
Nov 12 '05 #5
-----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/AwUBP6AyVoechKqOuFEgEQJXRwCfdoP38cL/zyfm3dJizVB0u7/nL8MAnja+
G1dokMi75sHsBGE3QwWEG8tm
=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>

Nov 12 '05 #6
>
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/AwUBP6AyVoechKqOuFEgEQJXRwCfdoP38cL/zyfm3dJizVB0u7/nL8MAnja+
G1dokMi75sHsBGE3QwWEG8tm
=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>

Nov 12 '05 #7
-----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/AwUBP6FYnoechKqOuFEgEQJnDQCcCVt+npKOJUJlGap2r+jaHe YDSN4An05U
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:::mgf
Oakland, CA (USA)

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

iQA/AwUBP6AyVoechKqOuFEgEQJXRwCfdoP38cL/zyfm3dJizVB0u7/nL8MAnja+
G1dokMi75sHsBGE3QwWEG8tm
=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>


Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Michelle Collier-Moore | last post by:
I have a form where the user selects a client name originally from a table and then proceeds to complete other details in a sub form. The users asked to see two pieces of information relating to...
5
by: Colleyville Alan | last post by:
I have built a SQL statement that is trying to loop through the fields of a table that was built from a spreadsheet and hence is "short and fat". So rather than hard-coding, I have a loop from...
4
by: Martin Lacoste | last post by:
(Access 2000) Two issues: Within a query, I need to return a field name as data (see eg. below). I need to search within 80 fields (same criteria) - is there a way to avoid 80 separate...
9
by: Colin McGuire | last post by:
Hi, I have an report in Microsoft Access and it displays everything in the table. One column called "DECISION" in the table has either 1,2, or 3 in it. On my report it displays 1, 2, or 3. I want...
3
by: GL | last post by:
Hi, Is there a way to add a field to an existing table using a query of some sort (without needing to manually add a field to the table). I know how to do it with a make table query, but I have...
12
by: Steve Elliott | last post by:
I have a query set up to gather together data between two specified dates. Shown in the query column as: Between #24/09/2004# And #01/10/2004# Is it possible to enter several different date...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
10
by: Robert | last post by:
How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that...
10
by: john | last post by:
I have a report to print envelopes. The report is based on a query. Now I need to make 10 more queries to make different selections of addresses. Every query has the same output fields as the...
21
KevinADC
by: KevinADC | last post by:
Note: You may skip to the end of the article if all you want is the perl code. Introduction Uploading files from a local computer to a remote web server has many useful purposes, the most...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.