Date: Thu, 26 Oct 2006 12:03:11 -0500 (CDT) From: Mark Jacobson To: 810-022-01-FALL@uni.edu Subject: [810-022-01-FALL] Micro Apps Friday Homework... UserForms VBA Assignment Hello 022 students, Be sure that you have defined the Database to be whatever area of the spreadsheet you want to have it located on. For those of you who are using a saved Friday the 13th lab .xls Workbook, here is what you have to do: Insert menu, Name... submenu, Define subcommand gives you the Define Names dialog box Select the Database name or theDatabase name -------- ----------- Click DELETE button. Click OK button to close the dialog box. Select the Rows and the Columns, probably at least A1:E2 if you have one start row of data, along with the column headings you manually entered, but A1:E1 would work too, if you want to start with an empty database, with only the column headings. Now, do the same sequence: Insert menu, Name... submenu, Define subcommand Type in the choice of name, probably Database is your choice. Select Add Click the OK button. Check to see if choosing that name selects all the cells you wanted it too. ---------------------------------------------------------------------------- The macro code: You have to adapt the Friday the 13th lab code to handle the additional two fields of the database. Where does the Input and Output come from and go to? The INPUT for the 1st 3 columns is identical to what is on your handout, it comes from txt1, txt2, and txt3 TextBox controls. They were named this instead of txtFirstName, txtLastName and txtIDnumber for what reason. The VBA code shows a concatenation of "txt" with the 1 or 2 or 3, to get the INPUT item for column 1 or 2 or 3, or rather to get the INPUT to be placed in .Column + 0 or .Column + 1 or .Column + 2 Where do the 4th and 5th columns of the new record come from? One comes from the NOW() VBA function call for the date/time stamp field, and one comes from the combobox. Guess what? There are no txt4 and txt5 Textboxes, like the txt1 and -- txt2 and txt3.... txt4 and txt5 are NOT the controls to get these new database fields from!!! So if you use that code with the For i loop you will have to makes sure you do NOT go to Range("Database").Columns.Count because the .Columns.Count will be 5 instead of 3, and you only have 3 txt1 1 txt2 2 txt3 3 TextBoxes. Where will Cells(theNewRow, .Column + 3).Value come from????? ----- Now() that is a good question, isn't it? ----- Where will Cells(theNewRow, .Column + 4).Value come from????? From the ComboBox1 choice that the user made. cboYear is probably what you named your ComboBox1 control. Note that it is NOT a TextBox control. Should setting those values be OUTSIDE the For Next loop? ------- Yes, and yes. I What is the INPUT? The given. Where does it come from and what is it? What is the Database that is the Input to the record Add operation? Range("Database") O What is the OUTPUT? The goal. Where does it go? Is the row where it will go relevant? What is the Database after the Output has occurred? P What is the Processing required to get from I to O? Gotta get the new record into the row after the end of the existing Database, and then after adding it, gotta redefine the Name "Database" to include that new record in its range definition. IPO I P O What is it to get a new record into the database? Add the five fields of the record to the database. What are the fields of the database record? Where do they come from? The first field comes from txt1 The second field comes from txt2 The third field comes from txt3 The 4th field comes from Now(), a VBA function call. The 5th field comes from your ComboBox control. You have to do alot of thinking and writing and drawing on paper, or in English and maybe with a few ---------> and whatever graphics you can muster using Notepad or pico or emacs or vi, or perhaps with keyboard and graphics both using Microsoft Word and the Drawing toolbar. I prefer paper and pencil or pen. Here I had to use pine and the pico editor and ASCII to convey the thinking that has to occur to adapt the Friday the 13th code to the new 5 field records Database problem. I should have written it out on scratch paper and then scanned it in, to honor the Biction metaphor and be a better yet example of how to develop software solutions. Mark