Building an incremental bid sheet

[Sara Mellen]

This is for our church auction, for which I've developed a pretty usable
little database. One of the things I want to do is to create bid sheets
that contain the description above, then a subreport that has a table with a
line for each of the possible bids that can be made for that item. An
example is called for, I think: Let's say someone has donated a six-pack of
home-brewed beer (yum!). The Minimum Bid is $10, the Maximum Bid is $20,
and the Increment is $2. The possible bids are 10, 12, 14, 16, 18, and 20,
so I would need the subreport to create a 6-line bid sheet.

Here's what the bid sheet would look like this (but there would be a column
to the right of the incremental bid numbers where people would write in
their bidder numbers):

Bid Amount
10 (this is the MinimumBid)
12 (This is the next bid amount--based on the increment of $2)
14
16
18
20 (This is the MaximumBid)

Given that we have over 250 items, it sure would be nice to come up with
either code that would produce this automatically.

One idea was to create a table called BidIncrements, where each ItemID would
have records for each of the possible increments, like this:

ItemID BidAmount
1 10 (where 10 is the MinimumBid)
1 12
1 14 (and 14 is the MaximumBid)
2 50 (MinimumBid)
2 55
2 60
2 65 (MaximumBid)
and so on for all the items.

I just can't figure out how to do it. Just a query won't work (I don't
think) and I don't have the code skills to create it. Thanks a lot for your
help!

Sara

 

[Duane Hookom]

You can create a report with a very tall detail section so the detail
section takes up a full page. Make sure you have three text boxes in the
detail section txtMinBid, txtMaxBid, and txtIncrement.
Then use code in the On Format event of the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intRow As Integer
Dim intRowHeight As Integer
Dim intRows As Integer
Dim lngY As Long
intRows = (Me.txtMaxBid - Me.txtMinBid) / Me.txtIncrement
intRowHeight = 480
Me.FontSize = 24
For intRow = 0 To intRows
lngY = intRowHeight * (intRow + 1)
Me.CurrentY = lngY
Me.CurrentX = 10
Me.Print Format(Me.txtMinBid + (Me.txtIncrement * intRow),
"Currency")
lngY = lngY + intRowHeight
Me.Line (1440, lngY)-(8000, lngY)
Next
End Sub

 

[Max Moor]

Hi Sara,
I did the same thing for my children's school auction last year.
(I'm rewritting it with product intentions in mind now)
We noticed that in the past, our silent auction items had minimum
bids of 40% of the value, and then incremented up to about 150% of that for
the "guaranteed bid." For the bid sheets, I made a single report that had
ten pairs of cells for the price level and bidder number. Each price
"cell" of the bid sheet had a formula like:

=Round([Auction Value]*0.46,1)

It wasn't the most elegant solution. It didn't allow me to have
different levels or "flat dollar" raises for some items, but it worked.
Having done everything by hand in previous years, we were plenty happy to
have automatically generated bid sheets, even with that constraint. BTW,
we did two sheets/page on two-copy OCR paper. We printed them in our home
printer, cut them and gummed them together at the top edge. We did all 300
+ sheet in a night.
Anyway, good luck with your auction. Feel free to drop me an e-mail,
if you want to talk auctions.

Max