Access - VBA - FAO Anthony

Hi Anthony

I am helping my husband's sister, develope an Access Database for our Auction House.

Their current one is so locked away and hidden I can't amend it, and they want so many changes we are starting from scratch.

They have a long list of customers (who can be buyers as well as sellers), and a list of items which get's added to weekly.

I've set up the tables and a hole host of reports.

and a few forms, for viewing both customer and item details.

However, they use the main item table and add information straight into that.

for instance, they will hold items for 3 weeks, on the first week, the item is listed, with (or without a reserve), the 2nd week, the list price is reduced by 10%, 3rd week, the reserve is removed and item is then on open auction (unless they decide they would rather not relist for the 3rd week and come and collect). They get charged £1.20 each week as a listing fee.

They currently run off reports each week, but the current system doesnt hold any history, as they overide it rather than create a new line for each item, per week. So if someone comes in after 3 weeks, to see if item sold, it is very time consuming going through huge files and manually adding all the information together.

(*) I'd like to run them a report, that can be run at any point, that will show all items for that one person.

Listing any items that have sold (and when they were originally listed), as well as associated listing fee's, as well as showing any unsold items, how long they have been there and associated listing fee's.

It would also be helpful, if the cusomter has also bought any items, to see these listed (they pay the hammer price (what item sold for under hammer) as well as 15.5%.

(**) They need a report which they can run at the end of every auction (Saturdays), showing exactly whats sold. They then compare this to their till receipt and card payment slips (they tend to get 25% in card payments and 75% cash).

They have the table live, during the auction, and add in for all items for that day (they have a search filter attached to the table to be able to concentrate purely on the items for that day), and add the hammer price as well as the buyer ID. This all automatically also updates the buyer's log (and sellers).

So at the end of the auction, buyers can come up, pay for their goods straight away.

They do use a form for the cusomters, which is searchable by name or id number.

They can see all invoices, and outsanding items.

They can create one new invoice for that day, and can click and add all items that person has bought, onto that invoice, and they print 2 copies off. One copy goes to the customer, the other goes into a tray by the till, which then gets used to reconcile the till. This I would like to replace with idea (**).

Once the barn is closed, they then, print off an invoice (one by one), for all sellers, they get filed into another file, ready for any enquiries during the week. This I would like to replace with the suggestion above (*)

What do you suggest? I have printed off as much information as I can about invoices within Access.

On a Monday, they need to be able to see a list of all unsold items (this is a straight forward report), and see how long its been with them for (I've done that - re my previous forum help post), but they want to be able to go through, and flag some to be returned to seller, and which ones they are going to relist, and automatically relist those items into new rows within the item table.

Each week, once they have manually gone in and done this, they then have to allocate "lot" numbers, so run a report of items ready for that coming Saturday, and put a sticker on the item, and manually add the lot number straight into the items table. This step is always going to have to be manual.

But, I'd like to simplify how they relist items/return items to sellers, then the rest of the auction is made up of items that are brought in on Monday's and Tuesdays, Wednesday takes them approx 13-14 hours to sort all the items for the saturday within the Barn, and manually label and add the numbers into the table (one puts a sticker and the other enters the number as they work round the barn).

I've got all my access/VBA books at home, I've got various forum notes etc printed off ready, and I plan to start my next steps in developing their database this Sunday.

I spent the weekend there, to get an understand of how it actually all works (I'd never been to an auction before).

On Sundays, it takes her 4-6 hours, to reconcile the till, against the pile of duplicate buyers invoices, some items don't get entered into the till in the rush, and sometimes the odd error goes through on card payments and the odd duplicate entry.

The items table is always correct though, as they run the buyers invoices off there and then, with all their purchases, and are entering directly into the table. That leaves them with a list of unsold items, but some people can come in on Mondays and Tuesdays, and may purchase any left over items, and they default to the following Saturday's auction date, which they change back to the Saturday just gone, and this also causes them a host of issues.

Any pointers or tips, or links you can send would be most appreciated. I really want to help them as much as I can, and am pretty sure I can do it.

I know what access/vba is capbile of to a point.

Am using access 2003.

Thank you, sorry for long long email, but wanted to explain everything as clearly as possible.

PS I do have all the information held on their database as of Saturday.

Best wishes
Clair