Course List - Access 30x

Student Interaction: Microsoft Access 307

Richard on 1/1/2007: Custom search button, DoCmd.FindRecord, GoToControl, InputBox, BeforeUpdate, Requery, DB Password, Splitter

�Moheb on 6/18/2008: Want to email record which will be in report form but a single record. I tried it via macro but it sends all the records.

Richard Rost on 6/26/2008: Moheb, you need to limit the results with a Query, or use the CRITERIA in the DoCmd.OpenReport method.

Nathan�Wittmann on 9/8/2008: I am struggling with VBA a little. I can follow examples, but I am unable to do somethings that seem simple to me. In the past with Excel VBA I would use the macro recorder to give me the code that I need to do specific tasks. I am unable to do that here. For an example I am trying to have the Data settings changed on a form with a button click. I want to have the record change from allow edits "no" to allow edit "yes". I have trie [more...]

Richard Rost on 9/9/2008: Nathan, Access really doesn't have a macro recorder like you're used to with Excel and Word. I show throughout the Access series different ways to change various properties. For example, in Access 202 I showed you how to change the Visible property of a text box. Well, to change the Allow Edits property of a form, all you have to do is say: Me.AllowEdits=FALSE. You can do this at run time in pretty much any event.

Nathan�Wittmann on 10/20/2008: First of all thanks for all your help so far. I am struggling with some basic things that I used to do in Excel VBA. I want to be able to validate information by searching for a duplicate entry. So to give you an example of what I have setup is: I have a projectdetailID, ProjectID, ItemID. I adding components to a project using ProjectDetailsID. What I don't want is the user to add the same ItemID twice. So I need an error when I [more...]

Denise�Nichols on 10/24/2008: I especially like how you show us how to troubleshoot a problem. That is worth a ton.

Richard Rost on 10/25/2008: Nathan, just use a BeforeUpdate event and DLOOKUP to see if that ItemID exists in the table already. If so, just issue a "1" for the Cancel property and optionally pop a warning message up. I cover BeforeUpdate and cancelling an event in Access 305. DLOOKUP was covered back in Access 302.

Richard Rost on 10/25/2008: Thank you, Denise.

Paula on 4/8/2009: Richard, I've gone thru Access 307, Lessons 2 & 3 re. nested combo boxes. Do you have a lesson on how to used 2 or 3 combo boxes in multiple rows . . . (cboBox1) (cboBox2) (cboBox3) BudgetCategory Descript ExpenseType 1. 2. 3. 4. 5. Thanks!

Richard Rost on 4/8/2009: Paula, I'm sorry but I don't understand your question. Can you please elaborate and give me an example?

Paula on 4/9/2009: Sorry, I'll try again. I was unable to paste a small screenshot here as an example, so here's a simple different one. Say I have a subform to record salespeople's trips to various vendors around the country each month. Some of the fields are: Date cboState cboCity cboVendor cboMerchandise 03/05/09 NY NYC A Taylor Co dresses 03/10/09 FL Miami Turen Inc jewelry 03/25/09 CA Fresno ABC Inc blouses The s [more...]

Richard Rost on 4/9/2009: Paula, I understand your problem now. I cover this in Access 310. If you need to refer to a field on a FORM it's: Forms!FormName!FieldName If you need to refer to a field on a SUBFORM, it's: Forms!FormName!SubformName.Form!FieldName It's confusing. I know. If, for example, you need to get the OrderTotal field off of your OrderSubform which is a subform of the OrderF form, you'd say: Forms!OrderF!OrderSubform.Form!OrderT [more...]

Paula on 4/13/2009: Thanks Richard for trying to help. I have not yet been successful in getting 3 nested combo boxes to work on a continuous form. Here's what I've tried: 1) I tried the code you suggested, substituting my form/subform/field names: Forms!FormName!SubformName.Form!FieldName If ComboBox A is based on straightforward data with no criteria (ie States table), and ComboBox B (cities) is based on ComboBox A(states), when I enter data in Row 2 [more...]

Richard Rost on 4/14/2009: Paula, I think I have a solution for you. I posted it as an update at the bottom of this page: http://599cd.com/tips/access/two-combo-boxes

ian�dudley on 5/6/2009: Hi Richard, Re: Dynamic Combo Boxes I have a question relating to dynamic combo boxes (just included in my own CRM database using your lesson guide 307). Although the filtering works correctly and displays in the dependent combo box the data will not save to its table/record and disappears altogether if I close the form and revisit the same record. Any ideas why the data is not being saved? Thanks, ian

Richard Rost on 5/7/2009: Ian, the only way the data wouldn't be saved is if your combo boxes aren't BOUND to the underlying table. Check the Control Source properties. Also, you might need to issue a ComboBoxName.Requery in the OnCurrent event of the form in order for them (especially the dependent one) to have the right set of data in them.

Paula on 5/12/2009: Hello Richard, The nested combo boxes work GREAT! I followed the info at http://599cd.com/tips/access/two-combo-boxes/ It worked fine. But then I went back and edited some of the data in the first combo box, and the selections in the remaining combo boxes would not change. I needed to add the AfterUpdate refresh command. I have 4 nested combo boxes on my form. IT WORKS WONDERFULLY! MANY THANKS!

Lorraine McCord on 5/14/2009: Hi Richard, I have taken many of your courses and currently viewing Access 307, but it is not quite answering my question. I have a form like this: Phone Numbers------------------------ Business 800-555-1212 Business 2 Business Fax 800-555-1213 Home Mobile 832-888-1212 Phone type (Business, Business 2�) needs to be glued to the phone number next to it. PhoneType is a combo box. I have a subform called frm [more...]

Richard Rost on 5/18/2009: Paula, I'm glad it works for you. Yes, you'll need to manually requery the combo boxes if you add records.

Richard Rost on 5/20/2009: Lorraine, I don't quite understand what you're trying to do. Are you saying that the customer record already has the different phone numbers and you want the user to pick a phone number type (business, fax, etc.) from a combo box and have that number displayed? Yes, a DLOOKUP in an AfterUpdate event for the combo box should do that. I'm curious as to why you're setting the database up this way (instead of just showing all of the numb [more...]

�Jay on 6/13/2009: Hi, you refer to a class which will go into greater detail on database security and workgroups. Have you completed this course yet?

Richard Rost on 6/13/2009: Jay, I have not completed it, and I might not even bother. Reason: Microsoft removed user-level security from Access in version 2007. User-level security in Access isn't very secure at all (see my blog post about it). If there are enough people interested, I might cover security in Access 2003, but if you need REAL security, you might want to consider upsizing your ba [more...]

Add Your Questions or Comments