I am designing a database for someone that collects items. Every so often, he sends some of them off to an auction. He wants to be able to create an invoice for the items. However, when choosing those items he wants to browse the collection of active items and use a checkbox to select the items to appear on the invoice lines.

After various approaches, the current approach is to create a new table (from INVENTORY table) which contains all "active" inventory items and an empty textbox for the invoice number. I'm trying is to have him fill in the invoice header (consigned to, invoice number (auto), and date (now()), then click a command button to display a datasheet from the new table of all currently active items. At this point he can check or uncheck checkboxes to identify which items are selected to be auctioned.

What I would like is to transfer the new invoice number created when fillingin the the invoice header to the invoice number on the datasheet IF selected is checked.

My plans then are to update the status of the selected items in the INVENTORY table to indicate they are at auction (and not in inventory), and copy the item number and invoice number from selected items into the INVOICELINE table. I can then use this information to for other reports and things he wants.

I have a strong knowledge of VB, and Access, but not VBA with Access.

My plan is to use a global variable to capture the invoice number when the invoice header is saved, then write to the invoice number field of the datasheet if the SEL.Seleted (and clear the invoice number field if he changes his mind). I"m going to try to get this to work -but so far no luck. Any suggestions would be helpful.

Where I'm unclear is how to change the INVENTORY table status to "Auction" only for those selected as "Auction" in the browse datasheet, and also to write to the INVOICELINE table only those selected items.

This was the best route I could think of as he could be having items in 3-4 auctions at a time. Any suggestions on how to do the writebacks, or perhaps choose another solution would be greatly appreciated.

I don't do work in the inventory area . . . but if I did, I might approach your situation like this:

Have a tblInventory that contains the items.

Have a tblInvoice that would be created to hold the items . . . then use a linking tblOrderDetails that would create a separate record that would point to a inventory item.

Then, when it comes time to invoice, you could invoice and provide the use some editing ability to delete inventory items not billed on that invoice . . . while at the same time flagging the inventory record with the InvoiceID.

Then you could qry the inventory list based on items not assigned invoices and are ready to be auctioned.

Steve