Suggestions for a better form design?

[Stretchcoder]

I've coded an auction db for my kid's school fundraiser, but am stuck on the
best way to enter the auction winners.
I have 2 tables:
Items
Attendees

and at the end of the night, I need to enter final sale price and attendee
into the item table - then I can print invoices (with a report based on a
query that joins items to attendees). Currently, I set up a form with an
entry place for the sale price and attendee, but it isn't all that useable
because you have to either go through the items in item number order, or
click find on each item.

What I'd like to do is have a form with 3 text fields - item num, sold
price, and attendee. When these are filled out the sold price and attendee
get recorded on the row with item num.

This should be super easy to do but it's boggeling me - any suggestions? on
what the item num field should look like/do?

Thanks for the help - you're supporting education in America if you respond

 

[Clifford Bass]

Hi,

I would probably have the items sorted by name instead of number and
use a combo box for the buyer that lists the buyers in last name, first name
order. It would use a query with two columns:

select AttendeeID, LastName & ", " & FirstName & " (" & AttendeeID & ")" as
FullNameID
from Attendees
order by LastName, FirstName, AttendeeID

The AttendeeID column would be the bound column, but hidden (width of
zero). Then to locate John Smith, you start typing "smith" into the combo
box.

Additionally, I probably would use the form in either datasheet or
continuous forms mode. Quick to find the item, quick to enter sale price and
purchaser.

Hope that helps,

Clifford Bass

 

[Stretchcoder]

The datasheet suggestion is a good one, it will probably help speed up the
data entry. The physical process for this silent auction is that each item
has a bid sheet, on which is printed the item number, name, description, etc.
There are lines for people to write their assigned bid numbers down, so at
the end of the day, we have a stack of about 250 bid sheets where the final
bid number is the winner. I'm not really concerned with bidder name - it will
automatically be pulled onto the report at a later step. My main concern is
entering all of the item/winner/sold price combinations as FAST as
possible... Which is why I'm envisioning a sheet to just type in the numbers,
have the numbers pull up (non-editable) info about the item and winner for
verification purposes, and move on!

It's almost like I want 2 separate forms - one that pulls up item info based
on an item typed in, and a second that pulls up attendee info based on an
attendee number typed in, with a button to associate them by copying the
attendee number onto the item table.

I guess this is part access and part UI design

Thanks for the help,
Emily

 

[Clifford Bass]

Hi Emily,

In that case, it is probably easiest to just enter the numbers into a
separate table ([successful] bids) with three columns: item number, bidder
number, price. This may have the advantage of allowing for joint purchases,
should those happen. You could use a query with a couple of left joins to
automatically show the item and purchaser names:

select Bids.*, ItemName, LastName & ", " & FirstName as FullName
from (Bids left join Items on Items.ItemID = Bids.ItemID) left join
Attendees.AttendeeID = Bids.AttendeeID

Make that as the record source of your data sheet. Place the five
fields on the datasheet. Make the ItemName and FullName locked and set their
Tab Stop properties to No. As the IDs are entered the names will pop
up--useful for verification on the entry of the number. Each time the tab
key is pressed it will only go to the number fields.

Hope that helps,

Clifford Bass

 

[Maarkr]

I've coded an auction db for my kid's school fundraiser, but am stuck on the

best way to enter the auction winners.

I would set up your tables as a 1:many relationship, one bidder to many
items, linking BidderIDs in each table, so each attendee could have many
items. Have one query/form showing items with the linked attendee number, so
you could go thru each item at the end and enter the winning bidder number.
The other query/report would list each bidder and the many items purchased,
with a total in the report footer. You could filter the query to only print
out those who won an item.