Drew Wutka
DWUTKA at Marlow.com
Tue Jun 10 12:22:01 CDT 2008
Ok folks, here's an example of an unbound form. In the course of this thread, there was a big misconception that an unbound form was meant to replace a bound form. That's just not the case. It would be reinventing the wheel if you make what should be a bound form into an unbound form. Download this zip file: http://www.marlow.com/Inventory.zip Unzip the two .mdbs into a folder. Open the one that has FE at the end. You will be prompted for the location of the .mdb with BE at the end. Once you locate where you saved the backend, the code will relink the tables in the front end. Now, this system was built to be a stand alone inventory system. The business logic of the system is built into several global classes. So let's look at an unbound form. There is a special menu that starts with 'Categories'. Click the Transactions menu button on that menu. This will open the 'Transaction Creation Form'. Everything on this form is unbound. The combos and listboxes are populated with callback functions. The REASON this form is unbound is due to the structure of the database. This system was built so that a company that had multiple 'sites' (construction sites essentially) could track the tools and materials that they moved around. It was quoted and designed for one person (at their central site) to use. To 'track' the inventory, sites and inventory parts have their own tables with a unique key. Then there is tblTransactions, which has it's own TransactionID, timestamp, originating site, destination site, and expected return date. And the quantities transferred are in the tblTransactionItems table, with the TransactionID, the PartID and the Quantity. So let's use the form. Select 'Yard, Texas' in the Originating Site box. Notice that the left listbox just populated with a lot of stuff (that is all of the items in the Texas Yard. Then select 2100 Mckinney Office Bldg. in the Destination Site. Now let's select Forming Materials in the Category box, that will 'filter' the left listbox for just Forming Materials. Double click a part, let's use the C-4 channel @28'. We get prompted for how many items we want to transfer (the default value being the total in the Texas yard). Let's move 10, and click ok. Let's do the same for FM000139 (Plate washer). And again for FM000142 (C-5 channel). So on the right, we have 3 items, 10 each, ready to move to the 2100 site. Ooops, didn't mean that C-5 channel @ 16', double click it in the right listbox, and it disappears. Now we're ready to commit this transaction, click Complete Transaction. We are notified that the transaction is complete, and the left listbox now shows the updated quantities, and a new Transaction record was created, with 2 Transaction Item records. Nothing was done data wise until we clicked the complete Transaction button. Could this be done with a bound form? Probably, but the work involved in doing it would have been a pain, and it wouldn't have the extra bells and whistles: With the transaction creation form still open...hey, it's not the Texas yard, it's the Dallas Texas Yard. Click the Sites menu item in the custom menu. See Yard, Texas at the bottom of the list? Double click it. (That populates the unbound controls on top of the 'Extra Sites' menu. Change the Site Name to 'Yard, Dallas Texas' and click Save Site. Look at that, the Originating Site box on the Transaction Creation Form instantly changed to show the new name. Hey, that C-4 channel @ 28' is really 26'. Let's start a transaction again, move ten (FM000138) to the Destination site (but don't commit the transaction). Now, click the Inventory Items button on the custom menu. Select Forming Materials in the Category, and scroll down to FM000138. Double click that item in the listbox. Change the Part Name to be 26' instead of 28'. Click 'Save Part Information'. Look at that, both list boxes on the Transaction Creation Form immediately show the new part name. If two Transaction Creation Forms were open, creating a transaction on one would show the new inventory levels immediately on the other. Now, could I have built the site and part forms as bound forms? Yes, but I built this application by creating the table structure, building the business logic Classes, and the forms were built on top of the business logic, which was much easy to do unbound then bound. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin at Beach Access Software Sent: Tuesday, June 10, 2008 9:48 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] VBA Unbound data entry / update form I know how to make an unbound form but I haven't got enough nerve to present such heresy - it runs counter to strongly held religious beliefs. OK - briefly - but you didn't hear it from me. 1. Design your form as you would a bound form but no Control Source in the text boxes. 2. Create a recordset in the Open event of the form (DAO or ADO - I prefer DAO) using pretty much the same SQL or query as you would for your Record Source in a bound form. 3. Add a module to put the fields from the current record of the recordset into the text boxes which you can call whenever you want to display the data on your form. 4. Add a module to put the values in the text boxes into the fields of the current record (in DAO use .Edit or .Add and .Update) which you can call whenever you want to write the textbox values back to the table. 5. I always put my own navigation buttons on the unbound for - First, Last, Next, Previous with Click events that move the recordset point appropriately. HTH Rocky Smolin Beach Access Software 858-259-4334 www.e-z-mrp.com www.bchacc.com The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.